Backend Deep Dive
by Sergey Okinchuk
Multi-Tenancy in One CodebaseThe Connection Pool

Chapter Six · Data Access

Data Access

The engine keeps the truth; the service decides how it is reached. Six topics on the pool, the transaction, the mapper, the row two buyers reached for at once, the migration that ran during on-sale, and the replica that showed an order as missing for a second. This is the chapter that closes the first wound: seat 14C, sold twice.

6 topics

Seat 14C was sold twice because hold_seat read the seat's status in one statement and updated it in the next, and under Read Committed two requests four milliseconds apart both read available. Every line was correct. The read was true when it ran, the write was valid when it ran, and nothing bound the two together. Topic 34 walks the six steps of that race and closes it three ways: a row lock before the decision, a version column checked by the write, and a unique constraint that refuses the second hold whatever the code does.

The rest of the chapter is what Marek found on the way there. The connection pool was sized at 100 per process against a database that allows 200, and nobody had multiplied by the eight processes. A transaction was held open across the 3-second Payrail call, with the seat row locked and the connection pinned for the length of a network round trip. The list-orders endpoint issued 51 statements to return 50 orders, one per order for its tickets, and the mapper had done exactly what it was told. A migration adding a column to seats queued for a lock for four minutes during on-sale and broke the old code when it finally ran. And the organizer reports on pg-replica-a showed a buyer's order as missing for a second after it was placed, because the replica was that second behind.

Each of those is the application's side of a boundary that PostgreSQL Deep Dive owns from the engine's side. This book does not teach the lock table or the WAL; it teaches where the transaction begins, which pool a read names, which ALTER TABLE is instant and which is the outage, and why a read followed by a write is never one operation until something makes it one.

The read-then-write, and the three ways this chapter binds them
Readstatus = available
→
Decidein the handler
→
Writestatus = held, twice
→
Bind themlock, version, constraint

Topics in This Chapter

Topic 31
The Connection Pool
Why a connection is a budget, 8 loops × 20 against a limit of 200, and the three settings that decide whether a slow database becomes 503s or a hung instance. What a pooler in front forbids.
Data
Topic 32
Transactions From the Application Side
One unit of work, one transaction, nothing that leaves the process inside it. What Read Committed promises per statement, when Serializable is worth its retry loop, and the trap a framework installs as a feature.
Data
Topic 33
The ORM Question
What a mapper buys and what it hides: 51 statements for 50 orders, the lazy load on the event loop, the 2-megabyte column fetched to read a status. Where Stagedoor composes with a builder and where it writes SQL.
Data
Topic 34
The Oversell — Concurrency at the Row
Seat 14C, six steps, both reads correct and the seat sold twice. The row lock, the version column and the unique constraint, which one fits the on-sale path, and why the same shape is in every double-spend ever written.
Data
Topic 35
Migrations Ship With the Code
Numbered files applied by the deploy, expand-migrate-contract across three deploys, and which forms of ALTER TABLE are instant on Postgres 18 and which are the four-minute outage. Backfills are jobs; rollback is forward.
Data
Topic 36
Reads That Scale — Replicas and Stale Data
Two pools and a decision written beside every read. What 20 milliseconds of lag means, how a buyer sees their own order after writing it, which reads may be stale, and what happens when the replica is gone.
Data