PostgreSQL Extensions: The Hidden Superpower

A Lean Core, Not a Monolith

Oracle ships as one giant engine and sells capabilities as licensed options. PostgreSQL does the opposite: a small, solid core plus a rich ecosystem of extensions you add with a single command — most of them free. For a migrating Oracle DBA this is the mental unlock, because a surprising number of Oracle features you assumed you’d lose are just an extension away.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT * FROM pg_available_extensions ORDER BY name;   -- what you can install

Oracle feature → PostgreSQL extension

Oracle                         PostgreSQL extension
────────────────────────────   ───────────────────────────
DBMS_CRYPTO / hashing      →    pgcrypto
DBMS_SCHEDULER (jobs)      →    pg_cron
Database links             →    postgres_fdw  (dblink for ad-hoc)
UTL_MATCH / fuzzy search   →    pg_trgm
AWR / query stats          →    pg_stat_statements
Spatial (Oracle Locator)   →    PostGIS
GUID / SYS_GUID()          →    pgcrypto gen_random_uuid()

The ones worth knowing by name

• pg_stat_statements — normalized query stats. Install it first on any new database.
• postgres_fdw — query a remote PostgreSQL (or other source) as if it were local; the DB-link replacement.
• pgcrypto — hashing, encryption, and gen_random_uuid().
• pg_trgm — trigram indexes that make LIKE '%foo%' and fuzzy matching fast.
• citext — case-insensitive text, so you stop lowercasing everything by hand.
• pg_partman — automated partition management; pgvector — AI embeddings in the database; PostGIS — the gold standard for spatial.

The gotcha: some need a preload and a restart

Most extensions install live with CREATE EXTENSION. A few that hook deep into the server — pg_stat_statements, pg_cron, auto_explain — must be listed in shared_preload_libraries and require a restart before CREATE EXTENSION will work:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pg_cron'
# → restart, then:  CREATE EXTENSION pg_stat_statements;

The discipline is the payoff: you add exactly the capabilities your workload needs, when it needs them — rather than paying for, patching and securing a bundle you mostly don’t use.