SQL: схема і зміни
Навіщо це
Section titled “Навіщо це”Реліз «Крамниці» містить міграцію з одного рядка: ALTER TABLE orders ADD COLUMN gift_wrap boolean NOT NULL DEFAULT false. На тестовій базі вона виконується за три мілісекунди.
У продакшні в цей момент аналітик тримає відкриту транзакцію зі звітом. Міграція стає в чергу за нею, а всі запити до orders стають у чергу за міграцією. Навіть вибірка одного замовлення за ключем. Сам ALTER нічого не робить довго, він просто не може почати, і сайт стоїть, доки звіт не завершиться.
Але почнемо з типів і обмежень, бо міграції найчастіше виправляють саме їх. Гроші у float чи час без часового поясу не ламаються одразу: помилка накопичується в даних, і виправляти її доводиться під навантаженням, тією самою міграцією.
Передумови. Запити й NULL: модуль 3. Ключі й обмеження цілісності: модуль 2. Версії й блокування рядків: модуль 10; блокування таблиць пояснено нижче. Числа з датасету в модулі виміряно на розмірі small, див. Датасет.
Типи даних
Section titled “Типи даних”Числа і гроші
Section titled “Числа і гроші”float8 (double precision) зберігає число двійковим дробом, а 0.1 у двійковій системі нескінченний, як 1/3 у десятковій. Похибка мала, але справжня:
float_sum | float_eq | numeric_sum | float_million---------------------+----------+-------------+--------------------- 0.30000000000000004 | f | 0.3 | 100000.00000133288У float4 сума цін 3500 товарів (розмір small) розходиться з точною на вісім гривень (6.1421855e+06 проти 6142176.70). numeric(12,2) рахує десяткові цифри точно: 12 цифр усього, 2 після коми. Максимум 9 999 999 999.99, а більше число база відхилить помилкою numeric field overflow.
Плата — швидкість: numeric повільніший за float. Для грошей обмін правильний, для вимірювань, де похибка й так є, доречний float.
Час і часові пояси
Section titled “Час і часові пояси”Назва timestamptz (час із часовим поясом) вводить в оману: пояс він не зберігає. Значення лежить як момент у UTC, а пояс застосовується на вході й виході за налаштуванням сесії. timestamp зберігає те, що ви написали, і не знає, чиї це «12:00».
Якщо сесія в Europe/Kyiv, то '2025-07-01 12:00:00'::timestamptz виведеться як 2025-07-01 12:00:00+03. У сесії з UTC ті самі цифри означали б інший момент, а timestamp цього не розрізняє. У датасеті час в UTC, а покупці в Києві: замовлення 1 оформлене о 23:24 UTC 31 грудня 2024, а для покупця це 1 січня, 01:24. Найгостріше це видно, коли рахують «за день»:
utc_day | kyiv_day---------+---------- 242 | 244У «чорну п’ятницю» за київським часом замовлень на два більше, ніж за UTC.
Моменти (замовлення, платіж) зберігають у timestamptz, календарні дати — у date.
Текст, jsonb і ключі
Section titled “Текст, jsonb і ключі”text і varchar(n) у PostgreSQL зберігаються однаково (pg_column_size('abc') дає 7 байтів в обох). Тож n нічого не економить і не прискорює, це лише перевірка довжини. INSERT довшого значення падає з value too long for type character varying(3), а явне приведення 'abcd'::varchar(3) мовчки обрізає до abc. Тому в «Крамниці» скрізь text, а довжину, коли вона є правилом предметної області, задають CHECK.
jsonb зберігає документ у розібраному вигляді: порядок ключів і дублі не зберігаються, пошук швидкий, його можна індексувати (модуль 8). У products.attributes набір ключів різний: warranty_months є лише в 953 товарів із 3500 (small). В інших attributes->>'warranty_months' дає NULL, і відрізнити відсутній ключ від значення null можна лише оператором ?. Поле, потрібне кожному рядку, має бути колонкою з типом і обмеженням. У jsonb живе те, чия форма справді різна (модулі 5 і 16).
Сурогатні ключі в датасеті — GENERATED ALWAYS AS IDENTITY. Це стандартний SQL, і на відміну від serial, база відмовляє, якщо в INSERT передали значення вручну. uuid не залежить від бази, але впливає на індекс: gen_random_uuid() розкидає вставки по дереву, uuidv7() (є в PostgreSQL 18) зберігає порядок за часом. Докладно в модулі 5.
Обмеження
Section titled “Обмеження”Обмеження тримають правила біля даних: жоден клієнт їх не обійде. Створимо таблицю промокодів, якої в «Крамниці» досі немає, хоч orders.promo_code уже є:
CREATE TABLE coupons ( code text PRIMARY KEY, discount_pct numeric(5,2) NOT NULL CHECK (discount_pct > 0 AND discount_pct < 100), valid_from timestamptz NOT NULL, valid_to timestamptz, max_uses integer CHECK (max_uses > 0), CHECK (valid_to IS NULL OR valid_to > valid_from));
INSERT INTO coupons (code, discount_pct, valid_from)SELECT promo_code, 10, '2025-01-01 00:00+02'FROM ordersWHERE promo_code IS NOT NULLGROUP BY promo_code;NOT NULL забороняє відсутнє значення. CHECK перевіряє вираз над одним рядком. UNIQUE і PRIMARY KEY не дають двом рядкам збігтися за ключем і для цього створюють індекс. FOREIGN KEY вимагає, щоб значення існувало в батьківській таблиці. Порушення закінчується помилкою, і рядок до таблиці не потрапляє. Спроба вставити знижку 100 дає violates check constraint "coupons_discount_pct_check", а повторний WELCOME10 — duplicate key value violates unique constraint "coupons_pkey".
Тризначна логіка дає два наслідки. CHECK пропускає рядок, коли вираз дає NULL, тож умова на valid_to не заборонила б порожнє значення й без IS NULL OR. А UNIQUE вважає будь-які два NULL різними. Змінює це UNIQUE NULLS NOT DISTINCT.
Тепер зовнішній ключ: кожен promo_code у orders має існувати в coupons, а при видаленні промокоду замовлення його забувають, а не зникають.
ALTER TABLE orders ADD CONSTRAINT orders_promo_code_fkey FOREIGN KEY (promo_code) REFERENCES coupons (code) ON DELETE SET NULL NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_promo_code_fkey;ON DELETE вирішує, що робити з дочірніми рядками, коли батьківський зник. За замовчуванням (NO ACTION) видалення батька заборонене, доки є діти. SET NULL і SET DEFAULT міняють значення, а CASCADE видаляє й дітей, тож один DELETE може непомітно знести півбази. NOT VALID і VALIDATE пояснено в розділі про міграції.
Кожне видалення з coupons змушує базу шукати дітей в orders. Індексу на promo_code немає, тож це повне сканування. Індекс на колонку зовнішнього ключа в дочірній таблиці створюють самі.
Зміна даних
Section titled “Зміна даних”INSERT, UPDATE і DELETE у PostgreSQL мають RETURNING: команда повертає змінені рядки, окремий SELECT не потрібен. Разом із ним зникає вікно для гонитви між двома запитами.
Upsert і MERGE
Section titled “Upsert і MERGE”«Вставити, а якщо є, оновити» не можна писати як SELECT, а потім INSERT чи UPDATE: між ними інша сесія встигне вставити той самий ключ (модуль 10). INSERT … ON CONFLICT робить це однією атомарною командою. Надходження товару на склад:
INSERT INTO inventory (product_id, quantity, reserved, updated_at)VALUES (39, 5, 0, now())ON CONFLICT (product_id)DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity, updated_at = EXCLUDED.updated_atRETURNING product_id, quantity; product_id | quantity------------+---------- 39 | 7EXCLUDED — рядок, який намагалися вставити, inventory.quantity — той, що вже лежить (було 2). DO NOTHING замість DO UPDATE пропускає конфліктний рядок.
MERGE (PostgreSQL 15) порівнює цільову таблицю з джерелом і виконує свою дію для кожного збігу чи його відсутності. Оновимо знижку наявного промокоду й додамо новий:
merge_action | code | discount_pct--------------+-----------+-------------- UPDATE | WELCOME10 | 12.00 INSERT | SUMMER5 | 5.00Різниця з ON CONFLICT проявляється під конкуренцією. Дві сесії виконали MERGE … WHEN NOT MATCHED THEN INSERT для того самого нового ключа, і друга, дочекавшись першої, впала з duplicate key value violates unique constraint. MERGE вирішує «збігу немає» за знімком, а ON CONFLICT перевіряє в момент вставки. Тому для простого upsert із конкурентними писачами беруть ON CONFLICT, а MERGE для синхронізації за зразком.
Представлення й обчислювані колонки
Section titled “Представлення й обчислювані колонки”Представлення (view) — збережений запит із ім’ям. Він підставляється в запит під час звернення, тож дані завжди свіжі. Матеріалізоване представлення зберігає результат як таблицю й оновлюється за командою. Воно підходить, коли продажі за днями читаються дорого, а змінюються раз на добу.
CREATE MATERIALIZED VIEW daily_sales ASSELECT (placed_at AT TIME ZONE 'Europe/Kyiv')::date AS day, count(*) AS orders, sum(total_amount) AS revenueFROM ordersWHERE status <> 'cancelled'GROUP BY 1;
CREATE UNIQUE INDEX daily_sales_day_idx ON daily_sales (day);REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;Звичайний REFRESH MATERIALIZED VIEW бере ACCESS EXCLUSIVE: читачі чекають (у штучному прикладі з повільним запитом 1.96 с). REFRESH … CONCURRENTLY будує нову копію поруч і вносить різницю, тож читачі працюють (та сама вибірка зайняла 0.5 мс).
На представленні має бути унікальний індекс без WHERE, інакше база відмовляє з HINT: Create a unique index with no WHERE clause. CONCURRENTLY бере EXCLUSIVE: читачам він не заважає, іншому оновленню того ж представлення заважає.
Обчислювана колонка (generated column, GENERATED ALWAYS AS (вираз)) рахується з інших колонок того ж рядка, і записати в неї значення не можна. У PostgreSQL 18 колонка без слова STORED віртуальна: не займає місця на диску й рахується під час читання. STORED рахується під час запису й зберігається. Індекс можна створити лише на STORED.
Міграції без простою
Section titled “Міграції без простою”Міграція теж транзакція, і блокує вона не рядок, а всю таблицю. Режимів таких блокувань вісім, від ACCESS SHARE до ACCESS EXCLUSIVE (матриця сумісності в розділі «Explicit Locking» документації). SELECT бере ACCESS SHARE, INSERT, UPDATE і DELETE — ROW EXCLUSIVE. ACCESS EXCLUSIVE несумісний ні з чим, навіть із ACCESS SHARE, і його бере більшість форм ALTER TABLE. Блокування тримається до кінця транзакції.
Черга за ALTER
Section titled “Черга за ALTER”Блокування видаються в порядку черги: запит, що чекає ACCESS EXCLUSIVE, не пропускає повз себе нових. Експеримент на PostgreSQL 18.6 в Docker, три сесії, таблиця orders:
| Час | Сесія A | Сесія B | Сесія C |
|---|---|---|---|
| 0 с | BEGIN, SELECT count(*) FROM orders, SELECT pg_sleep(10) |
||
| 2 с | ALTER TABLE orders ADD COLUMN gift_wrap boolean чекає |
||
| 4 с | SELECT … FROM orders WHERE order_id = 1 чекає |
||
| 10 с | COMMIT |
ALTER TABLE, виконалась |
рядок повернуто |
Зріз pg_locks разом із pg_stat_activity на 6-й секунді:
app | mode | granted | blocked-----+---------------------+---------+--------- A | AccessShareLock | t | f B | AccessExclusiveLock | f | t C | AccessShareLock | f | tC не конфліктує з A: два ACCESS SHARE сумісні. Але C не може обігнати B, що вже в черзі. Вибірка за ключем, яка зазвичай триває долі мілісекунди, чекала 5.9 с.
Ціна визначається не самим ALTER, а найдовшою відкритою транзакцією, яка торкалась таблиці. Зокрема й сесією, що застрягла в idle in transaction. Звідси перше правило: перед ALTER виставити lock_timeout, щоб міграція здавалась, а не ставала в чергу.
Якщо в B перед ALTER виконати SET lock_timeout = '500ms', міграція впаде через 500 мс з canceling statement due to lock timeout. Вибірка C, що прийшла через 0.2 с, чекатиме 255 мс замість шести секунд. Помилка означає «не зараз»: міграцію повторюють.
Які міграції безпечні
Section titled “Які міграції безпечні”Режим кожної форми перевірено запитом до pg_locks у тій самій транзакції. Таблиця orders_big — копія з 3 млн рядків (196 МБ), час виміряно на настільній машині. На ноутбуці він більший, пропорції ті самі.
| Команда | Блокування таблиці | Наслідок |
|---|---|---|
ADD COLUMN (без DEFAULT або зі сталим) |
ACCESS EXCLUSIVE |
миттєво, але тримає чергу |
SET NOT NULL |
ACCESS EXCLUSIVE |
сканує таблицю, 118 мс |
ADD CONSTRAINT … CHECK … NOT VALID |
ACCESS EXCLUSIVE |
миттєво, без сканування |
VALIDATE CONSTRAINT (CHECK) |
SHARE UPDATE EXCLUSIVE |
читання й запис тривають |
ADD FOREIGN KEY … NOT VALID |
SHARE ROW EXCLUSIVE на обох |
записи в обидві таблиці чекають мить |
VALIDATE CONSTRAINT (FK) |
SHARE UPDATE EXCLUSIVE (+ ROW SHARE на батьківській) |
читання й запис тривають |
CREATE INDEX |
SHARE |
записи чекають усю побудову |
CREATE INDEX CONCURRENTLY |
SHARE UPDATE EXCLUSIVE |
записи тривають |
ALTER COLUMN … TYPE |
ACCESS EXCLUSIVE |
залежить від типу |
SHARE UPDATE EXCLUSIVE сумісний із читанням і записом, але не з собою: два VALIDATE чи VACUUM на одній таблиці одночасно не запустяться.
ADD COLUMN зі значенням за замовчуванням. До PostgreSQL 11 будь-який DEFAULT переписував таблицю. Тепер стале значення база запам’ятовує в каталозі й підставляє під час читання: на orders_big ADD COLUMN gift_wrap boolean NOT NULL DEFAULT false виконався за 3.4 мс, а файл таблиці (relfilenode) лишився тим самим. DEFAULT random() дає різні значення в різних рядках, тож таблицю довелося переписати: 3.0 с під ACCESS EXCLUSIVE.
SET NOT NULL. Щоб довести, що NULL немає, база сканує таблицю під ACCESS EXCLUSIVE. Обхід: CHECK (col IS NOT NULL) NOT VALID (миттєво), VALIDATE CONSTRAINT (довго, але без блокування запису), потім SET NOT NULL, який бачить перевірене обмеження й сканування пропускає: 0.6 мс проти 118 мс напряму. Після цього CHECK прибирають.
NOT VALID не означає «не діє». Таке обмеження вже перевіряє нові й змінені рядки (INSERT із промокодом NOPE у ключі вище було б відхилено ще до VALIDATE), а старі пропускає. Їх перевіряє VALIDATE CONSTRAINT з послабленим блокуванням, після чого convalidated у pg_constraint стає t.
Індекс. CREATE INDEX блокує запис на всю побудову: на orders_big індекс із трьох колонок будувався 2.2 с, і UPDATE одного рядка, що прийшов на 0.4 с, чекав 1.8 с. З CONCURRENTLY той самий UPDATE зайняв 97 мс, а побудова тривала 2.9 с, бо база проходить таблицю двічі й чекає на старіші транзакції. Обмеження: не можна в транзакції, відкату немає.
Якщо побудова впала, лишається недійсний індекс. Приклад: унікальний індекс по lower(email) у «Крамниці» падає на дублікатах зі старого імпорту з could not create unique index … is duplicated, і \d customers показує його як INVALID. Його й далі оновлює кожен запис, але запити ним не користуються. Видаляють його (DROP INDEX CONCURRENTLY) і будують знову після чистки даних.
Зміна типу. ALTER COLUMN … TYPE бере ACCESS EXCLUSIVE і або лише міняє запис у каталозі, або переписує таблицю. За порівнянням relfilenode без переписування проходять numeric(12,2) → numeric(14,2), varchar(50) → varchar(100) і timestamp → timestamptz, якщо сесія в UTC. З переписуванням: numeric(14,2) → numeric(14,3), varchar(100) → varchar(20), text → varchar(10), integer → bigint, а timestamp → timestamptz у сесії з поясом Europe/Kyiv. Переписування на мільярді рядків під ACCESS EXCLUSIVE означає години простою.
Розширити, мігрувати, звузити
Section titled “Розширити, мігрувати, звузити”Схема expand/contract розбиває небезпечну зміну на безпечні кроки, сумісні з обома версіями застосунку. Перейменуємо orders.customer_note на note.
- Розширити. Додати
note; тригер синхронізує колонки в обидва боки, тож стара версія (пише вcustomer_note) і нова (пише вnote) бачать одні дані. - Мігрувати. Заповнити
noteдля старих рядків малими порціями, щоб транзакції були короткими. - Перемкнути застосунок на
note. - Звузити. Коли стара версія не працює, прибрати тригер і
customer_note.
ALTER TABLE orders ADD COLUMN note text;
CREATE FUNCTION orders_sync_note() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF NEW.note IS DISTINCT FROM (CASE WHEN TG_OP = 'UPDATE' THEN OLD.note END) THEN NEW.customer_note := NEW.note; -- пише нова версія ELSE NEW.note := NEW.customer_note; -- пише стара END IF; RETURN NEW;END $$;
CREATE TRIGGER orders_sync_note BEFORE INSERT OR UPDATE ON ordersFOR EACH ROW EXECUTE FUNCTION orders_sync_note();
UPDATE orders SET note = customer_noteWHERE order_id > 0 AND order_id <= 5000 AND note IS DISTINCT FROM customer_note;Перша порція зачепила 374 рядки з приміткою серед перших 5000 замовлень. Наприкінці DROP TRIGGER, DROP FUNCTION і ALTER TABLE … DROP COLUMN customer_note. Останній швидкий, але теж бере ACCESS EXCLUSIVE, тож потрібні lock_timeout і повтор. Так само змінюють integer на bigint. Розплата — кілька релізів замість одного.
Як це насправді
Section titled “Як це насправді”Скільки чекатиме черга за міграцією, визначає найстаріша відкрита транзакція. Її шукають до міграції запитом до pg_stat_activity за xact_start. Ланцюжок очікувань видно функцією pg_blocking_pids(pid): для C у прикладі вище це B, для B це A. Міграцію запускають у циклі повторів, де перед кожним ALTER виставлено SET lock_timeout.
Розбір: ALTER за довгою транзакцією
Section titled “Розбір: ALTER за довгою транзакцією”Першоджерела про міграцію, що заблокувала таблицю в продакшні (постмортем, який можна відкрити й звірити), знайти й прочитати не вдалося. Тому розбір — відтворений експеримент на PostgreSQL 18.6 у Docker Desktop, а не історія конкретної компанії.
pgbench запускає 8 клієнтів, які читають замовлення за ключем: близько 110 тис. запитів на секунду. На 3-й секунді стартує «звіт» (транзакція з SELECT count(*) і паузою на вісім секунд), на 5-й виконується міграція з початку модуля.
Без захисту на 6-й секунді лишається 7.8 тис. запитів, а з 7-ї по 11-ту нуль. Жоден запит не впав, вони просто не завершувались. Міграція чекала 6.0 с. Завантаження процесора не зросло, тож моніторинг за ним нічого б не показав.
З lock_timeout = 200ms і повтором кожні півсекунди міграція пройшла з сьомої спроби. Пропускна здатність не опускалась нижче 78 тис. запитів на секунду: кожна невдала спроба ставила чергу на 200 мс, не більше, а клієнти помилок не отримали. Для SET NOT NULL на великій таблиці цього мало б не вистачити. Успішна спроба тримала б блокування ще й на час сканування, тож потрібна форма з NOT VALID.
Небезпечні дві речі: найдовша відкрита транзакція, з якою зустрівся ALTER, і час, який він потім тримає блокування. Перше лікують lock_timeout із повтором, друге вибором форми міграції з таблиці вище.
Типові помилки розуміння
Section titled “Типові помилки розуміння”«ALTER TABLE блокує таблицю лише на час виконання». Блокування ще треба дочекатись, а все, що прийшло за ALTER, чекає з ним.
«NOT VALID означає, що обмеження ще не діє». Воно діє на нові й змінені рядки одразу, а VALIDATE CONSTRAINT лише перевіряє старі.
«CREATE INDEX CONCURRENTLY безпечний завжди». Він не блокує запис, але довший, не працює в транзакції, а після помилки лишає INVALID індекс.
«Гроші можна зберігати в float, а округляти при виведенні». Похибка накопичується в сумах (мільйон додавань 0.1 дає 100000.00000133288). Потрібні numeric або цілі копійки.
Перевір себе
Лабораторна
Section titled “Лабораторна”L2. Схема і міграція без простою спирається на цей модуль і на модуль 5: спроєктувати схему за описом предметної області й виконати міграцію живої таблиці так, щоб фонове навантаження не відчуло змін.
Джерела
Section titled “Джерела”- PostgreSQL 18, документація: Explicit Locking, ALTER TABLE, CREATE INDEX, REFRESH MATERIALIZED VIEW, INSERT, MERGE, Generated Columns, lock_timeout.
- PostgreSQL 11 Release Notes:
ADD COLUMNзі сталим значенням за замовчуванням без переписування таблиці.