Indexing
6 engineering logs on Indexing. Newest first.
- Your JSONB column became the schemaless disaster you migrated away from
Why a metadata column with no schema accumulates forty shapes in two years, what TOAST does to read cost when you fetch one key from a large document, and how to promote the keys that turned out to be load bearing.
- Foreign keys are not overhead, and the thing you measured was a missing index
Why dropping foreign keys for performance usually removes a cheap index lookup and leaves the expensive sequential scan in place, and what actually replaces the guarantee once it is gone.
- Covering indexes: the cheap 10x that most schemas leave on the table
Why a matching index still costs you one random read per row, how INCLUDE columns turn that into an index-only scan, and why Postgres will quietly keep hitting the heap anyway if the visibility map is stale.
- Why your index is not being used, and why the planner is usually right
The index exists and EXPLAIN still says sequential scan. A field guide to sargability, stale statistics, the leftmost prefix rule, and the cost settings that make a planner reject an index it should have chosen.
- The RUM Conjecture: You Cannot Optimize Reads, Updates, and Memory at Once
How the RUM Conjecture explains real-world database trade-offs between read latency, write throughput, and memory overhead across B-Trees, LSM-Trees, and hash indexes.
- Shazam finds songs by voting on time offsets, not by comparing audio
How Shazam's fingerprinting works according to the published Wang 2003 paper: constellation maps, combinatorial peak pairing into 32-bit hashes, and the offset histogram that turns song matching into counting.