DB 병목을 나누는 시스템 설계

공연 예약 시스템

Yunho Kee

[Bay Area K Group] 스터디 클럽++

2025-03-29

기능적 시스템 요구사항 분석

필수 기능

사용자가 직접 사용할 기능

  • 자원 현황 검색
  • 자원 현황 조회
  • 실시간 자원 선점

부가 기능

우선 순위 질문 후 폐기

  • 자원 생성/변경/삭제
  • 이전 현황 검색/조회
  • 사용자별 자원 조회
  • 시세 변동

비기능적 시스템 요구사항 분석

특수 제약

어떤 성능이 관건일까?

  • CAP Theorem
    • Consistency:
      Concurrent Booking
    • Availability:
      Live View
  • Read/Write Ratio: Anonymous Guests
  • Bursts: Popular Events

선택 목표

다다익선

  • Security, i.e. Authentication
  • CI/CD Pipelines
  • Backups
  • General Data Protection Regulation
  • Fault Tolerance

Functional High-Level Design

Functional Core Entities

  1. User: Dummy
  2. Venue: 장소 ↔︎ Seat Map
  3. Event: 시간, 장소
    • Ticket[]
  4. Ticket: 시간별, 장소별 Seat Map 자원
  5. Performer: Dummy
  6. Booking: Dummy
    • Ticket[]

Functional API

  1. GET /events/search
    → Event[] 
    •   ?keyword=Tailor  
  &start=2025-03-01&end=2025-04-01  
  &pageSize=10&page=1
  2. GET /events/:eventId
    → Event, Venue, Performer, Ticket[]
  1. POST /bookings/:eventId
    → bookingId 
    •   {
    "ticketIds": string[], 
    "paymentDetails": ...
  }

Functional High-Level Design

  1. Single Scalable API Gateway
    • Routing/Forwarding
    • Rate Limit
    • Authentication
    • Logging
  2. Microservices
    • One Application Service per One API
  1. (Sidecars of Load Balancers)
    • Horizontal Scale Out
    • Least Connections
    • Round Robin
  1. Familiar DBMS for Persistence and Consistency
    • RDBMS, e.g. PostgreSQL
    • NoSQL, e.g. DynamoDB
  2. External API for Payment
    • Asynchronous Webhooks

Lost Updates Prevention

Transaction A

Read AVAILABLE



Write BOOKED
Commit

Transaction B


Read AVAILABLE Write BOOKED
Commit

  1. SELECT ... FOR UPDATE
  • Isolation Level
    1. SERIALIZABLE, REPETABLE READ 1
    2. READ UNCOMMITTED 2 
      • UPDATE ... WHERE BOOKED=FALSE

Non-Functional Deep Dives

1. Payment Availability: Move Lock Up and Hold Longer

  1. Brute Force without Database Column Addition
    • No Native Timeout for PostgreSQL? 1
  • Database Column Addition: Expiration Time
    1. Cron: Periodic Expiration
      • Additional Dependency aside from Database
    2. Lazy Expiration
  1. (Atomic) Cache: Natural Expiration of Time To Live
    • Additional Dependency aside from Database
    • RedLock: Scalable Distributed Locks

2. Live View Availability

  1. Long Polling
  2. Server-Sent Events
    • Unidirectional
  3. WebSockets
    • Bidirectional, Full-Duplex
    1. (Virtual) Waiting Queue
      • Dequeue Timing
        • Metadata

3. Common Read Performance

  1. Cache: Redis, MemCached
    • Read-Through Cache Strategy
      • Sync Triggers
    • Individual TTLs
    • Content Distribution Networks
      • AWS CloudFront
  1. Index
    • Compound Index
      • Columns to Rows
    • Partial Index
    • Covering Index
  2. Materialized View
  1. SQL
    • EXPLAIN: Query Execution Plan Analysis
    • Avoidance of SELECT *
    • LIMIT, Pagination
    • UNION instead of OR

4. Search Performance

1. Full-Table Scan Latency

%LIKE%

  • Full-Text Search
    • Inverted Indexes
      • Keywords to Rows
      • Fuzzy Search
    • Database Indexes
    • ElasticSearch
      • Change Data Capture

2. Frequent Queries

  • ElasticSearch/OpenSearch
    • Node Level Filter
      • Least Recently Used Cache
    • Shard Level Aggregation
      • Adaptive Caching Strategies

References

Kang, Sihoo. 2024. “(데이터베이스) Lost update와 serializable.” July 14, 2024. https://mirrorofcode.tistory.com/416.
King, Evan. n.d. “Design a Ticket Booking Site Like Ticketmaster.” Accessed March 27, 2025. https://www.hellointerview.com/learn/system-design/problem-breakdowns/ticketmaster.
Mihalcea, Vlad. 2023. “Minimum Transaction Isolation Level to Avoid "Lost Updates".” October 7, 2023. https://stackoverflow.com/a/77249495.
Ringer, Craig. 2014. “Controlling Duration of PostgreSQL Lock Waits.” January 7, 2014. https://stackoverflow.com/a/20963803.
Ullman, J. D., and J. Widom. 2014. A First Course in Database Systems. Always Learning. Pearson. https://books.google.co.kr/books?id=9vNgngEACAAJ.