(opens in a new tab)

12 PostgreSQL Extensions You Should Know in 2026

August 19, 2026 by Chat2DB Team

Postgres ships deliberately lean and pushes capability into extensions. That design is why one database can credibly serve as a relational store, a vector index, a geospatial engine, a job scheduler and a time-series database — and why so many teams run features they did not know were one CREATE EXTENSION away.

This is a tour of the twelve extensions worth knowing, what each actually solves, and how to install and verify them.

Finding what is available

Before installing anything, see what your server already has compiled:

-- Everything installable on this server
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;
 
-- What is already active in this database
SELECT extname, extversion FROM pg_extension;

Extensions install per database, not per cluster. Installing pg_stat_statements in app_production does nothing for app_staging. Most also require superuser or a role with CREATE on the target schema.

A note on hosted Postgres: RDS, Cloud SQL and Azure each publish their own allowlist, and some extensions below will not be available. Check pg_available_extensions rather than assuming.

1. pg_stat_statements — query performance

If you install exactly one extension, install this one. It aggregates execution statistics for every normalised query, which is the only reliable way to find what your database actually spends its time on.

It requires a shared library preload, so it needs a restart:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

CREATE EXTENSION pg_stat_statements;

The query that pays for the effort — total time is what matters, not per-call time:

SELECT
  round(total_exec_time::numeric, 1) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

A query taking 5 ms called two million times costs far more than a 4-second report run twice a day. Reset the counters after a deploy to measure a clean window:

SELECT pg_stat_statements_reset();

Cache hit ratio per query is the other high-value view:

SELECT
  left(query, 60) AS query,
  calls,
  shared_blks_hit,
  shared_blks_read,
  round(100.0 * shared_blks_hit /
        NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

2. pgvector — embeddings and similarity search

The extension that turned Postgres into a credible vector database, and the reason many teams never added a separate one.

CREATE EXTENSION vector;
 
CREATE TABLE documents (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content   text NOT NULL,
  embedding vector(1536)
);
 
-- HNSW: better recall and query speed, slower to build, more memory
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Query by distance, using the operator matching your index opclass:

SELECT id, content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

<=> is cosine distance, <-> is L2, <#> is negative inner product. The ORDER BY must use the same operator as the index opclass or the index is skipped entirely.

Hybrid search — combining vector similarity with a normal SQL filter — is where Postgres beats a dedicated vector store, because the filter is just a WHERE clause on indexed columns rather than a bolted-on metadata feature.

3. PostGIS — geospatial

The most mature geospatial system in any database, open source or otherwise.

CREATE EXTENSION postgis;
 
CREATE TABLE stores (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text NOT NULL,
  location geography(Point, 4326)
);
 
CREATE INDEX stores_location_idx ON stores USING GIST (location);

"Nearest stores within 5 km", which would otherwise be a painful haversine expression:

SELECT
  name,
  round(ST_Distance(location, ST_MakePoint(-0.1276, 51.5072)::geography)) AS metres
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(-0.1276, 51.5072)::geography, 5000)
ORDER BY location <-> ST_MakePoint(-0.1276, 51.5072)::geography
LIMIT 10;

Use geography for anything measured in metres on the globe, and geometry for planar work in a projected coordinate system. Mixing them up is the usual source of distances that are off by a factor of a hundred thousand.

4. pg_trgm — fuzzy text search

Trigram matching gives you typo-tolerant search and, more importantly, makes LIKE '%foo%' indexable — something no B-tree can do.

CREATE EXTENSION pg_trgm;
 
CREATE INDEX products_name_trgm_idx
  ON products USING GIN (name gin_trgm_ops);
 
-- This now uses an index
SELECT name FROM products WHERE name ILIKE '%keyboard%';
 
-- Similarity ranking
SELECT name, similarity(name, 'mechnical keybord') AS sim
FROM products
WHERE name % 'mechnical keybord'
ORDER BY sim DESC
LIMIT 10;

The % operator uses the pg_trgm.similarity_threshold setting, default 0.3. Lower it for looser matching:

SET pg_trgm.similarity_threshold = 0.2;

For "did you mean" on short strings, pair it with levenshtein from fuzzystrmatch.

5. pg_cron — scheduled jobs inside the database

Cron that lives in the database, so schedules travel with backups and failovers instead of sitting on one machine's crontab.

# postgresql.conf — needs a restart
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'

CREATE EXTENSION pg_cron;
 
-- Refresh a materialized view every 15 minutes
SELECT cron.schedule(
  'refresh-daily-stats',
  '*/15 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats$$
);
 
-- Nightly cleanup at 03:00
SELECT cron.schedule(
  'purge-old-events',
  '0 3 * * *',
  $$DELETE FROM events WHERE created_at < now() - interval '90 days'$$
);

Check on jobs and their history:

SELECT jobid, jobname, schedule, active FROM cron.job;
 
SELECT jobid, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC LIMIT 20;

cron.job_run_details grows forever unless you schedule a job to prune it — a small irony worth handling on day one.

6. postgres_fdw — query other Postgres servers

Foreign data wrappers make a remote table look local.

CREATE EXTENSION postgres_fdw;
 
CREATE SERVER analytics_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'analytics.internal', port '5432', dbname 'analytics');
 
CREATE USER MAPPING FOR current_user
  SERVER analytics_db
  OPTIONS (user 'reader', password 'secret');
 
IMPORT FOREIGN SCHEMA public
  LIMIT TO (events, sessions)
  FROM SERVER analytics_db
  INTO remote;
 
SELECT count(*) FROM remote.events WHERE created_at > current_date - 7;

Modern postgres_fdw pushes down WHERE clauses, joins between two foreign tables on the same server, and aggregates. Verify with EXPLAIN (VERBOSE) — if the Remote SQL line shows a bare SELECT * FROM events, the filter is being applied locally after fetching everything, which is the difference between a fast query and a catastrophic one.

file_fdw does the same for CSV files on the server's filesystem, which is handy for querying logs without loading them.

7. TimescaleDB — time series

Not bundled with Postgres, but the standard answer for time-series workloads. It turns a regular table into a transparently partitioned hypertable.

CREATE EXTENSION timescaledb;
 
CREATE TABLE metrics (
  time       timestamptz NOT NULL,
  device_id  bigint      NOT NULL,
  temperature double precision,
  humidity    double precision
);
 
SELECT create_hypertable('metrics', 'time');
 
-- Automatic columnar compression on older chunks
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);
 
SELECT add_compression_policy('metrics', INTERVAL '7 days');

Continuous aggregates are incrementally maintained materialized views — filling the gap plain Postgres leaves:

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  avg(temperature) AS avg_temp,
  max(temperature) AS max_temp
FROM metrics
GROUP BY bucket, device_id;
 
SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset => INTERVAL '3 days',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

8. citext — case-insensitive text

Stops the lower(email) = lower($1) pattern spreading through your codebase and, crucially, makes the uniqueness constraint case-insensitive too.

CREATE EXTENSION citext;
 
CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email citext UNIQUE NOT NULL
);
 
INSERT INTO users (email) VALUES ('Alice@Example.com');
SELECT * FROM users WHERE email = 'alice@example.com';  -- matches
INSERT INTO users (email) VALUES ('ALICE@EXAMPLE.COM'); -- unique violation

The alternative — a unique index on lower(email) — works equally well and avoids a non-standard type. Choose citext when many queries touch the column, and the expression index when only a couple do.

9. hstore — flat key-value pairs

Predates JSONB and is narrower: keys and values are text, with no nesting. That constraint makes it smaller and faster for genuinely flat data.

CREATE EXTENSION hstore;
 
ALTER TABLE products ADD COLUMN meta hstore;
UPDATE products SET meta = 'colour => black, weight => 1.2kg' WHERE id = 1;
 
CREATE INDEX products_meta_idx ON products USING GIN (meta);
 
SELECT name FROM products WHERE meta -> 'colour' = 'black';
SELECT name FROM products WHERE meta ? 'weight';
SELECT name FROM products WHERE meta @> 'colour => black';

For new work, JSONB is the better default — it handles nesting and types, and gets more development attention. Know hstore because you will meet it in older schemas.

10. uuid-ossp and pgcrypto

UUID generation and cryptographic functions.

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();

Since Postgres 13 you rarely need uuid-ossp — gen_random_uuid() is built in:

CREATE TABLE sessions (
  id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id bigint NOT NULL
);

pgcrypto remains useful for hashing and encryption:

CREATE EXTENSION pgcrypto;
 
-- Password hashing with a generated salt
INSERT INTO users (email, password_hash)
VALUES ('a@b.com', crypt('secret', gen_salt('bf', 12)));
 
SELECT id FROM users
WHERE email = 'a@b.com'
  AND password_hash = crypt('secret', password_hash);
 
-- Symmetric encryption for a column
SELECT pgp_sym_encrypt('sensitive value', current_setting('app.enc_key'));

Storing the encryption key in the same database as the ciphertext defeats the purpose — pass it in from your application or a secrets manager.

11. pgstattuple and pg_repack — bloat

pgstattuple measures bloat, pg_repack removes it without the ACCESS EXCLUSIVE lock that VACUUM FULL takes.

CREATE EXTENSION pgstattuple;
 
SELECT * FROM pgstattuple('orders');
SELECT * FROM pgstatindex('orders_pkey');

free_percent above roughly 20% on a large table is worth acting on. pg_repack is a client utility rather than pure SQL:

pg_repack -d mydb -t orders --no-superuser-check

It rebuilds into a new table and swaps at the end, taking a brief exclusive lock only for the swap. It needs free disk space roughly equal to the table plus its indexes.

12. auto_explain — log slow plans automatically

Captures the plan for slow queries as they happen in production, without you having to reproduce them.

# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on

Any query exceeding three seconds writes its full EXPLAIN ANALYZE output to the log. Note that log_analyze adds per-node timing instrumentation to every query, not just slow ones; on hot systems measure the overhead, or set auto_explain.log_timing = off to keep row counts without the timing cost.

Installing and managing extensions

-- Install into a specific schema, keeping the public schema clean
CREATE SCHEMA extensions;
CREATE EXTENSION pg_trgm SCHEMA extensions;
 
-- Upgrade after a package update
ALTER EXTENSION pg_stat_statements UPDATE TO '1.11';
 
-- Remove
DROP EXTENSION hstore;          -- fails if anything depends on it
DROP EXTENSION hstore CASCADE;  -- takes dependents with it

Two operational points. Extensions requiring shared_preload_libraries need a full restart, not a reload — plan for it. And extensions are part of your schema: pg_dump records CREATE EXTENSION statements but not the extension binaries, so restoring onto a server without them installed fails. Keep the list in your provisioning code.

Auditing what is installed across several databases is tedious by hand. Chat2DB (opens in a new tab) connects to Postgres alongside twenty-plus other databases and can generate catalog queries like the ones above from a plain-language prompt, which is quicker than remembering the pg_available_extensions column names.

Summary

Install pg_stat_statements on every server — you cannot tune what you cannot measure. Add pg_trgm the first time someone asks for fuzzy search, pgvector when embeddings appear, PostGIS for anything with coordinates, and pg_cron to stop schedules living on a single machine's crontab.

Check pg_available_extensions before planning around one, remember that installation is per-database, and keep your extension list in provisioning code so restores do not surprise you.