Booking Time-Slot Inventory

Per-night inventory, booking every night or none, checkout holds and fast availability search.

Hotels, rental homes, restaurant tables, doctor appointments, meeting rooms, rental cars: in each, users first search for what's free across a range of time, then book it without anyone else booking the same thing. The two halves have opposite needs — search must be fast and can be slightly stale; booking must be exactly right — and good designs treat them separately.

Model availability as units of time

The first instinct is a bookings table with check_in and check_out, then an overlap query:

SELECT 1 FROM bookings
WHERE listing_id = :id
  AND check_in < :requested_check_out
  AND check_out > :requested_check_in;

That's correct for one unique resource (one apartment, one doctor), and Postgres can even enforce it with an exclusion constraint on the date range. But it gets awkward for interchangeable inventory (a hotel has 40 identical "standard king" rooms) and for per-night pricing.

The common alternative: one row per resource per time unit.

room_type_id | date       | total | reserved | price
-------------+------------+-------+----------+------
king_std     | 2026-11-03 |  40   |   38     | 189
king_std     | 2026-11-04 |  40   |   40     | 219   ← sold out
king_std     | 2026-11-05 |  40   |   31     | 189

A 3-night stay is available only if every night has reserved < total. Per-night rows also hold per-night prices, minimum-stay rules and blackout dates naturally. For appointments, the unit is a slot (a 30-minute block) instead of a night.

[!TIP] Do the storage math 2M listings × 365 days = 730M rows — large but fine for a sharded database, especially partitioned by listing ID. Only generate rows for a rolling window (say 12–18 months ahead).

Booking without double-booking

Booking is a contention problem across several rows at once. All nights must be taken atomically, or none.

BEGIN;
UPDATE room_inventory
SET reserved = reserved + 1
WHERE room_type_id = :rt
  AND date >= :check_in AND date < :check_out
  AND reserved < total;
-- affected rows must equal the number of nights; otherwise ROLLBACK
COMMIT;

The conditional UPDATE locks the rows it touches, so two concurrent bookings for the last room serialize, and the second sees reserved = total and updates fewer rows than nights. Keep the transaction short, and always touch the rows in date order so concurrent multi-night bookings can't deadlock.

For a unique resource with the overlap model, SELECT … FOR UPDATE on the listing row (or the exclusion constraint) does the same job.

Holds during checkout

Users take minutes to pay. Never hold a database transaction that long; record a hold instead:

On "Reserve", run the conditional update and create a booking with status = 'held' and expires_at = now() + 10 minutes.
On payment success, flip it to confirmed.
A sweeper (or a scheduled job — see Scheduling Future Work) releases expired holds and decrements reserved.

Show the hold's countdown to the user, so it's clear why the room is "theirs" for only 10 minutes.

Searching availability fast

Search asks "which of 50,000 listings in Paris are free for these 4 nights, under €200, with a pool?" Running the booking-side query for each listing is far too slow. Instead:

[Booking Service] -> (Inventory DB: source of truth)
(Inventory DB: source of truth) -> [CDC] -> [Availability Indexer] -> (Search index)
[Search API] -> (Search index)

A search index (Elasticsearch) holds each listing's location, attributes and an availability summary: a compact set of available dates, or a bitmap with one bit per day for the next year (365 bits ≈ 46 bytes per listing). Checking a 4-night range is a bitwise check.
Filter on geography and attributes first, then check availability only for the candidates.
Accept staleness. The index lags the inventory by seconds. A listing that looks free in search may be gone at booking time — the booking transaction is the only real check, and the UI handles "just sold out" gracefully.
Cache popular searches (city + dates) for 30–60 seconds.

External calendars and channel managers

Hotels and hosts often list the same inventory on several sites. Syncing calendars is slow and eventually consistent, so double-bookings across platforms can happen. Shorten the window with webhooks instead of periodic polling, and have a clear policy for resolving a conflict when it happens (the earlier confirmed booking wins; the other is rebooked or refunded).

Overbooking on purpose

Airlines and hotels sometimes sell more than they have because a predictable share of bookings cancel or no-show. Model it as total = physical capacity × (1 + overbook rate) for that resource. Mention it only if the interviewer goes there — but it shows you know the domain.

Hot inventory

A popular event or a holiday weekend means many users fighting over the same few rows. Options from the contention playbook: queue booking attempts per resource, add a virtual waiting room, or split inventory into buckets. See Design Ticketmaster for the extreme case.

[!INTERVIEW] What a strong answer sounds like "Inventory is a row per room type per night, partitioned by hotel. Booking runs one transaction that conditionally increments reserved on every night of the stay, in date order, and checks that all nights updated; that's what prevents double-booking. Checkout holds expire after 10 minutes via a sweeper. Search never touches that table: CDC updates a 365-bit availability bitmap per room type in Elasticsearch, so a date-range search is a geo filter plus a bit check, and we accept a few seconds of staleness."