Availability calendars, double booking prevention via optimistic locking, geospatial search with PostGIS, and dynamic pricing signals at 6M+ listing scale.
Airbnb is a distributed inventory system where every listing is a unique, time-bounded resource. Unlike e-commerce (buy another unit), there is only one Beach House on June 15th. Getting this wrong means two guests, one room, and an emergency rebooking at Airbnb's expense.
Calendar showing which dates are booked — must be accurate across 6M listings simultaneously. Stale availability leads to users booking unavailable dates.
Race condition — 2 users book same listing same date simultaneously. Both see "available" → both submit → without locking, both succeed. Catastrophic UX failure.
Geospatial queries (listings near NYC) + date range availability + price filter + amenities + ratings. Combining all filters in one fast query is non-trivial.
Dynamic pricing based on demand, events, seasons. Smart Pricing suggests higher rates when search volume spikes for a date window with few available listings.
When a double booking occurs, Airbnb must: find the guest alternative accommodation (often at Airbnb's cost), pay a travel credit of up to $100, and potentially pay hotel rates for the displaced guest. Preventing double bookings is not a nice-to-have — it has direct, large financial consequences.
Compare what happens with and without locking when two users try to book the same listing simultaneously. Click "Book Now" for both users quickly in the left panel to trigger a race condition.
Each listing has a "version" integer. When Alice loads the page, she sees version=3. When she submits, the DB runs: UPDATE listings SET version=4 WHERE id=X AND version=3. If another user already changed the version (Bob committed first), 0 rows are updated → Alice's transaction detects this and retries or shows an error. Exactly one person wins the booking.
The reservation table is the source of truth for all bookings. Date ranges with overlap detection using PostgreSQL's native range operators are the canonical approach.
| Field | Type | Notes |
|---|---|---|
| id | uuid | Primary key — globally unique booking identifier |
| listing_id | bigint | Foreign key to listings table, partition key for sharding |
| guest_id | bigint | User who made the booking |
| start_date | date | Check-in date (inclusive) |
| end_date | date | Check-out date (exclusive, standard range convention) |
| status | enum | PENDING / CONFIRMED / CANCELLED — only CONFIRMED blocks dates |
| version | int | Optimistic lock version — incremented on every write to listings row |
-- Optimistic locking: check version matches before booking BEGIN; -- Step 1: Check availability (no lock yet) SELECT COUNT(*) FROM reservations WHERE listing_id = $1 AND status = 'CONFIRMED' AND daterange(start_date, end_date) && daterange($2, $3); -- If count > 0: reject (dates unavailable) -- Step 2: Update listing version (optimistic lock) UPDATE listings SET version = version + 1 WHERE id = $1 AND version = $4; -- $4 = version user saw on page load -- If 0 rows updated: another booking won the race → retry -- Step 3: Insert reservation INSERT INTO reservations (listing_id, guest_id, start_date, end_date, status) VALUES ($1, $2, $3, $4, 'CONFIRMED'); COMMIT;
PostgreSQL's daterange overlap operator && returns true if two ranges share any day. daterange('2024-06-15', '2024-06-20') && daterange('2024-06-18', '2024-06-22') = true. A GiST index on the daterange column makes this query fast even across millions of reservations per listing. Without the index: full table scan on every availability check.
Drag the radius slider to see how spatial indexing narrows the search space. PostGIS's ST_DWithin function with a GiST index makes radius queries on 6M listings take milliseconds.
ST_DWithin(location, ST_Point(-74.0, 40.7), 50000) — find all listings within 50km of NYC in milliseconds. Without a spatial index: full table scan of 6M listings, comparing distance for every row. With a GiST index: the index prunes the search space to a bounding box, then checks exact distance only for nearby candidates. 10,000x speedup at scale.
-- Airbnb-style geospatial + availability query SELECT l.id, l.title, l.price_per_night, ST_Distance(l.location, ST_Point($lon, $lat)) AS distance_m FROM listings l WHERE -- Geo: within 50km of search center ST_DWithin(l.location, ST_Point($lon, $lat), 50000) -- Price: within budget AND l.price_per_night BETWEEN $min_price AND $max_price -- Availability: no confirmed booking in requested range AND NOT EXISTS ( SELECT 1 FROM reservations r WHERE r.listing_id = l.id AND r.status = 'CONFIRMED' AND daterange(r.start_date, r.end_date) && daterange($checkin, $checkout) ) ORDER BY distance_m LIMIT 50;