engineering
Postgres 16 added pg_stat_io, a system view that exposes I/O activity broken down by backend type and I/O context. It closes a long-standing observability gap. Most teams have not noticed the view exists yet.
engineering
The pg_locks system view shows every lock currently held or waiting in the database. Most teams reach for it during an incident and discover the columns are not obviously interpretable. The structure is worth understanding before the next incident.
engineering
Postgres 12 changed the default behavior of CTEs from optimization-fence to inlineable. This was almost always an improvement, but the cases where the old behavior was actually load-bearing are worth understanding.
engineering
The reflexive answer to 'we need a job queue' is Redis, RabbitMQ, or SQS. The unreflective version of that answer skips a Postgres feature that turns a single table into a job queue with semantics that match what most teams actually need: SKIP LOCKED.
engineering
The intuition that sequential scans are slow and index scans are fast is correct most of the time and wrong in the cases that produce the most surprising query plans. Understanding when the planner picks sequential scan over an existing index is the difference between fighting the planner and giv...
engineering
Most Postgres failure modes are gradual. Bloat accumulates and queries get slower; a forgotten replication slot fills the disk over weeks; a missing index turns a five-millisecond query into a five-second one. Transaction ID wraparound is the rare exception. The database runs at full speed up to the moment it
engineering
A tablespace is a named directory where Postgres can store table and index data, separate from the default data directory. The feature looks like an obvious optimization, but in practice the operational cost catches most teams that reach for it by reflex.
engineering
A replication slot tells the primary to keep WAL files until a downstream consumer has read them. If the consumer goes away and nobody notices, the WAL pile grows until the disk fills. It happens to teams that should know better.
engineering
The shared_buffers configuration parameter is one of the most-discussed and least-examined Postgres knobs. The 25-percent-of-RAM rule of thumb is correct often enough that most teams leave it there forever, but the rule answers the wrong question. The question is not how much memory to allocate; it is whether the
engineering
When the database feels slow but no individual query is slow, the answer is usually visible in pg_stat_activity. Reading connection states and wait events is the diagnostic skill that separates intermediate from senior Postgres operators.
engineering
Most teams optimize the queries they think are slow. The pg_stat_statements extension shows the queries that are actually consuming database time, which is often a different set entirely.
engineering
EXPLAIN ANALYZE tells you how long each operator took. EXPLAIN ANALYZE BUFFERS tells you why. The buffer accounting separates cache hits from disk reads and turns most performance mysteries into questions about cache fit.