Cheatsheet for Postgres extensions. Add only what you need.

Install

CREATE EXTENSION IF NOT EXISTS extension_name;

In Alembic:

op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")

Core extensions

uuid-ossp

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();

Largely superseded by PG 13+ gen_random_uuid().

pg_trgm (trigram fuzzy)

CREATE EXTENSION pg_trgm;

__table_args__ = (
    Index("ix_users_name_trgm", "name",
          postgresql_using="gin",
          postgresql_ops={"name": "gin_trgm_ops"}),
)

stmt = select(User).where(User.name.op("%")(q))       # similarity
stmt = select(User).where(User.name.ilike(f"%{q}%"))  # uses trigram

citext

CREATE EXTENSION citext;

from sqlalchemy.dialects.postgresql import CITEXT
email: Mapped[str] = mapped_column(CITEXT, unique=True)

Case-insensitive text type.

hstore (legacy)

CREATE EXTENSION hstore;

Flat key-value type. JSONB superseded.

pgcrypto

CREATE EXTENSION pgcrypto;
SELECT gen_random_uuid();
SELECT crypt('password', gen_salt('bf'));

Random UUIDs + crypto functions.

Data extensions

pg_partman

CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.events', 'occurred_at', 'range', 'monthly');

Automated partition management.

timescaledb (time-series)

CREATE EXTENSION timescaledb;
SELECT create_hypertable('metrics', 'ts', chunk_time_interval => interval '1 day');

Time-series superpowers. Continuous aggregates, compression.

pgvector

CREATE EXTENSION vector;

Embedding storage and similarity. See Cheatsheet 12 .

postgis (geospatial)

CREATE EXTENSION postgis;

from geoalchemy2 import Geometry

class Place(Base):
    location: Mapped = mapped_column(Geometry("POINT", srid=4326))

# Query within 5km
stmt = select(Place).where(
    func.ST_DWithin(Place.location, func.ST_MakePoint(lon, lat), 5000)
)

Scheduling and ops

pg_cron

CREATE EXTENSION pg_cron;
SELECT cron.schedule('cleanup', '0 2 * * *', $$DELETE FROM sessions WHERE expires_at < now()$$);

Cron inside Postgres. Managed services often support it.

pgaudit (audit logging)

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl'

Logs all writes / DDL to Postgres log. Compliance feature.

pg_stat_statements

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

Query profiling.

pg_buffercache

CREATE EXTENSION pg_buffercache;
SELECT relname, count(*)
FROM pg_buffercache JOIN pg_class ON pg_class.oid = relfilenode
GROUP BY relname ORDER BY count(*) DESC LIMIT 20;

What’s in the shared buffer cache.

pg_repack

Not a SQL extension; a CLI tool:

pg_repack -h db -U user -d myapp -t huge_table

Online table rewrite.

Search and analytics

pg_jsonschema

CREATE EXTENSION pg_jsonschema;

__table_args__ = (
    CheckConstraint(
        "jsonschema_is_valid('{\"type\":\"object\",\"required\":[\"x\"]}'::json, payload)",
    ),
)

JSON schema validation in Postgres.

rum (FTS-aware GIN)

CREATE EXTENSION rum;

Faster ts_rank queries; specialized GIN.

pg_duckdb (vectorized analytics)

CREATE EXTENSION pg_duckdb;
SELECT * FROM duckdb.query('SELECT * FROM read_parquet(...)');

DuckDB as a Postgres extension; columnar / OLAP queries.

Foreign data wrappers

postgres_fdw

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '...', dbname '...', port '5432');
CREATE USER MAPPING FOR app SERVER remote_db OPTIONS (user '...', password '...');
IMPORT FOREIGN SCHEMA public FROM SERVER remote_db INTO local_schema;

Federated queries across databases.

file_fdw, mongo_fdw, etc.

Many FDWs for non-Postgres sources.

Other useful

  • plpgsql (default): PL/pgSQL language.
  • plpython3u: Python in stored procedures.
  • pgstattuple: precise bloat measurement.
  • pg_freespacemap: free space inspection.
  • pg_walinspect: WAL inspection.
  • earthdistance: distance calculations.

Listing installed

SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;

Managed DB caveats

Managed services (RDS, Cloud SQL, Aurora, Neon) support a subset:

  • Always: uuid-ossp, citext, pg_trgm, pgcrypto, pg_stat_statements.
  • Usually: pg_partman, pgvector, postgis, hstore.
  • Sometimes: pg_cron, timescaledb, pgaudit.
  • Rarely / never: plpython3u, custom extensions.

Check provider docs.

Common mistakes

  • Installing extensions in public then needing them in other schemas.
  • Forgetting CREATE EXTENSION IF NOT EXISTS in migrations — fails on re-apply.
  • Using uuid-ossp when gen_random_uuid() (built-in) would do.
  • Loading shared_preload_libraries extensions without restart.

Read this next

If you want my common-extensions Alembic migration, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .