- Book: Database Playbook: Choosing the Right Store for Every System You Build
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
You've seen the shape of this. A team I talked to spent days hunting a "mystery" p99 spike on a checkout endpoint. They had APM, they had dashboards, they had a flame graph. What they didn't have was pg_stat_statements enabled. The query that ate their latency was a LIKE '%foo%' on a multi-million-row products table that nobody had instrumented, called twice per request because someone wrapped a search helper in a loop.
Two CREATE EXTENSION lines would have surfaced it in fifteen minutes.
That's the shape of 2026 Postgres. The base engine is excellent. The extensions are where the production wins and production losses live, and four of them have crossed the line from "nice if you have time" to "you are flying blind without these." Below: the four, the pain each one ends, and the five-line example that gets you started.
1. pg_stat_statements — the one you should already have on
Every Postgres database that has ever served a production request has a slow query problem. You either know what it is, or you're about to find out at 3 a.m. pg_stat_statements is the extension that turns "find out at 3 a.m." into "find out at 9 a.m. on a Tuesday with coffee."
It records normalized query text, call count, total and mean execution time, rows returned, buffer hits, I/O timing. All of it, for every distinct query the server has executed since the last reset. The Postgres docs call it "a means for tracking planning and execution statistics of all SQL statements." Engineers who've debugged a production database call it the first thing they SELECT * from when something is on fire.
Add to postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
Restart, then in the database:
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
The first time you run that query against a real production database, the answer surprises you. Either there's a query taking 400ms that nobody knew about, or there's a query running 80,000 times an hour that nobody knew was hot, or both. As pganalyze puts it, it moves you from guessing to evidence.
Two pitfalls. First, the view shows cumulative stats since the last pg_stat_statements_reset(). Without snapshots, you can't tell whether a query got worse this week. Most teams either snapshot the view into a metrics table on a schedule, or pipe it into pganalyze, Datadog DBM, or Percona Monitoring. Second, pg_stat_statements.max defaults to 5,000. On a busy app with lots of distinct queries, you'll churn the table and lose the long tail. Bump it to 10,000 or 20,000 and check pg_stat_statements_info to confirm you're not dropping entries.
If you take one thing from this post: turn this on today. Most managed providers (RDS, Cloud SQL, Neon, Supabase) ship it pre-installed and only need the CREATE EXTENSION.
2. pgvector — the one AI made non-negotiable
Three years ago, "Postgres for vector search" was a side project. In 2026 it's the default. Every team building anything with embeddings has either reached for pgvector or for a managed equivalent that's pgvector under a different label. Semantic search over docs, RAG over a knowledge base, recommendation reranking, deduplication of user-generated content: same answer.
The reason it's the default isn't speed (specialized vector DBs are still faster on synthetic benchmarks). It's that your embeddings live next to your business data. You can JOIN a similarity search against your users, documents, and permissions tables, in one query, in one transaction, with one set of backups. According to Tiger Data's own benchmarks, pgvector with pgvectorscale matches or beats Pinecone at 75% lower cost in their tested workloads. For most workloads "good enough and operationally trivial" wins.
Install:
CREATE EXTENSION vector;
Minimal use:
CREATE TABLE docs (
id bigserial PRIMARY KEY,
body text,
embedding vector(1536)
);
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
SELECT id, body
FROM docs
ORDER BY embedding <=> '[0.12, -0.04, ...]'::vector
LIMIT 5;
Three things to know before you ship. The <=> operator is cosine distance; <-> is L2; <#> is negative inner product. Pick the one that matches your embedding model. HNSW is the index you almost always want over IVFFlat in 2026 (see the pgvector README for the trade-offs and tuning knobs). And if your corpus pushes past a few million vectors, look at pgvectorscale: it adds StreamingDiskANN and statistical binary quantization on top, and it's where the published 50M-vector benchmarks live.
The pain pgvector solves: you don't run a second database, you don't sync embeddings across systems, you don't write code to keep two stores consistent. Your vector index is a Postgres index. It backs up, replicates, and fails over with everything else.
3. pg_cron — the one that ends your "tiny scheduler" service
Every production Postgres acquires a list of scheduled jobs. Refresh a materialized view every five minutes. Archive events rows older than 90 days. Recompute a leaderboard at midnight. Vacuum a hot table on a tighter cadence than autovacuum.
Teams used to solve this with a Lambda, a Kubernetes CronJob, or a tiny Node service that opened a connection and ran SQL. Three problems with that pattern: it's another thing to deploy, it needs database credentials, and when it silently fails at 2 a.m. nobody notices until the materialized view is two days stale.
pg_cron, originally from the Citus team, runs the cron daemon inside Postgres. Jobs are rows in a table. They run as background workers with a database role you control. They show up in pg_stat_statements like every other query. Every major managed Postgres supports it: RDS, Cloud SQL, Supabase, Neon, Azure.
Add to postgresql.conf:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'app'
Restart, then:
CREATE EXTENSION pg_cron;
SELECT cron.schedule(
'archive-events',
'0 3 * * *',
$$DELETE FROM events WHERE created_at < now() - interval '90 days'$$
);
SELECT * FROM cron.job_run_details
ORDER BY start_time DESC LIMIT 20;
Things to know. Schedules default to UTC, not your local timezone (some managed providers let you set cron.timezone); set your cron expressions accordingly or you'll be debugging a "midnight" job that runs at 8 p.m. Jobs run only on the primary in a replicated setup, which is what you want. And pg_cron will not run the same job twice in parallel. If a 5-minute job takes 7 minutes, the next run queues until the first finishes. That's a feature; design your jobs to be idempotent and you'll never lose sleep over overlap.
The pain pg_cron solves: it deletes a service from your architecture diagram. The "tiny worker that runs SQL on a timer" is a real maintenance burden when you have ten of them. Move them into the database.
4. pg_trgm — the one that ends "we need Elasticsearch for typos"
There's a meeting that happens at every B2B SaaS company around year two. Someone says "users keep complaining that search doesn't find things if they typo." Someone else says "we should add Elasticsearch." The meeting takes an hour. The Elasticsearch project takes six months and nobody removes it after, even though 80% of what it powers is fuzzy name matching that Postgres can do natively.
pg_trgm indexes text by trigrams (three-character sequences) and exposes similarity operators. As the Postgres docs describe, it handles misspellings, partial matches, and "did you mean" suggestions, with GIN or GiST indexes that turn a 14-million-row LIKE '%query%' from a sequential scan into milliseconds. Use cases include user/customer search, autocomplete, deduplication, fraud signals, and product-name fuzzy matching for e-commerce.
Install:
CREATE EXTENSION pg_trgm;
CREATE INDEX customers_name_trgm
ON customers USING gin (name gin_trgm_ops);
SELECT id, name, similarity(name, 'jonh smtih') AS score
FROM customers
WHERE name % 'jonh smtih'
ORDER BY score DESC
LIMIT 10;
The % operator returns rows above a similarity threshold (default 0.3, settable per session via pg_trgm.similarity_threshold). The <-> distance operator gives you "closest first" ordering. Combined, they cover most of what teams reach for Elasticsearch to do — without a second datastore, without keeping it in sync, without learning DSL.
One tuning note: 0.3 is too permissive for production. Real systems usually run between 0.4 (forgiving, autocomplete-ish) and 0.7 (strict, person-name dedup). Pick a threshold per use case. And combine pg_trgm with Postgres full-text search (tsvector) when you need linguistic features like stemming — the two extensions complement each other.
The pain pg_trgm solves: it kills the "we need a search service" project for the entire class of apps where the real requirement is typo tolerance over a few text columns. You may still want Elasticsearch eventually. You probably don't want it yet.
What about pgaudit, pg_partman, pgvectorscale?
Each is excellent. None is universal in 2026.
pgaudit is essential if you have compliance requirements (SOC 2 Type II, HIPAA, PCI). If you don't, the audit overhead and log volume aren't worth it. pg_partman matters once your largest tables cross ~100M rows and you need declarative partitioning maintenance — before that, you're carrying complexity for no payoff. pgvectorscale is the right answer at vector-DB scale (tens of millions of vectors), and the wrong answer at 200k vectors where plain pgvector with HNSW is faster to set up and just as quick to query.
The four above (pg_stat_statements, pgvector, pg_cron, pg_trgm) are the ones I'd argue belong on basically every Postgres database serving a real workload in 2026. Two for observability and operations, one for the AI features your product manager is about to ask for, one for the search problem that always shows up.
A 30-minute Tuesday
Here's an assignment for the next maintenance window. Connect to your production primary and run:
SELECT extname, extversion FROM pg_extension ORDER BY extname;
Compare to the four above. For each one missing, decide: do you have the pain it solves? If yes, add it to shared_preload_libraries (where required), schedule the restart, and run the CREATE EXTENSION. If no, write a one-line runbook note explaining why you skipped it.
Postgres in 2026 is a database, a vector store, a job scheduler, and a search engine. The base install gives you the database. The other four come from CREATE EXTENSION.
If this was useful
The Database Playbook covers when Postgres is the right store and when it isn't — the trade-offs against MongoDB, DynamoDB, ClickHouse, dedicated vector DBs, and the rest. If you're at the point where "just use Postgres" is starting to feel like a reflex instead of a decision, it's the book that gives you a real framework to choose.
Top comments (0)