Episode 4 · The same last room, twice

What is a database?

Tuesday, 14:31: two guests booked the last room at a Porto guesthouse 40 milliseconds apart, and both got a confirmation email. There is one room, two travellers, and one database that let it happen.

Why apps don't just save files, how a database keeps bookings correct, and why it's usually the first thing to struggle when you grow.

30 min foundationWhy databases existRelational model & SQLTables, primary keys and foreign keysTransactions & ACIDDatabase indexesBig-O intuition: O(n) vs O(log n)

Problem
Many app servers, thousands of users, one shared truth: which rooms are booked, by whom, for how much.
Why the simple system fails
Files can't be safely written by many processes at once, can't be searched quickly, and leave half-written data after a crash. Two guests end up with the same room.
Engineering concept
A database is a server that stores data durably, answers questions about it efficiently (indexes), and applies changes all-or-nothing, in isolation (transactions).
Technology
Relational databases (PostgreSQL, MySQL) with SQL, B-tree indexes and ACID transactions; later caches, read replicas and connection poolers.
Trade-offs
Indexes speed reads but slow writes. Strong guarantees cost coordination. A single primary is simple and consistent but becomes the bottleneck; every scaling trick buys capacity with complexity or staleness.

The problem

2 min

Tuesday, 14:31. An email from a guesthouse owner in Porto.

Maya · Product manager
Room 9 in Porto, 14–16 November. Two guests, two confirmations, one room.
Omar · On-call SRE
Bookings #88412 and #88413, created 40 ms apart, one on each app server.
You · Engineer at StayHub
But the code checks the room is free before inserting.
Lina · Senior engineer, your mentor
Guess first. Then we replay both requests.

The big idea

4 min

Forty milliseconds apart

The big idea

A database keeps one shared truth correct while many clients change it at once.

App servers are copies we add and remove; each has its own memory. The database is the one place they all share, so "only one guest gets the room" can only be enforced there.

How it works

5 min

A is another server. It stores data in tables: rows of one kind of thing (one booking per row) with fixed columns (room, guest, dates). Replay the two requests:

Two requests, 40 ms apart

1
A checks
Server 1 asks: any booking for room 9 overlapping 14–16 Nov? None.
2
B checks
A few ms later server 2 asks the same. A hasn't written yet, so: none.
3
A inserts
Server 1 inserts #88412 and confirms.
4
B inserts
Server 2 inserts #88413 and confirms. A : both "won".
5
The fix
Run check + insert as one (changes applied all together or not at all, isolated from others) that takes a lock on room 9: others wanting that row wait.
6
B waits, then loses politely
When A commits, B's check finds #88412. B answers "sorry, just taken" (409 Conflict).

The race only shows when requests overlap by milliseconds, which is why tests never caught it. The database's other big tool is the : a sorted lookup on a column that lets it jump to rows instead of reading them all.

Watch a decision being made

4 min

Worked example

Lina makes "one room, one guest" a database rule

Room 9 was booked twice, 40 ms apart, by two app servers. Make double bookings impossible, whichever server handles them and however close they arrive.

1Reproduce the race
In a test copy, fire two bookings for the same room and dates at the same moment. Both succeed.
Why: Races hide in normal testing. You must force the overlap to see it and prove the fix.
2Find the gap
The handler runs SELECT then INSERT as two statements outside any transaction.
Why: Naming the exact gap tells you what must become one step.

3Wrap check + insert in one locked transaction

Lock the room's row, check for overlaps, insert, commit. Found a row? Roll back and return 409.

sql

BEGIN;
SELECT id FROM rooms WHERE id = 9 FOR UPDATE;   -- second request waits here
SELECT 1 FROM bookings
 WHERE room_id = 9 AND check_in < '2026-11-16' AND check_out > '2026-11-14';
-- a row came back? ROLLBACK, answer 409 Conflict
INSERT INTO bookings (room_id, guest_id, check_in, check_out)
VALUES (9, 5521, '2026-11-14', '2026-11-16');
COMMIT;

Why: The lock makes the second request wait until the first commits, so its check sees the new booking.

4Race it again
Re-run the parallel script 100 times: exactly one succeeds each time. Then search past data for other double bookings.
Why: One lucky run proves nothing for a race.

In real life

3 min

Two well-known products, two kinds of database

Instagram: relational at its core

Its engineers have publicly described building on PostgreSQL and splitting it across servers as it grew
Tables, keys and transactions keep related data consistent

Fits when data is related and must stay consistent.

Discord: a wide-column store for messages

Has publicly described storing billions of messages in Cassandra, later ScyllaDB
Built for huge write volumes over many machines
Gives up some cross-row transactions

Fits when the data is a huge, simple stream of writes.

What goes wrong

3 min

Adding app servers when the database is at 100%

More servers send more queries to the same full database.

Instead

Make the database do less: indexes, query fixes, caching. Check with EXPLAIN ANALYZE.

Check-then-insert without a transaction or lock

Two requests both see "free" and both insert.

Instead

A transaction with a row lock, or a constraint that makes overlaps impossible.

Indexing every column "just in case"

Every index is updated on every insert and takes space.

Instead

Index the columns real queries filter and sort on.

Trade-offs

3 min

Option	Fits when	You pay	Main risk
Add an index	A frequent query does a full scan	Slightly slower writes, disk	Indexes nobody uses
Cache reads (Redis)	Read far more than changed	Another system to run	Stale data
Read replicas	Reads dominate, queries already efficient	More machines	A guest misses their own new booking
Bigger database	Headroom needed now	Money	Hiding bad queries

Friday, 10:00. The index is in. Karim wants more.

Karim · CTO
Put "My bookings" in Redis. Zero database queries, done forever.
Lina · Senior engineer, your mentor
It's 0.1 ms now. A cache means a guest books and doesn't see it in My bookings until it refreshes.
Karim · CTO
Then refresh the cache on every booking.

Your job as tech lead

4 min

Claude Code can write the code for this. You still own the decisions, and you have to know what good looks like to accept or reject its work.

Decisions you own

Which rules the database itself enforces (no two bookings for the same room and night), instead of trusting every code path.
Which steps happen together or not at all: "is it free?" and "save the booking" belong in one transaction.
Which slow queries get an index, based on what the app actually asks, never every column.

Tell Claude Code

Two guests got Porto room 9 for 14–16 November, 40 ms apart. Our handler runs a SELECT to check the room is free, then a separate INSERT, with no transaction or lock. Make check and insert one all-or-nothing step, and have the database reject overlapping bookings if possible. Show the plan and migration before running anything; don't touch payment code. Write a test firing two bookings at the same moment: exactly one succeeds, the other gets "room taken".

Check before you accept

Claude explains in plain words what happens to two requests 40 ms apart: a transaction plus a lock or a constraint.
The test sends both bookings at the same time, not one after the other.
You read the migration before it runs on production, and know how it's undone.

Red flags

It fixes the double booking by disabling the Book button in the frontend.
A check in code, but no transaction, lock or constraint.
It runs SQL directly on production "to clean up the duplicates" without asking.

Check yourself

2 min

Q1/3Debugging

Two guests booked the same last room for the same night. The code first runs a SELECT to check availability, then an INSERT. What's the root cause?

How confident are you?

Next episode: StayHub's first mobile app retries a payment on a shaky train connection, and a guest is charged twice for one booking.

Finished?

Mark it complete to update your skill map.

Next: What is an API?

Go deeper (optional)You don't need this to understand the lesson. Open it when a project needs the details: Technical deep dive, Interactive simulation, Visual diagram, Analogy, Check your understanding, Practice ladder, Explain it yourself.

Technical deep dive

Why one query reads 1.2 million rows

sql

CREATE TABLE rooms (
  id          BIGSERIAL PRIMARY KEY,
  hotel_id    BIGINT NOT NULL REFERENCES hotels(id),   -- foreign key
  nightly_eur NUMERIC(10,2) NOT NULL CHECK (nightly_eur > 0)
);

CREATE TABLE bookings (
  id         BIGSERIAL PRIMARY KEY,
  room_id    BIGINT NOT NULL REFERENCES rooms(id),
  guest_id   BIGINT NOT NULL REFERENCES guests(id),
  check_in   DATE NOT NULL,
  check_out  DATE NOT NULL CHECK (check_out > check_in)
);

Tables, keys and constraints. The database enforces these rules for every client, whichever server sends the query.

Worked example

Lina makes "one room, one guest" a database rule

Room 9 in Porto was booked twice, 40 ms apart, by two app servers. Make double bookings impossible, whichever server handles the request and however close together they arrive.

1Reproduce the race
In a test copy, send two booking requests for the same room and dates at the same moment (a small script with two parallel calls). Both succeed.
Why: Race conditions hide in normal testing. You need to force the overlap to see it and to prove the fix.
2Find the gap
The handler runs SELECT (is it free?) then INSERT as two separate statements, outside any transaction. Anything can happen between them.
Why: Naming the exact gap tells you what must become one step.

3Wrap check + insert in one transaction

Start a transaction, lock the room's row, check for overlapping bookings, insert, commit.

sql

BEGIN;
SELECT id FROM rooms WHERE id = 9 FOR UPDATE;      -- second request waits here
SELECT 1 FROM bookings
 WHERE room_id = 9 AND check_in < '2026-11-16' AND check_out > '2026-11-14';
-- a row came back? ROLLBACK and answer 409 Conflict
INSERT INTO bookings (room_id, guest_id, check_in, check_out)
VALUES (9, 5521, '2026-11-14', '2026-11-16');
COMMIT;

Why: The lock makes the second request wait until the first commits, so its check sees the new booking.

4Tell the second guest kindly
When the check finds a booking, roll back and return 409 Conflict. The page shows "Sorry, this room was just booked" and suggests similar rooms.
Why: The database now protects the data, but the guest still needs a clear answer, not an error page.
5Race it again
Re-run the parallel script 100 times. Exactly one booking succeeds each time; the other gets 409. Then search for other rooms that were already double-booked.
Why: One lucky run proves nothing for a race. Repeating it, and checking past data, does.

ACID letter	Guarantee	StayHub example
Atomicity	All statements commit, or none do	Never a booking without its payment record
Consistency	Constraints always hold	check_out > check_in; room_id must exist
Isolation	Concurrent transactions don't see each other's half-done work	Two guests can't both grab room 9
Durability	Committed data survives crashes	The booking still exists after a power cut

ACID: the four guarantees of a database transaction.

Indexes. Without an , WHERE guest_id = 81723 is a : read all 1.2 million bookings to find 6. With a index the database walks a sorted tree and reaches the rows in a few page reads.

sql

EXPLAIN ANALYZE SELECT * FROM bookings WHERE guest_id = 81723;
Seq Scan on bookings  (rows=6) (actual time=0.03..148.70 ms)
  Rows Removed by Filter: 1199994

CREATE INDEX CONCURRENTLY bookings_guest_id_idx ON bookings (guest_id);

Index Scan using bookings_guest_id_idx on bookings (rows=6) (actual time=0.04..0.09 ms)

EXPLAIN ANALYZE shows the plan the database picked. Numbers are for StayHub's 1.2M bookings.

Interactive simulation

Visual diagram

API servers

Connection pooler

PostgreSQL primary

Redis cache (later)

Read replica (later)

Disk + write-ahead log

Click a component

A booking request reaching the database, plus the scaling tools you'll meet later (dashed).

Analogy

At the hotel officeIn a database

At the hotel officeLedgers for guests, rooms, bookingsIn a databaseTables
At the hotel officeA unique number on each lineIn a databaseA primary key
At the hotel officeA booking that says "room 12"In a databaseA foreign key
At the hotel officeAlphabet tabs on the ledger's sideIn a databaseAn index
At the hotel office"Check, charge, write, or do nothing"In a databaseA transaction
At the hotel officeOne ledger room, a long queueIn a databaseThe database as the bottleneck

Where the analogy breaks: a clerk handles one entry at a time. A database serves thousands of readers and writers at once, and still keeps each booking all-or-nothing.

Check your understanding

Q1/4Decision

Same app: database CPU at 100%, mostly from read queries. Select all changes that directly reduce the work the primary database does per user request:

Select all that apply.

How confident are you?

Practice ladder

Step 1 · Trace

Trace two guests and a lock

Scenario Warm-up

StayHub now books rooms with this transaction:

BEGIN;
SELECT id FROM rooms WHERE id = 9 FOR UPDATE;
SELECT 1 FROM bookings WHERE room_id = 9
  AND check_in < '2026-11-16' AND check_out > '2026-11-14';
-- found a row? ROLLBACK and answer 409
INSERT INTO bookings (…) VALUES (…);
COMMIT;

Guest A's request starts at 10:00:00.000 and takes 30 ms in total. Guest B's request for the same room and dates starts at 10:00:00.010.

Trace both requests: where does B wait, what does B's check find, and what does each guest see?

Step 2 · Complete

Finish Lina's note on the slow search

Debugging Practice

Maya: "Searching hotels by city takes over a second." Omar runs the query:

EXPLAIN ANALYZE SELECT id, name FROM hotels WHERE city = 'Lisbon';
Seq Scan on hotels (rows=48) (actual time=0.02..3.10 ms)

EXPLAIN ANALYZE SELECT * FROM rooms WHERE hotel_id = 311;   -- run once per hotel
Seq Scan on rooms (rows=25) (actual time=0.03..21.40 ms)

Lina's note: 1. Measure: the search runs 1 query on hotels, then 1 query on rooms for each of the 48 hotels. 2. Find the biggest slice: ___ 3. Name the cause: ___ 4. Pick the fix: add an index on rooms(hotel_id), and fetch all rooms for the 48 hotels in one query. 5. Measure again.

Fill in steps 2 and 3.

Step 3 · Solve

The overbooked yoga class

Design Practice

StayHub now sells extras: a hotel in Lisbon offers a yoga class with 12 spots. The code that reserves a spot:

const taken = await db.query("SELECT count(*) FROM spots WHERE class_id = $1", [classId]);
if (taken < 12) {
  await db.query("INSERT INTO spots (class_id, guest_id) VALUES ($1, $2)", [classId, guestId]);
}

On Sunday the class has 15 guests. Explain what happened, and redesign the reservation so the class can never go over 12, whichever app server handles the request.

Step 4 · Decide

Index the search filters?

Decision Stretch

Hotel search filters on city, stars, price and has_pool. Omar wants to add an index on each of the 4 columns of rooms "to be safe". Facts:

rooms has 30,000 rows and grows slowly. Hotels update rates about 2,000 times a day.
90% of searches filter by city. Fewer than 2% filter by has_pool.
EXPLAIN shows a full scan of rooms taking 21 ms for a city search.
The database is at 35% CPU.

Write a short decision: "We chose X over Y because…, accepting the risk of…"

Explain it yourself

Explain why a growing app's database usually becomes the bottleneck before its app servers, and what you'd do first when it does.

0 words