Senior: питання на співбесіді з теми «Проєктування схеми»

Три основні моделі, від найпростішої до найізольованішої.

1. Спільні таблиці з колонкою tenant_id:

CREATE TABLE projects (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL REFERENCES tenants (id),
    name text NOT NULL,
    UNIQUE (tenant_id, name)
);
CREATE INDEX projects_tenant_idx ON projects (tenant_id);

• Плюси: одна схема, прості міграції, ефективне використання ресурсів, легко рахувати аналітику по всіх тенантах.
• Мінуси: головний ризик - забутий фільтр WHERE tenant_id = ?, і один клієнт бачить дані іншого. Захист - глобальні скоупи в застосунку, Row-Level Security у PostgreSQL, тести на ізоляцію. «Галасливий» тенант навантажує всіх.

2. Схема на тенанта (tenant_42.projects):

• Плюси: ізоляція на рівні об'єктів бази - запит без фільтра не побачить чужих даних; легко видалити чи вивантажити дані одного клієнта.
• Мінуси: міграції треба застосовувати до сотень чи тисяч схем; тисячі схем × десятки таблиць - велике навантаження на системний каталог і на pg_dump; пул з'єднань ускладнюється (search_path на кожен запит).

3. Окрема база на тенанта:

• Плюси: максимальна ізоляція, окремі бекапи й відновлення, можна розмістити великого клієнта на окремому сервері, виконати вимоги щодо розташування даних.
• Мінуси: найдорожче в експлуатації: міграції, моніторинг і оновлення для кожної бази; крос-тенантна аналітика вимагає окремого сховища.

Як обирати:

• Багато невеликих клієнтів (SaaS для малого бізнесу) - спільні таблиці + RLS чи надійні глобальні скоупи.
• Помірна кількість клієнтів з вимогами ізоляції - схеми.
• Великі корпоративні клієнти з договірними вимогами - окремі бази (часто гібрид: більшість у спільних таблицях, найбільші - окремо).

Незалежно від моделі: tenant_id у всіх унікальних обмеженнях і індексах; тести, що перевіряють ізоляцію для кожної сутності; продумана поведінка фонових задач і кешу (ключі кешу з ідентифікатором тенанта).

Докладніше в документації: Схеми

Категорії, оргструктура, коментарі з відповідями, меню - дерева. Є кілька моделей, кожна з компромісами між простотою запису й читання.

1. Список суміжності (adjacency list) - parent_id:

CREATE TABLE categories (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parent_id bigint REFERENCES categories (id),
    name text NOT NULL
);

• Найпростіше: вставка й переміщення - зміна одного parent_id, є зовнішній ключ.
• Піддерево чи шлях до кореня - через WITH RECURSIVE. У PostgreSQL це працює добре, тож для більшості задач цього достатньо.

2. Матеріалізований шлях - шлях до вузла зберігається рядком: 1.5.12.

CREATE EXTENSION IF NOT EXISTS ltree;
ALTER TABLE categories ADD COLUMN path ltree;
CREATE INDEX categories_path_gist ON categories USING gist (path);

SELECT * FROM categories WHERE path <@ '1.5';     -- усе піддерево
SELECT * FROM categories WHERE path @> '1.5.12';  -- усі предки

• Піддерево й предки - одним індексованим запитом без рекурсії; глибина - nlevel(path).
• Переміщення вузла - оновлення шляхів усього піддерева.

3. Вкладені множини (nested sets) - кожен вузол має lft і rgt, піддерево - усе в проміжку між ними.

• Дуже швидке читання піддерев і підрахунок нащадків.
• Вставка чи переміщення перераховує межі для великої частини дерева - погано для дерев, що часто змінюються. У Laravel - пакет kalnoy/nestedset.

4. Таблиця замикань (closure table) - окрема таблиця всіх пар «предок - нащадок» з глибиною.

• Будь-які запити до ієрархії - простими JOIN з індексами.
• Таблиця зв'язків росте як O(n × глибина); вставка й переміщення - кілька запитів.

Як обирати:

• Типова ієрархія (категорії, меню) у PostgreSQL - parent_id + WITH RECURSIVE, а за потреби в швидких запитах до піддерев - додати ltree.
• Дерево рідко змінюється, а читається постійно - nested sets чи closure table.
• Дуже глибокі дерева з частими переміщеннями - adjacency list.

Не забути: захист від циклів (вузол не може стати нащадком самого себе) і обмеження глибини для даних від користувачів.

Докладніше в документації: ltree

Події, логи, метрики, кліки мають особливий профіль: лише вставки, рідкісні оновлення, читання переважно свіжих даних, і постійне видалення старих. Звичайна таблиця з таким навантаженням за рік-два стає проблемою.

Принципи:

1. Append-only. Рядки не оновлюються - лише додаються. Немає мертвих версій, менше роботи для вакууму, таблиця залишається компактною. Якщо подію треба «виправити» - нова подія, а не UPDATE.

2. Партиціонування за часом:

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    type text NOT NULL,
    user_id bigint,
    payload jsonb,
    PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

• Видалення старих даних - DROP чи DETACH партиції, миттєво й без навантаження.
• Запити за останні дні читають лише свіжі партиції.
• Партиції створюються наперед (pg_partman чи заплановане завдання).

3. Мінімум індексів. Кожен індекс - ціна кожної вставки. Часто достатньо BRIN за часом і B-tree лише на тих полях, за якими справді шукають окремі записи.

4. Пакетний запис. Не INSERT на кожну подію з веб-запиту, а накопичення в черзі чи буфері й вставка порціями (або COPY). Це в рази зменшує навантаження.

5. Вузькі рядки. Типи з мінімальним розміром, порядок колонок з урахуванням вирівнювання, довідники замість повторюваних рядків, payload у jsonb лише для справді змінних даних.

6. Агрегати окремо. Дашборди читають не сирі події, а попередньо агреговані таблиці (за годину, день), що оновлюються пакетно чи матеріалізованими поданнями.

7. Політика зберігання. Сирі події - 30-90 днів, агрегати - роками, архів - в об'єктному сховищі (Parquet в S3).

Коли PostgreSQL уже не той інструмент: мільярди подій на день і аналітичні запити по всьому обсягу - тут колоночні сховища (ClickHouse, BigQuery) чи розширення на кшталт TimescaleDB дають на порядки кращу швидкість і стиснення.

Докладніше в документації: Партиціонування таблиць

Проблема подвійного запису. Замовлення треба зберегти в базу і повідомити інші системи (черга, брокер повідомлень, вебхук). Це дві різні системи, і атомарно оновити обидві неможливо:

DB::transaction(function () use ($order) {
    $order->save();
});
$broker->publish(new OrderPlaced($order));   // впало тут - подію втрачено назавжди

Якщо поміняти місцями - можна опублікувати подію про замовлення, транзакція якого потім відкотиться.

Transactional outbox: подія записується в ту саму базу й ту саму транзакцію, що й зміна даних, - у спеціальну таблицю:

CREATE TABLE outbox (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    aggregate_type text NOT NULL,
    aggregate_id bigint NOT NULL,
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    published_at timestamptz
);

DB::transaction(function () use ($order) {
    $order->save();
    Outbox::create(['event_type' => 'OrderPlaced', 'payload' => [...]]);
});

Тепер або є і замовлення, і подія, або немає нічого.

Окремий процес-ретранслятор читає неопубліковані записи й надсилає їх у брокер:

SELECT * FROM outbox WHERE published_at IS NULL ORDER BY id LIMIT 100 FOR UPDATE SKIP LOCKED;

Після успішної відправки - позначає published_at (або видаляє запис). Альтернатива опитуванню - читати зміни таблиці з WAL через Change Data Capture (Debezium).

Що з цього випливає:

• Доставка «щонайменше раз»: ретранслятор міг надіслати подію й упасти, не позначивши її. Отримувачі мають бути ідемпотентними (ID події + перевірка, чи вже оброблено).
• Порядок подій зберігається в межах одного агрегата, якщо ретранслятор обробляє їх за id.
• Таблицю outbox треба чистити - вона росте з кожною подією.

У Laravel частково цю задачу вирішують afterCommit для завдань і подій (відправити в чергу лише після успішного коміту), але це не захищає від падіння процесу між комітом і відправкою - для справжньої гарантії потрібен саме outbox.

Докладніше в документації: Pattern: Transactional outbox