Meet Cartwheel

Every example in this book is one system. Cartwheel is an online grocery-delivery service running on a single Postgres box called pg-primary: 16 vCPU, 64 GB of RAM, NVMe storage, one cluster, one database, eight tables. Nadia owns that database. She is a developer for the first half of the book and the person carrying the pager for the second.

This topic lays out the tables, the traffic they carry, and two things about the system the team cannot currently explain. Both are real failures with real customer consequences, and both stay unexplained on purpose: explaining them honestly needs machinery that does not exist yet. A guess made here is how a team ends up buying a bigger server to hide a bug.

The Business and Its Shape

A customer fills a basket and picks a delivery window. Checkout writes a row to orders and several to order_items, decrements the relevant rows in inventory, and appends to delivery_events as the order is picked, dispatched and delivered. Everything after checkout is an append.

Reads split cleanly in two. The application's reads are narrow and constant: "my orders" for one customer, one order and its items, the stock level for one product in one warehouse. The other read workload is a single analytics dashboard that aggregates yesterday (orders per city, revenue per hour, delivery times by courier) and runs on pg-replica-a so it never competes with checkout. Half the decisions in this book are trades between those two profiles, because they want opposite things from the same schema.

The Eight Tables

These are the exact tables and columns used for the rest of the book. Nothing else gets invented later; when a chapter needs pressure, it applies it here.

customers        id bigint, email text, city text, created_at timestamptz
products         id bigint, sku text, name text, price numeric(10,2),
                 attributes jsonb
inventory        product_id bigint, warehouse_id int, on_hand int
orders           id integer, customer_id bigint, placed_at timestamptz,
                 status text, total numeric(10,2)         -- ~40,000,000 rows
order_items      order_id bigint, product_id bigint, qty int,
                 unit_price numeric(10,2)
couriers         id bigint, name text
courier_shifts   courier_id bigint, shift tstzrange
delivery_events  id bigint, order_id bigint, event_type text,
                 occurred_at timestamptz, payload jsonb   -- +4,000,000 rows/day

The difference between three of them decides which advice applies. orders is large and mostly historical: 40 million rows, written once, read constantly, almost never updated except for a status change early in its life. delivery_events is enormous and append-only, growing by 4 million rows a day toward 1.2 billion, and nothing ever updates a row in it. inventory is tiny, 12,000 rows, and updated far more often than it is inserted into, because every item in every basket touches it.

Keep that contrast in mind for the next thirteen chapters. A mechanism that is free on delivery_events can be expensive on inventory, and an index that pays for itself on orders can be pure overhead on either. Alongside public there is a second schema, analytics, holding the materialized views the dashboard reads; Chapter 3 builds them.

The Traffic

A weekday runs at a few hundred orders a minute. Saturday morning is a different system: the peak reaches 3,000 orders a minute, and every one of them is a multi-statement transaction that touches four tables. app-01 and app-02 between them attempt 400 database connections against a max_connections of 200, which is the arithmetic the previous topic said would not work and does not.

The rest of the numbers are the ones every recommendation in this book is a function of, and they are worth writing down for your own system before tuning anything. shared_buffers on pg-primary is still the packaged 128 MB default, on a machine with 64 GB of RAM, untouched since the install. The analytics dashboard on pg-replica-a tolerates 30 seconds of replica lag, and the actual lag has never been measured. Backups are a nightly base backup plus continuous WAL archiving with a 7-day recovery window, and the restore has never been tested.

Wound One: The Saturday Slowdown

On the first Saturday of each month, checkout latency goes from 40 ms to about 6 seconds. It lasts roughly twenty minutes and then recovers on its own, with nobody touching anything.

The team has ruled out the obvious. Nothing was deployed that morning — the last release was Wednesday. No host is short of CPU, memory or disk during the window; pg-primary sits well below saturation while checkout is timing out. It is not every Saturday, only the first of the month, which kills the "weekend traffic" theory outright, because the second Saturday carries the same load and behaves perfectly. Restarting the API makes it look fixed, which is what convinced two engineers it was an application problem, and once the incident is over there is nothing left in the logs but slow queries.

That is everything currently known. There is a mechanism behind it, it is entirely a database mechanism, and it is visible in a place nobody has looked yet. Chapter 9 names it and shows the evidence.

Wound Two: The Strawberries

On a Saturday in March, two customers were both sold the last box of strawberries. Both received a confirmation. One of them received an apology and a refund on Monday. The stock level for that product ended the morning at 0 with two sales recorded against a single unit.

The checkout code reads the stock level, decides in the application whether the sale is allowed, and writes the new value in a second statement.

BEGIN;

SELECT on_hand FROM inventory
 WHERE product_id = 4471 AND warehouse_id = 2;   -- returns 1

-- the application decides here: 1 is enough, allow the sale,
-- and compute the new value in Python as 1 - 1 = 0

UPDATE inventory SET on_hand = 0
 WHERE product_id = 4471 AND warehouse_id = 2;

COMMIT;

Two sessions ran that sequence at the same moment. Both SELECT statements returned 1. Both applications concluded there was stock. Both wrote 0, both committed, and both customers got their confirmation email. Nothing errored, no constraint was violated, and no log line records anything unusual — the database did exactly what it was asked to do by two transactions that could not see each other. Chapter 6 resolves this one, and the answer is not the first thing most engineers reach for.

How the Book Uses Cartwheel

Every chapter changes this same system instead of inventing a fresh example. Chapters 2 to 4 refine the schema: types chosen deliberately, constraints that make bad rows impossible, migrations that ship without taking the site down, and the SQL that replaces three round trips with one statement. Chapters 5 to 9 explain the machine underneath, and that is where both wounds get closed. Chapters 10 to 14 operate it — pooling, partitioning, a point-in-time recovery after a bad migration deletes 90,000 rows, a planned failover to pg-replica-b, a read-only role for the analytics team, and the spring upgrade from 17 to 18.

By the last page you will have watched one database go from "it works on my laptop" to "it survives a failover", with the same eight tables throughout. A book that resets its example every chapter can only teach mechanisms in isolation, and the interesting problems in Postgres are the ones where two mechanisms meet: a vacuum setting that is correct until an index is added, a pool size that is correct until a replica is promoted.