Skip to the content
Software Made Clear
Search the whole site About

TOPIC 18 ARTICLES

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.

Help me choose a first step
LATEST IN DATABASES & PERSISTENCE Single table inheritance: one table for a class hierarchy

EXPLORE THE TOPIC

Browse the articles

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

COMPARISON B-Tree vs LSM-Tree Compare B-tree and LSM-tree write, read and compaction costs through two visual models, then choose what to measure for your workload. Read it →
DEEP DIVE Connection pooling: reuse, sizing and waiting See how a database connection pool reuses connections. Change a twelve-query example to compare pool size, database capacity and where queries wait. Read it →
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 →
DEEP DIVE Data warehouse design Choose a warehouse's fact grain, shared dimensions and history rules. Step through a failed reload to see why delete and insert must commit together. Read it →
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 →
DEEP DIVE Pages, heaps and tuples See how PostgreSQL stores row versions inside pages, when HOT can avoid index work, and why reusable space is different from a smaller table file. Read it →
DEEP DIVE MVCC without the jargon See how row versions let ordinary reads see a consistent view, why the snapshot changes with isolation level, and when old versions cost space. Read it →
COMPARISON OLTP vs OLAP Compare a checkout and a sales report on the same orders. See when row or column layout helps, and why a separate reporting workload may be useful. Read it →
DEEP DIVE The star schema Choose one fact-row grain, then compare a flat dimension with a snowflake. See what changes in the query and what still needs measuring. Read it →
DEEP DIVE Why PostgreSQL is not using your index Use EXPLAIN to check why PostgreSQL chose a scan: inspect index conditions, row estimates and costs before changing the query or statistics. Read it →
COMPARISON Data warehouse vs database Compare operational databases and data warehouses: transaction versus analysis workloads, integrated history, reporting replicas, and hybrid systems. Read it →
DEEP DIVE Database replication Understand PostgreSQL replication through a booking example: what reaches the standby, what commits wait for and why replay lag is not a data-loss timer. Read it →
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 →
DEEP DIVE Single table inheritance: one table for a class hierarchy See how a discriminator maps several entity types to one table, then decide when sparse columns and schema coupling outweigh simple queries. Read it →
DEEP DIVE Clustered indexes See why InnoDB's primary key decides where a row physically lives, why every secondary index carries a copy of it, and what a wide key costs a schema. Read it →
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 →
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 →
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 →