System design by example: a booking service and scaling it

-- Measurement: space per bookings row including indexes (100,000 bookings)
INSERT INTO masters (salon_id, name)
SELECT 1, 'Stylist ' || g FROM generate_series(3, 1000) g;

INSERT INTO bookings (salon_id, master_id, service_id, client_phone, during, idempotency_key)
SELECT 1, m, 1, '+7700' || lpad((m * 100 + d)::text, 7, '0'),
       tstzrange(timestamptz '2026-11-01 10:00+05' + (d - 1) * interval '1 day',
                 timestamptz '2026-11-01 11:00+05' + (d - 1) * interval '1 day'),
       gen_random_uuid()
FROM generate_series(1, 1000) m, generate_series(1, 100) d;

VACUUM ANALYZE bookings;
SELECT count(*) AS rows,
       pg_size_pretty(pg_table_size('bookings'))          AS table,
       pg_size_pretty(pg_indexes_size('bookings'))        AS indexes,
       pg_total_relation_size('bookings') / count(*)      AS bytes_per_row
FROM bookings;

  rows  | table | indexes | bytes_per_row
--------+-------+---------+---------------
 100007 | 14 MB | 15 MB   |           293
(1 row)

POST /v1/bookings HTTP/1.1
Host: api.example.com
Content-Type: application/json
Idempotency-Key: 6f1c2a8e-0000-4000-8000-000000000001

{"salon_id": 1, "master_id": 1, "service_id": 1,
 "starts_at": "2026-10-20T10:00:00+05:00", "client_phone": "+77000000001"}

# the slot is free
HTTP/1.1 201 Created
Location: /v1/bookings/1

# the slot is already taken
HTTP/1.1 409 Conflict
Content-Type: application/problem+json

{"type": "https://api.example.com/problems/slot-taken",
 "title": "This time is already taken", "status": 409}

# the sql/ directory holds the lesson files; the password test is for a throwaway local container only
docker run -d --rm --name kb-booking-pg -e POSTGRES_PASSWORD=test \
  -v "$PWD/sql:/sql" postgres:17-alpine

docker exec -i -e PGTZ=Asia/Almaty kb-booking-pg psql -U postgres -v ON_ERROR_STOP=1 -f /sql/01-schema.sql
docker exec -i -e PGTZ=Asia/Almaty kb-booking-pg psql -U postgres -v ON_ERROR_STOP=1 -f /sql/02-seed.sql

# interactive sessions for the concurrency experiments, each in its own terminal
docker exec -it -e PGTZ=Asia/Almaty kb-booking-pg psql -U postgres

-- Data model of the booking service (PostgreSQL 17)
CREATE EXTENSION IF NOT EXISTS btree_gist;  -- "=" on bigint inside a GiST constraint

CREATE TABLE salons (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  tz   text NOT NULL DEFAULT 'Asia/Almaty'
);

CREATE TABLE masters (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  salon_id bigint NOT NULL REFERENCES salons (id),
  name     text NOT NULL
);

CREATE TABLE services (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  salon_id bigint NOT NULL REFERENCES salons (id),
  name     text NOT NULL,
  duration interval NOT NULL CHECK (duration > interval '0')
);

CREATE TABLE bookings (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  salon_id        bigint NOT NULL REFERENCES salons (id),
  master_id       bigint NOT NULL REFERENCES masters (id),
  service_id      bigint NOT NULL REFERENCES services (id),
  client_phone    text NOT NULL,
  during          tstzrange NOT NULL CHECK (NOT isempty(during)),
  status          text NOT NULL DEFAULT 'confirmed'
                  CHECK (status IN ('confirmed', 'cancelled')),
  idempotency_key uuid NOT NULL UNIQUE,
  created_at      timestamptz NOT NULL DEFAULT now(),
  -- one staff member never has two confirmed bookings that overlap in time
  CONSTRAINT bookings_no_overlap
    EXCLUDE USING gist (master_id WITH =, during WITH &&)
    WHERE (status = 'confirmed')
);

-- Outgoing events: written in the same transaction as the booking
CREATE TABLE outbox (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  topic        text NOT NULL,
  payload      jsonb NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  processed_at timestamptz
);
CREATE INDEX outbox_pending ON outbox (id) WHERE processed_at IS NULL;

INSERT INTO salons (name) VALUES ('Example Salon');
INSERT INTO masters (salon_id, name) VALUES (1, 'Stylist 1'), (1, 'Stylist 2');
INSERT INTO services (salon_id, name, duration) VALUES (1, 'Haircut', interval '60 minutes');

-- Booking: the row and the notification event in one transaction
BEGIN;
WITH b AS (
  INSERT INTO bookings (salon_id, master_id, service_id, client_phone, during, idempotency_key)
  VALUES (1, 1, 1, '+77000000001',
          tstzrange('2026-10-20 10:00+05', '2026-10-20 11:00+05'),
          '6f1c2a8e-0000-4000-8000-000000000001')
  ON CONFLICT (idempotency_key) DO NOTHING
  RETURNING id, master_id, during
)
INSERT INTO outbox (topic, payload)
SELECT 'booking.created',
       jsonb_build_object('booking_id', id, 'master_id', master_id, 'starts_at', lower(during))
FROM b;
COMMIT;

$ docker exec -i -e PGTZ=Asia/Almaty kb-booking-pg psql -U postgres -f /sql/03-book.sql   # first request
BEGIN
INSERT 0 1
COMMIT
$ docker exec -i -e PGTZ=Asia/Almaty kb-booking-pg psql -U postgres -f /sql/03-book.sql   # retry with the same key
BEGIN
INSERT 0 0
COMMIT

-- Session A: booking for 12:00–13:00, the transaction stays open
A=# BEGIN;
BEGIN
A=# WITH b AS (INSERT INTO bookings … '2026-10-20 12:00+05', '2026-10-20 13:00+05' …) INSERT INTO outbox …;
INSERT 0 1
A=# SELECT pg_sleep(4);
-- One second later, session B: same staff member, 12:30–13:30
B=# \timing on
B=# BEGIN;
BEGIN
B=# WITH b AS (INSERT INTO bookings … '2026-10-20 12:30+05', '2026-10-20 13:30+05' …) INSERT INTO outbox …;
-- B waits. Session C checks who it is waiting for:
C=# SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by
C-#   FROM pg_stat_activity WHERE wait_event_type = 'Lock';
 pid | wait_event_type |  wait_event   | blocked_by
-----+-----------------+---------------+------------
 113 | Lock            | transactionid | {106}
(1 row)
-- A's pause is over
A=# COMMIT;
COMMIT
-- B gets its answer:
ERROR:  conflicting key value violates exclusion constraint "bookings_no_overlap"
DETAIL:  Key (master_id, during)=(1, ["2026-10-20 12:30:00+05","2026-10-20 13:30:00+05")) conflicts with existing key (master_id, during)=(1, ["2026-10-20 12:00:00+05","2026-10-20 13:00:00+05")).
Time: 3063.304 ms (00:03.063)
B=# \echo :LAST_ERROR_SQLSTATE
23P01
B=# ROLLBACK;
ROLLBACK

=# SELECT id, client_phone, during FROM bookings WHERE master_id = 1 ORDER BY id;
 id | client_phone |                       during
----+--------------+-----------------------------------------------------
  1 | +77000000001 | ["2026-10-20 10:00:00+05","2026-10-20 11:00:00+05")
  3 | +77000000002 | ["2026-10-20 12:00:00+05","2026-10-20 13:00:00+05")
(2 rows)
=# SELECT id, topic, payload FROM outbox ORDER BY id;
 id |      topic      |                                   payload
----+-----------------+-----------------------------------------------------------------------------
  1 | booking.created | {"master_id": 1, "starts_at": "2026-10-20T10:00:00+05:00", "booking_id": 1}
  2 | booking.created | {"booking_id": 3}
(2 rows)

-- Trap: ON CONFLICT without a target also swallows the EXCLUDE violation
INSERT INTO bookings (salon_id, master_id, service_id, client_phone, during, idempotency_key)
VALUES (1, 1, 1, '+77000000004', tstzrange('2026-10-20 12:15+05', '2026-10-20 13:15+05'),
        '6f1c2a8e-0000-4000-8000-00000000000c')
ON CONFLICT DO NOTHING;

INSERT INTO bookings (salon_id, master_id, service_id, client_phone, during, idempotency_key)
VALUES (1, 1, 1, '+77000000004', tstzrange('2026-10-20 12:15+05', '2026-10-20 13:15+05'),
        '6f1c2a8e-0000-4000-8000-00000000000c')
ON CONFLICT (idempotency_key) DO NOTHING;

-- without a conflict target: the overlap is silently swallowed
INSERT 0 0
-- with a target (idempotency_key): the EXCLUDE violation surfaces
ERROR:  conflicting key value violates exclusion constraint "bookings_no_overlap"
DETAIL:  Key (master_id, during)=(1, ["2026-10-20 12:15:00+05","2026-10-20 13:15:00+05")) conflicts with existing key (master_id, during)=(1, ["2026-10-20 12:00:00+05","2026-10-20 13:00:00+05")).

-- One outbox worker pass: claim a batch, publish to the queue, confirm
BEGIN;
WITH batch AS (
  SELECT id
  FROM outbox
  WHERE processed_at IS NULL
  ORDER BY id
  LIMIT 100
  FOR UPDATE SKIP LOCKED   -- skip rows another worker has claimed
)
UPDATE outbox o
SET processed_at = now()
FROM batch
WHERE o.id = batch.id
RETURNING o.id, o.topic, o.payload;
-- the application publishes the returned events to the queue:
-- success → COMMIT, failure → ROLLBACK (the rows become available again)
COMMIT;

-- Worker 1: batch of 2, holds its transaction for 3 seconds
 id |      topic
----+-----------------
  1 | booking.created
  2 | booking.created
(2 rows)
-- Worker 2, started a second later: does not wait, takes the next rows
 id |      topic
----+------------------
  3 | booking.reminder
  4 | booking.reminder
(2 rows)

-- Free start times for a 60-minute service with staff member 1 on 20 October (30-minute step)
SELECT to_char(s, 'HH24:MI') AS starts_at
FROM generate_series(timestamptz '2026-10-20 10:00+05',
                     timestamptz '2026-10-20 18:00+05',
                     interval '30 minutes') AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM bookings b
  WHERE b.master_id = 1
    AND b.status = 'confirmed'
    AND b.during && tstzrange(s, s + interval '60 minutes')
)
ORDER BY s;

 starts_at
-----------
 11:00
 11:30
 13:30
 14:00
 14:30
 15:00
 15:30
 16:00
 16:30
 17:00
 17:30
 18:00
(12 rows)

-- 1) Partitioning by time: the "no overlap" constraint cannot be declared this way
CREATE TABLE bookings_by_month (
  salon_id  bigint NOT NULL,
  master_id bigint NOT NULL,
  starts_at timestamptz NOT NULL,
  during    tstzrange NOT NULL,
  EXCLUDE USING gist (master_id WITH =, during WITH &&)
) PARTITION BY RANGE (starts_at);

-- 2) Partitioning by salon (tenant): the key is part of the constraint via "="
CREATE TABLE bookings_by_salon (
  salon_id  bigint NOT NULL,
  master_id bigint NOT NULL,
  during    tstzrange NOT NULL,
  EXCLUDE USING gist (salon_id WITH =, master_id WITH =, during WITH &&)
) PARTITION BY HASH (salon_id);
CREATE TABLE bookings_by_salon_0 PARTITION OF bookings_by_salon FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE bookings_by_salon_1 PARTITION OF bookings_by_salon FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE bookings_by_salon_2 PARTITION OF bookings_by_salon FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE bookings_by_salon_3 PARTITION OF bookings_by_salon FOR VALUES WITH (MODULUS 4, REMAINDER 3);

INSERT INTO bookings_by_salon VALUES (7, 1, tstzrange('2026-10-20 10:00+05', '2026-10-20 11:00+05'));
INSERT INTO bookings_by_salon VALUES (7, 1, tstzrange('2026-10-20 10:30+05', '2026-10-20 11:30+05'));

-- 1) bookings_by_month
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  EXCLUDE constraint on table "bookings_by_month" lacks column "starts_at" which is part of the partition key.
-- 2) bookings_by_salon: table and 4 partitions created, first booking: INSERT 0 1, second:
ERROR:  conflicting key value violates exclusion constraint "bookings_by_salon_3_salon_id_master_id_during_excl"
DETAIL:  Key (salon_id, master_id, during)=(7, 1, ["2026-10-20 10:30:00+05","2026-10-20 11:30:00+05")) conflicts with existing key (salon_id, master_id, during)=(7, 1, ["2026-10-20 10:00:00+05","2026-10-20 11:00:00+05")).

-- "Outbox lag" metric: how many events are waiting and how long the oldest has waited
SELECT count(*)                         AS pending,
       coalesce(now() - min(created_at), interval '0') AS oldest_age
FROM outbox
WHERE processed_at IS NULL;

 pending |   oldest_age
---------+----------------
       1 | 00:00:03.34143
(1 row)