MySQL проти PostgreSQL: чому ALTER TABLE стає кошмаром

Що насправді означає «кошмар із ALTER TABLE» у продакшні

Зміна схеми стає кошмаром, коли вона порушує одну з трьох продакшн-істин:

• Бюджети затримки реальні. Усе, що блокує записи на секунди, проявиться як таймаути, повтори, накопичення черг і іноді каскадні відмови.
• Реплікація є частиною шляху обслуговування. Навіть якщо ви «не читаєте з реплік», відставання може зламати failover, бекапи, аналітику та будь-який механізм безпеки, що залежить від актуальних вторинних серверів.
• Сховище не безмежне, а I/O не безкоштовний. Перезапис таблиці на 2 ТБ — це не просто повільно: це може заповнити диски, перевантажити буферні кеші й викликати хвилі autovacuum або purge.

На практиці біль від змін схеми концентрується навколо:

• Блокувань, що блокують трафік (явні блокування, неявні блокування і особливо гострий випадок: метадані-блокування в MySQL).
• Перезапису таблиць (поширено в PostgreSQL; можливе в MySQL залежно від движка та режиму DDL).
• Поведінки реплікації (наслідки statement-based vs row-based, серіалізація DDL, відставання застосування на репліках).
• Довгих транзакцій (обидві бази їх ненавидять, просто по-різному).
• Очікувань інструментів (хтось вважає, що «онлайн» означає «нульовий вплив», і продакшн вчить скромності).

Цитата, яку варто мати на стікері:

«перефразована ідея» — Werner Vogels: ви будуєте надійність, припускаючи, що щось зламається, і проєктуючи швидке відновлення.

Зміни схеми — це робота з надійності. Ставтеся до них як до деплою, а не як до редагування стовпця в електронній таблиці.

Кілька історичних фактів, що пояснюють сучасну поведінку

Це не дрібниці; це причини, чому ваш DDL поводиться так, як він поводиться.

1. PostgreSQL рано обрав транзакційний DDL: DDL бере участь у транзакціях, тож його можна відкотити. Це добре для коректності, але також означає, що блокування можуть утримуватись до коміту.
2. «Online DDL» MySQL — це переважно історія InnoDB: поведінка залежить від можливостей движка зберігання, і історично MyISAM охоче блокував усе навколо.
3. InnoDB ввів «Fast Index Creation» (FIC), щоб знизити вартість перестроювання, але дрібні деталі важливі: деякі ALTER і досі перестроюють таблицю.
4. PostgreSQL з версії 9.2+ зробив CREATE INDEX CONCURRENTLY практичним як операційний інструмент, з трейд-офом: він триває довше і може зірватися, якщо за ним не доглядати.
5. PostgreSQL 11 покращив «add column with default», уникаючи повного перезапису таблиці для константних значень за багатьма випадками — одне з великих покращень для операцій.
6. MySQL 5.6/5.7 розширили опції online DDL з ALGORITHM і LOCK, але це радше домовленість, ніж гарантія.
7. MySQL 8.0 додав «instant ADD COLUMN» для деяких випадків, але щойно ви змінюєте атрибути стовпця або порядок, ви повертаєтесь до важчих операцій.
8. MVCC PostgreSQL означає, що старі версії рядків лишаються до очищення vacuum; великі оновлення під час змін схеми можуть створити післяшок vacuum/I/O.
9. Реплікація MySQL історично застосовувала DDL в одному потоці (навіть з паралельною реплікацією DDL має обмеження серіалізації), що робить «швидко на первинному» іноді «повільно всюди».

Жарт №1: Називати ALTER «онлайн», бо воно не бере ексклюзивного блокування, — це як називати ведмедя «дружнім», бо він ще вас не укусив.

MySQL: online DDL, метадані-блокування і ілюзія «миттєвості»

Ключова проблема MySQL: метадані-блокування не переймаються вашими намірами

У MySQL багато болю від ALTER — це не стільки робота, скільки MDL (metadata locks). Будь-який DDL потребує метаданого-блокування, і ці блокування взаємодіють з поточними запитами так, що дивують команди, які ототожнюють «online DDL» з «без блокувань».

Класичний сценарій відмови виглядає так:

• Ви запускаєте ALTER TABLE.
• Воно чекає MDL, бо якась сесія читає таблицю.
• Нові запити наростають в черзі за очікуючим ALTER, бо вони хочуть конфліктні блокування.
• Ваш додаток плавиться, навіть якщо «ALTER ще не почався».

Клаузи ALGORITHM і LOCK: корисні, але не бронепояс

MySQL дозволяє просити алгоритм DDL і поведінку блокувань. Приклад:

• ALGORITHM=INSTANT для змін тільки в метаданих, коли підтримується.
• ALGORITHM=INPLACE щоб уникнути копіювання таблиці, якщо можливо.
• LOCK=NONE або LOCK=SHARED щоб зменшити блокування.

Операційний трюк: завжди вказуйте їх, коли у вас суворі вимоги до доступності, бо вони перетворюють несподіванки на миттєві помилки. Помилка — це подарунок. Вона каже вам: «Не сьогодні, не так».

Що MySQL робить добре

• Деякі зміни можуть бути справді миттєвими (наприклад, деякі випадки ADD COLUMN в 8.0), що чудово, коли працює.
• Екосистема інструментів для онлайн-змін схеми зріла: pt-online-schema-change, gh-ost та саморобні варіанти — поширені.
• InnoDB може виконувати багато операцій з індексами «майже онлайн», з керованим впливом, якщо ви розумієте обмеження.

Де MySQL болить

• Накопичення MDL катастрофічне: воно виглядає як раптовий «база даних впала», навіть коли сервер здоровий.
• Відставання реплік може стати прихованим простоєм: DDL може блокувати застосування або конфліктувати з паралелізмом.
• «Онлайн» все одно споживає I/O і CPU: побудова вторинного індексу на великій таблиці конкуруватиме з продукційним навантаженням. Відсутність великого блокування не означає відсутність болю.

PostgreSQL: транзакційний DDL, перезаписи та наслідки у вигляді vacuum

Ключова проблема PostgreSQL: перезаписи чесні, жорсткі й іноді неминучі

PostgreSQL послідовний: якщо зміна схеми потребує торкнутися кожного рядка, він зробить саме це. І робитиме це в межах транзакційної семантики. Це означає:

• Перезаписи таблиць можуть бути величезними (час + I/O + додаткове місце на диску).
• Блокування можуть бути сильними (для деяких операцій потрібен ACCESS EXCLUSIVE).
• Довгі транзакції все погіршують, бо вони перешкоджають очищенню старих кортежів і можуть утримувати блокування довше, ніж ви думаєте.

Операційна суперсила PostgreSQL: передбачувані примітиви

Postgres дає вам будівельні блоки, які поводяться передбачувано:

• CREATE INDEX CONCURRENTLY уникає важких блокувань (ціною довшого виконання і більшої чутливості до збоїв).
• ALTER TABLE ... ADD COLUMN часто недорогий, і з v11 додавання константного значення за замовчуванням часто уникає повного перезапису таблиці.
• Багата інструментальна діагностика через системні каталоги і pg_stat_activity, що дозволяє відповісти на питання «хто кого блокує?».

Де Postgres болить

• Деякі DDL все ще потребують ACCESS EXCLUSIVE, що блокує читання і запис. Список не малий: зміни типів стовпців, валідація деяких обмежень, певні перезаписи таблиць.
• Побічні ефекти MVCC: зміни, що торкаються багатьох рядків, можуть створювати bloat; vacuum тоді конкурує за I/O. Міграція може «закінчитися», а реальний біль початися пізніше.
• Реплікація на фізичних стендбі: великий обсяг записів під час перезапису означає об’єм WAL, а це відставання стендбаїв і тиск на сховище.

Жарт №2: PostgreSQL дозволяє вам робити майже все в транзакції; іноді проблема в тому, що транзакція — це «перебудуй всесвіт».

Хто гірший? Практичний вирок за типом зміни

1) Додати nullable-стовпець

Зазвичай: обидва поводяться добре.

• MySQL 8.0: часто ALGORITHM=INSTANT, якщо ви не змінюєте порядок стовпців і лишаєтесь у підтримуваних шаблонах.
• PostgreSQL: метадані-операція, швидко.

Ризик кошмару: низький, якщо ви не викликаєте накопичення MDL (MySQL) або не берете сильних блокувань, комбінуючи кілька операцій (Postgres).

2) Додати стовпець з дефолтом

PostgreSQL значно покращився у v11. Для константних дефолтів він може зберігати значення в метаданих і застосовувати його при читанні, уникаючи повного перезапису таблиці в багатьох випадках.

MySQL: може все ще вимагати перестроювання таблиці залежно від точної зміни і версії/движка. «Default» рідко буває єдиною зміною; люди підсовують NOT NULL і перерендеринг стовпців і дивуються, чому таблиця копіюється.

Вирок: сучасний Postgres часто перемагає для константних дефолтів. MySQL може перемогти, якщо це instant; може й програти феєрично, якщо відбувається rebuild.

3) Додати індекс

Жодна з баз не дає цього безкоштовно. Ви читаєте багато даних і пишете багато структур.

• MySQL: створення індексу може бути «онлайн», але все одно споживає I/O/CPU; метадані-блокування мають значення, і реплікація може відставати.
• PostgreSQL: CREATE INDEX CONCURRENTLY — вибір для збереження аптайму, з операційною застереженням, що він може зайняти більше часу і не може виконуватися в транзакційному блоці.

Вирок: Postgres перемагає за уникненням блокувань з CONCURRENTLY. MySQL перемагає, коли навантаження толерантне і ви можете запланувати операцію — плюс у нього зрілі зовнішні інструменти онлайн-міграції.

4) Змінити тип стовпця

Тут ви заробляєте свою зарплату.

• PostgreSQL: може перезаписувати таблицю і часто вимагає ACCESS EXCLUSIVE. Є хитрощі (додати новий стовпець, бекафіл, swap), які уникають довгих ексклюзивних блокувань.
• MySQL: часто перестроює таблицю; може бути заблокований MDL; підтримка «inplace» варіюється.

Вирок: обидва можуть бути кошмарами. Postgres більш прозорий щодо перезапису; MySQL частіше дивує MDL і симптомами «чому все застрягло?».

5) Додати зовнішній ключ

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

• PostgreSQL: можна додати обмеження з NOT VALID і валідовати пізніше, щоб керувати часом блокування. Це реальна продакшн-функція, а не трюк.
• MySQL: поведінка валідації і блокувань залежить від движка/версії; ви можете отримати довге блокування або важкий I/O.

Вирок: Postgres зазвичай дає безпечніший поетапний підхід. MySQL може бути нормальним, але має менше примітивів для «відкладеної валідації».

6) Видалити стовпець

Обидва deceptively небезпечні. Не через те, що видалення важке, а через те, що застосунки і ORM чудово помиляються по-новому.

Вирок: база рідко є основним кошмаром; хореографія деплою — часто. Виконуйте це поетапно.

Швидкий план діагностики

Це послідовність «ми запустили ALTER і тепер усе горить». Мета — швидко визначити, чи ви заблоковані через блокування, перевантажені I/O/CPU або тоне в реплікаційному відставанні.

Перше: чи заблоковано через блокування або чекає на метадані?

• MySQL: перевірте processlist і очікування метаданих; шукайте сесії в стані «Waiting for table metadata lock.»
• PostgreSQL: перевірте pg_stat_activity і pg_locks на предмет ланцюгів блокувань і режимів типу AccessExclusiveLock.

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

Друге: чи сервер перевантажений ресурсами?

• CPU завантажено: побудова індексу, валідація обмежень або важкі тригери.
• I/O перевантажений: перезапис таблиці, побудова індексу, vacuum, фонові InnoDB-операції.
• Тиск пам’яті: часті зміни буферного пулу, сорти, що переходять на диск.

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

Третє: чи відставання реплікації перетворюється на другий простій?

• MySQL: моніторте затримку застосування на репліках і чи SQL-потік заблокований через DDL.
• PostgreSQL: слідкуйте за генерацією WAL і відставанням реплею; DDL, що перезаписує, генерує багато WAL.

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

Практичні завдання: команди, виводи та рішення (14)

Це практичні тести, які ви можете виконати під час планування або інциденту. Кожне завдання містить команду, що означає вивід і рішення, яке вона стимулює.

Завдання 1 (MySQL): подивитися, хто чекає на метадані-блокування

cr0x@server:~$ mysql -uroot -p -e "SHOW FULL PROCESSLIST\G"
*************************** 1. row ***************************
     Id: 12345
   User: app
   Host: 10.0.2.15:53412
     db: prod
Command: Query
   Time: 120
  State: Waiting for table metadata lock
   Info: ALTER TABLE orders ADD COLUMN promo_code varchar(32)
*************************** 2. row ***************************
     Id: 12201
   User: bi
   Host: 10.0.9.20:41233
     db: prod
Command: Query
   Time: 980
  State: Sending data
   Info: SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 30 DAY

Значення: ALTER заблоковано через поточний запит, що тримає конфліктне метадані-блокування.

Рішення: Якщо доступність важливіша за цей BI-запит, вбийте блокувальника (або скасуйте ALTER). Інакше заплануйте DDL в вікні або застосуйте таймаути для запитів.

Завдання 2 (MySQL): ідентифікувати InnoDB lock waits (рядкові блокування, не MDL)

cr0x@server:~$ mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,160p'
=====================================
2025-12-29 10:18:07 0x7f9a2c1a0700 INNODB MONITOR OUTPUT
=====================================
------------
TRANSACTIONS
------------
Trx id counter 92839110
Purge done for trx's n:o < 92839000 undo n:o < 0 state: running
History list length 2341
...
LATEST DETECTED DEADLOCK
...

Значення: Показує транзакційний тиск, довжину списку історії (purge lag), deadlock-и та очікування блокувань.

Рішення: Якщо history list length швидко зростає під час DDL + backfill, уповільніть backfill і скоротіть транзакції.

Завдання 3 (MySQL): перевірити, чи ваш ALTER може бути INSTANT/INPLACE

cr0x@server:~$ mysql -uroot -p -e "ALTER TABLE orders ADD COLUMN promo_code varchar(32), ALGORITHM=INSTANT, LOCK=NONE;"
ERROR 1845 (0A000) at line 1: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE.

Значення: MySQL відмовив INSTANT; можливо, він все ще буде INPLACE, або потрібен COPY (перестроювання таблиці).

Рішення: Не дозволяйте йому «вибирати». Спробуйте з ALGORITHM=INPLACE, LOCK=NONE і прийміть відмову — тоді переходьте до інструментів онлайн-міграції.

Завдання 4 (MySQL): перевірити відставання репліки і чи SQL-потік застряг на DDL

cr0x@server:~$ mysql -uroot -p -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_SQL_Running_State|Slave_IO_Running|Slave_SQL_Running"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Waiting for dependent transaction to commit
Seconds_Behind_Master: 187

Значення: Репліка відстає; SQL-потік застосування чекає, часто через велику транзакцію або серіалізацію DDL.

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

Завдання 5 (MySQL): перевірити розмір таблиці перед потенційним rebuild

cr0x@server:~$ mysql -uroot -p -e "SELECT table_name, ROUND((data_length+index_length)/1024/1024/1024,2) AS size_gb FROM information_schema.tables WHERE table_schema='prod' AND table_name='orders';"
+------------+---------+
| table_name | size_gb |
+------------+---------+
| orders     | 612.84  |
+------------+---------+

Значення: Ви збираєтесь торкнутися об’єкта ~613 GB. Перебудови займуть час і простір, і дестабілізують I/O.

Рішення: Розгляньте gh-ost/pt-osc, забезпечте запас дискового простору і погодьте план обмеження навантаження.

Завдання 6 (PostgreSQL): побачити активні запити і чи ваш DDL очікує

cr0x@server:~$ psql -d prod -c "SELECT pid, state, wait_event_type, wait_event, now()-query_start AS age, left(query,80) AS query FROM pg_stat_activity WHERE datname='prod' ORDER BY age DESC LIMIT 10;"
  pid  | state  | wait_event_type |     wait_event     |   age   |                                        query
-------+--------+-----------------+--------------------+---------+--------------------------------------------------------------------------------
 81231 | active | Lock            | relation           | 00:02:01| ALTER TABLE orders ALTER COLUMN total TYPE numeric(12,2)
 80910 | active |                 |                    | 00:18:22| SELECT * FROM orders WHERE created_at > now() - interval '30 days'

Значення: ALTER очікує на блокування; інший запит активний достатньо довго, щоб викликати підозру.

Рішення: Знайдіть блокувальників, потім або скасуйте їх, або скасуйте міграцію і перенесіть з безпечнішою стратегією (dual-write, backfill, swap).

Завдання 7 (PostgreSQL): знайти ланцюг блокувань

cr0x@server:~$ psql -d prod -c "SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query, blocking.query AS blocking_query FROM pg_locks blocked JOIN pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid JOIN pg_locks blocking ON blocking.locktype = blocked.locktype AND blocking.database IS NOT DISTINCT FROM blocked.database AND blocking.relation IS NOT DISTINCT FROM blocked.relation AND blocking.page IS NOT DISTINCT FROM blocked.page AND blocking.tuple IS NOT DISTINCT FROM blocked.tuple AND blocking.virtualxid IS NOT DISTINCT FROM blocked.virtualxid AND blocking.transactionid IS NOT DISTINCT FROM blocked.transactionid AND blocking.classid IS NOT DISTINCT FROM blocked.classid AND blocking.objid IS NOT DISTINCT FROM blocked.objid AND blocking.objsubid IS NOT DISTINCT FROM blocked.objsubid AND blocking.pid != blocked.pid JOIN pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid WHERE NOT blocked.granted AND blocking.granted LIMIT 5;"
 blocked_pid | blocking_pid |                  blocked_query                   |                  blocking_query
------------+--------------+--------------------------------------------------+-----------------------------------------------
      81231 |        80910 | ALTER TABLE orders ALTER COLUMN total TYPE ...    | SELECT * FROM orders WHERE created_at > ...

Значення: Маєте прямого блокувальника. Часто це запит на читання, який ви вважали «не блокує зміни схеми». Але блокує.

Рішення: Безпечно скасуйте блокувальний запит (pg_cancel_backend), або завершіть його (pg_terminate_backend), — проте усвідомте вплив на додаток.

Завдання 8 (PostgreSQL): оцінити розмір таблиці і наскільки страшний перезапис

cr0x@server:~$ psql -d prod -c "SELECT pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size, pg_size_pretty(pg_relation_size('public.orders')) AS heap_size;"
 total_size | heap_size
------------+----------
 835 GB     | 612 GB

Значення: Перезапис heap може означати сотні ГБ нових записів, плюс WAL, плюс вимоги вільного простору.

Рішення: Не намагайтесь робити перезапис у піковий час. Використовуйте підхід expand/backfill/contract, якщо не можете терпіти довгі ексклюзивні блокування і сильний I/O.

Завдання 9 (PostgreSQL): перевірити, чи додавання дефолту перепише в вашій версії

cr0x@server:~$ psql -d prod -c "SHOW server_version;"
 server_version
----------------
 14.10

Значення: На v11+ існує оптимізація для константних дефолтів; на старіших версіях ADD COLUMN DEFAULT може переписати таблицю.

Рішення: На старому Postgres уникайте додавання дефолту напряму; додайте nullable-стовпець, бекафіть пакетами, потім встановіть дефолт для майбутніх рядків.

Завдання 10 (PostgreSQL): створити індекс без блокування записів

cr0x@server:~$ psql -d prod -c "CREATE INDEX CONCURRENTLY idx_orders_created_at ON public.orders(created_at);"
CREATE INDEX

Значення: Індекс побудовано без найважчого блокування. Ймовірно, це зайняло більше часу, ніж звичайне створення.

Рішення: Віддавайте перевагу цьому в продакшні. Моніторте прогрес і будьте готові повторити, якщо відбудуться конфлікти чи відміни.

Завдання 11 (PostgreSQL): спостерігати прогрес побудови індексів

cr0x@server:~$ psql -d prod -c "SELECT pid, relid::regclass AS table_name, index_relid::regclass AS index_name, phase, blocks_done, blocks_total FROM pg_stat_progress_create_index;"
  pid  | table_name |        index_name         |  phase   | blocks_done | blocks_total
-------+------------+---------------------------+----------+-------------+-------------
 82110 | orders     | idx_orders_created_at     | building |      120331 |      612840

Значення: Перегляд прогресу показує, де ви зараз; blocks_done/blocks_total дає приблизну завершеність.

Рішення: Якщо дуже повільно або система голодує, подумайте про обмеження I/O на рівні інфраструктури або перенесення на інший час.

Завдання 12 (PostgreSQL): виміряти відставання реплікації під час важкого DDL/backfill

cr0x@server:~$ psql -d prod -c "SELECT application_name, state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS byte_lag FROM pg_stat_replication;"
 application_name |   state   | byte_lag
------------------+-----------+----------
 standby-a        | streaming | 3.1 GB

Значення: Ваша стендбай відстає на кілька ГБ в термінах WAL; це може бути хвилинами або годинами залежно від пропускної здатності.

Рішення: Якщо потрібен гарячий failover, зупиніть або уповільніть операцію, поки відставання не стане прийнятним.

Завдання 13 (MySQL): використовувати pt-online-schema-change як безпечнішу альтернативу

cr0x@server:~$ pt-online-schema-change --alter "ADD COLUMN promo_code varchar(32)" D=prod,t=orders --execute
No slaves found.  See --recursion-method if host has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Creating new table...
Created new table prod._orders_new OK.
Altering new table...
Altered `prod`.`_orders_new` OK.
Creating triggers...
Created triggers OK.
Copying rows...
Copying `prod`.`orders`:  12% 00:03 remain

Значення: Інструмент створює тіньову таблицю, копіює рядки і використовує тригери для синхронізації. Це уникає довгого блокування оригінальної таблиці, ціною додаткового навантаження і складності.

Рішення: Використовуйте, коли прямий ALTER ризикує блокуванням або перестроюванням. Розумійте тригери, реплікацію і поведінку cutover.

Завдання 14 (MySQL): виявити контенцію MDL через performance_schema

cr0x@server:~$ mysql -uroot -p -e "SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA='prod' AND OBJECT_NAME='orders' LIMIT 5;"
+--------------+-------------+-----------+------------+-----------+
| OBJECT_SCHEMA| OBJECT_NAME | LOCK_TYPE | LOCK_STATUS| THREAD_ID |
+--------------+-------------+-----------+------------+-----------+
| prod         | orders      | SHARED_READ | GRANTED  |     88121 |
| prod         | orders      | EXCLUSIVE   | PENDING  |     88210 |
+--------------+-------------+-----------+------------+-----------+

Значення: Є очікуване ексклюзивне блокування (ваш DDL), яке застрягло за наданими shared-блокуваннями (читаннями).

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

Три корпоративні міні-історії з передової

Міні-історія 1: інцидент через хибне припущення

У середньої за розміром маркетплейс-компанії команда запланувала «онлайн» створення індексу на MySQL. Зміну схвалив старший розробник, який пам’ятав, що InnoDB може будувати індекси без блокування записів. Усі розійшлися додому.

Через десять хвилин рівень помилок API підскочив. Латентність пішла вгору. CPU MySQL був нормальний. Диск навіть не був сильно зайнятий. Графіки виглядали як мережевий інцидент — саме так ви розумієте, що це інцидент бази даних.

Причина була в метадані-блокуванні. Довготривалий звітний запит тримав shared metadata lock на таблиці. ALTER TABLE ... ADD INDEX чекав ексклюзивного метадані-блокування. Коли він став у чергу, вхідні запити почали ставати в чергу за ним, бо їхні запити метаданих не могли отримати права таким чином, щоб не нашкодити очікуючому ексклюзивному блоку. Таблиця не просто сповільнилася; вона фактично припинила приймати нову роботу.

Хибне припущення було в тому, що «читання не блокують зміни схеми». У MySQL вони можуть. Виправлення не було героїчним: вбили звітну сесію, DDL пройшов, трафік відновився. Наступні кроки дали справжній ефект: таймаути для звітних запитів, вивантаження BI на репліки з обмеженнями та попередня перевірка на наявність довготривалих запитів на цільових таблицях.

Міні-історія 2: оптимізація, що увійшла боком

Фінтех-команда на PostgreSQL хотіла знизити латентність, змінивши колонку грошей з numeric на bigint у центах. Це слушна оптимізація: цілі числа швидші в операціях, індекси менші, і ви уникаєте дивних десяткових країв.

Вони спробували прямий шлях: ALTER TABLE ... ALTER COLUMN ... TYPE bigint USING .... На staging це пройшло швидко. У продакшні це взяло ACCESS EXCLUSIVE і почало перепис великої «гарячої» таблиці.

Додаток не впав одразу. Він загальмував. З’єднання накопичувалися. Оскільки зміна була в транзакції (як це заохочує Postgres), блокування трималося до коміту. Тим часом обсяг WAL зріс, репліки відстали, і команда втратила свій страховий сіток: вони більше не могли виконати failover без втрати частини підтверджених записів.

Оптимізація була правильна в принципі і катастрофічна в виконанні. Врешті-решт застосували поетапну міграцію: додали нову колонку, писали в обидві колонки на рівні додатку, бекафіли партіями, валідували, переключили читання і пізніше видалили стару колонку. Преформаційний бонус все одно отримали — просто не одним героїчним ALTER.

Міні-історія 3: нудна, але правильна практика, що врятувала день

Команда enterprise SaaS мала жорсткий runbook міграцій: кожна зміна схеми вимагала префлайту, канарки на репліці, явних налаштувань lock/algorithm (для MySQL) або поетапних обмежень (для Postgres) і плану відкату, що не включав відновлення з бекапу.

Потрібно було додати зовнішній ключ і зробити колонку NOT NULL у PostgreSQL. Саме такі зміни можуть викликати вечірку блокувань. Замість того, щоб робити це напряму, вони додали зовнішній ключ як NOT VALID, виправили сирітські рядки контрольованою задачею очищення, а потім провалідовували в період низької активності.

Для зміни на NOT NULL вони спочатку додали CHECK (col IS NOT NULL) NOT VALID, бекафілинули null-значення партіями, провалідовували перевірку, і тільки потім швидко виконали SET NOT NULL.

Нічого захопливого не сталося. Жодних інцидентів. Жодних оплесків. Але найкращі міграції — це ті, про які ніхто не чує, включно з вами.

Поширені помилки: симптом → корінна причина → виправлення

1) Симптом: трафік MySQL раптово стає на місці; CPU низький; багато з’єднань «waiting»

Корінна причина: накопичення MDL. ALTER чекає метадані-блокування; нові запити стають в чергу за ним.

Виправлення: Ідентифікуйте блокувальників, вбийте/обмежте довготривалі читачі і запускайте DDL з клауза ALGORITHM/LOCK. Використовуйте онлайн-інструменти для гарячих таблиць.

2) Симптом: PostgreSQL ALTER TABLE здається «застряглим» і блокує все на таблиці

Корінна причина: ACCESS EXCLUSIVE чекає на довгу транзакцію або запит.

Виправлення: Знайдіть блокувальника через pg_locks/pg_stat_activity, скасуйте його або перенесіть. Для зміни типів уникайте прямого ALTER; використовуйте expand/backfill/contract.

3) Симптом: використання диска різко зростає під час міграції Postgres, потім репліки відстають

Корінна причина: перезапис таблиці генерує багато записів у heap і WAL; стендбаї не встигають реплеїти.

Виправлення: Поетапна міграція, обмеження backfill, моніторинг WAL lag, забезпечення запасу дискового простору і розгляд тимчасового послаблення очікувань failover.

4) Симптом: відставання репліки MySQL зростає після «онлайн» створення індексу

Корінна причина: DDL і/або великі транзакції серіалізують застосування реплікації; побудова індексу конкурує з apply-потоком.

Виправлення: Моніторте Seconds_Behind_Master і стан SQL. Використовуйте інструменти онлайн-міграції з обмеженням швидкості; знижуйте швидкість записів; плануйте на низький трафік.

5) Симптом: Postgres CONCURRENTLY побудова індексу падає посередині

Корінна причина: CREATE INDEX CONCURRENTLY крихкіший: може зірватися через скасування, мертві кортежі або конфлікти; також залишає недійсні індекси.

Виправлення: Перевірте на недійсні індекси та drop/create заново. Виконуйте з операційним наглядом і таймаутами; уникайте під час нестабільності.

6) Симптом: «ALTER пройшов», але помилки додатку продовжуються

Корінна причина: невідповідність хореографії деплою: додаток очікує стовпець, якого ще немає/вже немає; ORM кешує схему; підготовлені запити.

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

7) Симптом: міграція викликає сильне уповільнення запитів через тиждень

Корінна причина: bloat/vacuum-борг у Postgres або change buffer/purge-борг в InnoDB від великих backfill-ів.

Виправлення: Пакетні backfill-и, налаштування vacuum, моніторинг bloat і history list length, планування постміграційного обслуговування.

Чек-листи / покроковий план

План A: expand → backfill → switch → contract (працює на обох)

1. Expand: додайте нові колонки/таблиці/індекси так, щоб це було безпечно онлайн (nullable-стовпці, concurrent-індекси, shadow-table інструменти).
2. Dual-write: якщо змінюються семантики (зміна типу, денормалізація), нехай додаток пише у старі й нові поля.
3. Backfill: заповнюйте історичні рядки пакетами з невеликими транзакціями; вставляйте паузи між пакетами; моніторте відставання і блокування.
4. Validate: обмеження в Postgres через NOT VALID і подальша валідація; в MySQL — перевірки запитами й/або перевірки на рівні додатку.
5. Switch reads: перемкніть читання додатку на нове поле за feature-flag; спостерігайте за рівнем помилок.
6. Contract: видаліть старі колонки/індекси після повного циклу деплою і підтвердження, що немає споживачів.

Чек-лист для MySQL

• Завжди вказуйте ALGORITHM і LOCK. Якщо MySQL не може виконати запит — дозвольте йому вивести помилку.
• Перед DDL перевірте довго працюючі запити на таблиці і вбийте або стравіть їх.
• Забезпечте запас дискового простору для перестроювань навіть якщо ви «очікуєте inplace». Очікуйте, що хоча б раз помилитеся.
• Визначте свою політику реплікації: чи можете ви терпіти відставання? Якщо ні — уповільнюйте або використовуйте інструменти з перевіркою lag на репліці.
• Краще використовувати gh-ost/pt-osc для великих гарячих таблиць, де ризик MDL неприпустимий.

Чек-лист для PostgreSQL

• Припускайте, що зміни типів і деякі операції обмежень вимагатимуть сильних блокувань або перезаписів.
• Використовуйте CREATE INDEX CONCURRENTLY для продакшн-записів; моніторте прогрес і плануйте відновлення у разі збою.
• Використовуйте NOT VALID для зовнішніх ключів/check-обмежень, а потім валідовувати пізніше.
• Слідкуйте за обсягом WAL і відставанням стендбаїв; вважайте це частиною доступності.
• Тримайте транзакції короткими під час backfill-ів; довгі транзакції шкодять vacuum і управлінню блокуваннями.

Гейт рішення: «Чи виконувати зараз?»

• Зелений: операція тільки з метаданих або concurrent/instant, розмір таблиці керований, немає довгих транзакцій, відставання реплік низьке.
• Жовтий: операція важка, але піддається обмеженню; у вас є план відкату; ви можете прийняти тимчасове відставання.
• Червоний: потребує перезапису таблиці + сильних блокувань у піковий час; реплікація вже відстає; мало вільного місця на диску; немає безпечного шляху відкату.

Питання й відповіді

1) Чи кращий PostgreSQL за MySQL для змін схеми?

Postgres часто передбачуваніший: зазвичай можна проаналізувати за режимами блокувань і чи операція перепише таблицю. MySQL може бути плавнішим для деяких «миттєвих» операцій, але накопичення MDL може створити раптові відмови.

2) Яка найнебезпечніша поведінка DDL у MySQL?

Накопичення метадані-блокувань. Очікуючий DDL може змусити нові запити стояти в черзі за ним, перетворюючи невелику проблему на повний застій.

3) Яка найнебезпечніша поведінка DDL у PostgreSQL?

Непередбачене викликання перезапису таблиці під ACCESS EXCLUSIVE на гарячій таблиці. Воно блокує і при цьому пише багато — найгірше з обох світів.

4) Чи можна без простою безпечно додати NOT NULL у Postgres?

Часто так, з поетапним підходом: додайте CHECK (...) NOT VALID, бекафіть, валідуйте, потім SET NOT NULL. Останній крок може бути швидким, якщо дані вже сумісні.

5) Чому моя Postgres-міграція «закінчилася», але продуктивність погіршилася пізніше?

Backfill-и створюють мертві кортежі і bloat; vacuum після цього має більше роботи. Також кеші зазнають значного перемішування через великі послідовні сканування і записи. Плануйте післяшок.

6) Чи гарантують інструменти онлайн-міграції в MySQL відсутність впливу?

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

7) Чи потрібно запускати DDL всередині транзакції в Postgres?

Залежить. Транзакційний DDL потужний, але може утримувати блокування довше, ніж очікується. Операції як CREATE INDEX CONCURRENTLY взагалі не можуть виконуватися в транзакційному блоці. Використовуйте транзакції, коли потрібна безпека відкату і час блокування контрольований.

8) Який найбезпечніший спосіб змінити тип стовпця в будь-якій з баз?

Expand/backfill/contract. Додайте новий стовпець потрібного типу, dual-write, бекафіть пакетами, переключіть читання, потім видаліть старий стовпець пізніше.

9) Як зупинити ALTER, що спричиняє проблеми?

MySQL: знайдіть сесію і KILL її, але майте на увазі, що сервер може ще прибирати за собою. Postgres: спробуйте pg_cancel_backend спочатку; pg_terminate_backend, якщо потрібно. Завжди підтверджуйте, що саме ви вбиваєте.

10) Яка база загалом «менш кошмарна»?

Якщо ви цінуєте інструментальну діагностику і поетапні примітиви коректності, Postgres зазвичай спокійніший. Якщо ви покладаєтесь на «миттєві» метадані-зміни і зовнішні інструменти, MySQL може бути відмінним — поки MDL не перетворить ваш день на інтерпретативний танець.

Наступні кроки, які можна зробити цього тижня

1. Перелік ваших топ-10 найбільших таблиць і позначте, які з них «гарячі» (високий QPS) проти «холодних». Це одразу змінить планування DDL.
2. Додайте префлайти до міграцій: блокувати, якщо на цільовій таблиці є довготривалі запити (ризик MDL в MySQL) або якщо існують блокувальники (ланцюг блокувань у Postgres).
3. Стандартизувати патерн міграцій: expand/backfill/contract для ризикових змін і вимагати явних «online» налаштувань (ALGORITHM/LOCK або CONCURRENTLY).
4. Визначте SLO реплікації під час обслуговування: яке відставання прийнятне і яка дія викликає паузу.
5. Практикуйте шлях abort: репетируйте, як скасувати/вбити безпечно, як виявити очищення і як відновити сервіс, якщо зміна зависла.

Коли хтось питає «MySQL проти PostgreSQL: хто робить ALTER TABLE кошмаром», чесна відповідь: той, кого ви ставите легковажно. Другу за чесністю відповідь: MySQL здивує вас блокуваннями, які ви не бачили; Postgres здивує роботою, на яку ви не заклали часу. Виберіть свої несподіванки — а потім спроєктуйте їх так, щоб уникнути.