MySQL проти PostgreSQL: чесний вибір «бази даних для сайту» (на основі реальних вузьких місць)

Теза: оберіть вузьке місце, з яким вам легше жити

Якщо ваш сайт — типовий вебдодаток: таблиці CRUD, сесії користувачів, замовлення, сторінки з елементами пошуку, кілька бекґраунд-джобів — ви можете успішно працювати і на MySQL, і на PostgreSQL. Чесна різниця не в «що швидше». Вона в тому:

• Який режим відмов частіше траплятиметься у вашій організації? (погані SQL‑запити? проблемні міграції? недбалі схеми? сумнівна гігієна індексів?)
• Який операційний робочий процес підходить вашій команді? (реплікація, переключення, резервні копії, міграції, аналіз запитів)
• Яке вузьке місце ви готові “доглядати”? (bloat/vacuum, конкуренція за блоки, відставання реплік, тиск на буферний пул, сплески підключень)

Мій упереджений дефолт для нової «бази даних для сайту» у 2025 році: PostgreSQL, якщо немає конкретної причини інакше. Не «бо ми завжди використовували MySQL». Причина має бути конкретна: «ми вже експлуатуємо MySQL в масштабі, у нас відроблені процедури переключення, є люди, що дебагують InnoDB, і наш додаток не потребує тих фіч, де Postgres сильніший».

Коли я схиляю людей до MySQL? Коли в компанії вже є оперативна пам’ять MySQL, навантаження переважно прості читання/записи з передбачуваними шляхами доступу, і коли ви хочете менше гострих крайок навколо кількості підключень (не тому що MySQL магічно імунний, а тому що per-connection накладні витрати Postgres швидше карають недбалий pooling).

Коли я рекомендую Postgres? Коли важлива коректність даних і адекватні семантики, коли ви робите більше ніж «SELECT по первинному ключу», коли очікуєте аналітичні запити, і коли хочете планувальник і екосистему індексів, що винагороджують хороше моделювання.

Жарт №1: Обирати базу даних за популярністю — як вибирати парашут за кольором: все добре, поки вам не доведеться його використовувати.

Цікаві факти й історія (бо це пояснює гострі краї)

Це не дрібниці заради дрібниць. Вони пояснюють, чому існують певні дефолти й поведінка.

1. PostgreSQL виріс із Postgres (1980‑ті) в UC Berkeley; його формували академічні ідеї, як MVCC і розширюваність, з самого початку.
2. Ранні успіхи MySQL були через «швидко і просто» для веб‑навантажень — особливо коли транзакції не були потрібні. Це спадщина досі помітна в екосистемі й практиках використання.
3. InnoDB став типовим движком збереження (для MySQL), бо веб вимагав транзакцій і відновлення після збоїв; сучасний MySQL фактично «InnoDB з SQL зверху».
4. Postgres реалізував MVCC без undo‑логів, у подібному стилі до InnoDB; замість цього старі версії рядків лишаються, поки їх не прибере VACUUM. Це корінь проблем bloat і налаштування vacuum.
5. Реплікація MySQL історично віддавала перевагу простоті (на основі binlog) і широкій сумісності; через це її всюди багато, у тому числі в стеках «зробили в 2012 і досі працює».
6. Розширення в Postgres — це культура першого класу (наприклад: кастомні типи індексів, повнотекстовий пошук, процедурні мови). Це не просто «база даних», це платформа.
7. Підхід до JSON розійшовся філософськи: JSONB у Postgres добре індексується; JSON у MySQL придатний і покращується, але має схильність штовхати команди до напівструктурних схем раніше.
8. Планувальник запитів Postgres відомий своїми уподобаннями і іноді помиляється в несподіваних випадках; зазвичай виправлення — це статистика й індекси, а не молитва.
9. Екосистема MySQL має кілька великих гілок і вендорів (community‑збірки, комерційні пропозиції, форки). Добре для вибору; але також добре для плутанини «яка саме поведінка у нас?».

Що насправді означає «база даних для сайту» (і що не означає)

Більшість сайтів — не чистий OLTP. Це заплутане поєднання:

• Гарячі шляхи запитів: логін, перевірки сесій, сторінки продуктів, операції кошика. Чутливі до затримки.
• Фонові завдання: відправлення емейлів, індексація, періодичні синхронізації. Чутливі до пропускної здатності.
• Адмінка/звіти: «покажи мені замовлення за статусом» з десятком фільтрів і сортуванням. Чутливі до планувальника.
• Пошук і стрічки: пагінація, сортування за часом, «рекомендоване». Чутливі до індексів.
• Випадкові сплески: маркетингові кампанії, краулери, cron, що зламався. Чутливі до підключень.

Обидві бази можуть це робити. Трюк у тому, що вони ламаються по‑різному:

• MySQL зазвичай карає блокуванням, сюрпризами реплікації і тим, що «було добре, доки не стало погано» — проблемами буферного пулу або I/O‑насиченістю.
• Postgres карає хвилями підключень, debt‑вакуумом, bloat і запитами, які з 50 мс раптово стають 50 с, коли статистика псується.

Ви не обираєте базу; ви обираєте набір операційних клопотів. Оберіть ті, що ваша команда справді виконуватиме.

Реальні вузькі місця: де що болить

1) Блокування та конкурентність: «чому все чекає?»

Postgres використовує MVCC і блокування на рівні рядка. Зчитування зазвичай не блокує записів і навпаки, поки ви не вперлися в явні блокування, перевірки зовнішніх ключів, довгі транзакції або зміни схеми. Коли щось йде не так, зазвичай це: одна довга транзакція тримає vacuum і створює чергу заблокованих запитів.

MySQL/InnoDB теж застосовує MVCC, але його поведінка блокувань (gap locks, next‑key locks) може дивувати при певних рівнях ізоляції й шаблонах доступу. Типовий відмовний сценарій: запит, що «повинен торкатися одного рядка», захоплює блоки над діапазоном через вибір індекса — і раптом у вас пробка трафіку.

2) Планування запитів і індексація: «чому цей запит став тупим?»

Postgres блищить при складних запитах, множинних JOIN, часткових індексах і функціональних індексах. Але він вимагає хороших статистик і дисциплінарної еволюції схеми. Коли autovacuum/analyze відстає, плани псуються. Ви бачите це як раптові послідовні скани або поганий порядок джоінів.

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

3) Реплікація і переключення: «додаток каже committed, а репліка не погоджується»

Обидві системи мають зрілу реплікацію. Обидві можуть вдарити по вам.

• MySQL реплікація всюди і добре зрозуміла. Класичний біль — відставання репліки при вибухах записів і операційна складність зміни топології, якщо ви її не спланували.
• Postgres streaming replication надійна, але потрібно явно вибирати trade‑off між synchronous та asynchronous. Частий біль — «ми думали, що репліки можна читати для всього», потім виявляються конфлікти hot standby або відставання при довгих запитах.

4) Поведінка движка збереження: кеш буферу, I/O і write amplification

MySQL/InnoDB хоче великий, правильно налаштований buffer pool. Коли він замалий або робочий набір виріс — відбувається thrash. Ви побачите зростання читань з диска, сплески латентності і сервер, що «виглядає нормально» по CPU, але вмирає по підсистемі зберігання.

Postgres залежить в значній мірі від page cache ОС плюс shared_buffers. Він може працювати відмінно, але чутливий до bloat таблиць і індексів. Bloat означає, що ви читаєте сторінки, заповнені мертвими кортежами та застарілими записами індексу. Ваша підсистема збереження платитиме за ваші гріхи.

5) Обслуговування: vacuum проти purge, і що означає «нудно»

Postgres вимагає vacuum. Autovacuum хороший, але «хороший» не означає «налаштував і забув». Якщо ви часто робите оновлення/видалення і не налаштували vacuum, зрештою ви натрапите на стіну: надування таблиць, повільні запити і можливі аварійні ситуації через transaction ID wraparound.

MySQL робить purge всередині (undo логи) і не має прямого еквівалента vacuum. Це менше налаштувань, але не безкоштовний обід: індекси, фрагментація і погані рішення по схемі все одно потребують управління. Також великі ALTER TABLE і поведінка online DDL можуть стати окремою особливою проблемою у вихідні.

6) Обробка підключень: Postgres карає недбалий pooling

Postgres у багатьох налаштуваннях використовує process‑per‑connection; надто багато підключень означає накладні витрати пам’яті, перемикання контексту і біль. Майже завжди ви хочете pooler (наприклад, pgbouncer) для вебзастосунків з бурстовим трафіком.

Модель потоків MySQL і типові дефолти можуть терпіти більше підключень до краху, але «терпіти» ≠ «безпечно». Якщо ви дозволите додатку відкривати тисячі підключень, бо «все працювало в staging», ви рано чи пізно отримаєте staging в production.

7) Фічі, що реально впливають на архітектуру сайту

• Postgres: сильні розширення, багаті типи індексів, кращі обмеження, розширені SQL‑функції, відмінна індексація JSONB, загалом суворіші семантики.
• MySQL: відмінна екосистема, широка підтримка хостингу, загальноприйняті операційні патерни, дуже сильна продуктивність для багатьох простих OLTP шаблонів.

Швидкий план діагностики (першочергово/далі/ще)

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

Першочергово: чи база чекає на CPU, I/O або блокування?

• CPU завантажений: шукати дорогі запити (погані плани), відсутні індекси, запуск звітів, spill‑и хешів/агрегатів.
• I/O завантажений: шукати пропуски в кеші/буфері, повні скани, bloat або регресію латентності зберігання.
• Блокування: шукати заблоковані запити, довгі транзакції, DDL або «гарячі» рядки/таблиці.
• Підключення: якщо кількість підключень сплескує, все інше може виглядати «нормально», поки додаток плавиться.

Друге: ідентифікуйте топ‑1–3 запити за сумарним часом, а не лише за найповільнішим

Найповільніший запит часто одноковий виняток. Вузьке місце зазвичай: «цей 20ms запит виконується 10 000 разів на хвилину» або «цей 200ms запит зараз виконується 500 одночасних копій».

Третє: перевірте план і шлях індексу

У Postgres: EXPLAIN (ANALYZE, BUFFERS). У MySQL: EXPLAIN плюс performance_schema і handler‑метрики. Ви шукаєте: несподівані скани, поганий порядок джоінів, тимчасові таблиці, filesort або величезні буферні читання.

Четверте: перевірте реплікацію і припущення додатку щодо читання/запису

Багато «база повільна» тікетів насправді — «відставання репліки + припущення read‑after‑write». Це не філософія. Це буквально користувач, що натиснув «зберегти» і побачив старі дані.

П’яте: зупиніть кровотечу, потім виправляйте корінь

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

Цитата (парафраз), John Allspaw: Надійність походить від того, як ви реагуєте на відмову, а не від того, що ви вдаєте, ніби відмов не буде.

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

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

Завдання 1 (Postgres): хто що виконує і хто чекає

cr0x@server:~$ psql -X -c "SELECT pid, usename, state, wait_event_type, wait_event, now()-query_start AS age, left(query,80) AS query FROM pg_stat_activity WHERE datname='appdb' ORDER BY age DESC LIMIT 10;"
 pid  | usename | state  | wait_event_type |  wait_event   |   age   |                                      query
------+--------+--------+-----------------+---------------+---------+--------------------------------------------------------------------------------
 8123 | app    | active | Lock            | transactionid | 00:05:12| UPDATE orders SET status='paid' WHERE id=$1
 7991 | app    | active |                 |               | 00:01:44| SELECT * FROM orders WHERE user_id=$1 ORDER BY created_at DESC LIMIT 50
 7902 | app    | idle   | Client          | ClientRead    | 00:00:33| 

Що це означає: PID 8123 чекає на lock transactionid вже 5 хвилин. Зазвичай це довга транзакція десь ще, що тримає ресурс, або патерн contention на «гарячому» рядку.

Рішення: Знайдіть блокувальника (наступне завдання), потім вирішіть, чи вбити його, виправити логіку додатку або додати індекс/підкоригувати рівні ізоляції.

Завдання 2 (Postgres): знайти запит‑блокувальник

cr0x@server:~$ psql -X -c "SELECT blocked.pid AS blocked_pid, blocker.pid AS blocker_pid, now()-blocker.query_start AS blocker_age, left(blocker.query,120) AS blocker_query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid=blocked.pid JOIN pg_locks kl ON kl.locktype=bl.locktype AND kl.database IS NOT DISTINCT FROM bl.database AND kl.relation IS NOT DISTINCT FROM bl.relation AND kl.page IS NOT DISTINCT FROM bl.page AND kl.tuple IS NOT DISTINCT FROM bl.tuple AND kl.virtualxid IS NOT DISTINCT FROM bl.virtualxid AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid AND kl.classid IS NOT DISTINCT FROM bl.classid AND kl.objid IS NOT DISTINCT FROM bl.objid AND kl.objsubid IS NOT DISTINCT FROM bl.objsubid AND kl.pid != bl.pid JOIN pg_stat_activity blocker ON blocker.pid=kl.pid WHERE NOT bl.granted;"
 blocked_pid | blocker_pid | blocker_age |                        blocker_query
-------------+-------------+-------------+-------------------------------------------------------------
 8123        | 7701        | 00:12:09    | BEGIN; UPDATE users SET last_seen=now() WHERE id=$1; -- no COMMIT yet

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

Рішення: Вбити блокер PID 7701, якщо безпечно; потім виправити код, щоб уникати довгих транзакцій; додати таймаути; провести аудит області транзакцій.

Завдання 3 (Postgres): перевірити здоров’я vacuum і ризик bloat

cr0x@server:~$ psql -X -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
   relname   | n_live_tup | n_dead_tup |    last_autovacuum     |    last_autoanalyze
-------------+------------+------------+------------------------+------------------------
 events      |   24000000 |    9800000 | 2025-12-29 01:12:43+00 | 2025-12-29 00:55:21+00
 sessions    |    3100000 |    1200000 | 2025-12-28 22:41:10+00 | 2025-12-28 22:40:58+00
 orders      |     900000 |      12000 | 2025-12-29 01:10:03+00 | 2025-12-29 01:10:02+00

Що це означає: Таблиця events має велику кількість мертвих кортежів. Якщо запити по цій таблиці з часом гальмують, bloat — головний підозрюваний.

Рішення: Налаштувати autovacuum для цієї таблиці, розглянути партиціювання, зменшити churn при оновленнях і перевірити, чи індекси теж не забиті.

Завдання 4 (Postgres): підтвердити поганий план через EXPLAIN ANALYZE

cr0x@server:~$ psql -X -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id=42 ORDER BY created_at DESC LIMIT 50;"
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..2451.11 rows=50 width=512) (actual time=1832.504..1832.573 rows=50 loops=1)
   Buffers: shared hit=102 read=9180
   ->  Seq Scan on orders  (cost=0.00..98010.22 rows=1999 width=512) (actual time=0.041..1829.721 rows=950000 loops=1)
         Filter: (user_id = 42)
         Rows Removed by Filter: 899000
 Planning Time: 0.412 ms
 Execution Time: 1832.711 ms

Що це означає: Послідовний скан з величезними читаннями. Сервер прочитав приблизно 9k буферів з диска. Це кричить «відсутній індекс» (ймовірно (user_id, created_at)).

Рішення: Додайте індекс, потім перевірте ще раз. Якщо індекс існує — перевірте статистики і чи запит відповідає порядку індексу.

Завдання 5 (Postgres): перевірити використання індексів і знайти невикористовувані

cr0x@server:~$ psql -X -c "SELECT relname AS table, indexrelname AS index, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC LIMIT 10;"
  table  |          index           | idx_scan |  size
---------+--------------------------+----------+---------
 events  | events_payload_gin       |        0 | 312 MB
 orders  | orders_status_created_at |        2 | 148 MB
 users   | users_email_key          |     9012 |  42 MB

Що це означає: Великі індекси з майже нульовим використанням можуть бути магнітом для bloat і спричиняти write‑amplification. Але не видаляйте їх сліпо — деякі потрібні для рідкісних адмінських запитів або обмежень.

Рішення: Підтвердіть через логи запитів і пошук у коді; якщо дійсно не використовуються — видаліть, щоб пришвидшити записи й vacuum.

Завдання 6 (MySQL): подивитися активні сесії і чи чекають вони блокувань

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST;"
Id	User	Host	db	Command	Time	State	Info
221	app	10.0.1.12:55342	appdb	Query	120	Waiting for row lock	UPDATE orders SET status='paid' WHERE id=12345
238	app	10.0.1.15:49810	appdb	Query	15	Sending data	SELECT * FROM orders WHERE user_id=42 ORDER BY created_at DESC LIMIT 50
250	app	10.0.1.16:51011	appdb	Sleep	300		NULL

Що це означає: Є очікування блокувань і також сплячі з’єднання. «Waiting for row lock» з чергою означає contention, зазвичай через шаблон індекса або транзакцію, що тримається відкритою.

Рішення: Ідентифікуйте блокувальника через InnoDB lock tables, виправте обсяг транзакцій і індексацію.

Завдання 7 (MySQL): знайти ланцюжок очікувань InnoDB

cr0x@server:~$ mysql -e "SELECT * FROM information_schema.INNODB_TRX\G"
*************************** 1. row ***************************
trx_id: 54100912
trx_state: LOCK WAIT
trx_started: 2025-12-29 01:20:01
trx_mysql_thread_id: 221
trx_query: UPDATE orders SET status='paid' WHERE id=12345
*************************** 2. row ***************************
trx_id: 54100901
trx_state: RUNNING
trx_started: 2025-12-29 01:05:43
trx_mysql_thread_id: 199
trx_query: UPDATE orders SET shipping_label=... WHERE id=12345

Що це означає: Тред 199 виконує транзакцію з 01:05. Це не нормально для веб‑запиту. Ймовірно, джоб додатку тримає транзакцію під час повільної операції (API‑виклик, завантаження файлу, цикл ретраїв).

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

Завдання 8 (MySQL): швидко перевірити відставання репліки

cr0x@server:~$ mysql -e "SHOW SLAVE STATUS\G"
Seconds_Behind_Master: 87
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replica_SQL_Running_State: Waiting for dependent transaction to commit
Last_SQL_Error:

Що це означає: Репліка відстає приблизно на 87 секунд. Читання з неї будуть застарілими. «Waiting for dependent transaction to commit» може вказувати на вибухи записів або блокування в треді apply репліки.

Рішення: Маршрутизуйте критичні читання на primary тимчасово; зменшіть write‑amplification; перевірте наявність великих транзакцій; налаштуйте паралельність apply, якщо можливо.

Завдання 9 (Postgres): перевірити відставання реплікації і стан відтворення

cr0x@server:~$ psql -X -c "SELECT client_addr, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
 client_addr |   state   | sync_state | write_lag | flush_lag | replay_lag
-------------+-----------+------------+-----------+-----------+------------
 10.0.2.21   | streaming | async      | 00:00:01  | 00:00:02  | 00:00:24

Що це означає: Replay lag — 24 секунди. Якщо додаток читає з реплік, ви побачите read‑after‑write аномалії, якщо не врахувати це в коді.

Рішення: Реалізуйте «read‑your‑writes» маршрутизацію (stickiness) або переключіться на synchronous replication для тієї частини трафіку, що потребує цього.

Завдання 10 (Postgres): виявити сплески підключень і потребу в pooler

cr0x@server:~$ psql -X -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count(*) DESC;"
 state  | count
--------+-------
 idle   | 420
 active | 85
 null   | 0

Що це означає: Сотні idle‑підключень означають, що ваш додаток або пул тримають з’єднання відкритими. Це накладні витрати пам’яті і може стати fork‑бомбою при сплеску трафіку.

Рішення: Поставте справжній pooler, задайте розумні max_connections і налаштуйте розміри пулів у додатку.

Завдання 11 (Linux): підтвердити, чи ви I/O‑bound

cr0x@server:~$ iostat -x 1 3
Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         820.0   210.0 51200.0 18400.0   9.80   0.45  89.0

Що це означає: %util близько 90% і await ≈10ms вказують, що диск зайнятий і запити ставляться в чергу. Якщо одночасно з цим зростають затримки чутливих до латентності запитів — ви I/O‑bound.

Рішення: Зменшити читання (вирішити скани, bloat), збільшити пам’ять/кеш‑хіт‑відсоток або перейти на швидше зберігання. Також перевірити, чи не насичує I/O бекап, vacuum або пакетна задача.

Завдання 12 (Postgres): подивитися співвідношення кеш‑хітів і буферні читання

cr0x@server:~$ psql -X -c "SELECT datname, blks_hit, blks_read, round(blks_hit*100.0/nullif(blks_hit+blks_read,0),2) AS hit_pct FROM pg_stat_database WHERE datname='appdb';"
 datname | blks_hit  | blks_read | hit_pct
---------+-----------+-----------+---------
 appdb   | 190224455 |   9221440 |   95.38

Що це означає: 95% хіт‑рейт — непогано, але якщо він падає під піком або якийсь запит викликає величезні читання, ви це відчуєте. «Hit rate» — не трофей; це підказка.

Рішення: Якщо читання ростуть — розслідуйте bloat і відсутні індекси. Якщо загальний hit rate низький — перегляньте розмір пам’яті та шаблони запитів.

Завдання 13 (MySQL): перевірити ефективність InnoDB buffer pool

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
Variable_name	Value
Innodb_buffer_pool_read_requests	184455912
Innodb_buffer_pool_reads	2219441

Що це означає: Промахи буферного пулу = Innodb_buffer_pool_reads. Якщо промахи швидко ростуть під час трафіку — робочий набір не вміщується або запити сканують.

Рішення: Додати індекси, зупинити скани і правильно розмірити buffer pool. Якщо датасет виріс за межі RAM — плануйте репліки для читання, шардинг або кращі патерни кешування.

Завдання 14 (Обидві): перевірити зростання місця на диску і траєкторію «о, ми заповнились»

cr0x@server:~$ df -h /var/lib
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p2  1.8T  1.6T  120G  94% /var/lib

Що це означає: 94% заповнення — це не «нормально». Це прелюдія інциденту. Бази поводяться погано, коли не можуть розширити файли, не можуть комфортно чекпоінтитись або записувати тимчасові дані.

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

Завдання 15 (Postgres): перевірити вік транзакцій і ризик wraparound

cr0x@server:~$ psql -X -c "SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;"
 datname  | xid_age
----------+---------
 appdb    | 145000000
 template1|   4300000

Що це означає: Великий вік XID означає, що vacuum не встигає фризити кортежі. Якщо він підходить до небезпечних порогів, ви отримаєте аварійний autovacuum і падіння продуктивності.

Рішення: Дізнайтеся, чому vacuum не справляється (довгі транзакції, неправильно налаштований autovacuum, гігантські таблиці) і виправте до того, як це стане критичним.

Завдання 16 (MySQL): захопити топові дайджести запитів (performance_schema)

cr0x@server:~$ mysql -e "SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1000000000000,2) AS total_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 3;"
DIGEST_TEXT	COUNT_STAR	total_s
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT ?	120000	980.12
UPDATE users SET last_seen = NOW() WHERE id = ?	900000	410.33
SELECT * FROM products WHERE status = ? ORDER BY updated_at DESC LIMIT ?	80000	155.20

Що це означає: Найбільший сумарний час споживає перший запит, навіть якщо він «доволі швидкий» за один виклик. Він виконується постійно.

Рішення: Прондексувати, кешувати або зменшити кількість викликів. Сумарний час важливіший за «найповільніший запит» для більшості веб‑інцидентів.

Три міні‑історії з компаній (анонімізовані, правдоподібні й болісно знайомі)

Міні‑історія 1: інцидент через неправильне припущення (репліки «майже такі самі»)

Середній SaaS мав чисту архітектуру: primary для записів, репліка для «всього іншого». Розробники трактували репліку як взаємозамінну. Запит записував рядок, а потім одразу читав його — з будь‑якого з’єднання, яке ORM виділяв.

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

Потім стався інцидент: користувачі оновлювали платіжні дані, бачили «Збережено!» і при оновленні сторінки — стару адресу. Підтримка закипіла. Інженери звинувачували кеш, потім CDN, потім «фронтенд». Графіки бази виглядали «нормально», бо CPU не був завантажений і латентність була лише трохи підвищена.

Корінь був нудний: read‑after‑write неконсистентність через асинхронну реплікацію. Неправильне припущення стало фатальним, коли бізнес‑процес вимагав коректності в UI.

Виправлення не було героїчним. Вони реалізували stickiness: після запису читання цього користувача прив’язувалися до primary на короткий час (або до закінчення сесії). Також зробили видимим відставання реплік на дашбордах і маршрутизували певні ендпоінти (білінг, авторизація) завжди на primary. Після цього «інциденти БД» стали архітектурним обмеженням, а не сюрпризом.

Міні‑історія 2: оптимізація, що відгукнулась фіаско (продиндексовали все й платять вічно)

Платформа e‑commerce вирішила «випередити проблеми продуктивності». Старший розробник додав індекси для кожної потенційної комбінації фільтрів в адмінці: статус, діапазон дат, країна, платіжний провайдер, SKU, код маркетингової кампанії. Схема виглядала як їжак.

Читання стали швидкі. Усі себе похвалили. Потім настав Black Friday. Записи сповільнилися, і не трохи. Латентність оформлення замовлення зросла. CPU був у порядку. Сховище почало трястись. Відставання реплік зросло. Платформа вижила, але в режимі «не можна деплоїти два дні».

У постмортемі вони виявили очевидне, чого ніхто не хотів сказати: кожен insert/update мав підтримувати купу рідко використовуваних індексів. Шлях запису платив «ренту» за фантазії адмінки. Гірше — обслуговування vacuum (Postgres) або фонове керування сторінками (MySQL) мало більше роботи. Система стала «швидкі читання, повільне все інше», і бізнес‑вузьке місце було чек‑аут.

Виправлення — дисциплінована жорсткість. Залишили невелику групу індексів з високою цінністю, пов’язаних з реальними топ‑запитами, і видалили решту. Для адмінських звітів перенесли важкі запити на репліку з більш вільними SLO і зробили summary‑таблиці для поширених звітів. Продуктивність покращилася, але головне: база перестала бути крихким кришталем.

Міні‑історія 3: нудна, але правильна практика, що врятувала день (відпрацювання відновлення)

Контентний сайт використовував Postgres. Нічого складного: primary, одна репліка, нічні бекапи. Команда не була відома процесністю. Але один інженер наполягав на квартальних restore‑дрилях — як флос для інфраструктури.

У них був чекліст: взяти останню base backup, відновити в ізольоване середовище, відпрацювати WAL до точки в часі, виконати sanity‑запити і перевірити, чи додаток може стартувати проти нього. Вони робили це, коли ніхто не палає, тому це здавалося рутинною справою. Це також була єдина причина, чому вони знали, що бекапи справжні.

Через місяці одна міграція випадково видалила колонку, яку використовував фоновий джоб. Джоб ретраївся, падав, ретраївся і записував сміттєві дані в іншу таблицю. Радіус ушкоджень не був миттєвий; це була повільна корупція — найгірший тип. Коли вони помітили, погані дані вже репліковані й забекаплені кілька разів.

Вони виконали point‑in‑time recovery до часу перед міграцією, верифікували через ті самі drill‑запити і відновили сервіс з мінімальною драмою. Інцидент усе одно трапився, але не став «кар’єрним» подією.

Жарт №2: Плани резервного копіювання як абонементи в спортзал — сам факт володіння не змінює нічого, поки ви ними не користуєтесь.

Типові помилки: симптом → корінь → виправлення

1) Симптом: «CPU БД низький, але запити тайм‑аутяться»

Корінь: Блокування або насичення пулу підключень. База здебільшого чекає, а не працює.

Виправлення: Ідентифікувати блокуючі запити/транзакції; скоротити області транзакцій; додати таймаути; обмежити паралелізм; ввести pooler (Postgres) або налаштувати ліміти потоків/підключень (MySQL).

2) Симптом: «Один запит повільний лише в проді»

Корінь: Інша розподільність даних, відсутні статистики або різні індекси. У проді є скоси і гарячі точки; staging — мрії про мир.

Виправлення: Захопити production EXPLAIN/ANALYZE; оновити статистики; додати цільові індекси; розглянути часткові індекси (Postgres) або покривні композитні індекси (MySQL).

3) Симптом: «Репліка показує старі дані»

Корінь: Асинхронне відставання реплікації і припущення додатку про read‑after‑write.

Виправлення: Читати з primary після записів (stickiness), або використати synchronous replication для критичних операцій, або зробити UI, що толерує eventual consistency явно.

4) Симптом: «Місце на диску росте вічно, хоч трафік стабільний»

Корінь: Bloat Postgres через churn оновлень/видалень; зростання таблиць/індексів у MySQL через фрагментацію або необмежену ретеншн політику.

Виправлення: Postgres: налаштувати autovacuum, зменшити churn оновлень, партиціонувати таблиці з інтенсивним churn, розглянути періодичний REINDEX/VACUUM FULL з планом простою. MySQL: архівувати/пуржити старі дані, акуратно перебудувати таблиці, перевірити надмірні індекси.

5) Симптом: «Деплой спричинив повний аутейдж, потім відновив»

Корінь: Блокуючий DDL або міграція, що переписала велику таблицю, наситивши I/O і блокуючи запити.

Виправлення: Використовувати online schema changes, розбивати міграції, додавати колонки без дефолтів спочатку, бекапфілити бекфіл батчами, потім застосовувати обмеження.

6) Симптом: «Все повільно, коли запускається звіт»

Корінь: Довгі запити, що споживають I/O і буфери, або тримають блокування, або викликають конфлікти реплікації (Postgres hot standby).

Виправлення: Маршрутизувати звіти на репліку, додати statement timeouts, попередньо обчислювати агрегати, індексувати під звіт або заборонити його в робочі години.

7) Симптом: «Продуктивність гіршає за дні, а потім тимчасово покращується після обслуговування»

Корінь: Debt від vacuum і bloat у Postgres, або churn buffer pool у MySQL через зміну робочого набору і зростання індексів.

Виправлення: Postgres: налаштувати autovacuum і analyze; моніторити мертві кортежі і freeze age. MySQL: перевірити розмір buffer pool, відсутні індекси і прибрати write‑amplifying індекси.

8) Симптом: «Високі сплески латентності під час бурсту трафіку»

Корінь: Сплески підключень, черги до бази або насичення диска I/O.

Виправлення: Встановити жорсткі ліміти на паралелізм, забезпечити pooling, використовувати зворотний тиск і вимірювати глибину черги/await на сховищі.

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

Чекліст для рішення: обираємо MySQL чи PostgreSQL для сайту

• Якщо у вашої команди є глибока MySQL‑операційна майстерність (реплікація, апґрейди, тонке налаштування індексів, відновлення бекапів) і ваше навантаження — простий OLTP: обирайте MySQL і рухайтесь далі.
• Якщо ви очікуєте складні запити, багатші обмеження і хочете адекватні SQL‑семантики з екосистемою розширень: обирайте Postgres.
• Якщо ви не готові до дисципліни пулінгу підключень: MySQL зазвичай довше вам пробачить, але це борг. Виправте pooling у будь‑якому разі.
• Якщо ви часто робите масштабні оновлення/видалення великих таблиць: Postgres потребує уваги до vacuum; MySQL — дисципліни індексів і purge. Обирайте за тим, який режим обслуговування ви виконаєте надійно.
• Якщо ви сильно покладаєтесь на read‑репліки: проектуйте eventual consistency з першого дня, незалежно від СУБД.

Налаштування першого тижня: продова гігієна (обидві бази)

1. Увімкніть видимість запитів: slow query logs (MySQL) або pg_stat_statements (Postgres). Якщо ви не бачите запитів — ви дебагуєте по відчуттях.
2. Встановіть таймаути: statement timeouts, lock timeouts і transaction timeouts відповідні до веб‑запитів.
3. Налагодьте бекап + тест відновлення: один скриптований шлях відновлення, який хтось може запустити в стресі.
4. Визначте семантику реплікації: які ендпоінти можуть читати з реплік; зафіксуйте це в коді.
5. Задайте політику міграцій: без перепису таблиць під час піків, розбити backfill, батч‑оновлення.

Workflow продуктивності: коли сторінка повільна

1. Знайдіть ендпоінт і зв’яжіть з топ‑запитами за сумарним часом.
2. Захопіть EXPLAIN/ANALYZE (або MySQL EXPLAIN + digest‑статистики).
3. Перевірте, чи запит відсутній індекс або використовує неправильний.
4. Підтвердіть кількість рядків і селективність (skew буває).
5. Виправте шлях доступу (індекс або перепис запиту) перед тим, як чіпати серверні налаштування.
6. Тільки потім розгляньте налаштування і зміну обладнання.

План зміни схеми: безпечні міграції без драм

1. Спочатку додаткове: додавайте нові колонки nullable, без дефолтів, без обмежень спочатку.
2. Backfill батчами: невеликі транзакції, паузи між батчами, моніторинг відставання реплік.
3. Якщо треба — dual‑write: записуйте одночасно в старе й нове поле під час rollout.
4. Додавайте обмеження вкінці: валідуйте ретельно, бажано онлайн/з малою імпактністю, якщо підтримується.
5. Видаляйте старі поля тільки після верифікації: і тільки коли маєте шляхи відкату.

Поширені питання

1) Що швидше для типового сайту: MySQL чи PostgreSQL?

За замовчуванням — ні. Найшвидша база — та, у якої правильні індекси, нормальні запити і немає самонаведених проблем з реплікацією/блокуванням. Для простого OLTP обидві швидкі. Для складних запитів Postgres часто перемагає — доки ви не запустите його без статистик і vacuum.

2) Яка більш «надійна»?

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

3) Чи потрібен пулер підключень з PostgreSQL?

Для веб‑додатків з бурстовим трафіком: так, практично завжди. Без pooler ви рано чи пізно вдаритеся в сплески підключень, накладні витрати пам’яті і погані хвости латентності. З pooler Postgres значно спокійніший під навантаженням.

4) Чи є vacuum у Postgres вироком?

Ні, але це відповідальність. Якщо ваші дані переважно вставки з малою кількістю оновлень/видалень — vacuum простий. Якщо у вас високий churn оновлень у великих таблицях — потрібно моніторити і налаштовувати autovacuum або партиціювати. Якщо команда не робитиме цього, ви ставитеся проти фізики.

5) Чи безпечні репліки для «звичайних читань»?

Безпечні для некритичних читань — так, якщо ви погоджуєтеся з eventual consistency. Небезпечні для read‑after‑write сценаріїв, якщо ви не реалізуєте stickiness або synchronous replication для цих шляхів коду.

6) Хто краще працює з JSON для сайту?

JSONB у Postgres зазвичай кращий, якщо вам потрібна індексація і запити по полям JSON. JSON у MySQL працює, але команди часто дрейфують до зберігання надто багато напівструктурованих даних без обмежень. Якщо JSON — перший клас об’єкту для запитів — Postgres безпечніша ставка.

7) Яка головна причина інцидентів «база повільна»?

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

8) Якщо ми вже працюємо на одній СУБД, чи слід переходити?

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

9) Як вибрати для e‑commerce конкретно?

Оберіть ту, якою ваша команда може оперувати без героїчних зусиль. E‑commerce потребує коректності (інвентар, платежі) і передбачуваної латентності. Postgres — сильний дефолт, якщо ви можете забезпечити pooling і vacuum. MySQL чудовий, якщо у вас вже є зріла MySQL‑операційна команда і ви дисциплінуєте транзакції та індекси.

10) Що моніторити з першого дня?

Розподіл латентності запитів (p95/p99), топ‑запити за сумарним часом, очікування блокувань, відставання реплік, кількість підключень, зростання дискового простору і латентність зберігання (await). Це знаходить вузькі місця раніше за клієнтів.

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

1. Оберіть базу за вузькими місцями, які ви можете управляти, а не за ідеологією. Якщо сумніваєтесь, дефолт — Postgres для нових проєктів.
2. Інструментуйте видимість запитів (дайджести, slow logs, pg_stat_statements). Без видимості немає правди.
3. Впровадьте дисципліну підключень: pooler для Postgres, розумні розміри пулів для обох.
4. Запишіть семантику реплікації: які ендпоінти можуть терпіти застарілі читання; зафіксуйте це в коді.
5. Заплануйте відновлення з резервної копії і проведіть його. Якщо вам це некомфортно — ви знайшли реальний ризик.
6. Побудуйте рутину «топ‑запитів»: тижневий огляд топ‑споживачів часу і топ‑учасників блокувань.

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