Notes for the problems that show up after launch: bad plans, awkward migrations, index debt, vacuum pressure, replica lag, and the small decisions that keep PostgreSQL, MySQL, MariaDB, and SQL Server easier to operate.
Find the queries dragging your Postgres down, read their plans, and fix them in order of impact.
Read the guide →Which indexes to add, which to drop, and how to spot redundant index debt before it costs you writes.
Read the guide →How autovacuum works, when it falls behind, and how to keep bloat from eating your storage and your buffer cache.
Read the guide →Sizing pools, PgBouncer modes, and how to stop idle-in-transaction sessions from exhausting max_connections.
Read the guide →Track lag, WAL throughput, and replication slots before they turn into stale reads or a full disk.
Read the guide →
Backups, restores, PITR, archive gaps, and recovery drills for PostgreSQL.
AWS RDS, Azure Database for PostgreSQL, Google Cloud SQL, AlloyDB, and managed PostgreSQL monitoring notes.
Connection storms, pool saturation, PgBouncer behavior, and max_connections pressure.
CPU, memory, disk, temp files, checkpoints, buffers, and workload pressure.
Index design, index debt, reindexing, constraint indexes, and evidence-driven DDL.
Lock waits, deadlocks, blocking chains, idle transactions, and transaction hygiene.
Galera clustering, InnoDB and Aria internals, replication, optimizer behavior, and MariaDB operations notes.
InnoDB internals, replication, locking, query performance, and MySQL operations notes.
HNSW, IVFFlat, recall, embedding search, and production RAG performance notes.
Replica lag, WAL growth, failover readiness, hot standby behavior, and replication slots.
Query plans, EXPLAIN ANALYZE, planner regressions, pagination, joins, and statistics.
Wait statistics, tempdb, Query Store, Always On availability groups, and SQL Server operations notes.
Autovacuum, dead tuples, bloat, wraparound, MVCC, freeze age, and visibility.
A nested loop join is fast or slow depending on the row counts. Here is when it is the right plan, when it is not, and how to read the EXPLAIN output to tell.
Hash joins are the workhorse for large equi-joins. They are also the join type that quietly spills to disk when work_mem is too small, turning a fast query into a slow one.
A sort that fits in `work_mem` is fast. A sort that does not is 10x slower. Here is how to tell which case you are in and what to do about it.
Planner problems usually start with one wrong estimate. Learn how to spot the first lie in the plan, then fix the statistics, predicate, or index that caused it.
CTEs read beautifully and sometimes cost beautifully too. The behavior changed in Postgres 12, and most teams I work with have not updated their mental model.
Window functions are the right tool for ranked-per-group queries. They are also surprisingly fragile in performance. Here is how to write them so they actually scale.
GROUP BY queries either run instantly because Postgres can stream from a sorted index, or they sort 50GB on disk. Here is how to put yourself in the first camp.
DISTINCT ON is a Postgres-specific shortcut for "give me one row per group, sorted some way." It is fast, it is pleasant, and the version in your head is probably wrong.
OFFSET pagination gets slower the deeper you go. Keyset pagination is constant time. Here is how to switch over and the cases where you cannot.
A `SELECT count(*)` on a small table is instant. On a 500M-row table it can take a minute. Here is what to do when the count itself is the slow query.