PostgreSQL Production Engineering

An index on (customer_id, created_at) serves queries filtering on customer_id alone, or customer_id + created_at range — but not created_at alone. Column order matters.

CREATE INDEX ON orders(id) WHERE status = 'pending' — index only the rows a query actually needs, keeping the index small and fast to maintain.

Add non-key columns to the index leaf so a query can be answered from the index alone (an index-only scan), without visiting the heap.

An ORM issuing one query per row instead of a single JOIN. The classic application-layer performance bug — invisible in Postgres itself.

The planner's cost estimates come from ANALYZEd statistics. If autoanalyze falls behind after a bulk load, the planner can pick a terrible plan.

cost=0.29..8.31 — startup cost .. total cost, in arbitrary planner units. Compare relative costs across plans, not absolute milliseconds.

A big gap between rows= (estimate) and the actual row count is the #1 sign of stale statistics or a correlated-columns problem the planner can't see.

A background process that runs VACUUM automatically once a table's dead-tuple count crosses a threshold (autovacuum_vacuum_scale_factor, default 20% of table size).

Transaction ids are a finite 32-bit counter. If autovacuum can't freeze old tuples fast enough, Postgres eventually forces a shutdown to prevent wraparound corruption — a rare but catastrophic failure mode.

The default. Fast writes, but a few milliseconds of the most recent commits can be lost if the primary fails before the replica catches up.

synchronous_commit = on with synchronous_standby_names set — the primary waits for at least one replica to acknowledge before confirming commit. Zero data loss, higher write latency.

Split by a range, most commonly time — one partition per month. Old partitions can be dropped instantly instead of running a slow DELETE.

Split by discrete values (e.g. tenant_id) or an even hash — useful for multi-tenant workloads where you want to isolate or shard by tenant.

The standard external pooler. transaction pooling mode reuses a small number of real Postgres connections across many client connections, multiplexed per transaction.

Live view of every connection and what it's currently running — the first place to look during an incident.

Aggregated per-query statistics: total time, calls, mean time. The single most useful extension for finding your worst queries.

pg_stat_replication on the primary shows how far behind each replica is — critical for read-replica correctness and failover readiness.

Too many app instances, no pooler, max_connections reached — new connections start failing outright.

One forgotten BEGIN without COMMIT in a debugging session can hold a lock for hours, blocking every writer behind it.

Autovacuum falling behind on a high-churn table — queries get slower over weeks with no obvious single cause.

A replica disconnects, and a replication slot (or a failing archive command) makes the primary keep WAL it cannot yet discard, so WAL accumulates and the disk fills — this can take the primary down entirely. max_slot_wal_keep_size (PostgreSQL 13+) caps what slots can retain.