Senior: питання на співбесіді з теми «Експлуатація БД»

Головна небезпека не в тривалості самої зміни, а в блокуваннях. ALTER TABLE у PostgreSQL бере ACCESS EXCLUSIVE-блокування. Якщо в цей момент іде довгий запит, ALTER стає в чергу - і всі нові запити до таблиці стають у чергу за ним. Сайт лягає, хоча сама операція займає мілісекунди.

Правила, що рятують:

SET lock_timeout = '3s';       -- краще впасти й повторити, ніж повісити таблицю
SET statement_timeout = '60s';
ALTER TABLE orders ADD COLUMN note text;

Що дешево, а що ні (PostgreSQL):

• Додати колонку без значення за замовчуванням чи з незмінним default (PostgreSQL 11+) - миттєво, лише метадані.
• Додати NOT NULL до наявної колонки - повна перевірка таблиці під блокуванням. Безпечніше: CHECK (col IS NOT NULL) NOT VALID, потім VALIDATE CONSTRAINT (без блокування запису), потім SET NOT NULL (PostgreSQL 12+ використає перевірене обмеження).
• Зовнішній ключ - так само: NOT VALID, потім VALIDATE.
• Змінити тип колонки - часто переписування всієї таблиці. Краще нова колонка, поступове заповнення, перемикання.
• Індекс - лише CONCURRENTLY.

Перейменування й видалення - через «розширити, потім звузити» (expand/contract):

1. Додати нову колонку, код пише в обидві.
2. Перенести старі дані порціями.
3. Код читає з нової.
4. Окремим деплоєм прибрати стару колонку.

Так кожен крок сумісний і з попередньою, і з наступною версією коду, і деплой можна відкотити.

MySQL: багато змін виконуються online (ALGORITHM=INSTANT для додавання колонки з 8.0), для решти - gh-ost чи pt-online-schema-change.

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

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

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL,
    payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Коли це виправдано:

• Дані «старіють»: логи, події, метрики, де запити дивляться на останні тижні, а старе треба видаляти. Видалити місяць - DROP TABLE events_2025_10 (мить), а не DELETE мільйонів рядків з навантаженням на вакуум.
• Запити майже завжди фільтрують за ключем партиціонування - тоді спрацьовує partition pruning: база читає лише потрібні частини.
• Таблиця настільки велика, що індекси не вміщаються в пам'ять, а обслуговування (вакуум, перебудова індексу) окремих частин значно легше.

Коли НЕ варто: таблиця на кілька мільйонів рядків, з якою добре справляються індекси. Партиціонування - не заміна індексам.

Обмеження й пастки:

• Запит без ключа партиціонування в умові читає всі частини - часто повільніше, ніж одна таблиця.
• Первинний ключ і унікальні обмеження мусять містити ключ партиціонування: PRIMARY KEY (id, created_at).
• Частини треба створювати заздалегідь (cron чи розширення pg_partman), інакше вставка в майбутній місяць упаде - або потрапить у DEFAULT-партицію.

Партиціонування ≠ шардинг: частини живуть на одному сервері. Розподіл по серверах - окреме, набагато складніше рішення.

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

Failover - підвищення репліки до нового primary, коли старий недоступний. Сам PostgreSQL уміє лише виконати підвищення за командою (pg_promote()); вирішувати, коли це робити, має зовнішній інструмент.

Інструменти: Patroni (найпоширеніший), pg_auto_failover, repmgr, Stolon; у хмарі - керовані сервіси (RDS Multi-AZ, Cloud SQL HA), які роблять це самі.

Як працює Patroni:

• Кожен вузол PostgreSQL має агента Patroni.
• Хто зараз лідер, записано в розподіленому сховищі консенсусу (etcd, Consul, ZooKeeper) з обмеженим терміном оренди.
• Лідер постійно поновлює оренду. Якщо він зник і оренда спливла, агенти реплік обирають нового лідера - зазвичай найменш відсталу репліку, - і та підвищується.
• Застосунок підключається через точку доступу, що завжди веде до поточного лідера: HAProxy з перевіркою стану, віртуальний IP чи DNS.

Split-brain - два вузли одночасно вважають себе primary і обидва приймають записи. Дані розходяться, і злити їх автоматично неможливо. Типовий сценарій: мережа між старим primary і рештою розірвалася, решта обрала нового лідера, а старий продовжує працювати для частини клієнтів.

Як захищаються:

• Консенсус і кворум: лідером може бути лише той, хто тримає оренду в сховищі консенсусу; вузол, що втратив зв'язок з більшістю, сам знижується до репліки.
• Fencing («огорожа»): гарантовано зупинити старий primary (вимкнути, відрізати від мережі, watchdog), перш ніж підвищувати новий.
• Синхронна реплікація - щоб при перемиканні не втратити підтверджені транзакції.

Що ще враховувати:

• Асинхронна реплікація = можлива втрата останніх транзакцій при failover. Скільки - визначає затримка реплікації.
• Застосунок має переживати перемикання: повторні підключення, повтори транзакцій, короткі таймаути з'єднань.
• Регулярні навчання: failover, який ніколи не перевіряли, у критичний момент зазвичай не спрацьовує.

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

Row-Level Security (RLS) - політики на рівні бази, що визначають, які рядки таблиці бачить і може змінювати роль. Фільтр додається до кожного запиту автоматично - навіть якщо в коді його забули.

Ізоляція тенантів:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;   -- діє й на власника таблиці

CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

• USING - які рядки видно (SELECT, UPDATE, DELETE).
• WITH CHECK - які рядки дозволено записати (INSERT, UPDATE): не можна вставити рядок з чужим tenant_id.

Застосунок на початку кожного запиту чи транзакції встановлює тенанта:

BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT * FROM projects;   -- лише проєкти тенанта 42, без WHERE у коді
COMMIT;

Чому це цінно: у мультитенантному застосунку найнебезпечніший баг - запит без фільтра за тенантом. З RLS такий запит поверне не чужі дані, а лише дані поточного тенанта (або нічого, якщо тенант не встановлений).

Підводні камені:

• Суперкористувачі й ролі з BYPASSRLS політики ігнорують, а власник таблиці - теж, без FORCE ROW LEVEL SECURITY. Застосунок має працювати від звичайної ролі.
• Пул з'єднань: з PgBouncer у режимі transaction pooling - лише SET LOCAL усередині транзакції. SET без LOCAL залишить тенанта в з'єднанні, яке потім отримає інший запит - найгірший можливий сценарій.
• Продуктивність: умова політики додається до кожного запиту - потрібен індекс на tenant_id, а функції в політиці мають бути простими.
• Фонові задачі й адмінка мають явно працювати або в контексті тенанта, або від окремої ролі з обґрунтованим обходом.
• Налагодження складніше: «чому запит нічого не повертає» часто означає, що не встановлено змінну тенанта.

RLS - сильний додатковий рівень захисту, але не заміна перевіркам у застосунку.

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

DELETE FROM events WHERE created_at < '2025-01-01' на 50 мільйонах рядків в одній транзакції:

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

Правильно - порціями:

-- повторювати, доки видаляється хоч щось
DELETE FROM events
WHERE id IN (
    SELECT id FROM events
    WHERE created_at < '2025-01-01'
    ORDER BY id
    LIMIT 10000
);

Кожна порція - коротка транзакція. Між порціями - невелика пауза, щоб вакуум і репліки встигали. У Laravel - команда з циклом і ->limit(10000)->delete() чи chunkById з видаленням.

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

Ще краще - не видаляти рядки взагалі. Якщо дані регулярно видаляються за віком (логи, події, метрики), таблицю варто партиціонувати за часом. Тоді видалення старого місяця - це:

ALTER TABLE events DETACH PARTITION events_2024_12;
DROP TABLE events_2024_12;

Миттєво, без мертвих рядків, без навантаження на вакуум і без величезного WAL.

Якщо треба видалити більшість таблиці (залишити 5%), швидше скопіювати потрібні рядки в нову таблицю, перейменувати й видалити стару - але це потребує вікна обслуговування й уваги до зовнішніх ключів і прав.

Після масового видалення: VACUUM (звичайний) звільнить місце для повторного використання, але файл таблиці не зменшиться. Повернути місце ОС без довгого ексклюзивного блокування допоможе pg_repack; VACUUM FULL блокує таблицю на весь час роботи.

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