Skip to the content
Software Made Clear
About

PILLAR

Databases & Persistence

Index structure, lock behaviour, MVCC, write-ahead logs, compaction. Every one of them is state that changes over time, which is what a diagram is for.

New to this topic? Help me choose a first step →

Browse the articles

Pick a question that interests you, or narrow the list above.

August 31, 2026 COMPARISON B-Tree vs LSM-Tree Take the B-tree when reads must stay predictable and the LSM-tree when writes outrun where you can place them — one bill now, one bill later, your choice. Read it →
August 31, 2026 DEEP DIVE Database replication See why a replica is fed the log rather than the data, what the default setting quietly trades away, and how much you actually lose when a primary dies. Read it →
August 19, 2026 DEEP DIVE Change data capture See why reading the database's own log beats polling for changes, what a replication slot guarantees, and the way that same slot can take the server down. Read it →
August 19, 2026 DEEP DIVE Clustered indexes See why a primary key decides where a row physically lives, why every other index carries a copy of it, and what a wide key costs across a whole schema. Read it →
August 19, 2026 DEEP DIVE Data warehouse design Learn why a warehouse is modelled for the questions it has to answer rather than the transactions it records, and what that choice costs you at load time. Read it →
August 19, 2026 COMPARISON Data warehouse vs database Learn which questions each of them is built to answer, so you stop asking a system tuned for one row at a time to summarise a whole year of them. Read it →
August 19, 2026 DEEP DIVE Key-value stores See why a flat key space pushes structure into your naming, why expiry belongs to the data rather than your code, and the command that blocks a server. Read it →
August 19, 2026 DEEP DIVE MVCC without the jargon Understand why readers never block writers under MVCC, because every transaction is looking at its own snapshot instead of fighting over the same row. Read it →
August 19, 2026 COMPARISON OLTP vs OLAP Tell the two workloads apart by what they are judged on, so you stop trying to make one schema serve a checkout and a year-end report at once. Read it →
August 19, 2026 DEEP DIVE Pages, heaps and tuples See what a database row physically is on disk, what happens when an update no longer fits its page, and why that single fact explains half of table bloat. Read it →
August 19, 2026 DEEP DIVE The star schema See why the fact table stays narrow and the dimensions stay wide, and what happens to every query the day somebody normalises those dimensions away. Read it →
August 19, 2026 DEEP DIVE The write-ahead log See why the log commits before the data file does, so a crash mid-write costs you a replay instead of a corrupted table and a very bad on-call night. Read it →
August 18, 2026 VISUALISER B-tree splits, key by key Insert keys one at a time and watch a B-tree page fill, split and push its middle key up a level, until the shape that indexes rely on makes sense. Read it →
August 18, 2026 DEEP DIVE Connection pooling Find out why two hundred open connections make a database slower than twenty do, and where the real ceiling sits before your pool size makes things worse. Read it →
August 15, 2026 DEEP DIVE Deadlocks, seen Watch two transactions grab the same two locks in opposite order, so the next deadlock in your logs reads as a diagram instead of an unreadable stack trace. Read it →
August 15, 2026 DEEP DIVE Isolation levels, and what each one lets through Stop treating isolation levels as a safety dial and start treating them as a list of anomalies you have agreed to tolerate, so surprises stop being surprises. Read it →
August 2, 2026 DEEP DIVE Why your index is not being used Find the exact row count where Postgres stops trusting your index, fix the statistics instead of forcing a plan, and stop guessing why the query slowed down. Read it →