Installing extensions + gotchas with cloud providers

Before you can use any extension, you have to install it. Local PostgreSQL makes it easy — one command. Cloud providers (RDS, Aurora, Supabase, Neon) are trickier: extensions are subject to a whitelist that the provider maintains. Some that you need might not be available, and finding that out AFTER designing your schema is an expensive surprise.

In this capsule you'll learn how to install extensions, check availability, and the most common gotchas with managed cloud.

---

Install an extension: CREATE EXTENSION

CREATE EXTENSION IF NOT EXISTS pg_trgm;

IF NOT EXISTS is defensive: no error if it's already installed. Important for idempotent migrations.

After CREATE EXTENSION, the extension's functions, types, and operators are available in the current schema.

Schema specific

CREATE EXTENSION IF NOT EXISTS pg_trgm SCHEMA myschema;

By default, it installs into public. Specifying a schema is useful for keeping the namespace clean.

Permissions

CREATE EXTENSION typically requires superuser. This matters on cloud providers where your user is not a superuser.

---

Check what's available

-- What's installed in this database
SELECT extname, extversion FROM pg_extension;

-- What CAN be installed on this server
SELECT name, default_version, comment
FROM pg_available_extensions
ORDER BY name;

pg_extension tells you what's active. pg_available_extensions lists everything you could install (binaries available on disk).

Filter by specific names

SELECT name, default_version, comment
FROM pg_available_extensions
WHERE name IN ('pg_trgm', 'citext', 'uuid-ossp', 'hstore', 'pgcrypto')
ORDER BY name;

Typical output on local PostgreSQL:

   name      | default_version | comment
-------------+-----------------+----------------------------------
 citext      | 1.6             | data type for case-insensitive text
 hstore      | 1.8             | data type for storing key/value pairs
 pg_trgm     | 1.6             | text similarity measurement
 pgcrypto    | 1.3             | cryptographic functions
 uuid-ossp   | 1.1             | generate UUIDs

If an extension doesn't show up in pg_available_extensions, you CANNOT install it on this server. On local PG you can install it via apt install postgresql-XX-extension-name (Linux) or the equivalent.

---

Cloud providers: the reality

Each cloud provider maintains its own whitelist of extensions. This matters because:

1. Your local app works with extension X.
2. You deploy to RDS and CREATE EXTENSION X fails.
3. You have to redesign.

AWS RDS PostgreSQL

Official list of supported extensions: AWS docs by PG version. Roughly 70 supported extensions. Most of the common ones (citext, pg_trgm, pgcrypto, uuid-ossp) are available. Some like pg_partman require superuser and RDS manages it via rds_extension.

-- Check on RDS
SHOW rds.allowed_extensions;
-- Comma-separated list of allowed extensions

AWS Aurora PostgreSQL

More restrictive than RDS. Same concept but a smaller whitelist.

Supabase

The list is visible in its dashboard (Database → Extensions). Generally generous: pg_trgm, citext, uuid-ossp, pgcrypto, pgvector, postgis, etc.

-- List available
SELECT name, comment, installed_version FROM pg_available_extensions
WHERE installed_version IS NOT NULL OR default_version IS NOT NULL
ORDER BY name;

Neon

The list is in the docs. Designed for serverless with a focus on developer experience. Generally good extensions available.

GCP Cloud SQL PostgreSQL

A specific whitelist. Some extensions require flags when creating the instance.

Azure Database for PostgreSQL

A whitelist per service tier.

---

Check before you plan

Recommended workflow:

1. Identify which extensions your app needs. Make the list at the start of the project.
2. Check availability on YOUR cloud provider. Before designing the schema.
3. Plan B if one is missing. Alternative implementation? Switch provider?

Without this, you discover the problem at deploy time and have to redesign late.

Migration with a check

# alembic/versions/XXX_install_extensions.py
def upgrade() -> None:
    # Check availability
    conn = op.get_bind()
    available = conn.execute(text("""
        SELECT array_agg(name) FROM pg_available_extensions
        WHERE name IN ('pg_trgm', 'citext', 'pgcrypto')
    """)).scalar() or []

    required = {'pg_trgm', 'citext', 'pgcrypto'}
    missing = required - set(available)

    if missing:
        raise Exception(
            f"Required extensions not available: {missing}. "
            f"Cloud provider doesn't support these. "
            f"Check provider docs for alternatives."
        )

    # Install
    op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")
    op.execute("CREATE EXTENSION IF NOT EXISTS citext")
    op.execute("CREATE EXTENSION IF NOT EXISTS pgcrypto")


def downgrade() -> None:
    op.execute("DROP EXTENSION IF EXISTS pg_trgm")
    op.execute("DROP EXTENSION IF EXISTS citext")
    op.execute("DROP EXTENSION IF EXISTS pgcrypto")

This pattern fails early and clearly if the extensions aren't available.

---

Extension versions

Some extensions have different versions with different features:

-- View the installed version
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_trgm';
-- pg_trgm | 1.6

-- View the available version
SELECT name, default_version FROM pg_available_extensions WHERE name = 'pg_trgm';
-- pg_trgm | 1.6

-- Upgrade if there's a new version
ALTER EXTENSION pg_trgm UPDATE TO '1.6';

For typical apps, the basic features don't change between versions. But occasionally a new version adds useful operators or functions.

---

Dropping extensions

DROP EXTENSION IF EXISTS pg_trgm;
DROP EXTENSION IF EXISTS pg_trgm CASCADE;  -- Also drops dependent objects

CASCADE removes indexes, functions, and other objects that depend on the extension. Careful in production — dropping an extension breaks queries that use it.

---

Check whether a specific extension is active

In code:

async def is_extension_active(session, name: str) -> bool:
    result = await session.scalar(
        text("SELECT 1 FROM pg_extension WHERE extname = :name"),
        {"name": name}
    )
    return result is not None


# Use in a startup check
async def startup():
    async with SessionLocal() as session:
        if not await is_extension_active(session, "pg_trgm"):
            raise RuntimeError("pg_trgm extension required but not installed")

Defense at startup: if your app DEPENDS on an extension, verify it's active before serving requests.

---

Traps and common mistakes

1. CREATE EXTENSION in every migration without IF NOT EXISTS.

-- ❌ Fails on re-run
CREATE EXTENSION pg_trgm;

-- ✅ Idempotent
CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. Assuming your local PG has the same extensions as the cloud.

Your local has 100+ extensions by default. Cloud has ~50-70. Some are cloud-only (rds_*), some are local-only. Always check.

3. Permissions when installing.

CREATE EXTENSION requires superuser. On RDS, your user is rds_superuser, which has permissions for whitelisted extensions, but is NOT a full superuser. Some extensions that require a full superuser won't work.

4. Dropping without CASCADE, breaking objects.

If you have an index gin (col gin_trgm_ops) and you run DROP EXTENSION pg_trgm, you get an error because the index depends on it. You need CASCADE or to drop the index first.

5. Different versions between dev and prod.

Local might have pg_trgm 1.6, prod pg_trgm 1.4. Some features (new operators) are missing. Check extversion in both.

6. Forgetting to install in every DB.

Extensions are installed per database, not per server. If you have dev_db and test_db on the same server, install in each one. CI typically creates a fresh DB per test run — extensions must be installed in the setup.

7. Extensions that affect performance at load.

Some extensions (like pg_stat_statements) require shared_preload_libraries config. CREATE EXTENSION isn't enough — you have to restart PG with the config. Check each extension's docs.

8. The cloud provider changes its whitelist.

Eventually, cloud providers add or remove extensions. Keep the list up to date in your code vs surprises.

---

Exercise: check extensions in your setup

Step 1: local PG.

psql -d your_db -c "
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('pg_trgm', 'citext', 'uuid-ossp', 'hstore', 'pgcrypto', 'postgis', 'pgvector')
ORDER BY name;
"

Which are available? Which are installed?

Step 2: install the module's extensions.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS hstore;

Step 3: verify they're installed.

SELECT extname, extversion FROM pg_extension ORDER BY extname;

Step 4: simulate a cloud check.

Implement the migration helper:

def check_extensions_available(conn, required: set[str]) -> set[str]:
    """Returns set of MISSING extensions."""
    available = conn.execute(text("""
        SELECT array_agg(name) FROM pg_available_extensions
    """)).scalar() or []
    return required - set(available)


# Usage
missing = check_extensions_available(conn, {'pg_trgm', 'citext', 'pgcrypto', 'somerare_ext'})
if missing:
    print(f"Missing: {missing}")

Step 5: experiment with cloud-like restrictions.

Some extensions that are NOT on RDS by default:

• pg_partman (requires special setup)
• cstore_fdw (Citus-specific)
• Some custom community ones

Check: SELECT * FROM pg_available_extensions WHERE name = 'pg_partman'. On your local it might be there; on RDS it might not.

---

Summary and next step

What you learned:

• CREATE EXTENSION IF NOT EXISTS name — idempotent install.
• pg_extension — what's active. pg_available_extensions — what can be installed.
• Cloud providers have whitelists. Check before designing.
• Versions — the same name can have different features.
• Permissions — superuser for CREATE EXTENSION.
• Per database — install in every DB.
• Check pattern in migrations: fail early if one is missing.

Before moving on, you should be able to:

• Install and uninstall extensions.
• Check availability before using.
• Know which cloud providers support what.
• Implement a defensive check in migrations.

In the next capsule we go to the first specific extension: citext (case-insensitive text). You'll learn the decision matrix vs LOWER(), typical use cases (emails, usernames), and how to declare CITEXT columns in SQLAlchemy.

---

Resources

1. PostgreSQL Docs — CREATE EXTENSION — reference.
2. AWS RDS — Available extensions per version — official reference.
3. Supabase Extensions — list for Supabase.
4. Neon — Available extensions — list for Neon.
5. Crunchy Data — Extensions on managed Postgres — comparative analysis.

---

Capsule 02 of 08 — Module 7 — Advanced PostgreSQL for Backend Guide