10 Postgres Extensions Worth Enabling (And the Gotchas)

10 Postgres Extensions Worth Enabling (And the Gotchas)

A stock Postgres install is a perfectly good database with most of its diagnostic and indexing power sitting behind one SQL statement you haven't run yet. This is the reference version of that argument: the exact CREATE EXTENSION syntax, a platform availability table for RDS, Cloud SQL and Azure, the version floors that matter, and the specific reasons extensions fail at 2am on a managed instance when they worked fine on your laptop. (There's also a three-minute walkthrough on the MyDBA YouTube channel if you just want the names.)

<div style="position:relative;padding-bottom:56.25%;height:0;overflow:hidden;border-radius:0.75rem;margin:2rem 0;"> <iframe src="https://www.youtube.com/embed/yUY_YQWsUdw" title="The 10 Postgres Extensions Every User Should Know" style="position:absolute;top:0;left:0;width:100%;height:100%;border:0;" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe> </div>

▶ Watch on YouTube: The 10 Postgres Extensions Every User Should Know

The ten worth knowing, so you can jump: pg_stat_statements, pgcrypto, pg_trgm, postgres_fdw, pgvector, btree_gin / btree_gist, uuid-ossp, hstore, pg_cron, auto_explain.

How Postgres Extensions Work, and Why Some Need a Restart

An extension is a control file plus a SQL script, optionally backed by a shared library (.so). CREATE EXTENSION runs the script, which creates functions, types, operator classes and views, and records the whole bundle in pg_extension so DROP EXTENSION can clean it up later.

The split that matters operationally:

Needs shared_preload_libraries and a restart: pg_stat_statements, auto_explain, pg_cron. These hook into the planner, executor or the postmaster's background worker machinery. That code has to be resident before the first backend forks, so the library loads at startup or not at all. Running CREATE EXTENSION pg_stat_statements; on a server that hasn't preloaded the library gives you a view that returns nothing useful — the docs are explicit that preloading is required.

Does not need a restart: pgcrypto, pg_trgm, hstore, btree_gin, btree_gist, uuid-ossp, postgres_fdw, pgvector. Pure SQL, types and operator classes, loaded on demand.

Three clauses worth knowing:

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA extensions;
CREATE EXTENSION postgis CASCADE;  -- pulls in dependencies automatically

Put extensions in a dedicated schema if you care about search_path hygiene and dump/restore tidiness. CASCADE installs whatever the requested extension depends on.

And the thing that catches people every single time: extensions are per-database objects. Installing pg_trgm in app_prod does nothing for app_reporting on the same cluster. shared_preload_libraries is cluster-wide; CREATE EXTENSION is not.

Managed platforms add an allow-list on top, because most extensions install superuser-owned C functions and you don't have superuser. RDS uses rds.allowed_extensions, Cloud SQL uses cloudsql.* database flags, Azure Flexible Server uses the azure.extensions server parameter. If the extension isn't on the list, CREATE EXTENSION fails with a permissions error that tells you almost nothing.

1. pg_stat_statements: Find Your Slowest Queries

Install this before anything else. It normalises every statement into a queryid, strips the literals, and accumulates calls, total_exec_time, mean_exec_time, max_exec_time, rows, shared block hits/reads/dirtied, and WAL bytes on PG13+. It's the difference between "the database feels slow" and "these four queries are 80% of your execution time."

-- after shared_preload_libraries = 'pg_stat_statements' and a restart
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    substring(query, 1, 100)                     AS query,
    calls,
    round(total_exec_time::numeric / 1000, 1)    AS total_sec,
    round(mean_exec_time::numeric, 2)            AS mean_ms,
    rows,
    shared_blks_read
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

On PostgreSQL 12 and earlier, those columns are total_time and mean_time. PG13 split execution from planning and gave you total_exec_time, mean_exec_time and total_plan_time. Copy-paste the wrong one and you get a column-does-not-exist error, which is a fast way to remember which major version you're on.

The I/O version, which is the one to run first when disk is the complaint:

SELECT
    substring(query, 1, 80) AS query,
    calls,
    shared_blks_read,
    shared_blks_dirtied,
    round(total_exec_time::numeric / 1000, 1) AS total_sec
FROM pg_stat_statements
ORDER BY shared_blks_read + shared_blks_dirtied DESC
LIMIT 20;

Config card:

shared_preload_libraries = 'pg_stat_statements'   # restart required
pg_stat_statements.max = 5000                     # default; bump to 10000+ on busy OLTP
pg_stat_statements.track = top                    # 'all' includes nested statements in functions, 'none' disables tracking
pg_stat_statements.track_utility = on

The trap: the view holds a bounded number of distinct statements, pg_stat_statements.max, default 5000. When it fills, the least-executed entries are evicted silently. If your application generates unparameterised SQL, or you have thousands of distinct utility statements, your history quietly rots away and you never see the query that only runs at month end. Sample the view into a rollup table on a schedule, or use something that does it for you. Call pg_stat_statements_reset() deliberately, and write down when you did.

Managed enablement: RDS and Aurora via a DB parameter group plus reboot. Cloud SQL via the cloudsql.enable_pg_stat_statements flag rather than editing shared_preload_libraries yourself. Azure Flexible Server needs pg_stat_statements in both azure.extensions and shared_preload_libraries. There's measurable overhead on very high statement rates, usually low single-digit percent, and it's rarely worth skipping.

2. pgcrypto: Hashing and Encryption Inside SQL

Crypto primitives that keep hashing inside SQL: crypt() with gen_salt(), digest(), hmac(), gen_random_bytes(), PGP functions.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- store
INSERT INTO users (email, pw_hash)
VALUES ('a@example.com', crypt('correct horse battery', gen_salt('bf', 10)));

-- verify
SELECT id FROM users
WHERE email = 'a@example.com'
  AND pw_hash = crypt('correct horse battery', pw_hash);

gen_salt('bf', 10) selects bcrypt with cost factor 10. Raise the cost as hardware improves; the cost is embedded in the stored hash, so old and new rows coexist.

Two things worth knowing. First, if log_statement = 'all' or log_min_duration_statement is low, plaintext passwords land in your server log in cleartext — check that before you ship this. Second, PGP symmetric encryption where the key is passed as a SQL parameter is theatre, because the key travels in the same channel and lands in the same logs. Use it for at-rest column encryption only when you've thought hard about key handling.

You almost never need pgcrypto just for UUIDs any more: gen_random_uuid() moved into core in PostgreSQL 13, which quietly removed the classic reason people installed pgcrypto or uuid-ossp in the first place.

3. pg_trgm: Fast ILIKE Search with a Trigram Index

The fix for ILIKE '%foo%' sequential scans. It decomposes strings into three-character trigrams and indexes those, which is what lets a leading-wildcard pattern use an index at all.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY idx_customers_name_trgm
    ON customers USING gin (name gin_trgm_ops);

-- similarity search, threshold via GUC
SET pg_trgm.similarity_threshold = 0.3;   -- default
SELECT name, similarity(name, 'jonathan') AS sim
FROM customers
WHERE name % 'jonathan'
ORDER BY sim DESC
LIMIT 10;

GIN versus GiST for a pg_trgm index: GIN gives faster lookups but bigger indexes and slower builds. GiST is smaller and supports the <-> distance operator for KNN "closest match" ordering, which GIN does not. If you need ORDER BY col <-> 'search' LIMIT 5, you need gist_trgm_ops.

Caveats worth internalising. A trigram GIN index on a wide text column can be larger than the table it indexes — a GIN trigram index on a long free-text description column can come out at 1.4x the size of the table itself. Check pg_relation_size before and after on a copy before committing to this on a 200GB table. Patterns with no extractable three-character sequence, so anything under three characters, can't use the index and fall back to a scan. And trigram search complements tsvector full-text search rather than replacing it: trigrams are good at typos and substrings, tsvector is good at words, stemming and ranking.

4. postgres_fdw Setup: Querying Remote Databases

Remote tables that behave like local ones. The full postgres_fdw setup, in order:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER analytics_srv
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'analytics.internal', port '5432', dbname 'warehouse',
             use_remote_estimate 'true', fetch_size '10000');

CREATE USER MAPPING FOR app_user
    SERVER analytics_srv
    OPTIONS (user 'fdw_reader', password 'REDACTED');

IMPORT FOREIGN SCHEMA public
    LIMIT TO (fact_orders, dim_customer)
    FROM SERVER analytics_srv INTO remote;

Use IMPORT FOREIGN SCHEMA rather than hand-writing CREATE FOREIGN TABLE. Column type mismatches you type by hand become runtime errors six months later.

postgres_fdw pushes down WHERE clauses, joins between tables on the same remote server, and some aggregates. Verify it rather than assuming:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT customer_id, sum(amount) FROM remote.fact_orders
WHERE order_date >= date '2026-01-01' GROUP BY 1;
-- look for the "Remote SQL:" line

If the Remote SQL shows a bare SELECT * FROM fact_orders, you're dragging the whole table across the network and aggregating locally. use_remote_estimate 'true' makes the local planner run EXPLAIN on the remote for costing, which usually fixes bad plan choices. Foreign table statistics aren't collected automatically, so run ANALYZE remote.fact_orders; periodically or rely on remote estimates.

Operational reality: credentials live in user mappings, so they're in your dump, and visible to superusers or anyone with select rights on pg_user_mappings. Network reachability, VPC peering and security groups cause most "extension doesn't work" tickets here. And a slow remote query holds your local transaction open, with all the vacuum-blocking consequences that implies. dblink is the older, more manual alternative; file_fdw reads CSVs as tables and is genuinely handy for loading.

5. pgvector and the pgvector HNSW Index

Vector similarity search in Postgres. Types vector and halfvec, operators <-> (L2), <=> (cosine), <#> (negative inner product).

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
    id     bigserial PRIMARY KEY,
    body   text,
    embedding vector(1536)
);

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 100;
SELECT id, body FROM docs ORDER BY embedding <=> $1 LIMIT 10;

Correction: HNSW support landed in pgvector 0.5.0, not 0.1.8. Before that your only index type was IVFFlat. Check what version your platform actually ships before designing around it.

IVFFlat tunes with lists at build time and ivfflat.probes at query time, and it must be built after the table holds representative data, because it clusters existing rows. Building it on an empty table produces an index with terrible recall and no error message.

Both index types return approximate results — that's the deal you're making. Also budget maintenance_work_mem for HNSW builds; they're memory-hungry and slow on large tables.

6. btree_gin and btree_gist: Multicolumn Indexes That Mix Types

The glue extensions. They add B-tree-equivalent operator classes to GIN and GiST, so a plain scalar column can share an index with a jsonb, trigram, full-text or range column.

CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE INDEX idx_docs_tenant_payload
    ON documents USING gin (tenant_id, payload jsonb_path_ops);

That single index serves WHERE tenant_id = 42 AND payload @> '{"status":"open"}' without a separate btree on tenant_id.

btree_gist gives you the canonical double-booking guard:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    id       bigserial PRIMARY KEY,
    room_id  int         NOT NULL,
    during   tstzrange   NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Two concurrent transactions cannot insert overlapping reservations for the same room. No advisory locks, no SELECT ... FOR UPDATE dance, no race condition at 09:00 when everyone books the same meeting room.

7. uuid-ossp, and Why You Probably Don't Need It

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";   -- quotes are mandatory, the name has a hyphen
SELECT uuid_generate_v4();

Since PostgreSQL 13, gen_random_uuid() is in core and needs no extension at all. Keep uuid-ossp only if you use v1, v3 or v5.

A wider point on the bloat side: random v4 UUIDs as primary keys scatter inserts across the whole index, which hurts index locality, inflates WAL from full-page writes, and grows indexes faster than a sequential key would. Time-ordered UUIDv7 fixes most of that. There's the pg_uuidv7 extension for older versions, and PostgreSQL 18 adds a core uuidv7() function.

8. hstore, and the jsonb Question

hstore is a flat string-to-string map. jsonb in core beats it for anything nested, typed or externally consumed. Where hstore still earns shelf space is trigger-based auditing:

CREATE EXTENSION IF NOT EXISTS hstore;

-- inside an AFTER UPDATE trigger: just the changed columns
SELECT hstore(NEW) - hstore(OLD);

That one expression gives you a diff of changed columns in a generic audit trigger, with no per-table code. For new key/value storage, use jsonb.

9. pg_cron: Scheduling Inside the Database

Scheduling inside the database means your jobs follow the data through failover, instead of living in a crontab on one app server nobody documented.

shared_preload_libraries = 'pg_stat_statements,pg_cron'   # restart required
cron.database_name = 'postgres'

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule('nightly-reindex', '15 3 * * *',
    $REINDEX INDEX CONCURRENTLY idx_orders_created_at$);

-- run something in a different database
SELECT cron.schedule_in_database('purge-events', '0 4 * * *',
    $DELETE FROM events WHERE created_at < now() - interval '90 days'$,
    'app_prod');

-- the job that cleans up the job log
SELECT cron.schedule('cron-cleanup', '0 5 * * *',
    $DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$);

Job metadata lives in a single database by default, set by cron.database_name. Every run is recorded in cron.job_run_details, which grows without limit until you purge it. Yes, the cleanup job for the scheduler is scheduled by the scheduler — it's fine, just don't forget it, because that table can quietly become the largest object in a small database.

RDS, Cloud SQL and Azure Flexible Server all support pg_cron with the usual flag or parameter enablement.

10. auto_explain: Capturing the Plan After the Fact

The answer to "it was slow at 03:12 and I have no plan."

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = off      # see warning below
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
auto_explain.sample_rate = 0.05

-- per-session, no restart, for a targeted investigation
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '200ms';
SET auto_explain.log_analyze = on;

log_analyze implies per-node timing unless you turn log_timing off, and the documentation warns this can impose significant overhead, badly so on kernels with slow clock reads. Start with log_analyze = off in production, use sample_rate to cap volume, and turn analyze on only during a controlled window. Plans go to the server log, so if you don't ship logs somewhere searchable, you've enabled a feature you can't read.

Honourable Mentions

• pgstattuple — real tuple-level bloat measurement by scanning the relation. Accurate, I/O expensive; pgstattuple_approx trades accuracy for speed on big tables.
• pg_buffercache — shows what's actually occupying shared_buffers, which settles arguments about cache sizing quickly.
• pg_repack — rebuilds tables and indexes to reclaim space without holding an exclusive lock for the duration. Available on RDS and Cloud SQL; check your platform's list.
• pg_partman — time and serial partition management, so you stop hand-creating next month's partition.
• pgaudit — session and object audit logging when a compliance team is involved.
• PostGIS — geospatial types and indexing, and the extension most likely to feature in the war story below.

Postgres Extensions on RDS, Cloud SQL and Azure

| Extension | RDS / Aurora | Cloud SQL | Azure Flexible Server | Restart? | |---|---|---|---|---| | pg_stat_statements | Parameter group: shared_preload_libraries | Flag cloudsql.enable_pg_stat_statements | azure.extensions + shared_preload_libraries | Yes | | auto_explain | Parameter group shared_preload_libraries (or per-session LOAD) | Flag cloudsql.enable_auto_explain / per-session | azure.extensions + shared_preload_libraries | Yes (global) | | pg_cron | Parameter group + rds.allowed_extensions | cloudsql.enable_pg_cron | azure.extensions + shared_preload_libraries | Yes | | pgcrypto | rds.allowed_extensions | Available | azure.extensions | No | | pg_trgm | rds.allowed_extensions | Available | azure.extensions | No | | postgres_fdw | rds.allowed_extensions + network path | Available + network path | azure.extensions + network path | No | | pgvector | rds.allowed_extensions, version varies | Available, version varies | azure.extensions, version varies | No | | btree_gin / btree_gist | rds.allowed_extensions | Available | azure.extensions | No | | uuid-ossp | rds.allowed_extensions | Available | azure.extensions | No | | hstore | rds.allowed_extensions | Available | azure.extensions | No |

Three queries to keep in your snippets file:

SELECT * FROM pg_available_extensions ORDER BY name;      -- what the server could install
SELECT extname, extversion FROM pg_extension;             -- what this database has
SHOW shared_preload_libraries;                            -- what actually loaded at startup

The version-drift check that catches most surprises:

SELECT e.extname, e.extversion AS installed, a.default_version AS available
FROM pg_extension e
JOIN pg_available_extensions a ON a.name = e.extname
WHERE e.extversion <> a.default_version;

The Upgrade Trap Nobody Warns You About

Extension SQL objects aren't updated by a major version upgrade. The catalog still says version 3.2 while the server ships 3.4, and you find out when a function signature changed underneath you. Run ALTER EXTENSION postgis UPDATE; after upgrades, and use the drift query above as a post-upgrade checklist item.

The worse failure is the shared library itself. In July 2026 I worked an incident on a cluster that had been moved to a new host. The move went fine, the database started, most of the application was healthy. But postgis-3.so wasn't present in the new host's library path. The extension still appeared in pg_extension with a version number, looking perfectly installed, so nothing in the catalog suggested a problem. Anything touching a geometry column failed at runtime. Maintenance jobs that touched those tables failed too, and autovacuum work on the affected relations was disrupted while the errors went unnoticed for long enough to start showing up as bloat and rising disk use on the dashboards — which is how it finally got found.

The fix, once found, was straightforward: reinstall the postgis library package on the new host, and the functions resolved immediately. But version drift in the catalog and a missing shared library are two different failure modes, and only one of them shows up in a SQL query — the drift check above catches the first, not the second.

The lesson: pg_extension tells you what the catalog believes, not what the filesystem contains. After any host move, restore, or platform migration, run a smoke test that actually calls a function from each installed extension, and monitor extension version drift as part of your regular checks rather than as an upgrade-day ritual.

What to Install Today, in Order

Day one, every cluster, no debate: pg_stat_statements and auto_explain. Both need a restart, so do them together in one maintenance window and be done.

Then as the workload demands: pg_trgm when search starts hurting, pgcrypto when you're hashing credentials, btree_gin / btree_gist when you hit a multicolumn index or exclusion constraint you can't otherwise express.

Treat pgvector, pg_cron and postgres_fdw as architecture decisions. Each one adds an operational surface: index recall tuning, a scheduler with its own log table, a network dependency inside your transactions. Adopt them on purpose.

If you'd rather not read pg_stat_statements by hand, MyDBA's free health check reads it directly and returns missing, unused and duplicate indexes with the exact DROP INDEX CONCURRENTLY statements to run, plus extension version drift across your instances. Same catalogs, same queries, less squinting at 3am. It won't fix a missing shared library after a host move, though — nothing but a careful runbook does that.