SQLAlchemy + Postgres Cheatsheet 20 — Useful Postgres Extensions

Cheatsheet for the Postgres extensions you’ll actually install.

Already shipped (built-in)

• uuid-ossp (deprecated; use gen_random_uuid() from pgcrypto / built-in in 13+).
• pgcrypto — gen_random_uuid(), hashing.
• hstore — flat key-value (mostly superseded by JSONB).
• tablefunc — crosstab, normal random.
• intarray — operators on integer arrays.
• unaccent — strip accents for FTS.
• citext — case-insensitive text.

Install

CREATE EXTENSION IF NOT EXISTS pg_trgm;

In Alembic:

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

pg_stat_statements (always install)

Top queries by execution time. Already covered: Cheatsheet 17 .

pg_trgm (trigram fuzzy search)

CREATE EXTENSION pg_trgm;

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

LIKE %foo% becomes index-friendly.

pgcrypto

CREATE EXTENSION pgcrypto;

gen_random_uuid(), digest('sha256', ...), crypt(...). Mostly built-in in modern PG.

pgvector (embeddings)

CREATE EXTENSION vector;

Vector type + HNSW / IVFFlat indexes. See Cheatsheet 14 .

pg_partman (partition automation)

CREATE EXTENSION pg_partman;

Auto-create new partitions; retention policies. See Cheatsheet 11 .

TimescaleDB (time-series)

Not a standard extension; separate distribution. Time-series-aware hypertables, continuous aggregates, compression.

CREATE EXTENSION timescaledb;
SELECT create_hypertable('metrics', 'ts');

For metrics / events at scale.

PostGIS (geospatial)

CREATE EXTENSION postgis;

from geoalchemy2 import Geometry

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

Spatial indexes, queries, projections.

pg_jsonschema (JSONB schema validation)

CREATE EXTENSION pg_jsonschema;

ALTER TABLE events ADD CONSTRAINT valid CHECK (
    jsonschema_is_valid('{"type":"object","required":["user_id"]}'::json, payload)
);

hypopg (hypothetical indexes)

CREATE EXTENSION hypopg;

SELECT * FROM hypopg_create_index('CREATE INDEX ON users (email)');
EXPLAIN SELECT * FROM users WHERE email = 'x';
-- planner uses hypothetical index without actually building it

Test index impact before creating.

pg_repack (online table rewrite)

Not a SQL extension; a binary. Bundled with Postgres distributions or separate.

pg_repack -h prod -U app -d mydb -t huge_table

Reclaim bloat online.

pgaudit (auditing)

CREATE EXTENSION pgaudit;

Log all DDL / role changes / dangerous ops. Compliance.

pg_cron (in-DB cron)

CREATE EXTENSION pg_cron;

SELECT cron.schedule('nightly-vacuum', '0 2 * * *', 'VACUUM ANALYZE');
SELECT cron.schedule('partman-maintenance', '*/30 * * * *', 'SELECT partman.run_maintenance()');

Run jobs inside the DB. Useful for partman, cleanup, materialized view refresh.

pg_logical / wal2json (CDC)

CREATE EXTENSION pg_logical;        -- pglogical 3rd-party
-- or use built-in logical replication with wal2json plugin for CDC

For Debezium / change data capture pipelines.

roaringbitmap

Compact bitmaps for set operations. Niche but powerful.

pg_squeeze

Alternative to pg_repack for online reclaim. Less popular.

pgvectorscale (Timescale)

Improvements on pgvector — faster ANN search, streaming index builds.

RUM (advanced GIN for FTS)

Stores ranking metadata in index for faster ts_rank.

Decision: when to install

Cloud provider support

Self-hosted: anything you want.

Common mistakes

• Installing too many — extension creep increases attack surface.
• Forgetting to put extension creation in Alembic — fresh envs miss them.
• Using uuid-ossp on modern PG when gen_random_uuid() built-in.
• pg_partman without pg_cron scheduling — partitions don’t auto-create.

Read this next

• Done with Postgres cheatsheets. See Pydantic Cheatsheets or Alembic Cheatsheets .
• Postgres-Focused Textbook

If you want my extension-install Alembic migration template, 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 .