The last blue mug

One fact, one place to change it

Put products in one table and orders in another. Each product has a stable identifier, such as mug_42. An order carries that identifier instead of copying the product's stock count. To find the product for an order, the database matches the identifiers: that's a join.

The tables describe relationships; they don't dictate a single disk layout. PostgreSQL usually stores row versions in table pages with separate indexes. MySQL's InnoDB stores the row data in a clustered index, normally ordered by primary key. Both can represent these same products and orders. InnoDB's storage description makes that distinction concrete.

We can give the database some rules to enforce. A primary key identifies each product. A foreign key requires an order's product to exist. NOT NULL and CHECK (stock >= 0) rule out missing or negative stock. These checks apply to writes from the checkout service, an import script, or a human fixing a record. They don't depend on every caller remembering the same validation. PostgreSQL's constraint rules also explain their boundaries: an ordinary check constraint cannot enforce an arbitrary condition over other rows.

Finding the mug without visiting every product

For this lookup, return to before Alice’s purchase, when one mug is still available. The checkout knows mug_42. Without an index, finding it could require examining every product row. The database stores data in pages: fixed-size chunks it can load into memory or write to storage. An index has pages too. A B-tree keeps keys ordered, with separator keys on upper pages directing the search toward leaf pages. A leaf entry leads to the row we need. B-tree indexes can support ordered ranges as well as exact matches.

Another query asks for Alice's recent orders. An index on (buyer_id, created_at) groups her orders together and orders them by time. The product-ID index cannot do that job. Index order follows the questions we ask, and the database chooses a plan using its estimate of the work involved. When a report needs most rows, a sequential scan may be cheaper than many separate lookups. Multicolumn indexes and query plans describe those choices.

Every added index takes space and gives writes more work. A new order needs entries in its relevant indexes. Updating an indexed value may require replacing an entry. An index is another stored arrangement of the data, with a maintenance bill; it isn't a faster-query switch that costs nothing. PostgreSQL's index introduction sets out that tradeoff.

Two changes, one commit

Transaction boundaries and Concurrent updates work through the SQL and its result checks.

Now take the mug out of stock and insert Alice's order inside one transaction. Until commit, other sessions don't see these pending changes. If inserting the order fails, rollback discards the stock change too. A successful commit makes the transaction's changes available together. This all-or-nothing property is called atomicity. PostgreSQL's transaction tutorial follows the same idea across several updates.

But Bob presses Buy at nearly the same moment. Alice reads stock 1. Bob reads stock 1. Each application computes 1 minus 1 and tells the database to set stock to 0. Alice writes and commits first. Bob waits for her write lock, then writes his already-computed zero and commits his order.

Each transaction kept its own changes together. The final stock is zero, so the nonnegative-stock constraint is satisfied. There are still two orders for one mug. The old read was treated as permission to purchase, though it reserved nothing.

For this rule, move the test into the update itself: decrement the current stock only if it is positive. In PostgreSQL's Read Committed isolation, a concurrent update waits for the first writer; after that writer commits, it rechecks the condition against the updated row. Bob finds zero and changes no row. The application must check that result and create an order only when it actually reserved stock. The Read Committed rules describe that recheck.

BEGIN;
UPDATE products
SET stock = stock - 1
WHERE product_id = 'mug_42' AND stock > 0
RETURNING product_id;
-- If no row returned: ROLLBACK and report sold out.
-- Otherwise insert the order, then COMMIT.

Another option is to lock the stock row before reading it, with SELECT … FOR UPDATE. The second checkout waits before making its decision. Both approaches make the conflicting checkouts meet at the same row. Locks also mean waiting; taking several locks in different orders can cause a deadlock, which applications must handle. PostgreSQL's lock documentation covers these cases.

A reader can keep seeing yesterday's version

Isolation and snapshots develops the visibility choices and a decision spanning several rows.

A warehouse report might already be reading the products when Alice commits. PostgreSQL keeps multiple row versions so an ordinary reader can use its snapshot while writers proceed. That mechanism is multiversion concurrency control, or MVCC. The new stock value can exist beside the older value; visibility rules decide which belongs to the report. MVCC separates reading a committed view from reserving a row for a write.

Keep one report transaction open. It reads mug stock 1; Alice then commits her purchase; the report reads stock again. Under Read Committed, the second statement gets a new snapshot and sees 0. Under PostgreSQL Repeatable Read, both ordinary reads use the transaction's snapshot and see 1. Keeping a view stable is useful for a report, but it doesn't reserve the mug.

Serializable protects a broader promise: successful transactions must have an outcome consistent with running them one at a time. PostgreSQL can reject a transaction when it detects a conflict that breaks that promise. The application must retry the whole transaction, including its reads and decisions. Repeating only the final write would preserve the old decision.

Consider two warehouse bins with one mug each, and a rule to leave one mug available for the display. Two transactions each count two mugs, then sell from different bins. They don't write the same row, so our single-row guard doesn't protect the total. We need a shared reservation record, a suitable locking scheme, or serializable execution with retries. A rule involving several records needs a design covering those records.

Old versions eventually become useless. PostgreSQL's vacuuming makes their space reusable once readers no longer need them. Long-running transactions can hold old versions in use and delay cleanup. Frequent updates produce this extra storage work, even when every checkout changes only a small integer. Vacuuming explains reclamation; HOT updates describe an optimization that can avoid new index entries for some updates.

What survives a crash?

Handling waits, deadlocks, and retries follows the distinction between a known rollback and a missing commit reply.

Changing a row in memory isn't enough to promise Alice an order. PostgreSQL records changes in a write-ahead log before the affected data pages reach durable storage. With ordinary durable commit settings, the required log records, including commit, are flushed before success returns. Data pages can be written later. After a crash, recovery uses the log to replay changes that those pages were missing. WAL explains why this ordering works; commit settings can change the acknowledgement promise.

That protects against a crash with recoverable storage. Losing the entire storage device calls for another copy or a backup. And a connection can fail after commit but before Alice receives the reply. A retry should carry the same checkout identifier, with uniqueness enforced, so it can find the existing order. The database cannot make a payment provider's separate charge atomic with this local transaction; that needs its own retry and reconciliation design.

Where I'd start

For this shop's stock, orders, and payments recorded locally, I'd start with a transactional relational database. The records have useful shared relationships, and constraints protect them through several write paths. Indexes serve individual checkouts; joins support questions that cross orders and products. That conclusion follows from this workload, rather than a rule that every application needs SQL.

The family still contains different operational choices. SQLite runs inside the application and allows one writer at a time; in WAL mode, readers can keep their snapshots while that writer works. PostgreSQL and MySQL are server systems with different storage and concurrency details. SQLite's isolation description is a useful reminder to inspect the engine behind the relational model.

If the shop starts counting billions of page views, repeatedly touching full rows to aggregate a few fields is a different job; see column stores. If its catalogue is mostly read as whole nested products, document boundaries may fit those reads. Splitting checkout data across machines introduces distributed coordination; the word relational doesn't settle that deployment question.

One last change: an order now contains several products. Give it an order header and separate lines, one per purchased entry. A guarded decrement still protects each stock row, and one transaction can keep all reservations and the order together. Acquire rows in a consistent order, keep the transaction short, and handle rejection or retry for the whole cart. The same mechanisms apply; the set of records we must coordinate has grown.

← Back to the relational introduction