The Postgres connection pool that isn’t one.

Every Postgres connection is an operating-system process. max_connections=100 isn’t a queue in front of the database — it’s permission for up to 100 backend processes to compete for your cores, your locks, and your buffer pool at once. When latency climbs, the reflex fix is to raise the cap; now 500 backends thrash where a few dozen could work, and throughput falls as p99 rises. The saturation curve looks like a database problem, but it’s arithmetic: a machine does roughly cores-times-a-small-factor of useful database work at once, and every connection past that point pays context-switch and contention tax without adding throughput. The pool your system needs is a real one — in front of Postgres, in transaction mode — not a bigger cap.

max_connections = 100◂ processes, not a queue
# the classic “fix” that makes it worse
max_connections = 500◂ 500 backends thrash, throughput falls

work_mem defaults to 4 MB — and it is not per query. It’s per sort or hash operation, per node of the plan, and a complex query runs several at once. Multiply by hundreds of connections (Finding 1) and the same setting is either far too small — every sort spills to disk as temp files, quietly — or, after someone raises it globally to “fix reports,” far too large, and a busy hour turns into an out-of-memory kill. The default’s crime is that it invites a global answer to a per-workload question.

work_mem = 4MB◂ per sort/hash, PER PLAN NODE
# worst case ≈ work_mem × nodes × active connections
#           ≈ 4MB × 4 × 300 = ~4.8 GB … or 64MB × 4 × 300 = 75 GB

shared_buffers defaults to 128 MB — deliberately tiny so Postgres starts anywhere, including the container you first tried it in. On a production box with tens of gigabytes of RAM, the database’s own cache is a rounding error, so reads bounce between Postgres and the OS page cache with double-copying in between. The companion setting effective_cache_size (default 4 GB) allocates nothing — it only tells the planner how much caching to assume — but left at default on a big box it makes index scans look more expensive than they are.

shared_buffers       = 128MB◂ container-sized, on a 64 GB box
effective_cache_size = 4GB     # planner hint only — allocates nothing

random_page_cost defaults to 4.0 — a ratio written for spinning disks, where a random read genuinely cost ~4× a sequential one. On NVMe and modern cloud volumes the real ratio is close to 1. Left at 4.0, the planner systematically over-prices index scans, so it reaches for sequential scans and bitmap heaps on queries where a straight index scan is the honest winner. Teams then blame the query, add hints via extensions, or duplicate indexes — treating symptoms of a cost model that still lives in 2005.

random_page_cost = 4.0◂ priced for spinning rust
seq_page_cost    = 1.0   # on NVMe the true ratio is ~1.1 : 1

autovacuum is on by default — good — but its trigger is proportional: autovacuum_vacuum_scale_factor = 0.2 means a table earns a vacuum after ~20% of its rows are dead. On a 100-million-row table that’s 20 million dead tuples before cleanup starts, and autovacuum_analyze_scale_factor = 0.1 means the planner’s statistics can be 10 million rows stale on the same table. Proportional triggers are fine for small tables and quietly catastrophic for big ones: bloat accumulates, indexes swell, and the vacuum that finally runs is a monster that fights your peak traffic for I/O.

autovacuum                      = on
autovacuum_vacuum_scale_factor  = 0.2◂ 100M rows → 20M dead before vacuum
autovacuum_analyze_scale_factor = 0.1

idle_in_transaction_session_timeout defaults to 0 — disabled. One connection that ran BEGIN and then went quiet (an ORM that opened a transaction for a read and never closed it, a developer’s psql over lunch, a crashed worker holding its socket) pins the oldest-visible-transaction horizon. From that moment, vacuum — including every well-tuned autovacuum from Finding 5 — cannot reclaim any row version newer than that horizon, anywhere in the database. The bloat is cluster-wide, the cause is one idle socket, and nothing in the default configuration will ever kill it.

idle_in_transaction_session_timeout = 0◂ one idle BEGIN pins vacuum for ALL tables
statement_timeout                   = 0   # see Finding 7

statement_timeout and lock_timeout both default to 0 — no limit. The famous failure isn’t the runaway query itself; it’s the queue behind it. Postgres lock waits are fair: when a migration’s ALTER TABLE waits for an ACCESS EXCLUSIVE lock behind one long-running read, every subsequent query on that table — including plain SELECTs — queues behind the ALTER. One slow report plus one un-timed migration equals a full table outage, at the exact moment the deploy pipeline says everything is fine.

statement_timeout = 0◂ runaway queries run forever
lock_timeout      = 0◂ DDL queues everything behind it, forever

max_wal_size defaults to 1 GB and checkpoint_timeout to 5 minutes. Under a write burst, 1 GB of WAL arrives fast, so Postgres forces checkpoints early and often — and immediately after each checkpoint, full_page_writes means every touched page is written to WAL in full, amplifying I/O exactly when you’re busiest. The symptom is a latency sawtooth that correlates with nothing in your application and gets blamed on storage. The signal is sitting in pg_stat_checkpointer: requested checkpoints outnumbering timed ones means the fuse is too short.

max_wal_size       = 1GB◂ write bursts force early checkpoints
checkpoint_timeout           = 5min
checkpoint_completion_target = 0.9   # good default since v14 — keep it