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.
We replaced a 40 GB B-tree on created_at with a 40 MB BRIN and the time-range queries got faster, not slower. Here is how to pick between B-tree, BRIN, GIN, GiST, and hash by query shape.
An index-only scan is only index-only when the visibility map says the heap page is all-visible. How to read Heap Fetches in EXPLAIN, build covering indexes with INCLUDE, and keep vacuum honest.
ORDER BY tenant_id, created_at DESC LIMIT 50 took 14 seconds as an external merge sort spilling 1.7 GB — and 180 ms with incremental sort. How the presorted-prefix trick works, and when it backfires.
An UPDATE that touches no indexed column can skip index maintenance entirely, if the page has room. How HOT updates work, why fillfactor matters, and how to measure the ratio in pg_stat_user_tables.
The rule 'never use hash indexes' came from a real scar: before PG10 they skipped WAL, so replicas and crash recovery silently lost them. That was fixed eight major versions ago, and the reputation never recovered.
After five custom executions PostgreSQL starts weighing a cached generic plan against what those custom plans cost — and on skewed data the generic plan it settles on can be terrible. How the heuristic works, how to diagnose it, and…
Two API requests forty milliseconds apart booked the same room: both ran the availability SELECT, both saw zero conflicts, both inserted. EXCLUDE USING gist deleted that race window entirely.
Two weeks after a routine OS upgrade, inserts started failing with duplicate-key errors on values that did not exist. The culprit was a glibc collation change that had quietly scrambled every btree on text.
Btree indexes bloat quietly as updates and deletes churn. Here is how to measure bloat with pgstattuple, why it wrecks cache hit rates, and how REINDEX CONCURRENTLY fixes it without taking the table down.
A four-value status column over 300 million rows carried an 11.8 GB index before PostgreSQL 13. One REINDEX later it was 1.9 GB, same queries, same plans — posting lists did that, and the bloat math has not been the…