PostgreSQLconnection poolpgbouncervacuumautovacuumpartitioning

PostgreSQL in Production: Connections, VACUUM, Partitioning, Replication

Most PostgreSQL incidents are one of four things — connection exhaustion, autovacuum falling behind, a table that needed partitioning, or replication lag — and each is visible in the statistics views before it becomes an outage.

▶ Interactive Interview question See how this works internally →

Progress

Connections

Each connection is a process with several MB of memory and its own cache of catalog and plans. max_connections defaults to 100 and raising it to 1,000 does not give you 10× the throughput — it gives you 1,000 processes contending for the same cores. The right number of *active* connections is close to the core count. Everything else should queue in a pool: PgBouncer in transaction mode in front of the database, plus the pool in each application instance sized so that instances × pool ≤ what PgBouncer offers.

Transaction-mode pooling reuses one server connection for many client transactions, which breaks anything that assumes session state persists: SET without LOCAL, prepared statements by name, advisory locks, temp tables, LISTEN. Know which of those your stack uses before you switch.

VACUUM and bloat

MVCC leaves dead row versions behind; autovacuum removes them. Its defaults are conservative — it triggers at 20% dead rows — which is fine for small tables and far too slow for a hot 100-million-row table, where 20% is 20 million dead rows. Lower autovacuum_vacuum_scale_factor per table for large, update-heavy ones. Watch n_dead_tup, last_autovacuum, and the age of the oldest transaction; a session "idle in transaction" for an hour stops VACUUM from reclaiming anything newer than it.

The other reason to care: transaction id wraparound. Ids are 32-bit and VACUUM freezes old rows so they stay visible after the counter wraps. If it cannot run for long enough, the database eventually refuses writes to protect itself. Monitor age(datfrozenxid).

Partitioning

Declarative partitioning splits one logical table into physical child tables by range, list or hash of a key. The planner prunes partitions that the WHERE clause excludes; each partition has its own indexes that stay small; and dropping a partition is instant where deleting its rows would take hours and bloat the table. Time-range partitioning of append-only tables — events, logs, messages, metrics — is the canonical use.

The rules: every query should include the partition key or it touches every partition; unique constraints must include the key; and someone must create future partitions before rows arrive (pg_partman automates it). Partitioning does not add capacity — it is still one machine — but it keeps a huge table operable.

Monthly partitions with instant retention

1CREATE TABLE events (
2  id bigint GENERATED ALWAYS AS IDENTITY,
3  occurred_at timestamptz NOT NULL,
4  payload jsonb NOT NULL,
5  PRIMARY KEY (id, occurred_at)            -- the partition key must be in the PK
6) PARTITION BY RANGE (occurred_at);
7 
8CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
9CREATE TABLE events_2026_02 PARTITION OF events FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
10 
11-- retention: no DELETE, no bloat, no VACUUM
12DROP TABLE events_2025_01;

Replication and what to monitor

Streaming replication ships the WAL to replicas that replay it: read scaling and a failover target. Asynchronous by default, so replicas lag; synchronous_standby_names makes commits wait for a replica at the cost of write latency. Logical replication ships row changes instead of WAL bytes, which lets you replicate a subset, across versions, or into another system.

The short list to graph and alert on: connections in use vs max; replication lag in bytes and seconds; oldest transaction age; dead tuples per table; cache hit ratio; the top ten queries by total time from pg_stat_statements; disk space and WAL volume. Every one of the four incident classes shows up here first.

Key points

Active connections ≈ cores; queue the rest in PgBouncer. Transaction pooling breaks session state.
Tune autovacuum per table for large hot tables; alert on idle-in-transaction and dead tuples.
Partition append-only tables by time; every query must carry the key; retention becomes DROP TABLE.
Async replication lags; sync costs write latency. Monitor lag, connections, vacuum, top queries.

From one user to millions

Each rung is the cheapest next step, and each one buys capacity by adding a problem. Read the “what breaks next” line — that is what pushes you to the following rung.