DEV Community

Sumedh Bala
Sumedh Bala

Posted on

Hotel Booking: Schema Design Comparison

#systemdesign #booking #postgres #indexes

1. Introduction

Hotel booking systems face a fundamental design decision: how to model availability and reservations in the database. This document compares two primary approaches:

Schema 1: Row-Per-Day for Fungible Room Types

  • One row per (hotel_id, room_type_id, date)
  • Rooms of the same type are fungible (interchangeable)
  • Uses B-tree indexes and optimistic locking

Schema 2: GiST with Individual Rooms

  • One row per physical room
  • Rooms are non-fungible (each room tracked separately)
  • Uses GiST indexes and exclusion constraints to prevent overlapping bookings (see Section 3.2 for detailed explanation of how GiST works)

Both approaches solve the same problem (preventing double-booking) but with different trade-offs in complexity, performance, and flexibility.

Prerequisite Reading (Shared Building Blocks)

This document extends the event-ticketing series by @sumedhbala:

We reuse the same foundation: PostgreSQL as system of record, transactional outbox + Debezium CDC into Kafka, Elasticsearch for discovery, Redis for low-latency availability, and Stripe-style payment flows. Familiarity with those components is assumed so we can focus on hotel-specific schema and availability trade-offs here.

Key Technical Topics Covered (Interview Highlights)

  • GiST indexes + exclusion constraints (Section 3): Prevent overlapping range bookings with PostgreSQL GiST/exclusion constraints.
  • Optimistic vs. pessimistic locking (Sections 2.4 & 3.6): Version columns + FOR UPDATE SKIP LOCKED to avoid overbooking.
  • Fungible vs. non-fungible inventory modeling (Sections 2 & 3): When to use row-per-day counts vs. per-room range bookings.
  • Per-room-type pricing (Section 2.1): reservation_room_nights to support multiple room types in one reservation.
  • Hybrid design (Section 2.6): Schema 1 inventory + GiST room assignments to avoid room switching after check-in.
  • Scaling patterns (Section 7): Redis/search caches, read replicas, sharding by hotel_id, regional deployments, streaming events.

2. Schema 1: Row-Per-Day for Fungible Room Types

2.1 Schema Design 

-- Room types (fungible - Room 101 and 102 of same type are interchangeable)
CREATE TABLE room_types (
    hotel_id VARCHAR(50) NOT NULL,
    room_type_id VARCHAR(100) NOT NULL,
    type_name VARCHAR(100) NOT NULL,  -- e.g., "Deluxe", "Suite"
    max_capacity INT NOT NULL,
    amenities JSON,
    PRIMARY KEY (hotel_id, room_type_id)
);

-- Inventory: One row per room type per night
CREATE TABLE inventory (
    hotel_id VARCHAR(50) NOT NULL,
    room_type_id VARCHAR(100) NOT NULL,
    date DATE NOT NULL,  -- Specific night date
    total_rooms INT NOT NULL DEFAULT 0,
    available_rooms INT NOT NULL DEFAULT 0,
    reserved_rooms INT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 0,  -- For optimistic locking

    PRIMARY KEY (hotel_id, room_type_id, date),
    FOREIGN KEY (hotel_id, room_type_id) REFERENCES room_types(hotel_id, room_type_id),

    -- Database constraint ensures: total_rooms = available_rooms + reserved_rooms
    CONSTRAINT chk_inventory_balance 
        CHECK (total_rooms = available_rooms + reserved_rooms),

    -- Additional constraints for data integrity
    CONSTRAINT chk_inventory_non_negative 
        CHECK (available_rooms >= 0 AND reserved_rooms >= 0 AND total_rooms >= 0)
);

-- B-tree indexes for fast lookups
-- Note: The PRIMARY KEY already creates an index on (hotel_id, room_type_id, date),
-- so idx_inventory_lookup is technically redundant but kept for clarity/explicit naming.
CREATE INDEX idx_inventory_lookup ON inventory (hotel_id, room_type_id, date);
-- Partial index: Only includes rows where available_rooms > 0
-- This is smaller and faster for availability queries (the most common operation).
-- PostgreSQL will automatically use this index when querying for available rooms.
CREATE INDEX idx_inventory_available ON inventory (hotel_id, room_type_id, date) 
WHERE available_rooms > 0;

-- Reservations: Header table (one reservation can have multiple room types)
CREATE TABLE reservations (
    reservation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hotel_id VARCHAR(50) NOT NULL,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    num_guests INT NOT NULL,
    status ENUM('pending_payment', 'confirmed', 'checked_in', 'checked_out', 'cancelled', 'expired') NOT NULL,
    total_amount_minor_units BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (hotel_id) REFERENCES hotels(hotel_id)
);

-- Reservation rooms: One row per room type in the reservation
-- Allows booking multiple room types in a single reservation
CREATE TABLE reservation_rooms (
    reservation_room_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reservation_id UUID NOT NULL,
    hotel_id VARCHAR(50) NOT NULL,  -- Needed for composite foreign key
    room_type_id VARCHAR(100) NOT NULL,
    num_rooms INT NOT NULL DEFAULT 1,  -- Number of rooms of this type in the reservation

    FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id) ON DELETE CASCADE,
    FOREIGN KEY (hotel_id, room_type_id) REFERENCES room_types(hotel_id, room_type_id),
    CONSTRAINT chk_num_rooms_positive CHECK (num_rooms > 0),
    UNIQUE (reservation_id, room_type_id)  -- One row per room type per reservation
);

-- Reservation room nights: One row per room type per night for pricing
-- This allows different room types in the same reservation to have different nightly rates
-- (e.g., Deluxe room at $150/night, Suite at $300/night)
CREATE TABLE reservation_room_nights (
    reservation_room_night_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reservation_room_id UUID NOT NULL,
    night_date DATE NOT NULL,
    nightly_rate_minor_units BIGINT NOT NULL,  -- Rate per room of this type for this night

    FOREIGN KEY (reservation_room_id) REFERENCES reservation_rooms(reservation_room_id) ON DELETE CASCADE,
    UNIQUE (reservation_room_id, night_date)
);
Enter fullscreen mode Exit fullscreen mode

2.2 Key Characteristics

  • Fungible Inventory: Room 101 and Room 102 of type "Deluxe" are treated as identical
  • Multiple Room Types: A single reservation can include multiple room types (e.g., 2 Deluxe + 1 Suite) via the reservation_rooms table
  • Row-Per-Day: Each night gets its own inventory row
  • Count-Based: Tracks available_rooms count, not specific room assignments
  • B-tree Indexes: Standard indexes for point queries
  • Optimistic Locking: Uses version column to detect concurrent modifications

2.3 Availability Check Query 

-- Check availability for June 15-17 (3 nights) for 1 room
SELECT date, available_rooms, version
FROM inventory
WHERE hotel_id = 'hotel_123' 
  AND room_type_id = 'deluxe_001'
  AND date IN ('2024-06-15', '2024-06-16', '2024-06-17')
  AND available_rooms >= 1;  -- Check for at least 1 room

-- Check availability for multiple rooms (e.g., 3 rooms)
SELECT date, available_rooms, version
FROM inventory
WHERE hotel_id = 'hotel_123' 
  AND room_type_id = 'deluxe_001'
  AND date IN ('2024-06-15', '2024-06-16', '2024-06-17')
  AND available_rooms >= 3;  -- Check for at least 3 rooms
Enter fullscreen mode Exit fullscreen mode

2.4 Booking Creation (With Optimistic Locking)

Option 1: Single Room Type (Simplified) 

def create_booking_schema1_single_type(hotel_id, room_type_id, check_in_date, check_out_date, num_rooms=1, num_guests=1):
    """
    Create a booking for one or more rooms of the same type.

    Args:
        num_rooms: Number of rooms to book (default: 1)
        num_guests: Total number of guests across all rooms
    """
    # Use the multi-type function with single room type
    return create_booking_schema1(
        hotel_id, 
        [(room_type_id, num_rooms)], 
        check_in_date, 
        check_out_date, 
        num_guests
    )
Enter fullscreen mode Exit fullscreen mode

Option 2: Multiple Room Types (Full Implementation)

Example Usage:

# Book 1 room of single type
reservation_id = create_booking_schema1(
    'hotel_123', 
    [('deluxe_001', 1)], 
    '2024-06-15', 
    '2024-06-17', 
    num_guests=2
)

# Book multiple rooms of same type
reservation_id = create_booking_schema1(
    'hotel_123', 
    [('deluxe_001', 3)], 
    '2024-06-15', 
    '2024-06-17', 
    num_guests=8
)

# Book multiple room types in one reservation
# Example: 2 Deluxe rooms + 1 Suite for a family
reservation_id = create_booking_schema1(
    'hotel_123', 
    [('deluxe_001', 2), ('suite_001', 1)], 
    '2024-06-15', 
    '2024-06-17', 
    num_guests=10
)
Enter fullscreen mode Exit fullscreen mode

2.5 Pros and Cons

Pros:
✅ Simple and intuitive: Easy to understand and maintain
✅ Efficient point queries: Direct date lookups are O(log n) with B-tree index
✅ Easy updates: Update individual nights independently
✅ Supports per-night pricing: Each row can have different pricing/rates
✅ Handles overlapping bookings: User books June 15-17, another books June 16-18
✅ Flexible room assignment: Any available room of the type can be assigned
✅ Works with standard indexes: No special index types needed

Cons:
❌ More rows: 365-day window = 365 rows per room type (scales linearly with booking window)
❌ Optimistic locking complexity: Requires retry logic on conflicts
❌ No specific room assignment at booking: Don't know which exact room guest will get until check-in
❌ Count-based tracking: Must update all three fields together (total, available, reserved) - enforced by database CHECK constraint

2.6 Room Assignment Guarantee After Check-In

Question: Can Schema 1 guarantee that guests won't have to switch rooms once they've checked in?

Answer: Schema 1 alone cannot guarantee this because it only tracks room types, not specific room assignments. However, you can add a room assignment table that locks in the specific room at check-in time.

Option 1: Add Room Assignment at Check-In (Recommended)

Add a table to track specific room assignments after check-in:

-- Physical rooms table (needed for room assignments)
CREATE TABLE rooms (
    hotel_id VARCHAR(50) NOT NULL,
    room_number VARCHAR(20) NOT NULL,  -- e.g., "101", "102"
    room_type_id VARCHAR(100) NOT NULL,
    PRIMARY KEY (hotel_id, room_number),
    FOREIGN KEY (hotel_id, room_type_id) REFERENCES room_types(hotel_id, room_type_id)
);

-- Room assignments: Lock in specific room at check-in
CREATE TABLE room_assignments (
    assignment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reservation_id UUID NOT NULL,
    hotel_id VARCHAR(50) NOT NULL,
    room_number VARCHAR(20) NOT NULL,
    stay_dates DATERANGE NOT NULL,  -- [check_in_date, check_out_date)
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id) ON DELETE CASCADE,
    FOREIGN KEY (hotel_id, room_number) REFERENCES rooms(hotel_id, room_number),

    -- GiST exclusion constraint prevents overlapping assignments for same room
    -- GiST is explained in section 3.3
    EXCLUDE USING GIST (
        hotel_id WITH =,
        room_number WITH =,
        stay_dates WITH &&
    )
);
Enter fullscreen mode Exit fullscreen mode

How This Works:

  1. At Booking Time: Reservation is created with multiple room types via reservation_rooms table (no specific rooms assigned yet)
  2. At Check-In: Hotel staff assigns specific rooms (one room_assignments entry per room) matching the room types and counts
  3. GiST Constraint: Prevents any other reservation from being assigned to the same room for overlapping dates
  4. Guarantee: Once assigned, each room cannot be assigned to anyone else during the guest's stay

Check-In Flow:

def check_in_guest(reservation_id, room_assignments):
    """
    Assign specific rooms to a reservation at check-in.

    Args:
        reservation_id: The reservation ID
        room_assignments: Dict mapping room_type_id to list of room numbers
                         Example: {'deluxe_001': ['101', '102'], 'suite_001': ['201']}
    """
    reservation = get_reservation(reservation_id)

    if reservation.status != 'confirmed':
        raise InvalidStateError("Reservation must be confirmed")

    # Get reservation_rooms to validate assignments
    reservation_rooms = db.query("""
        SELECT room_type_id, num_rooms
        FROM reservation_rooms
        WHERE reservation_id = ?
    """, reservation_id)

    # Validate room assignments match reservation
    for res_room in reservation_rooms:
        assigned_rooms = room_assignments.get(res_room.room_type_id, [])
        if len(assigned_rooms) != res_room.num_rooms:
            raise InvalidRoomCountError(
                f"Room type {res_room.room_type_id}: Reservation requires {res_room.num_rooms} rooms, "
                f"but {len(assigned_rooms)} provided"
            )

    # Assign each room (GiST constraint prevents overlaps)
    try:
        for room_type_id, room_numbers in room_assignments.items():
            for room_number in room_numbers:
                db.execute("""
                    INSERT INTO room_assignments 
                    (reservation_id, hotel_id, room_number, stay_dates)
                    VALUES (?, ?, ?, daterange(?, ?, '[]'))
                """, reservation_id, reservation.hotel_id, room_number, 
                     reservation.check_in_date, reservation.check_out_date)

        # Update reservation status
        db.execute("""
            UPDATE reservations
            SET status = 'checked_in', checked_in_at = NOW()
            WHERE reservation_id = ?
        """, reservation_id)

        db.commit()
    except ExclusionViolation:
        # One or more rooms already assigned to another guest for these dates
        raise RoomAlreadyAssignedError(f"One or more rooms are not available")
Enter fullscreen mode Exit fullscreen mode

Multiple Room Types Example:

# Reservation for 2 Deluxe + 1 Suite
reservation_id = create_booking_schema1(
    'hotel_123', 
    [('deluxe_001', 2), ('suite_001', 1)], 
    '2024-06-15', 
    '2024-06-17', 
    num_guests=10
)

# At check-in, assign specific rooms for each type
check_in_guest(reservation_id, {
    'deluxe_001': ['101', '102'],  # 2 Deluxe rooms
    'suite_001': ['201']           # 1 Suite
})
# Creates 3 room_assignments entries total
# GiST constraint ensures no overlaps for any of the 3 rooms
Enter fullscreen mode Exit fullscreen mode

Benefits:

  • ✅ Guarantees no room switching: Once assigned, GiST constraint prevents conflicts
  • ✅ Flexibility before check-in: Can reassign rooms if needed before guest arrives
  • ✅ Best of both worlds: Fungible inventory for booking, specific assignment at check-in
  • ✅ Maintenance handling: If room needs maintenance, can reassign before check-in

Trade-offs:

  • Requires additional rooms and room_assignments tables
  • More complex than pure Schema 1, but simpler than full Schema 2

Option 2: Pure Schema 1 (No Guarantee)

If you don't add room assignments, Schema 1 cannot guarantee that guests won't switch rooms because:

  • No specific room is tracked
  • Hotel could theoretically reassign rooms (though this would be bad practice)
  • No database constraint prevents it

When This Is Acceptable:

  • Standard hotels where room switching is rare and handled operationally
  • Hotels that prioritize flexibility over guarantees
  • Systems where room assignment happens outside the booking system

Recommendation

For production systems, add room assignments at check-in (Option 1) to:

  1. Guarantee no room switching after check-in
  2. Maintain flexibility before check-in
  3. Provide audit trail of room assignments
  4. Enable room-level tracking for maintenance

This hybrid approach gives you the benefits of Schema 1 (fungible inventory, simple booking) with the guarantees of Schema 2 (specific room assignment, no conflicts).

3. Schema 2: GiST with Individual Rooms

3.1 Tables: Reused from Schema 1 vs. New Tables

Tables Reused from Schema 1:

  • ✅ room_types - Room type metadata (Deluxe, Suite, etc.)
  • ✅ reservations - Reservation header table (check-in/out dates, guest info, status)
  • ✅ reservation_room_nights - Pricing per room type per night (optional, for pricing calculations)

Tables NOT Used from Schema 1:

  • ❌ inventory - Not needed (availability calculated from room_bookings overlaps)
  • ❌ reservation_rooms - Not needed (bookings are per-room, not per-room-type)

New Tables for Schema 2:

  • 🆕 rooms - Physical room inventory (hotel_id, room_number, room_type_id)
  • 🆕 room_bookings - Individual room bookings with date ranges (uses GiST index with partial exclusion constraint to prevent overlaps for pending/confirmed/checked_in)

Key Difference:

  • Schema 1: Tracks availability by room type (fungible: "2 Deluxe rooms available")
  • Schema 2: Tracks availability by specific room (non-fungible: "Room 101 available, Room 102 booked")

3.2 Schema Design 

-- Physical rooms (non-fungible - each room tracked separately)
CREATE TABLE rooms (
    hotel_id VARCHAR(50) NOT NULL,
    room_number VARCHAR(20) NOT NULL,  -- e.g., "101", "102", "Suite-A"
    room_type_id VARCHAR(100) NOT NULL,  -- Links to room_types for metadata
    floor_number INT,

    PRIMARY KEY (hotel_id, room_number),
    FOREIGN KEY (hotel_id, room_type_id) REFERENCES room_types(hotel_id, room_type_id)
);

-- Room bookings: One row per booking with date range
CREATE TABLE room_bookings (
    booking_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hotel_id VARCHAR(50) NOT NULL,
    room_number VARCHAR(20) NOT NULL,  -- Specific physical room
    reservation_id UUID NOT NULL,

    stay_dates DATERANGE NOT NULL,  -- [check_in_date, check_out_date)
    status ENUM('pending', 'confirmed', 'checked_in', 'checked_out', 'cancelled') NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (hotel_id, room_number) REFERENCES rooms(hotel_id, room_number),
    FOREIGN KEY (reservation_id) REFERENCES reservations(reservation_id)
);

-- GiST index for fast range queries and overlap detection
CREATE INDEX idx_room_bookings_dates 
ON room_bookings USING GIST (hotel_id, room_number, stay_dates);

-- Exclusion constraint prevents overlapping bookings for same room
-- Option 1: Partial exclusion constraint (RECOMMENDED - most idiomatic)
-- This constraint ONLY applies to "active" bookings (pending, confirmed, checked_in)
-- Cancelled/checked_out bookings don't block new bookings
ALTER TABLE room_bookings
ADD EXCLUDE USING GIST (
    hotel_id WITH =,
    room_number WITH =,
    stay_dates WITH &&
) WHERE (status IN ('pending', 'confirmed', 'checked_in'));

-- Option 2: Use trigger (more flexible, but less idiomatic)
-- Triggers are more flexible because you can add custom logic beyond just checking overlaps.
-- For example, you could log failed booking attempts, allow VIP users to override conflicts,
-- or enforce additional business rules like "no bookings within 1 hour of each other".
-- However, we don't need this flexibility here - the simple overlap check is sufficient.
-- Note: We prevent overlaps for 'pending', 'confirmed', and 'checked_in' to avoid
-- double-booking scenarios where multiple pending reservations could later be confirmed
CREATE OR REPLACE FUNCTION check_room_overlap() RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM room_bookings
        WHERE hotel_id = NEW.hotel_id
          AND room_number = NEW.room_number
          AND stay_dates && NEW.stay_dates  -- Overlap operator
          AND status IN ('pending', 'confirmed', 'checked_in')  
          AND booking_id != NEW.booking_id
    ) THEN
        RAISE EXCEPTION 'Room already booked for these dates';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_overlap
BEFORE INSERT OR UPDATE ON room_bookings
FOR EACH ROW
WHEN (NEW.status IN ('pending', 'confirmed', 'checked_in')) 
EXECUTE FUNCTION check_room_overlap();
Enter fullscreen mode Exit fullscreen mode

3.3 How GiST Works

GiST (Generalized Search Tree) is a PostgreSQL index type optimized for complex data types like ranges, geometric data, and full-text search. Unlike B-tree indexes (which work with simple comparisons like <, =, >), GiST indexes support spatial and range operations.

What is GiST?

GiST is an extensible indexing framework that allows you to define custom index methods for complex data types. For date ranges, PostgreSQL provides built-in GiST support that understands range operations.

How GiST Indexes Range Data

1. Range Representation:

-- A date range is stored as: [start_date, end_date)
daterange('2024-06-15', '2024-06-17', '[]')  -- Includes both endpoints
daterange('2024-06-15', '2024-06-17', '[)')  -- Standard: includes start, excludes end
Enter fullscreen mode Exit fullscreen mode

2. GiST Index Structure:

  • GiST builds a tree where each node contains bounding boxes (the smallest range that contains all child ranges)
  • Leaf nodes contain actual date ranges
  • Internal nodes contain bounding boxes of their children

Example GiST Tree Structure:

                    Root Node
              [2024-01-01, 2024-12-31]  (bounding box)
                    /              \
         Node A                    Node B
    [2024-01-01, 2024-06-30]  [2024-07-01, 2024-12-31]
         /        \                  /        \
    Leaf 1      Leaf 2          Leaf 3      Leaf 4
[2024-06-15,  [2024-06-20,   [2024-07-10,  [2024-08-01,
 2024-06-17]   2024-06-22]    2024-07-15]   2024-08-05]
Enter fullscreen mode Exit fullscreen mode

Range Operations GiST Supports

1. Overlap Operator (&&):

-- Check if two ranges overlap
daterange('2024-06-15', '2024-06-17') && daterange('2024-06-16', '2024-06-18')
-- Returns: TRUE (they overlap on June 16)

daterange('2024-06-15', '2024-06-17') && daterange('2024-06-18', '2024-06-20')
-- Returns: FALSE (no overlap)
Enter fullscreen mode Exit fullscreen mode

How GiST Evaluates Overlap:

  1. Start at root node
  2. Check if query range overlaps with node's bounding box
  3. If yes, descend into that branch
  4. If no, skip entire branch (pruning)
  5. Continue until leaf nodes
  6. Check actual ranges for overlap

2. Contains Operator (@>):

-- Check if range contains a date
daterange('2024-06-15', '2024-06-17') @> '2024-06-16'::date
-- Returns: TRUE

-- Check if range contains another range
daterange('2024-06-10', '2024-06-20') @> daterange('2024-06-15', '2024-06-17')
-- Returns: TRUE
Enter fullscreen mode Exit fullscreen mode

3. Contained By Operator (<@):

-- Check if range is contained by another range
daterange('2024-06-15', '2024-06-17') <@ daterange('2024-06-10', '2024-06-20')
-- Returns: TRUE
Enter fullscreen mode Exit fullscreen mode

4. Adjacent Operator (-|-):

-- Check if ranges are adjacent (touch but don't overlap)
daterange('2024-06-15', '2024-06-17') -|- daterange('2024-06-17', '2024-06-19')
-- Returns: TRUE (they touch at June 17)
Enter fullscreen mode Exit fullscreen mode

How Exclusion Constraints Use GiST

Exclusion Constraint Syntax:

EXCLUDE USING GIST (
    hotel_id WITH =,        -- Equality operator for hotel_id
    room_number WITH =,     -- Equality operator for room_number
    stay_dates WITH &&      -- Overlap operator for date ranges
)
Enter fullscreen mode Exit fullscreen mode

What This Means:

  • For the same (hotel_id, room_number) combination
  • Prevent any two rows where stay_dates overlap (&&)
  • The constraint is enforced at the database level (before insert/update)

How It Works Internally:

  1. When inserting a new booking, PostgreSQL checks the GiST index
  2. Finds all existing bookings for the same (hotel_id, room_number)
  3. For each existing booking, checks if stay_dates && new_stay_dates
  4. If any overlap is found, the insert fails with an exclusion violation error
  5. This happens atomically - no race conditions possible

Example:

-- Booking 1: Room 101, June 15-17
INSERT INTO room_bookings VALUES
('booking_1', 'hotel_123', '101', 'reservation_1', 
 daterange('2024-06-15', '2024-06-17', '[]'), 'confirmed');
-- SUCCESS

-- Booking 2: Room 101, June 16-18 (OVERLAPS with Booking 1!)
INSERT INTO room_bookings VALUES
('booking_2', 'hotel_123', '101', 'reservation_2', 
 daterange('2024-06-16', '2024-06-18', '[]'), 'confirmed');
-- ERROR: conflicting key value violates exclusion constraint "room_bookings_excl"
-- The GiST index found that Booking 1's range [2024-06-15, 2024-06-17] 
-- overlaps with Booking 2's range [2024-06-16, 2024-06-18]
Enter fullscreen mode Exit fullscreen mode

Why GiST is Efficient for Range Queries

1. Pruning:

  • GiST can skip entire branches of the tree if their bounding boxes don't overlap
  • Example: If query range is [2024-06-15, 2024-06-17] and a node's bounding box is [2024-12-01, 2024-12-31], that entire branch is skipped

2. Index-Only Scans:

  • For overlap checks, GiST can often answer queries using only the index (without accessing table data)
  • This is much faster than scanning all rows

3. Spatial Locality:

  • Ranges that are close in time are stored near each other in the index
  • Queries for nearby dates are very efficient

Performance Comparison: GiST vs B-tree for Ranges

B-tree with Date Ranges (Inefficient):

-- Without GiST, you'd need to check every booking
SELECT * FROM room_bookings
WHERE hotel_id = 'hotel_123' 
  AND room_number = '101'
  AND (
    (check_in_date <= '2024-06-17' AND check_out_date >= '2024-06-15')
    OR (check_in_date BETWEEN '2024-06-15' AND '2024-06-17')
    OR (check_out_date BETWEEN '2024-06-15' AND '2024-06-17')
  );
-- Must scan many rows, complex WHERE clause
Enter fullscreen mode Exit fullscreen mode

GiST with Date Ranges (Efficient):

-- With GiST, single overlap operator
SELECT * FROM room_bookings
WHERE hotel_id = 'hotel_123' 
  AND room_number = '101'
  AND stay_dates && daterange('2024-06-15', '2024-06-17', '[]');
-- Uses GiST index, very fast, simple query
Enter fullscreen mode Exit fullscreen mode

GiST Index Maintenance

Insert Performance:

  • Inserting a new range requires updating the GiST tree
  • May need to split nodes if bounding boxes become too large
  • Generally O(log n) but can be slower than B-tree for simple inserts

Update Performance:

  • Updating a range may require rebalancing the tree
  • More expensive than B-tree updates

Query Performance:

  • Overlap queries: Excellent (O(log n) with pruning)
  • Point queries: Good (but B-tree is better)
  • Range containment: Excellent

3.4 Key Characteristics

  • Non-Fungible Inventory: Each physical room (Room 101, Room 102) is tracked separately
  • Date Ranges: Uses DATERANGE to store booking periods
  • GiST Indexes: Optimized for range queries and overlap detection (see section 3.3 for details)
  • Database-Enforced Overlaps: Exclusion constraints or triggers prevent double-booking
  • Specific Room Assignment: Know exactly which room guest will get

3.5 Availability Check Query 

-- Find available rooms of a specific type for June 15-17
-- Exclude rooms with pending, confirmed, or checked_in bookings
SELECT r.room_number
FROM rooms r
WHERE r.hotel_id = 'hotel_123' 
  AND r.room_type_id = 'deluxe_001'
  AND NOT EXISTS (
      SELECT 1 FROM room_bookings b
      WHERE b.hotel_id = r.hotel_id
        AND b.room_number = r.room_number
        AND b.stay_dates && daterange('2024-06-15', '2024-06-17', '[]')  -- Overlap check
        AND b.status IN ('pending', 'confirmed', 'checked_in') 
  )
LIMIT 1;
Enter fullscreen mode Exit fullscreen mode

3.6 Booking Creation

Single Room Booking 

def create_booking_schema2_single(hotel_id, room_type_id, check_in_date, check_out_date, num_guests):
    """
    Create a booking for a single room.
    """
    # Step 1: Find an available room of this type
    # Exclude rooms with pending, confirmed, or checked_in bookings for these dates
    available_room = db.query("""
        SELECT r.room_number
        FROM rooms r
        WHERE r.hotel_id = ? AND r.room_type_id = ?
          AND NOT EXISTS (
              SELECT 1 FROM room_bookings b
              WHERE b.hotel_id = r.hotel_id
                AND b.room_number = r.room_number
                AND b.stay_dates && daterange(?, ?, '[]')
                AND b.status IN ('pending', 'confirmed', 'checked_in')  -- Include pending
          )
        LIMIT 1
    """, hotel_id, room_type_id, check_in_date, check_out_date)

    if not available_room:
        raise InsufficientAvailabilityError()

    room_number = available_room.room_number

    # Step 2: Create reservation
    reservation_id = db.execute("""
        INSERT INTO reservations 
        (hotel_id, check_in_date, check_out_date, num_guests, status, total_amount_minor_units)
        VALUES (?, ?, ?, ?, 'pending_payment', 0)
        RETURNING reservation_id
    """, hotel_id, check_in_date, check_out_date, num_guests)

    # Step 3: Create room booking (exclusion constraint prevents overlap)
    try:
        db.execute("""
            INSERT INTO room_bookings 
            (hotel_id, room_number, reservation_id, stay_dates, status)
            VALUES (?, ?, ?, daterange(?, ?, '[]'), 'pending')
        """, hotel_id, room_number, reservation_id, check_in_date, check_out_date)
        db.commit()
    except ExclusionViolation:
        # Another booking took this room - retry with different room
        db.rollback()
        return create_booking_schema2_single(hotel_id, room_type_id, check_in_date, check_out_date, num_guests)

    return reservation_id
Enter fullscreen mode Exit fullscreen mode

Multiple Rooms Booking (Complex - Requires Retry Logic) 

def create_booking_schema2(hotel_id, room_type_id, check_in_date, check_out_date, num_rooms, num_guests):
    """
    Create a booking for multiple rooms of the same type.

    CRITICAL: This is significantly more complex than Schema 1's atomic decrement.
    We must find and book N different rooms atomically, which requires retry logic
    if any room becomes unavailable during the transaction.

    Args:
        num_rooms: Number of rooms to book (e.g., 3 Deluxe rooms)
    """
    max_retries = 5

    for attempt in range(max_retries):
        try:
            with db.transaction():
                # Step 1: Find N available rooms of this type
                # Use FOR UPDATE SKIP LOCKED to lock available rooms atomically
                # 
                # FOR UPDATE SKIP LOCKED Explanation (VERY USEFUL FOR INTERVIEWS):
                # This PostgreSQL feature is crucial for preventing race conditions in concurrent systems.
                # 
                # How it works:
                # 1. FOR UPDATE: Locks the selected rows for this transaction (prevents other transactions
                #    from modifying them until we commit/rollback)
                # 2. SKIP LOCKED: If a row is already locked by another transaction, skip it and continue
                #    to the next available row (instead of waiting/blocking)
                # 
                # Why it's useful:
                # - Prevents "lost update" problems: Multiple transactions can't grab the same room
                # - High concurrency: Transactions don't block each other - they just skip locked rows
                # - Perfect for work queues, booking systems, inventory allocation
                # - Common interview question: "How do you prevent two workers from processing the same job?"
                # 
                # Example scenario:
                #   T1: SELECT ... FOR UPDATE SKIP LOCKED LIMIT 3 → locks rooms 101, 102, 103
                #   T2: SELECT ... FOR UPDATE SKIP LOCKED LIMIT 3 → skips 101,102,103, locks 104, 105, 106
                #   Both transactions proceed without blocking each other!
                # 
                # Alternative (FOR UPDATE without SKIP LOCKED):
                #   T1: SELECT ... FOR UPDATE LIMIT 3 → locks rooms 101, 102, 103
                #   T2: SELECT ... FOR UPDATE LIMIT 3 → WAITS for T1 to commit (blocks, reduces concurrency)
                available_rooms = db.query("""
                    SELECT r.room_number
                    FROM rooms r
                    WHERE r.hotel_id = ? AND r.room_type_id = ?
                      AND NOT EXISTS (
                          SELECT 1 FROM room_bookings b
                          WHERE b.hotel_id = r.hotel_id
                            AND b.room_number = r.room_number
                            AND b.stay_dates && daterange(?, ?, '[]')
                            AND b.status IN ('pending', 'confirmed', 'checked_in')
                      )
                    ORDER BY r.room_number
                    LIMIT ?
                    FOR UPDATE SKIP LOCKED  -- Lock rows, skip if already locked
                """, hotel_id, room_type_id, check_in_date, check_out_date, num_rooms)

                if len(available_rooms) < num_rooms:
                    raise InsufficientAvailabilityError(
                        f"Only {len(available_rooms)} rooms available, need {num_rooms}"
                    )

                # Step 2: Create reservation header
                reservation_id = db.execute("""
                    INSERT INTO reservations 
                    (hotel_id, check_in_date, check_out_date, num_guests, status, total_amount_minor_units)
                    VALUES (?, ?, ?, ?, 'pending_payment', 0)
                    RETURNING reservation_id
                """, hotel_id, check_in_date, check_out_date, num_guests)

                # Step 3: Create room bookings for all N rooms
                # The exclusion constraint will prevent overlaps if another transaction
                # grabbed a room between our SELECT and INSERT
                for room in available_rooms:
                    try:
                        db.execute("""
                            INSERT INTO room_bookings 
                            (hotel_id, room_number, reservation_id, stay_dates, status)
                            VALUES (?, ?, ?, daterange(?, ?, '[]'), 'pending')
                        """, hotel_id, room.room_number, reservation_id, check_in_date, check_out_date)
                    except ExclusionViolation:
                        # Room was taken by another transaction - abort entire booking
                        raise ConcurrentModificationError(
                            f"Room {room.room_number} was booked by another transaction"
                        )

                db.commit()
                return reservation_id

        except (ConcurrentModificationError, ExclusionViolation):
            if attempt == max_retries - 1:
                raise InsufficientAvailabilityError(
                    f"Could not book {num_rooms} rooms after {max_retries} attempts"
                )
            # Exponential backoff before retry
            time.sleep(0.1 * (2 ** attempt))
            continue

    raise InsufficientAvailabilityError("Booking failed after retries")
Enter fullscreen mode Exit fullscreen mode

Key Challenges with Multi-Room Bookings in Schema 2:

  1. Concurrency Risk: Even with FOR UPDATE SKIP LOCKED, there's a small window between SELECT and INSERT where another transaction could grab a room. Important: FOR UPDATE does NOT prevent other transactions from reading the rows - it only prevents them from:
    • Taking a FOR UPDATE lock on the same rows (they'd wait or skip with SKIP LOCKED)
    • Modifying the rows (UPDATE/DELETE)
    • Other transactions can still read the rows with regular SELECT (no lock required)
    • This means another transaction could check availability (read) and insert into room_bookings
    • The exclusion constraint on room_bookings is the final protection (will raise ExclusionViolation if overlap detected)
    • This is why we catch ExclusionViolation and retry the entire booking
  2. All-or-Nothing for Same Room Type: If booking 3 rooms of the same type and we can only find 2 available, the entire booking fails. This is different from Schema 1, where booking 3 rooms of the same type is a single atomic UPDATE inventory SET available_rooms = available_rooms - 3 operation.
  3. Retry Complexity: Requires retry logic with exponential backoff
  4. Performance: FOR UPDATE SKIP LOCKED helps prevent conflicts but adds locking overhead (see detailed explanation above)

Comparison with Schema 1:

  • Schema 1 (same room type): UPDATE inventory SET available_rooms = available_rooms - 3 (single atomic operation for N rooms of same type)
  • Schema 1 (multiple room types): Also all-or-nothing - if any room type lacks availability, entire booking fails
  • Schema 2: Must find N different physical rooms, lock them, and insert N rows (multiple operations, higher failure risk even for same room type)

3.7 Pros and Cons

Pros:
✅ Automatic overlap prevention: Database enforces no double-booking at constraint level (for single rooms)
✅ Exact room assignment: Know exactly which room guest will get (Room 101, not "any Deluxe room")
✅ Room-level tracking: Can track maintenance, room-specific issues, preferences
✅ Efficient range queries: GiST index optimized for date range operations
✅ No optimistic locking needed: Exclusion constraint handles single-room concurrency

Cons:
❌ Multi-room booking complexity: Booking N rooms requires finding and locking N different rooms, with retry logic if any room becomes unavailable. Much more complex than Schema 1's atomic UPDATE inventory SET available_rooms = available_rooms - N
❌ Concurrency challenges for multi-room: Between finding available rooms and inserting bookings, another transaction may grab one, requiring full retry
❌ Less flexible: Can't reassign rooms easily (guest booked Room 101, but it needs maintenance)
❌ More complex queries: Need to find available rooms by checking non-overlapping ranges
❌ Room assignment logic: Must decide which available room to assign (first available? best view?)
❌ More dynamic booking rows: For a booking of 3 rooms, creates 3 rows in room_bookings (vs. Schema 1's 1 row in reservation_rooms)
❌ GiST index overhead: Larger index size, more complex query planning
❌ Not fungible: Room 101 and 102 are different, even if same type (like event seats)

4. Comparative Analysis

4.1 Schema Structure

Aspect Schema 1: Row-Per-Day (Fungible) Schema 2: GiST (Individual Rooms)
Inventory Model Fungible (Room 101 = Room 102) Non-fungible (Room 101 ≠ Room 102)
Inventory Table inventory(hotel_id, room_type_id, date) rooms(hotel_id, room_number)
Booking Table reservations + reservation_rooms(room_type_id, num_rooms) room_bookings(room_number, stay_dates)
Date Storage One row per night (DATE) Date range per booking (DATERANGE)
Index Type B-tree GiST
Rows per Booking 1 reservation + N nights 1 room_booking (with date range)

4.2 Query Performance

Operation Schema 1 Schema 2
Check Availability SELECT ... WHERE date IN (...) - O(log n) per date SELECT ... WHERE NOT EXISTS (overlap check) - O(log n) with GiST
Point Query Excellent (direct date lookup) Good (range overlap check)
Range Query Good (multiple point queries) Excellent (single range query)
Find Available Room Count-based (simple) Must query all rooms, check overlaps (more complex)

4.3 Concurrency Control

Aspect Schema 1 Schema 2
Method Optimistic locking (version numbers) Database constraints (GiST exclusion)
Conflict Detection Check version in WHERE clause Constraint violation on overlap
Retry Logic Required (on version mismatch) Required for multi-room (must find N rooms atomically)
Deadlocks No (no long-held locks) Possible (if using FOR UPDATE SKIP LOCKED)
Code Complexity Medium (retry logic for version) High for multi-room (find N rooms, lock, insert N rows, handle failures)
Single Room Atomic UPDATE ... SET available_rooms = available_rooms - 1 Simple: exclusion constraint handles it
Multi-Room Atomic UPDATE ... SET available_rooms = available_rooms - N Complex: Must find N different rooms, lock them, insert N rows

4.4 Update Operations

Operation Schema 1 Schema 2
Create Booking UPDATE inventory (decrement count) INSERT room_booking (with date range)
Cancel Booking UPDATE inventory (increment count) DELETE room_booking or update status
Modify Dates UPDATE multiple inventory rows DELETE + INSERT new date range
Room Reassignment No change needed (fungible) DELETE + INSERT (different room)

4.5 Storage and Scalability

Important Distinction: We must separate static inventory rows from dynamic booking rows.

Metric Schema 1 Schema 2
Static Inventory Rows 365 rows per room type per hotel (scales with booking window) 1 row per physical room (fixed)
Note on "365" This represents a 1-year (365-day) booking window (common in hotels). The number scales linearly: 90-day window = 90 rows, 365-day window = 365 rows. This is configurable based on business needs. Fixed regardless of booking window
Rows per Single-Room Booking 1 reservation + 1 reservation_rooms + N reservation_room_nights 1 reservation + 1 room_booking
Rows per Multi-Room Booking 1 reservation + M reservation_rooms + (M × N) reservation_room_nights
(M = room types, N = nights)		 1 reservation + N room_bookings (one per room)
Index Size ~2MB (B-tree, 100 hotels × 3 types × 365 days) ~200KB (GiST, but more complex)
Scalability Static rows scale with booking window Static rows fixed; dynamic rows scale with bookings

Example Calculation (100 hotels, 3 room types, 10 rooms per type, 365-day window):

  • Schema 1 Static: 100 × 3 × 365 = 109,500 inventory rows (large, but simple queries)
  • Schema 2 Static: 100 × 3 × 10 = 3,000 room rows (smaller, fixed)

Why 365 days? This is a typical booking window for hotels (1 year ahead). The number is configurable:

  • 90-day window: 90 rows per room type (shorter horizon, less storage)
  • 180-day window: 180 rows per room type (6 months ahead)
  • 365-day window: 365 rows per room type (1 year ahead, most common)
  • The number scales linearly: rows = booking_window_days

Example: Booking 3 Deluxe Rooms for 2 Nights:

  • Schema 1 Dynamic: 1 reservation + 1 reservation_rooms + 2 reservation_room_nights = 4 rows
  • Schema 2 Dynamic: 1 reservation + 3 room_bookings = 4 rows (same count, but different structure)

Example: Booking 2 Deluxe + 1 Suite for 2 Nights (multiple room types):

  • Schema 1 Dynamic: 1 reservation + 2 reservation_rooms + (2 × 2) reservation_room_nights = 7 rows
    • 1 reservation header
    • 2 reservation_rooms (one for Deluxe, one for Suite)
    • 4 reservation_room_nights (2 nights × 2 room types, each with potentially different rates)
  • Schema 2 Dynamic: 1 reservation + 3 room_bookings = 4 rows

Key Insight:

  • Schema 1 has more static rows (inventory table) but fewer dynamic rows per multi-room booking (1 reservation_rooms row regardless of N)
  • Schema 2 has fewer static rows (rooms table) but more dynamic rows per multi-room booking (N room_bookings rows for N rooms)

4.6 Flexibility and Use Cases

Requirement Schema 1 Schema 2
Room Reassignment Easy (any room of type works) Hard (must change room_number)
Maintenance Scheduling Flexible (remove from inventory) Must track per-room
Room Preferences Not supported Supported (per-room metadata)
Exact Room Assignment No (assigned at check-in) Yes (assigned at booking)
Boutique Hotels Less suitable More suitable
Standard Hotels More suitable Less suitable

5. Detailed Code Examples

5.1 Availability Check: Schema 1 

def check_availability_schema1(hotel_id, room_type_id, check_in_date, check_out_date, num_rooms=1):
    """
    Check if enough rooms are available for all nights.

    Args:
        num_rooms: Number of rooms needed (default: 1)
    """
    nights = get_nights(check_in_date, check_out_date)  # [2024-06-15, 2024-06-16]

    availability = db.query("""
        SELECT date, available_rooms
        FROM inventory
        WHERE hotel_id = ? 
          AND room_type_id = ?
          AND date IN (?)
          AND available_rooms >= ?
    """, hotel_id, room_type_id, nights, num_rooms)

    # Check if all nights have enough availability
    available_nights = {row.date for row in availability}
    if set(nights) != available_nights:
        missing = set(nights) - available_nights
        raise InsufficientAvailabilityError(
            f"Not enough rooms available on: {missing}. Need {num_rooms} rooms."
        )

    return True
Enter fullscreen mode Exit fullscreen mode

5.2 Availability Check: Schema 2 

def check_availability_schema2(hotel_id, room_type_id, check_in_date, check_out_date, num_rooms=1):
    """
    Check if enough rooms are available for the date range.

    Args:
        num_rooms: Number of rooms needed (default: 1)
    """
    available_count = db.query("""
        SELECT COUNT(*) as count
        FROM rooms r
        WHERE r.hotel_id = ? 
          AND r.room_type_id = ?
          AND NOT EXISTS (
              SELECT 1 FROM room_bookings b
              WHERE b.hotel_id = r.hotel_id
                AND b.room_number = r.room_number
                AND b.stay_dates && daterange(?, ?, '[]')
                AND b.status IN ('pending', 'confirmed', 'checked_in')  -- Include pending
          )
    """, hotel_id, room_type_id, check_in_date, check_out_date)

    if available_count.count < num_rooms:
        raise InsufficientAvailabilityError(
            f"Only {available_count.count} rooms available, need {num_rooms}"
        )

    return True
Enter fullscreen mode Exit fullscreen mode

5.3 Booking Creation: Schema 2 (Full Example)

Note: See Section 3.6 for the complete implementation with both single-room and multi-room booking examples. The key difference is that Schema 2 requires significantly more complex logic for multi-room bookings compared to Schema 1's atomic UPDATE inventory SET available_rooms = available_rooms - N.

Key Points:

  • Single room booking: Relatively straightforward with exclusion constraint
  • Multi-room booking: Requires finding and locking N rooms, with retry logic if any room becomes unavailable
  • See Section 3.6 for the full create_booking_schema2() implementation with num_rooms parameter

6. When to Use Each Schema

Use Schema 1 (Row-Per-Day, Fungible) When:

✅ Standard hotel operations: Rooms of the same type are truly interchangeable
✅ Flexibility needed: Want to reassign rooms easily (maintenance, upgrades)
✅ Simplicity priority: Prefer straightforward queries and standard indexes
✅ High booking volume: Need to handle many concurrent bookings efficiently
✅ Per-night pricing: Different rates per night (weekend vs weekday)
✅ Most hotels: Matches how most hotels actually operate

Example Use Cases:

  • Large chain hotels (Marriott, Hilton)
  • Standard hotel rooms (not unique suites)
  • High-volume booking systems

Use Schema 2 (GiST, Individual Rooms) When:

✅ Unique rooms: Rooms are non-fungible (boutique hotels, unique suites)
✅ Exact room assignment: Need to know which specific room guest will get
✅ Room-level tracking: Need to track maintenance, preferences per room
✅ Database-enforced integrity: Want strongest guarantees against double-booking
✅ Event venues: Similar to fixed-seat event booking (non-fungible)
✅ Lower booking volume: Can handle more complex queries

Example Use Cases:

  • Boutique hotels with unique rooms
  • Luxury resorts with distinct suites
  • Event venues (similar pattern)
  • Hotels where guests request specific rooms

Interview Tidbit: When to Use GiST Indexes

Use GiST when a row-per-entry approach is not scalable or not possible.

Classic Example: Calendar Meeting Booking

Imagine building a calendar system where users can book meetings. You need to check for time slot overlaps (e.g., "Is 2:00 PM - 3:00 PM available?").

❌ Row-Per-Minute Approach (Not Scalable):

-- Bad: One row per minute for every possible time slot
CREATE TABLE time_slots (
    date DATE,
    minute INT,  -- 0 to 1439 (minutes in a day)
    is_booked BOOLEAN
);
-- Problem: 1,440 rows per day × 365 days = 525,600 rows per year per resource
-- For 1000 resources: 525 million rows per year!
Enter fullscreen mode Exit fullscreen mode

✅ GiST Range Approach (Scalable):

-- Good: One row per booking with date range
CREATE TABLE meetings (
    meeting_id UUID PRIMARY KEY,
    resource_id VARCHAR(50),
    meeting_time TSRANGE,  -- Time range: [start_time, end_time)
    EXCLUDE USING GIST (resource_id WITH =, meeting_time WITH &&)
);
-- One row per actual booking - scales with usage, not time granularity
Enter fullscreen mode Exit fullscreen mode

Key Insight:

  • Row-per-entry: Scales with time granularity (minutes, seconds) × duration × resources → exponential growth
  • GiST ranges: Scales with actual bookings → linear growth

Other Use Cases for GiST:

  • Resource booking: Conference rooms, equipment, vehicles
  • IP address ranges: Network allocation, geolocation
  • Geographic data: Spatial queries, map boundaries
  • Time-series overlaps: Scheduling, availability windows

7. Recommendation

For most hotel booking systems, Schema 1 (Row-Per-Day, Fungible) is recommended because:

  1. Matches real-world operations: Most hotels treat rooms of the same type as interchangeable
  2. Simpler queries: Direct date lookups are easier to understand and optimize
  3. Better performance: B-tree indexes are well-understood and perform excellently
  4. More flexible: Can reassign rooms without database changes
  5. Proven pattern: Widely used in production systems

Use Schema 2 (GiST, Individual Rooms) only if:

  • You have unique, non-fungible rooms
  • You need exact room assignment at booking time
  • You're building an event venue system (where this design excels)
  • Room-level tracking is critical

Note: If you need Schema 1's fungible inventory but also want to guarantee no room switching after check-in, see Section 2.6 for a hybrid approach that adds a room assignment table with GiST constraints.

Scaling Beyond a Single Database

If a single database instance cannot sustain throughput/latency requirements:

  1. Vertical scale + read replicas:

    • Keep inventory, reservations, reservation_rooms, and reservation_room_nights on the primary for strong consistency.
    • Serve availability/search queries from read replicas (or a Redis cache) to offload the primary.

  2. Logical partitioning (sharding):

    • Shard by hotel_id (most queries are scoped to a hotel).
    • Each shard owns its own inventory, reservations, room_assignments, etc.
    • Global services (payments, user accounts) remain shared; booking services route to the correct shard via a metadata service.

  3. Regional replication:

    • Deploy per-region databases (still sharded by hotel) with asynchronous replication for cross-region disaster recovery.
    • Keep writes region-local; replicate summary events to a central analytics store.

  4. Streaming + caches:

    • Publish inventory delta events to Kafka/PubSub.
    • Materialize availability views in Redis/Elastic for search so that the OLTP database handles only booking-critical writes.
    • Ensure cache invalidation by listening to the same event stream.

  5. Background reconciliation:

    • Periodically reconcile per-shard inventory totals against master room counts (or via nightly jobs) to catch drift.
    • Use idempotent booking operations so retries across shards are safe.

These patterns let you grow from a single-node deployment to multi-shard, multi-region topologies without rewriting the core schemas.

8. Summary

Decision Factor Winner
Simplicity Schema 1 (Row-Per-Day)
Flexibility Schema 1 (Row-Per-Day)
Database-Enforced Integrity Schema 2 (GiST)
Exact Room Assignment Schema 2 (GiST)
Query Performance Tie (depends on query pattern)
Concurrency Control (Single Room) Schema 2 (GiST) - simpler
Concurrency Control (Multi-Room) Schema 1 (atomic decrement) - much simpler
Static Rows Schema 2 (fewer static rows)
Dynamic Rows (Multi-Room) Schema 1 (1 row per booking vs N rows)
Multi-Room Booking Complexity Schema 1 (atomic decrement vs find/lock N rooms)
Most Hotels Schema 1 (Row-Per-Day)

Final Recommendation: Start with Schema 1 (Row-Per-Day, Fungible) for most hotel booking systems. Consider Schema 2 (GiST, Individual Rooms) only if you have specific requirements for exact room assignment or unique, non-fungible rooms.

Top comments (1)

Collapse
 
youngfra profile image
Fraser Young •

Super insightful breakdown of fungible vs non‑fungible modeling — loved the concrete SQL, GiST deep dive, and the honest take on multi-room complexity. The hybrid “Schema 1 + room assignments” section feels especially practical for real-world hotel setups.