[Beginner → Intermediate] — Why not just use files, what ACID actually buys you, and how the landscape is shaped.

Source: [reference] — see the domain note.

Why not just files

Every database exists because somebody tried files first.

Store your records as JSON on disk. Then:

Concurrency. Two processes write the same file. One overwrites the other, or you get a half-written file. You need locking, and now you’re building a lock manager.

Crash safety. The power fails mid-write. Half a record is on disk. You need write-ahead logging and recovery. → Durability and Recovery

Finding things. “All orders over £100 from last month” means reading every record. You need indexes. → B-Trees and Indexes

Memory limits. The data exceeds RAM. You need a buffer pool and a page-based format. → Storage and Page Layout

Atomicity across records. Transfer money between two accounts — both writes must happen or neither. You need transactions. → Transactions and ACID

Consistency rules. “Every order must reference a real customer.” You need constraints.

A database is what you get when you take those six problems seriously. It’s not a fancy file format — it’s forty years of accumulated answers to problems that appear the moment more than one thing touches your data.

Which is the argument against writing your own. Not that it’s hard to start; it’s that you will re-derive all six, badly, over years. → build-your-own-database exists to make that concrete, and its own “where to stop” section is the honest version of this argument.

ACID

The transactional guarantees, and they’re more slippery than the acronym suggests.

Atomicity — all of a transaction happens, or none. Implemented by the write-ahead log: undo what a failed transaction did.

Consistency — the database moves from one valid state to another, where “valid” means your constraints hold.

The C is the odd one out. Atomicity, isolation and durability are properties the database provides. Consistency is a property of your application’s rules — the database just enforces the constraints you declared. Several database researchers have observed that C is in the acronym mostly because ACID is pronounceable.

Isolation — concurrent transactions don’t interfere. The one with degrees, and the one people misunderstand. → MVCC

Durability — once committed, it survives a crash. Implemented by fsync on the log.

The honest caveats:

Isolation is almost never full. Most databases default to Read Committed or Snapshot Isolation, not Serializable. You are probably running with anomalies your code doesn’t handle. → Isolation levels

Durability depends on fsync actually working. Disks lie about flushing, filesystems buffer, and virtualised storage adds layers. “Committed” is only as strong as your weakest layer.

ACID is per-node. Across machines it becomes a much harder problem. → Distributed Transactions

OLTP vs OLAP

The split that explains most of the database landscape, and getting it wrong is the commonest architectural mistake in this area.

OLTPOLAP
Workloadmany small reads/writesfew huge scans
Query”get order 12345""revenue by region by month”
Rows touchedone to a fewmillions
Columns touchedall of themthree of forty
Latency targetmillisecondsseconds to minutes
Storagerow-orientedcolumn-oriented
ExamplesPostgres, MySQLClickHouse, BigQuery, Snowflake, DuckDB

The storage layout follows directly from the access pattern. OLTP reads whole rows, so store rows together. OLAP reads three columns of forty, so store columns together — you read only what you need, and identical values compress enormously. A column store can be 10–100× faster on analytical queries and terrible at fetching one row. → Row vs Column Storage

The practical failure: running analytics on your production OLTP database. A single GROUP BY over a year of orders scans millions of rows, evicts the buffer pool, and your transactional latency collapses while it runs.

The standard answer: a read replica for reporting, or an ETL/CDC pipeline into a warehouse. HTAP systems claim to do both; treat the claim sceptically.

The landscape

FamilyModelGood atWeak at
Relationaltables, SQLgeneral purpose, joins, integrityhorizontal scale, flexible schema
DocumentJSON docsaggregates fetched together, flexible schemajoins, cross-document consistency
Key-valueK→Vspeed, caching, sessionsqueries other than “by key”
Wide-columnpartitioned rowswrite throughput, time seriesad-hoc queries, joins
Graphnodes and edgestraversals, relationshipsscans, aggregates
Searchinverted indexfull-text, facetingas a source of truth
Time-seriestimestamped pointsappend-heavy, retention, downsamplingupdates

Full tours of each in nosql-reference.

The default should be a relational database, and the reason isn’t conservatism. You usually don’t know your access patterns yet. A relational schema plus indexes supports queries you haven’t thought of; a document or wide-column model bakes today’s access pattern into the storage layout, and changing it later means a migration.

Postgres in particular has absorbed most of the specialised use cases — JSONB for documents, full-text search, PostGIS for geospatial, pg_trgm for fuzzy matching, TimescaleDB for time series, and arrays. “Just use Postgres” is a defensible default for most systems, and running one database well beats running four badly.

When you genuinely need something else:

  • Redis — you need sub-millisecond reads on hot data, or you need the data structures (sorted sets for leaderboards, streams for queues)
  • Cassandra/ScyllaDB — write throughput beyond what one node can take, with a known partition key
  • Elasticsearch — real full-text relevance ranking, not LIKE '%foo%'
  • A graph database — traversals of unbounded depth are the primary query. Recursive CTEs handle shallow cases fine
  • A column store — analytical scans over hundreds of millions of rows

Polyglot persistence — using several — is legitimate and it costs you: more operational surface, more failure modes, and consistency between stores becomes your problem. → Distributed Systems

The NoSQL story

Worth knowing because the vocabulary is still around.

Late 2000s. Web-scale companies hit limits scaling relational databases horizontally. Joins and ACID across shards are genuinely hard, so a wave of systems dropped them for scale and availability.

What was learned:

The scaling problems were real. So were the solutions — partitioning, eventual consistency, and replication ideas that fed back into everything.

“NoSQL” was never one thing. A document store and a graph database have nothing in common but the absence of SQL.

“Schemaless” mostly means the schema lives in your application, unversioned and unenforced. It hasn’t gone away; it’s moved somewhere nobody can check it.

Most applications never needed it. The scale that motivated Dynamo and Bigtable is rare, and a well-indexed Postgres on modern hardware handles far more than people assume — tens of thousands of transactions per second on one machine.

The systems converged. MongoDB added transactions and joins. Cassandra added SQL-ish CQL. Relational databases added JSON columns and horizontal scaling. “NewSQL” (CockroachDB, Spanner, TiDB, Vitess) is explicitly relational-with-horizontal-scale, which is where the argument landed.

Reading this track

These 12 notes are the internals layer — how a database works underneath. The vault’s four reference files cover the surface in depth and are not repeated here:

ForRead
SQL syntax, joins, window functions, CTEssql-reference (4,200 lines)
Schema design, normalisation, keys, patternsdatabase-design-reference
MySQL specifics, admin, replication setupmysql-reference
MongoDB, Redis, Cassandra, Neo4j, Elasticsearchnosql-reference

02–05 are storage — the relational model, pages, B-trees, LSM trees.
06–07 are query processing — the pipeline, join algorithms, the optimiser.
08–10 are transactions — ACID, MVCC, and crash recovery.
11–12 are scaling and operating.

Prerequisites: you should be able to write SQL. Trees and the memory hierarchy make notes 03–05 much more concrete.


↑ Back to Databases