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.
engineering
Postgres tables are heap-organized: rows live wherever there is free space. For most queries this does not matter. For range scans on a correlated column, physical order matters enormously, and CLUSTER is the tool that fixes it.
engineering
Indexes bloat. They corrupt rarely. They become suboptimal when their definition changes. The classic REINDEX takes an ACCESS EXCLUSIVE lock that production cannot tolerate. REINDEX CONCURRENTLY is the answer most teams do not know about.
engineering
PostgreSQL does not update rows in place. Every UPDATE writes a new row version and leaves the old one behind, and VACUUM is the background process that cleans up. Most teams discover this when autovacuum falls behind and the database starts getting slower without an obvious cause.
engineering
Streaming replication is the default in Postgres and the only kind most teams ever set up. The primary streams the WAL — the binary write-ahead log — to one or more replicas, which apply it block-for-block. The result is a byte-identical replica that lags the primary by network round trip plus apply
engineering
Partitioning is one of those Postgres features that gets reached for whenever a table is large, even though large is not the same as needs-partitioning. The honest framework is workload-shaped: certain access patterns benefit dramatically, others get worse, and the operational cost is permanent.