Welcome to Module 3. The previous module was about what changes the moment you stop being on one machine: networks fail, clocks disagree, consensus is expensive, exactly-once delivery is mostly a marketing claim that idempotency rescues. With those foundations in place, we can finally talk about the data layer with adult vocabulary.
Module 3 covers the major data-storage families, one model per lesson, and ties them together in lesson 24 under the polyglot-persistence heading. We start where most production systems start, stay, and where most teams should default in 2026: the relational database. By the end of this lesson you should know why Postgres is the boring correct answer for the bulk of transactional workloads, what ACID actually promises, where the relational model is the wrong fit, and why “we outgrew Postgres” is a sentence that should make you suspicious before you accept it.
The relational model, briefly
Edgar F. Codd published “A Relational Model of Data for Large Shared Data Banks” in 1970. The paper argued that data should be stored as relations (sets of tuples with named attributes), that operations should be expressed in a declarative language rather than by walking pointers, and that the physical layout should be hidden from the application. That paper is now 56 years old. The vocabulary has shifted (tables, rows, and columns rather than relations, tuples, and attributes), but the model is the same one in production today.
The pieces you actually use: tables with fixed schemas of typed columns; rows with one value per column; primary keys identifying a row uniquely; foreign keys pointing at a primary key elsewhere with referential integrity enforced by the engine; joins that combine rows on matching conditions, with the query planner deciding the algorithm; indexes (mostly B-trees, sometimes hash, GIN, GiST, BRIN) for fast lookups; and SQL, the declarative query language.
The model has been dominant for over fifty years for one good reason: it cleanly separates the logical question (what data do I want) from the physical question (how is it stored and retrieved). That separation lets the database evolve independently of the application, lets the same data answer queries the schema designer never imagined, and lets the operator add an index next Tuesday to fix a slow query without changing a single line of application code.
ACID, what it actually means
Every relational database advertises ACID. The acronym is so familiar it has become noise, but each letter is a specific promise that costs the engine something to keep.
Atomicity. A transaction is all or nothing. If a transaction contains five writes and the third one fails, the first two are rolled back and the database state looks as if the transaction never started. The mechanism is the write-ahead log (WAL): every change is appended to a durable log before being applied to the data files, and on restart the database replays committed transactions and discards uncommitted ones.
Consistency. Every transaction takes the database from one valid state to another. “Valid” is defined by the constraints you declared: not-null, unique, check, foreign-key, exclusion. The database refuses to commit a transaction that would violate a constraint. This C is not the same C as in CAP, by the way; it is much narrower. CAP-consistency is “every read sees the latest write across replicas.” ACID-consistency is “the integrity rules I declared still hold.”
Isolation. Concurrent transactions do not interfere with each other in a way that produces a result the serial schedule would not produce. The catch is that strict serializability is expensive, and the SQL standard defines four isolation levels with progressively weaker guarantees: read uncommitted, read committed, repeatable read, serializable. Postgres defaults to read committed; most production systems run on read committed or repeatable read. The phenomena you can hit at weaker levels (dirty reads, non-repeatable reads, phantom reads, write skew) have specific names because they have caused specific real-world bugs. Knowing which level your transactions run at, and what anomalies that level permits, is part of writing correct application code. Most engineers do not think about this often enough.
Durability. Once a transaction is committed, the change survives a crash. The mechanism is, again, the WAL: the commit returns only after the log entry is flushed to durable storage. On a single machine that means fsync to disk. In a replicated setup it usually means the WAL has reached at least one synchronous replica.
The four together are why a banking transfer works: the debit and credit are atomic (one or the other, never half), consistent (the integrity constraints hold throughout), isolated (a concurrent transaction cannot see the half-applied state), and durable (once the receipt prints, the money has actually moved). No application-level coordination required. The database does the work.
Why the relational model is “the right default” in 2026
The tooling is mature: migrations, ORMs, query analyzers, backup tools, replication frameworks, cloud-managed offerings, observability integrations. The operational characteristics are well-understood, with runbooks for every common workload shape. The hiring pool is enormous: every backend engineer has written SQL. The documentation is exhaustive (the Postgres manual alone is over three thousand pages). And the cost of being wrong is low: if Postgres does not fit, you can move data out, while exotic stores trap you at the moment your data model has hardened.
The relational model is also a knowledge multiplier. An engineer who learns Postgres learns transferable skills: SQL, indexing, query plans, locks, the WAL. An engineer who learns a proprietary NoSQL store learns that store, and starts over at the next job.
The two open-source dominators
In 2026 two open-source relational databases run most of the production transactional load on the internet: PostgreSQL and MySQL. They are not interchangeable, but the differences matter less than they once did.
Postgres is the more SQL-standard of the two. It supports a richer set of types out of the box: arrays, ranges, enums, full-text search, JSON and JSONB, hstore, geographic types via PostGIS. It has stronger extension points: you can add new data types, new index methods, new query-planner hooks, all without forking the source. Materialized views, common table expressions, window functions, recursive queries are first-class and have been for a decade. The default isolation level is read committed; serializable, when you need it, is implemented well. For new projects starting in 2026, Postgres is the unambiguous default.
MySQL is the more operationally simple of the two, especially in its long-running InnoDB-with-row-replication shape. The replication story is older and battle-tested at very long distances; a lot of legacy global infrastructure is on MySQL because in 2010 nothing else replicated as well across continents. The query planner is less sophisticated than Postgres’s, the type system is narrower, and JSON support, while present, is not at parity with Postgres’s JSONB. MySQL is still huge in legacy codebases (WordPress, Drupal, much of the early PHP-era web) and the MariaDB fork has kept it healthy. Pick MySQL today only if you have a strong operational reason or an existing MySQL footprint you do not want to migrate. Otherwise Postgres.
The cloud-managed reality
Almost no one runs Postgres themselves anymore. They run a cloud-managed offering and treat the underlying database as if it were a vendor product. Amazon RDS offers vanilla Postgres on AWS-managed infrastructure with backups, patching, failover, and read replicas. Aurora Postgres is AWS’s reimplementation of the storage layer beneath the Postgres query engine, with faster failover and cheaper-at-scale economics. Google Cloud SQL and Azure Database for PostgreSQL are the equivalents on the other two clouds. Neon is Postgres with a serverless, branching-friendly interface; Supabase is Postgres plus an auto-generated API and realtime layer; Crunchy Bridge and Timescale Cloud round out the specialist hosts.
The cloud offerings restrict what you can do (no arbitrary extensions, no tuning every server parameter), but they save you from the operational burden that used to be a full-time DBA’s job. For most teams the trade is worth it.
When SQL is the right answer, and when it is not
The relational model fits when transactions matter (money, inventory, identity, audit), when the data has natural relations (users have orders, orders have line items, line items reference products), when you will need ad-hoc queries you did not plan for (the reporting team, the operations team, the new product manager), and when reporting needs joins. If most of those apply, you want a relational database, Postgres specifically.
It is the wrong fit when the access pattern is single-key lookup at extreme throughput (use a KV store, lesson 18), when the schema evolves wildly across rows and a fixed schema would be a lie (consider a document store, lesson 19), for time-series with retention rules (TimescaleDB, InfluxDB, ClickHouse), when graph traversal is the dominant query (large graphs want a graph database), or for massive analytical scans (columnar stores like BigQuery, Snowflake, ClickHouse, Redshift are an order of magnitude cheaper at scale).
The honest test: list the top five queries your application will run, by frequency. If they are all single-key gets, you want a KV store. If they are complex joins, you want SQL. If they are mixed, the answer is usually SQL plus a cache.
The “Postgres is enough” thesis
Lesson 8 made the case that most teams over-engineer their first architecture. The Postgres corollary is sharper: most teams under ten million users do not need anything fancier than a well-tuned Postgres, a read replica or two, careful indexing, and a connection pooler.
Conservatively, a modern Postgres instance on a sensibly-sized machine (64 vCPU, 256 GB RAM, NVMe storage) handles low tens of thousands of write transactions per second and hundreds of thousands of read queries per second, with median latency in single-digit milliseconds. Read replicas behind PgBouncer multiply read throughput linearly. Tuned autovacuum keeps bloat manageable. Native partitioning handles tables in the hundreds of millions of rows without performance cliffs.
Stripe, Notion, GitLab, the Heroku-era Rails monoliths, much of the small and mid-size SaaS world ran and continues to run on this shape. The “we need to shard” conversation usually comes much later than the architecture diagrams suggest, and even then, sharding within Postgres (by tenant, by customer, by region) usually beats moving to a different engine.
A worked schema
Here is a small e-commerce schema as you would actually design it. Four tables: users, products, orders, line items. Foreign keys enforce referential integrity. Primary keys are surrogate IDs. The model is unremarkable, which is the point.
erDiagram
USERS ||--o{ ORDERS : places
ORDERS ||--|{ LINE_ITEMS : contains
PRODUCTS ||--o{ LINE_ITEMS : "appears in"
USERS {
bigint id PK
text email
text name
timestamp created_at
}
PRODUCTS {
bigint id PK
text sku
text name
numeric price
int stock
}
ORDERS {
bigint id PK
bigint user_id FK
text status
numeric total
timestamp placed_at
}
LINE_ITEMS {
bigint id PK
bigint order_id FK
bigint product_id FK
int quantity
numeric unit_price
}A query that asks “what did user 42 buy in the last 30 days, with product names and totals” is one join across four tables, expressed in maybe ten lines of SQL, executed in milliseconds with the right indexes. Try writing that query in a key-value store and see what falls out of your hands.
Where this lesson lands
The relational model is the boring correct default for transactional workloads. Postgres is the boring correct default within the relational model. Most teams will never need anything else for the system of record, and the teams that do will know it because of measured pain, not architectural fashion.
The next two lessons cover the families that genuinely beat the relational model on specific workloads: key-value stores (lesson 18) and document stores (lesson 19). Module 3 takes a tour of all the major families before lesson 24 brings them together as polyglot persistence: use Postgres as the system of record, plus a couple of specialist stores for the workloads where Postgres is not the right tool.
Citations and further reading
- Edgar F. Codd, “A Relational Model of Data for Large Shared Data Banks”, Communications of the ACM, June 1970. The founding document. Worth reading once just to see how much of the modern vocabulary is already there in the original.
- The PostgreSQL documentation,
https://www.postgresql.org/docs/(retrieved 2026-05-01). The most thorough free database manual on the internet. Start with the Concurrency Control and Performance Tips chapters. - Jim Gray and Andreas Reuter, “Transaction Processing: Concepts and Techniques” (Morgan Kaufmann, 1992). Still the reference text for ACID, isolation levels, and the WAL.
- Stripe Engineering, “Online Migrations at Scale”,
https://stripe.com/blog/online-migrations(retrieved 2026-05-01). The companion piece to the Stripe case study from lesson 8: how to evolve a Postgres schema without downtime. - Markus Winand, “Use The Index, Luke”,
https://use-the-index-luke.com/(retrieved 2026-05-01). The best free resource on SQL indexing, applicable across engines.