Database Topic Archive
Index design, index debt, reindexing, constraint indexes, and evidence-driven DDL.
ERROR: invalid page header in block 488291 of relation orders_pkey at 03:12 on a Saturday. Before touching REINDEX or reaching for a backup, pageinspect let us read the damaged page itself, prove the heap was clean, and turn a weekend-long…
A CREATE INDEX CONCURRENTLY killed by a lock timeout left behind an INVALID index: invisible to the planner, invisible in \d, and still eating write I/O on every insert. We found eleven of them during a storage audit — 40…
The store-locator endpoint had a spatial index on both engines and was doing a full scan on both — because ST_Distance_Sphere in a WHERE clause uses no index anywhere. Then a bulk load died at row 840,000 with ERROR 3643…
A cleanup query with NOT IN deleted nothing for six months because one NULL made every row 'unknown'. Then a migration flipped where NULLs sort, and a dedupe job started keeping the wrong record. NULL semantics are SQL-standard until the…
Covering indexes can avoid table reads, but row visibility still matters. Compare InnoDB clustered-record checks with PostgreSQL visibility-map and VACUUM behavior.
Deleting one tenant row took forty-five minutes because a foreign key trigger was sequentially scanning a 90-million-row events table — inside the deleting transaction, while the lock queue grew behind it. The detection query, the rollback-safe EXPLAIN trick, and the…
Our orders table carried a 13.6 GB btree on a status column with five distinct values across 400 million rows — two years after upgrading to PostgreSQL 14. One REINDEX CONCURRENTLY brought it back at 2.4 GB. The upgrade grants…
One RLS policy with a memberships subquery turned our indexed tenant lookups into per-row nested loops overnight — p95 went from 9 ms to 1.4 seconds. Here's how we found it and the pattern that fixed it.
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.