Extensions: PostgreSQL's Real Superpower

One-line installs that change what Postgres can do

An extension adds new types, functions, operators, or index methods to a Postgres database. CREATE EXTENSION foo; and you have a new capability — no separate service, no new operational story, just more Postgres.

The extensions every team should know

pg_stat_statements — per-query call counts and timings; the first thing you turn on in production.
pg_trgm — trigram fuzzy text matching; great for typo-tolerant LIKE/ILIKE.
pgcrypto — hashing, encryption, secure random bytes.
pgvector — vector similarity search for AI embeddings.
PostGIS — full geospatial database.
pg_partman — partition management automation.
pg_cron — schedule SQL jobs from inside the database.
btree_gist / btree_gin — combine btree-style operators with GiST/GIN for EXCLUDE constraints, etc.

How to discover what's available

SELECT * FROM pg_available_extensions; lists every extension your installation supports. Hosted services (Supabase, Neon, RDS) publish the list of allowed extensions per plan.

Code

Install + use an extension·sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Fuzzy similarity (between 0 and 1)
SELECT name, similarity(name, 'postgresqul')
FROM   products
ORDER  BY similarity(name, 'postgresqul') DESC
LIMIT  5;

-- pg_trgm-backed indexes for ILIKE '%term%'
CREATE INDEX products_name_trgm ON products USING gin (name gin_trgm_ops);

EXPLAIN ANALYZE
SELECT * FROM products WHERE name ILIKE '%postgres%';
-- Now Bitmap Heap Scan, not Seq Scan.

Discover what's installed·sql

-- Currently installed
SELECT * FROM pg_extension;

-- Available to install on this server
SELECT name, default_version, installed_version, comment
FROM   pg_available_extensions
WHERE  installed_version IS NULL
ORDER  BY name;

Extensions: PostgreSQL's Real Superpower

One-line installs that change what Postgres can do

The extensions every team should know

How to discover what's available

Code

External links

Exercise

Progress

Comments 0