Database internals in production
From the B+ tree up: why indexes get ignored, what fsync actually costs, how isolation levels lie to you, and the schema decisions that only hurt at scale.
Read in order: later instalments assume the earlier ones. 1 more instalment in the publish queue.
- 01 Why UUID Primary Keys Quietly Destroy Database Performance
How random UUID primary keys break clustered indexes, cause page splits and buffer pool churn, and what to use instead for mechanically sympathetic database design.
- 02 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.
- 03 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.
- 04 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.
- 05 LSM compaction is a background job that will wake you at 3am
Why an LSM engine can saturate a disk with no user traffic, how write stalls turn a background merge into a foreground outage, and why the disk needs headroom you will never see in a capacity graph.
- 06 fsync is the only thing between you and data loss, and it is slower than you think
Why a successful write() means nothing, what fsync actually costs in the cloud, and why synchronous_commit off and fsync off are not the same knob despite being discussed as if they were.
- 07 Decoding isolation levels: I built a toy DB to force dirty reads and phantom reads
Why the ANSI isolation table does not describe your database, what a phantom read actually is at the index level, and why snapshot isolation still lets two correct transactions corrupt each other.
- 08 Deadlocks are a lock ordering bug, and lock ordering is a design decision
Why two transactions doing the same thing in a different order will eventually stop forever, why the database picking a victim is the good outcome, and why sorting your identifiers before you touch them removes an entire class of incident.
- 09 ADD COLUMN is not always free, and the lock queue is what takes you down
Why a metadata-only migration still took the site offline for forty minutes, how a waiting ALTER blocks every query behind it, and why lock_timeout is the setting that turns a migration into a retry.
- 10 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.
- 11 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.
- 12 Soft deletes are a schema decision that breaks every query you write afterwards
Why deleted_at IS NULL leaks into every index, breaks unique constraints in a way that only shows up when a user re-registers, and moves referential integrity out of the database and into whichever service remembers to filter.
- 13 Your ORM issued 400 queries and the p99 looked fine until it didn't
Why N+1 queries are invisible to every database side metric you own, how a getter call becomes a network round trip, and why the fix that looks obvious produces a cartesian product.
- 14 One round trip beats a thousand, and your batch API probably is not batching
Why a 100,000 row insert takes forty minutes instead of twelve seconds, why addBatch does nothing on the wire without a driver flag, and why autocommit turns every row into a durability barrier.
- 15 Pagination at Scale: Why OFFSET and SKIP Will Eventually Break Your API
Why OFFSET/SKIP pagination degrades linearly with depth, how cursor-based pagination keeps latency flat, and when to switch before production bites back.
- 16 The replica lag you do not measure is the one serving checkout
Why byte lag reads zero on a broken replica, how read-your-writes breaks the moment you add a read replica, and why routing all reads to replicas is a correctness decision rather than a scaling one.
- 17 Connection pool sizing is a queueing theory problem, not a tuning knob
Why Little's law gives you the pool size in one line, why raising it past the knee adds latency without adding throughput, and why twenty pods with twenty connections each is a number nobody decided on.