PostgreSQL patterns for applications that growPostgreSQL patterns for applications that grow

PostgreSQL will comfortably carry more load than most applications ever generate. When a Postgres-backed system starts failing under growth, the database is usually not the thing that ran out; the connection layer, the index strategy or the deployment process is.

Connections are the first thing to run out

Each PostgreSQL connection is a backend process with its own memory. A few hundred is a lot; a few thousand is a different kind of problem. Application frameworks default to per-instance pools, which multiply by instance count, which multiplies again under autoscaling. The failure is abrupt: everything is fine, then nothing can connect.

The standard answer is a pooler in front of the database, usually PgBouncer in transaction mode for most web workloads. Two consequences follow from transaction mode and both need to be designed for: session-level state such as prepared statements and temporary tables does not survive between transactions, and advisory locks held at session scope will not behave as expected. Serverless and function-based workloads make this more acute rather than less, because instance count varies with traffic.

Index discipline in both directions

Adding indexes is well understood. Removing them is not, and unused indexes are not free: every write maintains every index on the table, and they consume memory that would otherwise cache data you actually read.

-- Indexes that have never been used since the last stats reset.
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

Two further habits pay for themselves: create indexes concurrently in production so the build does not block writes, and prefer a composite index whose leading columns match the query's equality predicates over several single-column indexes that the planner will have to combine.

Migrations that do not take the site down

The most common self-inflicted outage in a Postgres application is a migration that takes an ACCESS EXCLUSIVE lock on a large, busy table. The migration itself may be fast, but it queues behind existing transactions, and everything arriving after it queues behind the lock.

• Set a short lock_timeout on migrations so a blocked migration fails quickly rather than blocking the application.
• Add columns as nullable, backfill in batches, then add the constraint, rather than adding a NOT NULL column with a default to a large table in one statement.
• Add constraints as NOT VALID first and validate separately; validation takes a weaker lock.
• Deploy schema changes and the code that depends on them as separate steps, so each is independently reversible. Expand, migrate, contract.

Vector search without a second database

For applications that need semantic search over their own data, pgvector is usually a better first choice than a dedicated vector database, not because it is faster at scale, but because it keeps the embeddings transactionally consistent with the rows they describe, and removes an entire system from the operational surface.

The practical guidance is to use HNSW indexes for the recall and latency most applications need, to keep the embedding and the source row in the same transaction so they cannot drift, and to filter on ordinary indexed columns before the vector comparison where the query allows it. Move to a dedicated vector store when you have measured a reason to, not in anticipation of one.

Measure the query, not the impression

pg_stat_statements is the highest-value extension you can enable, and enabling it before you need it is the difference between diagnosing a slowdown in an hour and guessing at it for a week. It tells you which statements consume total time across the workload, which is frequently not the query anyone suspected, because a fast query executed ten thousand times per minute outweighs a slow one executed hourly.

Beyond that: EXPLAIN (ANALYZE, BUFFERS) on the specific statement, read the actual versus estimated row counts, and treat a large divergence as a statistics problem before treating it as an index problem.