When 500 travelers simultaneously click "Book" on the last 3 rooms for a holiday weekend while 9,000 search QPS runs multi-table availability joins, a single relational database melts down. Slow search queries starve the connection pool, concurrent checkouts deadlock on overlapping dates, and naive hold timers risk billing users after their room expires.
Hospitality traffic follows a steep 1,000:1 search-to-booking funnel, keeping search and checkout fast requires splitting the architecture across a strict CQRS boundary:
๐ญ. ๐ฅ๐ฒ๐ฎ๐ฑ ๐ฃ๐ฎ๐๐ต (9,000 Peak QPS, p95 < 200ms):
Client -> API Gateway -> BFF -> Search Service -> Elasticsearch (Geo & Facet Filters) -> Redis (Room Availability Counters) + Rate Service
๐ฎ. ๐ช๐ฟ๐ถ๐๐ฒ & ๐ฆ๐๐ป๐ฐ ๐ฃ๐ฎ๐๐ต (300 Peak Hold QPS, p95 < 500ms):
Client -> API Gateway (Idempotency Lease) -> Booking Service -> MySQL Primary (Inventory Lock + Outbox) + SQS (10-Min Hold Timer) -> CDC Stream -> Kafka -> Redis & Elasticsearch
The full two-tier idempotency design, the search pagination vs. sold-out rooms dilemma, and the 10x database sharding strategy are covered in the complete system design write-up here:
https://engineering.by-tech.workers.dev/articles/hotel-booking-system-design/
Even after separating reads from writes, three edge cases will still break checkout in production:
๐ญ. ๐ง๐ต๐ฒ ๐ ๐๐น๐๐ถ-๐ก๐ถ๐ด๐ต๐ ๐๐ฒ๐ฎ๐ฑ๐น๐ผ๐ฐ๐ธ
If User A locks Dec 20 then Dec 21 while User B locks Dec 21 then Dec 20, the database deadlocks. Instead of tracking individual physical rooms, we store one daily counter per room type and always lock rows in strict chronological order (Dec 20 -> Dec 21), keeping lock times under 5ms
๐ฎ. ๐ง๐ต๐ฒ ๐๐ฎ๐๐-๐ฆ๐ฒ๐ฐ๐ผ๐ป๐ฑ ๐ฃ๐ฎ๐๐บ๐ฒ๐ป๐ ๐ฅ๐ฎ๐ฐ๐ฒ
If a guest clicks "Pay" at minute 9:59 right as the 10-minute hold timer expires, the timer might release the room while the bank charges their card. To prevent charging for a lost room, checkout atomically flips the booking status to PAYMENT_PROCESSING before calling the payment gatewayโwhich tells the background timer to back off.
๐ฏ. ๐ง๐ต๐ฒ ๐๐ถ๐๐๐ฟ๐ถ๐ฏ๐๐๐ฒ๐ฑ ๐๐ผ๐ฐ๐ธ ๐ง๐ฎ๐
Putting Bookings and Room Inventory in separate microservices with separate databases forces you into slow two-phase commits during checkout. Keeping reservations and daily inventory counters (73M rows for 2 years = 7.3 GB in RAM) in the same MySQL database lets a single local ACID transaction secure the hold in 2ms.
SystemDesign #DistributedSystems #BackendEngineering #MySQL #Kafka
Top comments (0)