Skip to content

ADR-001: PostgreSQL over NoSQL for Appointment Bookings

Status: Accepted

Context

The Booking Service must store and manage appointment records for up to 50 million patients and 500,000 practitioners. At peak load the system handles 90 bookings/second, each of which involves reading a practitioner's availability, creating a booking, and atomically preventing any concurrent booking of the same slot.

The core requirement is strict consistency on the write path: two patients attempting to book the same practitioner at the same slot must result in exactly one confirmed booking and one failure — with no possibility of both succeeding.

The options evaluated were:

  1. Amazon RDS Aurora PostgreSQL (relational, ACID, strong consistency)
  2. Amazon DynamoDB (NoSQL, eventual consistency by default, optional strong reads)
  3. MongoDB Atlas (document store, tunable consistency)

Decision

Use Amazon RDS Aurora PostgreSQL for the Booking, Practitioner, and Patient services.

Double-booking prevention is implemented with a unique partial index:

CREATE UNIQUE INDEX idx_no_double_book
  ON bookings (practitioner_id, slot_start)
  WHERE status = 'confirmed';

This index makes it physically impossible at the storage engine level for two rows with the same (practitioner_id, slot_start) to both have status = 'confirmed'. The database enforces correctness without requiring application-level distributed locks.

Consequences

Positive:

  • Double-booking prevention is a single database primitive — no distributed lock infrastructure to operate or reason about.
  • ACID transactions allow cancellation and rescheduling to be atomic (cancel old + create new in one transaction).
  • country_code partitioning provides multi-country data isolation at the storage level without separate clusters.
  • Aurora PostgreSQL is a fully managed service: automated backups, Multi-AZ failover, storage auto-scaling, and Secrets Manager credential rotation are all built-in.
  • Reader replicas are available for reporting queries without impacting the write path.

Negative:

  • Schema migrations require coordination (addressed by the DB migration pipeline — see CI/CD).
  • Horizontal write scaling is limited to vertical instance sizing and connection pooling; the expected 90 bookings/sec is well within a db.r6g.xlarge writer's capacity.
  • NoSQL alternatives (DynamoDB) would offer near-unlimited write throughput, but replicating the double-booking guarantee would require a conditional write pattern (ConditionExpression) that is less ergonomic and harder to audit.

Trade-off accepted: PostgreSQL's write throughput ceiling is not a constraint at the projected load. The operational simplicity and correctness guarantees of a unique index outweigh the theoretical write scalability ceiling of a relational database.