L2. Схема і міграція без простою

базовийспирається на модуль 4, модуль 5

Мета

Схему перевіряють не очима, а даними: правильні вставки мають пройти, неправильні мусить відхилити сама база, а не застосунок. Після першої частини ви вмієте перетворити опис предметної області на таблиці в 3НФ з ключами й обмеженнями, які ловлять дублі, сиріт і від’ємні суми. Друга частина про те, що схема живе довше за першу версію: колонку в таблиці з 220 тисячами замовлень треба додати так, щоб ніхто з користувачів цього не помітив.

Завдання

Результат: каталог labs/l02-schema-design/solution/ із двома частинами.

schema.sql — схема повернень товарів.
migrate/NN_назва.sql — кроки міграції orders.

Готово, коли ./labs/l02-schema-design/check.sh виводить «усе гаразд». Чекер нічого не читає як текст: він застосовує вашу схему до копії датасету, вставляє рядки й ставить запити, а міграцію запускає під навантаженням. Ваша база shop не змінюється.

Частина 1. Повернення товарів

«Крамниця» запускає повернення. Покупець подає заявку на повернення за одним замовленням і називає причину з переліку: «брак», «не той товар», «передумав». У заявці вказано, які рядки замовлення він повертає і скільки штук з кожного. Менеджер розглядає заявку: відхиляє її або погоджує, отримує товар, виплачує гроші.

Правила предметної області:

Причини повернення — довідник. Код причини унікальний, назва обов’язкова.
Заявка стосується рівно одного замовлення. На одне замовлення можна подати кілька заявок, наприклад окремо на різні рядки.
Статус заявки: requested, approved, rejected, received, refunded. Інших немає.
Дата подання обов’язкова. Дата рішення, якщо вона є, не раніше за дату подання.
Сума виплати заповнена тоді й лише тоді, коли статус refunded. Вона не від’ємна й зберігається точно, не як число з рухомою комою.
Позиція заявки — це рядок замовлення (order_id, line_no) і кількість штук. Кількість додатна, рядок замовлення має існувати, один рядок не можна вказати двічі в одній заявці.
Причину, на яку вже є заявки, видалити не можна: історія повернень не зникає каскадом.
Покупця, товар і ціну в заявках не дублюйте. Їх визначає замовлення, а назву причини — довідник.

Чекер звертається до таких імен, тож вони обов’язкові. Решту колонок і таблиць додавайте на розсуд, але додаткові колонки мають бути NULL або мати DEFAULT: чекер вставляє рядки лише з перелічених.

Таблиця	Обов’язкові колонки
`return_reasons`	`reason_code`, `title`
`returns`	`return_id` (генерує база), `order_id`, `reason_code`, `status`, `requested_at` і `resolved_at` (`timestamptz`), `refund_amount` (`numeric`)
`return_items`	`return_id`, `order_id`, `line_no`, `quantity`

Що перевіряє чекер, у такому порядку:

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

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

Частина 2. Міграція `orders` без простою

Аналітики щодня групують замовлення за днем, і день у них київський. Вираз (placed_at AT TIME ZONE 'Europe/Kyiv')::date скопійовано в десятки звітів, а жоден індекс його не підхоплює. Вирішили зберігати день окремою колонкою.

Треба, щоб у orders з’явилась колонка placed_on date:

обов’язковий (NOT NULL);
завжди дорівнює (placed_at AT TIME ZONE 'Europe/Kyiv')::date, і це гарантує обмеження CHECK, а не лише тригер;
звичайний, не generated: новий застосунок писатиме його явно, і вставку з датою, що не збігається з placed_at, база має відхиляти;
значення для всіх 220 тисяч наявних замовлень заповнено.

Старий застосунок про колонку не знає. Він читає й оновлює замовлення й вставляє нові без placed_on, і так триватиме, доки не вийде його нова версія: під час усієї міграції та після неї.

Міграцію пишете ви, як послідовність кроків expand/contract у solution/migrate/: кожен крок — окремий файл NN_назва.sql. Як це влаштовано, сказано в модулі 4. Тут потрібні лише правила гри чекера.

Копія датасету medium (база l02_run) має на orders чотири вторинні індекси, як застосунок після L4. Поки йде міграція, працює навантаження «старого застосунку»: 8 клієнтів близько 400 транзакцій на секунду. Шість із десяти — читання замовлення й його рядків, три — UPDATE статусу, одна — INSERT нового замовлення.
Перед кожним кроком чекер запускає «звіт»: транзакцію, що читає orders і тримає її відкритою 4 секунди. Через 0,7 с стартує ваш крок. Так у справжній базі міграція потрапляє в чергу: довга транзакція є завжди.
Кроки виконуються за порядком імен, кожен окремим psql -f. Якщо крок упав із 55P03 (lock_timeout) чи 40P01, драйвер повторює його цілим, до 60 разів із паузою близько 0,3 с. Будь-яка інша помилка зупиняє міграцію.
Тож крок має бути безпечним для повтору: IF NOT EXISTS, CREATE OR REPLACE або одна команда у файлі. SET lock_timeout діє лише в межах файлу.
Під час міграції ні один запит навантаження не має чекати довше за THRESHOLD_MS (типово 1000 мс) і ні один не має впасти.
Після останнього кроку placed_on існує, обов’язковий, збігається з київською датою в усіх рядках (зокрема у вставлених навантаженням під час міграції), є перевірене обмеження CHECK, вставка зі хибним значенням відхиляється, а вставка «старого застосунку» без placed_on ще проходить.

Чого робити не треба: змінювати check.sh, lib/ і навантаження, додавати обчислювану колонку (generated column), міняти тип чи назви наявних колонок.

Перед початком

Прочитайте модуль 5, особливо розділи про зв’язки, нормалізацію й ідентифікатори, і модуль 4: його розділи про обмеження, ALTER TABLE і міграції без простою потрібні для другої частини.

Частині 2 потрібен розмір medium (про розміри див. Датасет). Згенеруйте його й підніміть базу з датасетом small (див. setup/README.md):

./setup/generate-dataset.sh medium
docker compose --profile postgres up -d --wait
docker context ls    # зірочка має стояти біля локального Docker

Навантаження працює всередині контейнера postgres (pgbench із самого PostgreSQL 18), тож крім Docker і bash (Git Bash на Windows, WSL, Linux, macOS) нічого не треба. Якщо ви запускаєте compose під власною назвою проєкту: COMPOSE_PROJECT_NAME=моя-назва ./labs/l02-schema-design/check.sh.

Скопіюйте заготовки:

cp -r labs/l02-schema-design/starter labs/l02-schema-design/solution

Етапи

Схема на папері. Накресліть ER-діаграму в нотації crow’s foot для трьох нових таблиць і двох старих, на які вони посилаються (orders, order_items). Для кожної колонки, що не входить у ключ, запишіть, від чого він залежить. Перевірте: чи є колонка, що залежить не від ключа таблиці, а від іншої колонки? Така колонка має жити в іншій таблиці.
schema.sql. Допишіть solution/schema.sql. Ключі, зовнішні ключі, NOT NULL, CHECK для статусу, дат, суми й кількості. Правила 5 і 6 потребують уважності: одне обмеження охоплює дві колонки, інше складається з двох колонок ключа. Запустіть ./labs/l02-schema-design/check.sh 1 і читайте вивід: кожен «НІ» каже, яку вставку прийнято чи відхилено не тим, чим треба.
Запити бізнесу. Коли всі вставки поводяться правильно, подивіться на шість запитів у розділі «Запити бізнесу». Якщо котрийсь не виконується чи дає інше число, причина в типі колонки (наприклад, 0.10 + 0.20 у double precision) або в дубльованій колонці.
Наївна міграція. Подивіться, що станеться, якщо зробити все одним файлом:
Terminal window
```
./labs/l02-schema-design/hammer.sh labs/l02-schema-design/starter/naive
```
Запишіть найдовшу затримку (max_ms), скільки запитів навантаження впало (failed) і чому зупинилась міграція. Це ваша точка відліку.
Expand. У solution/migrate/ напишіть кроки, що не ламають старий застосунок: порожню колонку і механізм, що заповнює його для нових рядків. Кожен крок, що просить блокування на orders, має швидко здаватися. Запустіть ./labs/l02-schema-design/hammer.sh і перевірте max_ms. Перевірте: чому тригер має з’явитися раніше, ніж міграція наявних рядків?
Міграція даних. Заповніть placed_on для наявних замовлень порціями, кожна в окремій транзакції. Порівняйте max_ms із одним великим UPDATE: на якому кроці виникає затримка і хто на кого чекає?
Contract. Додайте обмеження так, щоб жоден крок не сканував таблицю під сильним блокуванням: спершу обмеження без перевірки наявних рядків, потім його перевірка, потім NOT NULL. Подумайте, як зробити SET NOT NULL дешевим.
Здача. ./labs/l02-schema-design/check.sh. Кожен пункт, що не пройшов, пояснює, що не так.

Перевірка

./labs/l02-schema-design/check.sh                 # обидві частини з solution/
./labs/l02-schema-design/check.sh 1 [файл]        # лише схема
./labs/l02-schema-design/check.sh 2 [каталог]     # лише міграція
./labs/l02-schema-design/hammer.sh [каталог]      # міграція під навантаженням: сирі числа без оцінки
THRESHOLD_MS=500 ./labs/l02-schema-design/check.sh 2   # суворіший поріг

Чекер створює у вашому контейнері допоміжні бази: l02_small і l02_med (копії датасету, один раз), l02_design і l02_run (свіжі копії на кожну перевірку), і нічого не змінює в shop. Перший запуск довший на хвилину через копіювання medium. Частина 1 триває секунд десять, частина 2 близько хвилини: кожен крок чекає на «звіт», і це навмисно.

Звіти й поріг потрібні, щоб провал був стабільним. Наївна міграція без lock_timeout ставала в чергу за звітом 10 разів із 10, і разом з нею в чергу ставало все навантаження; одна велика UPDATE тримала блокування на рядках у всіх цих запусках теж. Якби звітів не було, на малому orders небезпечні команди промайнули б за мілісекунди, і чекер не відрізнив би їх від безпечних.

Часті помилки

ALTER TABLE без lock_timeout. Інструкція чекає за звітом, а всі нові запити до orders стають за нею в чергу. У виводі це один запит із затримкою в кілька секунд.

lock_timeout у три секунди. Драйвер повторить крок, але поки ALTER чекає, навантаження стоїть: поріг 1 с перевищено. Ставте сотні мілісекунд.

Кілька команд у файлі без захисту від повтору. Перша виконалась, друга впала за lock_timeout, драйвер повторює файл, і перша видає «already exists». Розбийте файл або додайте IF NOT EXISTS.

Тригер після міграції наявних рядків. Замовлення, вставлені між ними, лишаються без placed_on, і наприкінці NOT NULL не вдається поставити чи чекер знаходить порожні рядки.

Один UPDATE на всю таблицю. Він тримає блокування на всіх оновлених рядках до кінця транзакції, і записи старого застосунку стають за ним.

CHECK без NOT VALID. Перевірка наявних рядків іде під ACCESS EXCLUSIVE. На 220 тисячах рядків це десятки мілісекунд, на ста мільйонах хвилини, і чекер не може перевірити, який у вас розмір.

NOT VALID без VALIDATE. Обмеження діє для нових рядків, але про наявні нічого не відомо; чекер вважає його неперевіреним.

Тригер, що завжди перезаписує placed_on. Хибне значення, яке надсилає новий застосунок, мовчки виправляється замість відхилення.

Додаткова обов’язкова колонка без DEFAULT у returns. Чекер його не знає, вставка падає, і всі «правильні» проби червоні.

Docker дивиться на віддалену машину. Якщо docker context ls показує зірочку біля SSH-адреси, docker compose працює там, а не на вашому комп’ютері. docker context use desktop-linux (Docker Desktop) або default (Linux).

Далі, якщо цікаво

Доведіть, що заявка не може посилатися на рядок чужого замовлення: складений зовнішній ключ (return_id, order_id) на returns потребує суперключа UNIQUE (return_id, order_id), і це свідомий компроміс, бо order_id у return_items повторює returns.order_id. Формально order_id у return_items залежить лише від return_id, тож таблиця порушує 2НФ: поясніть, чому аномалії оновлення при цьому немає і що саме її унеможливлює. Спробуйте замінити identity у returns на uuidv7() і подивіться, що змінилося в розмірі індексу (модуль 5, розділ про ідентифікатори). Правило «кількість у заявці не перевищує куплену» база декларативно не виразить: подумайте, яке обмеження чи тригер потрібні і які розклади конкурентності воно пропустить (модуль 10). Спробуйте зробити placed_on обчислюваною колонкою (generated column) PostgreSQL 18 (VIRTUAL) і порівняйте з вашою міграцією: що змінилося для старого застосунку?