One of PostgreSQL’s biggest strengths is its extension system: add full-text fuzzy search, geospatial queries, time-series features, vector search or job scheduling to the same database, without running separate services. Some extensions ship with PostgreSQL itself, and others are a package install away.

Enable an extension

List available and installed extensions:

SELECT name, default_version, installed_version FROM pg_available_extensions ORDER BY name;
\dx                                   -- installed in the current database (psql)

Enable one in the current database (extensions are per database):

CREATE EXTENSION IF NOT EXISTS pg_trgm;

You need superuser rights, or ownership of the database for “trusted” extensions. Upgrade after a package update with ALTER EXTENSION name UPDATE;, and remove with DROP EXTENSION name;.

Install packages (Debian/Ubuntu, PGDG repository)

The contrib extensions (pg_stat_statements, pgcrypto, pg_trgm, hstore, uuid-ossp, and others) come with the server package. Third-party extensions are separate packages named after your PostgreSQL major version:

sudo apt install postgresql-17-postgis-3 postgresql-17-pgvector postgresql-17-cron

See installing PostgreSQL on a VPS.

The most useful extensions

Extension What it adds Ships with PG?
pg_stat_statements Query statistics: find slow, frequent queries contrib
pgcrypto Hashing, encryption functions contrib
pg_trgm Trigram similarity: fuzzy search, fast LIKE '%…%' contrib
uuid-ossp UUID generators (gen_random_uuid() is built in since PG 13) contrib
citext Case-insensitive text type (emails, usernames) contrib
hstore Key/value column type (mostly replaced by jsonb) contrib
postgres_fdw Query tables in other PostgreSQL servers contrib
PostGIS Geospatial types, indexes and functions Package
TimescaleDB Time-series hypertables, compression Package / repo
pgvector Vector similarity search for AI embeddings Package
pg_cron Cron-style jobs inside the database Package
pg_partman Automated partition management Package

pg_stat_statements: install this first

It needs preloading, so it’s a two-step setup:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
sudo systemctl restart postgresql
CREATE EXTENSION pg_stat_statements;
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms, left(query, 80)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

It shows which queries cost the most time in total, which is the best starting point for tuning. See PostgreSQL performance tuning and PostgreSQL monitoring.

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

SELECT name FROM products WHERE name ILIKE '%ubuntu%';            -- uses the index
SELECT name, similarity(name, 'ubunto') FROM products
WHERE name % 'ubunto' ORDER BY similarity(name, 'ubunto') DESC;    -- typo-tolerant

For language-aware search (stemming, ranking), combine it with full-text search. See PostgreSQL full-text search.

pgcrypto

CREATE EXTENSION pgcrypto;
SELECT crypt('user-password', gen_salt('bf'));                 -- bcrypt hash
SELECT digest('hello', 'sha256');

Usually, hash passwords in the application. pgcrypto is handy for migrations and database-side tasks.

pg_cron

shared_preload_libraries = 'pg_stat_statements,pg_cron'
cron.database_name = 'app'
CREATE EXTENSION pg_cron;
SELECT cron.schedule('nightly-cleanup', '0 3 * * *', $$DELETE FROM sessions WHERE expires_at < now()$$);
SELECT * FROM cron.job;

PostGIS, TimescaleDB and pgvector

  • PostGIS: store points and shapes, find “stores within 5 km”, spatial joins, and serve map tiles.
  • TimescaleDB: hypertables partitioned by time, automatic compression and retention. See installing TimescaleDB.
  • pgvector: store embeddings and run nearest-neighbour search for AI apps. See pgvector.

Things to watch

  • Upgrades: extensions must be installed for the new major version before pg_upgrade. See upgrading PostgreSQL major versions.
  • Backups: pg_dump records CREATE EXTENSION; the target server needs the extension packages installed. See pg_dump and pg_restore.
  • Managed databases allow only an approved list of extensions. On your own VPS, you can install anything.
  • Preloaded extensions use memory in every connection. Only preload what you use.

Quick reference

Task Command
List available SELECT * FROM pg_available_extensions;
List installed \dx
Enable CREATE EXTENSION name;
Update ALTER EXTENSION name UPDATE;
Preload shared_preload_libraries + restart

Related: PostgreSQL JSONB and psql commands.

  • PostgreSQL
  • Database