System design на примере: сервис записи и масштабирование

-- Замер: сколько места занимает строка bookings вместе с индексами (100 000 записей)
INSERT INTO masters (salon_id, name)
SELECT 1, 'Мастер ' || 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"}

# слот свободен
HTTP/1.1 201 Created
Location: /v1/bookings/1

# слот уже занят
HTTP/1.1 409 Conflict
Content-Type: application/problem+json

{"type": "https://api.example.com/problems/slot-taken",
 "title": "Это время уже занято", "status": 409}

# каталог sql/ с файлами урока; пароль test — только для одноразового локального контейнера
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

# интерактивные сессии для опытов с конкуренцией — каждая в своём терминале
docker exec -it -e PGTZ=Asia/Almaty kb-booking-pg psql -U postgres

-- Модель данных сервиса записи (PostgreSQL 17)
CREATE EXTENSION IF NOT EXISTS btree_gist;  -- «=» по bigint внутри GiST-ограничения

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(),
  -- у одного мастера не бывает двух подтверждённых записей с пересечением времени
  CONSTRAINT bookings_no_overlap
    EXCLUDE USING gist (master_id WITH =, during WITH &&)
    WHERE (status = 'confirmed')
);

-- Исходящие события: пишутся в той же транзакции, что и запись
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 ('Салон «Пример»');
INSERT INTO masters (salon_id, name) VALUES (1, 'Мастер 1'), (1, 'Мастер 2');
INSERT INTO services (salon_id, name, duration) VALUES (1, 'Стрижка', interval '60 minutes');

-- Бронирование: запись и событие для уведомлений — одной транзакцией
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   # первый запрос
BEGIN
INSERT 0 1
COMMIT
$ docker exec -i -e PGTZ=Asia/Almaty kb-booking-pg psql -U postgres -f /sql/03-book.sql   # повтор с тем же ключом
BEGIN
INSERT 0 0
COMMIT

-- Сессия A: запись на 12:00–13:00, транзакция остаётся открытой
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);
-- Через секунду сессия B: тот же мастер, 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 ждёт. Сессия C смотрит, кого именно:
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 закончилась
A=# COMMIT;
COMMIT
-- B дождалась ответа:
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)

-- Ловушка: ON CONFLICT без цели глушит и нарушение EXCLUDE
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;

-- без цели конфликта: пересечение молча проглочено
INSERT 0 0
-- с целью (idempotency_key): нарушение EXCLUDE видно
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")).

-- Один проход воркера outbox: забрать пачку, опубликовать в очередь, подтвердить
BEGIN;
WITH batch AS (
  SELECT id
  FROM outbox
  WHERE processed_at IS NULL
  ORDER BY id
  LIMIT 100
  FOR UPDATE SKIP LOCKED   -- строки, занятые другим воркером, пропускаем
)
UPDATE outbox o
SET processed_at = now()
FROM batch
WHERE o.id = batch.id
RETURNING o.id, o.topic, o.payload;
-- приложение публикует полученные события в очередь:
-- успех → COMMIT, ошибка → ROLLBACK (строки снова станут доступны)
COMMIT;

-- Воркер 1: пачка по 2, держит транзакцию 3 секунды
 id |      topic
----+-----------------
  1 | booking.created
  2 | booking.created
(2 rows)
-- Воркер 2, запущен через секунду: не ждёт, берёт следующие строки
 id |      topic
----+------------------
  3 | booking.reminder
  4 | booking.reminder
(2 rows)

-- Свободные начала услуги длительностью 60 минут у мастера 1 на 20 октября (шаг 30 минут)
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) Секционирование по времени: ограничение «без пересечений» так не объявить
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) Секционирование по салону (арендатору): ключ входит в ограничение через «=»
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: таблица и 4 секции созданы, первая запись — INSERT 0 1, вторая:
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»: сколько событий ждёт и сколько ждёт самое старое
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)