MySQL проти PostgreSQL: чому той самий запит швидкий в одній, а в іншій — катастрофічний

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

Коли запит швидкий в MySQL і катастрофічний у PostgreSQL (або навпаки), опирайтесь спокусі почати «налаштовувати» ручки. Спочатку визначте, в якій із трьох категорій ви опинились: поганий план, блокування або I/O‑зв’язана проблема. Тільки тоді виправляйте реальну проблему замість того, щоб перекладати меблі сервера.

Перший крок: доведіть, що це проблема плану (або ні)

1. Захопіть план:
   • Postgres: EXPLAIN (ANALYZE, BUFFERS)
   • MySQL: EXPLAIN ANALYZE (8.0+) та SHOW WARNINGS за потреби
2. Шукайте брехню кардинальності: оцінена кількість рядків проти фактичної. Якщо різниця в 10× — вже проблеми. Якщо в 100× — ви вже в режимі incident response.
3. Визначте домінуючий вузол: крок з найвищим фактичним часом (Postgres) або найвищим «actual time»/loops (MySQL 8.0 analyze). Саме там треба концентрувати зусилля.

Другий крок: виключіть блокування та довгі транзакції

1. Postgres: перевірте pg_stat_activity на wait_event і довгі транзакції.
2. MySQL: перевірте SHOW PROCESSLIST та очікування InnoDB lock waits.
3. В обох: якщо писач тримає блоки або довга транзакція перешкоджає прибиранню, ви можете нескінченно оптимізувати й усе одно програєте.

Третій крок: визначте, чи ви I/O‑зв’язаний, CPU‑зв’язаний чи обмежений пам’яттю

1. Якщо план показує багато промахів буфера / зчитувань, ви I/O‑зв’язаний.
2. Якщо CPU завантажений, а план містить великі сортування/хеші, ви CPU/пам’ять‑зв’язаний.
3. Якщо сплески латентності корелюють з чекпоінтами, скиданням буферів або fsync‑тиском, ви обмежені сховищем.

Парафразуючи ідею Вернера Фогельса: «Проєктуйте під невдачу як нормальний стан». Застосуйте це й до продуктивності запитів — розраховуйте й експлуатуйте, припускаючи, що плани інколи підведуть.

Чому «той самий запит» рідко буває тим самим

У продакшені «той самий запит» зазвичай означає «той самий SQL‑рядок». СУБД переймаються рештою: деталями схеми, правилами колації, типами даних, індексами, статистикою та конкуренцією. Запит може бути логічно еквівалентним, але фізично дуже відрізнятись.

Типи даних і неявні приведення: тихі вбивці планів

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

Класичний приклад: порівняння стовпця uuid з текстовим параметром у Postgres може змусити привести стовпець, роблячи індекс непридатним. MySQL може порівнювати рядки й використовувати індекс, якщо колація й правила префікса дозволяють. А може й ні. Послідовність — це функція, яку треба здобути.

Колації й порівняння тексту

Колації MySQL (особливо чутливі до регістру за замовчуванням) можуть змінювати здатність індексу працювати й поведінку порівнянь. Поведінка колацій у PostgreSQL залежить від libc/ICU і може впливати на порядок сортування, операторні класи індексів та продуктивність. Якщо ви мігрували дані й не перенесли колації цілеспрямовано, ви не мігрували навантаження — ви перемістили його й сподівались, що воно поводитиметься аналогічно.

Параметризація та кешування планів

PostgreSQL може вибрати generic‑план для підготовлених запитів, який «добрий в середньому», але поганий для поширеного значення параметра. Поведінка MySQL відрізняється залежно від конектора і чи використовуються server‑side prepares. Якщо ваш застосунок змінив спосіб прив’язки параметрів, ваш план може кардинально змінитись, хоч SQL‑рядок виглядає ідентично.

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

Історичний контекст: звідки беруться ці відмінності

• Факт 1: PostgreSQL походить від проєкту POSTGRES в UC Berkeley (кінець 1980‑х), який зосереджувався на плануванні запитів і розширюваності як на першорядних цілях.
• Факт 2: MySQL почався в середині 1990‑х з акцентом на швидкість і простоту для веб‑навантажень; ранні версії мали обмежену підтримку підзапитів і простіший оптимізатор.
• Факт 3: InnoDB став дефолтним рушієм у MySQL з 5.5; до того багато розгортань використовували MyISAM з іншими властивостями блокувань і надійності.
• Факт 4: MVCC у PostgreSQL довго був центральним елементом, але він вимагає vacuum для повернення простору — продуктивність страждає, якщо ігнорувати цей життєвий цикл.
• Факт 5: MySQL 8.0 ввів більш здатний словник даних і покращені можливості оптимізатора; поради з часів 5.6 можуть бути шкідливими.
• Факт 6: PostgreSQL 12+ покращив партиціювання і поведінку планувальника для партицій; старіші версії могли погано планувати партиційовані запити.
• Факт 7: Реплікація MySQL історично була statement‑ або row‑based з різними компромісами; репліки можуть поводитись інакше під навантаженням через apply lag і metadata locking.
• Факт 8: JIT (LLVM) у PostgreSQL може прискорювати деякі запити, але також додає накладні витрати; це залежить від робочого навантаження й може здивувати при системах чутливих до латентності.

Поведінка оптимізатора, що змінює все

Оцінка кардинальності: база даних гадає, і вона гадає по‑різному

Більшість історій «швидко в БД A, повільно в БД B» зводяться до оцінки кількості рядків. Оптимізатор обирає порядок і алгоритми з’єднань на основі очікуваної кількості рядків. Якщо він помиляється — обирає неправильну зброю.

PostgreSQL: використовує статистику з ANALYZE: по‑стовпчикові stats, most‑common‑values, гістограми, кореляцію та (опційно) розширені статистики між стовпцями. Це добре, але не ясновидне. Якщо предикати залучають кілька корельованих стовпців, стандартні статистики можуть сильно недооцінювати.

MySQL: використовує статистику рушія; InnoDB статистики можуть бути персистентними або перераховуватись; гістограми є і допомагають у 8.0. Без гістограм InnoDB може припускати рівномірний розподіл і робити трагічні вибори при скійованих даних.

Алгоритми з’єднання та коли кожен рушій їх віддає перевагу

PostgreSQL має сильний набір інструментів: nested loop, hash join, merge join. Він також може робити bitmap index scans, щоб поєднати кілька індексів. MySQL історично тяжів до nested loops (включно з block nested loop) і поступово покращувався, але підхід інший.

Це важливо, бо «одне велике JOIN» може бути швидким, якщо планувальник обрав hash join для великих наборів, або катастрофічним, якщо вибрав nested loop з неправильно оціненим внутрішнім результатом.

CTE, підзапити та «огорожі» оптимізації

Старіші версії PostgreSQL трактували CTE (WITH) як бар’єри оптимізації: їх матеріалізували й не дозволяли просування предикатів. Новіші версії можуть інлайнити CTE, але не завжди. MySQL трактує похідні таблиці інакше і має власні правила матеріалізації.

Якщо ваш «той самий SQL» використовує CTE, трактування планувальником може стати різницею між мілісекундами і «cancelled by on‑call».

Сортування, хешування та параметри пам’яті

Postgres використовує work_mem на операцію сортування/хешування, на вузол запиту, на кожного паралельного воркера. Один запит може створити кілька сортувань; поставте занадто високе значення — отримаєте сплеск пам’яті. Занадто низьке — вигризе на диск і доведеться плакати.

MySQL використовує інші структури пам’яті (join buffer, sort buffer, tmp tables). Він створює on‑disk тимчасові таблиці залежно від розміру та типів даних. Обидва рушії можуть здаватися «повільними на CPU», коли фактично «виливаються на диск повільно».

Індекси: одна ідея, різні підводні камені

B‑tree — це не обіцянка, це контракт з дрібним шрифтом

Обидва рушії використовують B‑tree індекси для більшості випадків. Але як вони їх використовують — різниться: index‑only scans, покриття індексом, visibility map, кластерна поведінка та чи може рушій застосувати умови на рівні індексу.

PostgreSQL: index‑only scans залежать від видимості

Postgres може виконати index‑only scan, якщо може довести видимість кортежів без звернення в heap. Це залежить від visibility map, яка залежить від vacuum. Якщо таблиця інтенсивно змінюється і autovacuum не встигає, ваш «покриваючий індекс» насправді не покриває під час виконання. План може й досі казати index‑only, але heap‑fetchи покажуть правду.

MySQL/InnoDB: первинний ключ — це таблиця

InnoDB зберігає рядки, кластеризовані за первинним ключем. Вторинні індекси вказують на первинний ключ, а не напряму на місце розташування рядка. Це означає додаткові переходи при зчитуванні по вторинному індексу. Також вибір первинного ключа впливає на локалізацію, розподіл сторінок і поведінку кеша. UUID як первинний ключ може бути прийнятним за правильної форми і стратегії вставок, але це не безкоштовно.

Багатоколонні індекси: порядок важить більше, ніж визнають

MySQL і Postgres обидва звертають увагу на провідні стовпці індексу. Але їх оптимізатори по‑різному комбінують індекси (bitmap scans у Postgres можуть бути дуже ефективними; MySQL має index merge, але це не універсальна заміна). Якщо запит швидкий у Postgres завдяки bitmap‑AND двох одноядерних індексів, та сама схема в MySQL може вимагати композитного індексу, щоб уникнути сканування.

Функціональні індекси та підтримка виразів

Postgres зручно працює з індексами виразів (наприклад, lower(email)) і частковими індексами (наприклад, WHERE deleted_at IS NULL). MySQL має функціональні індекси (через згенеровані стовпці) і може зробити подібне, але ергономіка відрізняється. Якщо ваш запит покладається на «натуральний» підхід однієї СУБД, перенесення може повільно працювати, поки ви не переосмислите індексну стратегію.

MVCC, vacuum та прихований податок «мертвих» рядків

І MySQL (InnoDB), і PostgreSQL використовують MVCC. Вони платять цей податок по‑різному.

Блоат у PostgreSQL: коли «видалене» не зникло

У Postgres оновлення створюють нові версії рядків. Видалення позначає рядки як мертві. Простір повертається пізніше. Якщо autovacuum не встигає — через довгі транзакції, неправильні налаштування або просто великий обсяг змін — таблиця та індекси роздуваються. Плани, які раніше були дружніми до індексів, стають I/O‑зв’язкими. Заборгованість vacuum — як кредитна картка: мінімальні платежі здаються нормальними, поки не перестануть працювати.

Пурдж і довжина history list в MySQL

InnoDB також зберігає старі версії в undo‑логах і очищає їх, коли це безпечно. Довгі транзакції можуть збільшити history list length, створюючи додаткову роботу для purge і підвищуючи накладні витрати для деяких робочих навантажень. Це інша форма болю, але все ще біль.

Видимість і «чому читає так багато?»

Якщо запит у Postgres починає робити багато heap‑fetchів, часто це проблема vacuum/видимості. Якщо навантаження InnoDB починає застопорюватись на purge або показує зростаючий undo‑тиск, проблема у життєвому циклі транзакцій. Жодне з цього не виправить додавання ще одного індексу «на всяк випадок».

Блокування та ізоляція: коли коректність коштує затримок

Різні значення за замовчуванням, різні сюрпризи

За замовчуванням рівень ізоляції PostgreSQL — READ COMMITTED. У MySQL зазвичай дефолт — REPEATABLE READ для InnoDB. Це може змінювати те, що бачать читання, і як довго тримаються певні метадані й gap‑блоки. Результат: той самий «read‑запит» може блокуватись по‑різному залежно від одночасних записів і меж транзакцій.

Метадані блокування та DDL

Метадані блокування MySQL можуть спричиняти несподівані затори, коли виконується DDL. PostgreSQL має власні поведінки блокувань навколо змін схеми. Операційне правило однакове: ставтесь до DDL як до деплойменту — плануйте, тестуйте й тайміть. Якщо ви запускаєте «швидке ALTER» опівдні, ви записались на ротацію on‑call.

Жарт #2: Нічого так не старить, як «онлайн‑зміна схеми», яка виявляється «онлайн» у сенсі: усі онлайн дивляться, як вона блокує.

Кешування та I/O: ваше сховище — частина бази даних

На великих навантаженнях час запиту часто — це час сховища з іншим брендингом.

Buffer pool проти shared_buffers: різна математика, одна фізика

MySQL/InnoDB має buffer pool, зазвичай великий (60–80% RAM на виділених серверах, з урахуванням робочого навантаження й іншої пам’яті). PostgreSQL має shared_buffers плюс OS page cache, що виконує основну роботу. Це впливає на поведінку при «теплому» і «холодному» кеші. Запит, який «швидкий» на прогрітому buffer pool MySQL, може бути «катастрофічним» на Postgres, який більше покладається на OS‑кеш і конкурує з іншими процесами або має інші налаштування пам’яті.

Чекпоінти, fsync та ампліфікація записів

Обидва рушії за замовчуванням надійні, але їхні патерни записів різняться. Чекпоінти PostgreSQL можуть створювати сплески I/O, якщо їх не налаштувати; redo‑log і doublewrite buffer MySQL мають власний I/O‑сліп. Якщо ваше сховище має обмежені IOPS або високу варіативність латентності, база чесно перетворить це на користувацьке нещастя.

Граблі сховища, на які SRE насправді наступають

• Блокове сховище в хмарі з кредитами burst: «швидко, поки не перестало бути швидким».
• Мережеві томи з шумними сусідами: чудово для бекапів, сумнівно для «гарячих» даних.
• Неправильні налаштування RAID / файлової системи: можна натренуватися в бенчмарках і себе обдурити.
• Стиснення та шифрування: відмінні фічі, але не безкоштовні.

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

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

Завдання 1: PostgreSQL — отримати реальний план із таймінгами й буферами

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT o.id FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.segment='enterprise' AND o.created_at > now()-interval '7 days';"
Hash Join  (cost=1250.11..9821.44 rows=42000 width=8) (actual time=38.221..412.903 rows=39812 loops=1)
  Output: o.id
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=10231 read=821
  ->  Seq Scan on public.orders o  (cost=0.00..7260.00 rows=500000 width=16) (actual time=0.020..220.111 rows=480321 loops=1)
        Output: o.id, o.customer_id
        Filter: (o.created_at > (now() - '7 days'::interval))
        Rows Removed by Filter: 120034
        Buffers: shared hit=8121 read=701
  ->  Hash  (cost=1025.00..1025.00 rows=18000 width=8) (actual time=37.902..37.904 rows=18210 loops=1)
        Buffers: shared hit=2110 read=120
Planning Time: 1.102 ms
Execution Time: 418.771 ms

Що це означає: послідовне сканування по orders домінує і читає 701 буфер з диска. Ваш фільтр по created_at не використовує індекс (або планувальник вирішив не використовувати).

Рішення: Перевірте, чи існує індекс на (created_at) або (created_at, customer_id), і чи селективний він. Також перевірте статистику та те, чи останні 7 днів — велика частка таблиці.

Завдання 2: MySQL — отримати план та фактичні таймінги

cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT o.id FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.segment='enterprise' AND o.created_at > NOW() - INTERVAL 7 DAY\G"
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=12453.20 rows=40000) (actual time=0.112..52.883 rows=39812 loops=1)
    -> Index lookup on c using idx_segment (segment='enterprise')  (cost=210.12 rows=18000) (actual time=0.041..6.233 rows=18210 loops=1)
    -> Index lookup on o using idx_customer_created (customer_id=c.id, created_at>(now() - interval 7 day))  (cost=0.62 rows=3) (actual time=0.002..0.002 rows=2 loops=18210)

Що це означає: MySQL керує від customers використовуючи idx_segment і потім пробує orders через композитний індекс. Ось чому це швидко.

Рішення: Якщо Postgres сканує orders, ймовірно потрібен композитний індекс (або кращі статистики), щоб заохотити план на основі індексу.

Завдання 3: PostgreSQL — перевірити наявні індекси на таблиці

cr0x@server:~$ psql -d appdb -c "\d+ orders"
Table "public.orders"
Column       | Type                     | Collation | Nullable | Default | Storage | Stats target | Description
-------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
id           | bigint                   |           | not null |         | plain   |              |
customer_id  | bigint                   |           | not null |         | plain   |              |
created_at   | timestamp with time zone |           | not null | now()   | plain   |              |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "idx_orders_customer_id" btree (customer_id)

Що це означає: немає індексу на created_at і немає композитного індексу (customer_id, created_at).

Рішення: Додайте композитний індекс, що співпадає з формою join + filter, або частковий індекс, якщо ви запитуєте лише свіжі дані.

Завдання 4: PostgreSQL — створити композитний індекс без зупинки всього

cr0x@server:~$ psql -d appdb -c "CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at);"
CREATE INDEX

Що це означає: Побудова індексу завершилась без блокування читань/записів (вона все одно займає час і I/O).

Рішення: Повторно запустіть EXPLAIN (ANALYZE, BUFFERS). Якщо план змінився і латентність зменшилась — впроваджуйте. Якщо ні — ймовірно проблема в статистиці/селективності або в іншому вузькому місці.

Завдання 5: PostgreSQL — освіжити статистику зараз (якщо безпечно)

cr0x@server:~$ psql -d appdb -c "ANALYZE orders; ANALYZE customers;"
ANALYZE
ANALYZE

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

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

Завдання 6: PostgreSQL — швидко знайти невідповідність estimate vs actual

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE) SELECT * FROM orders WHERE created_at > now()-interval '7 days';"
Seq Scan on orders  (cost=0.00..7260.00 rows=5000 width=64) (actual time=0.012..189.442 rows=480321 loops=1)
Planning Time: 0.211 ms
Execution Time: 205.118 ms

Що це означає: Оцінка (5,000 рядків) відрізняється від фактичної (480,321 рядків) приблизно в 96×. Це виразна ознака проблеми.

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

Завдання 7: PostgreSQL — підняти stats target для скійованого стовпця

cr0x@server:~$ psql -d appdb -c "ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 1000; ANALYZE orders;"
ALTER TABLE
ANALYZE

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

Рішення: Якщо запит часто виконується й чутливий — це прийнятний компроміс. Якщо це ad‑hoc аналітика — можливо, не варто.

Завдання 8: MySQL — підтвердити використання індексу та тип доступу

cr0x@server:~$ mysql -e "EXPLAIN SELECT o.id FROM orders o JOIN customers c ON c.id=o.customer_id WHERE c.segment='enterprise' AND o.created_at > NOW() - INTERVAL 7 DAY;"
id	select_type	table	type	possible_keys	key	key_len	rows	Extra
1	SIMPLE	c	ref	idx_segment	idx_segment	1022	18210	Using where
1	SIMPLE	o	range	idx_customer_created	idx_customer_created	16	39812	Using where; Using index

Що це означає: type=ref/range і вказаний ключ означають доступ по індексу. «Using index» свідчить про покриття індексом для обраних стовпців.

Рішення: Якщо бачите type=ALL несподівано — це означає сканування. Тоді перевіряйте визначення індексів і сарджабельність предикатів.

Завдання 9: MySQL — перевірити поведінку hit rate InnoDB buffer pool

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';"
Variable_name	Value
Innodb_buffer_pool_reads	1842231
Variable_name	Value
Innodb_buffer_pool_read_requests	987654321

Що це означає: reads — фізичні зчитування; read_requests — логічні. Якщо фізичні зчитування різко ростуть під час періоду повільності, ви йдете на диск.

Рішення: Якщо hit rate низький — збільшіть buffer pool (якщо RAM дозволяє), звузьте робочий набір (індекси, запит) або усуньте латентність I/O.

Завдання 10: PostgreSQL — перевірити блокування і довгі транзакції

cr0x@server:~$ psql -d appdb -c "SELECT pid, usename, state, wait_event_type, wait_event, now()-xact_start AS xact_age, query FROM pg_stat_activity WHERE datname='appdb' ORDER BY xact_age DESC LIMIT 5;"
 pid  | usename | state  | wait_event_type |  wait_event   | xact_age |                   query
------+--------+--------+-----------------+---------------+----------+--------------------------------------------
 9123 | app    | active | Lock            | transactionid | 00:41:22 | SELECT ... FROM orders ...
 8877 | app    | idle in transaction | Client | ClientRead | 02:13:10 | UPDATE customers SET ...

Що це означає: Сесія «idle in transaction» відкрита годинами. Це може блокувати vacuum і спричиняти блоат, і побічно псувати продуктивність читань.

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

Завдання 11: MySQL — знайти очікування блокувань і хто блокує

cr0x@server:~$ mysql -e "SHOW PROCESSLIST;"
Id	User	Host	db	Command	Time	State	Info
221	app	10.0.1.10:42218	appdb	Query	28	Waiting for table metadata lock	ALTER TABLE orders ADD COLUMN foo INT
305	app	10.0.1.11:53122	appdb	Query	3	Sending data	SELECT ... FROM orders ...

Що це означає: DDL чекає на metadata lock; це також може викликати накопичення інших сесій залежно від таймінгу і порядку блокувань.

Рішення: Призупиніть/завершіть DDL, якщо він впливає на продакшен, і переплануйте його. На майбутнє використовуйте онлайн‑методи зміни схеми відповідно до версії.

Завдання 12: PostgreSQL — перевірити індикатори блоату таблиці та індексів

cr0x@server:~$ psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
   relname   | n_live_tup | n_dead_tup |     last_autovacuum
-------------+------------+------------+----------------------------
 orders      |    5200000 |    2100000 | 2025-12-29 01:12:03+00
 events      |   22000000 |    9800000 | 

Що це означає: Багато мертвих кортежів, і одна таблиця давно не проходила autovacuum (або ніколи). Це може збільшувати сканування й погіршувати index‑only scans.

Рішення: Перевірте налаштування autovacuum і довгі транзакції; розгляньте ручний VACUUM (ANALYZE) у вікні техобслуговування та виправте корінь проблеми.

Завдання 13: Linux — підтвердити, чи ви зараз обмежені латентністю I/O

cr0x@server:~$ iostat -xz 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    5.11   24.33    0.00   52.34

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   w_await  aqu-sz  %util
nvme0n1         820.0  65536.0     0.0   0.00   18.40    79.90   210.0  18432.0   25.12   12.33   98.70

Що це означає: Високе %util і високі r_await/w_await з помітним %iowait. Сховище насичене або повільне.

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

Завдання 14: PostgreSQL — подивитись, чи сортування/хеші виливаються на диск

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000;"
Sort  (cost=980000.00..1030000.00 rows=5200000 width=64) (actual time=2100.551..2320.112 rows=100000 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 512000kB
  Buffers: shared hit=12000 read=88000
Execution Time: 2355.991 ms

Що це означає: «external merge Disk» означає, що відбулося виливання на диск. Часто саме тому запит у однієї СУБД катастрофічний: налаштування пам’яті та робочий набір не відповідають.

Рішення: Виправте, додавши індекс, що відповідає сортуванню ((created_at DESC)), зменшивши набір результатів або обережно збільшуючи work_mem для цього навантаження.

Три корпоративні міні‑історії з практики

1) Інцидент через хибне припущення: «індекс — це індекс»

Компанія хотіла перевести read‑heavy сервіс з MySQL у PostgreSQL, щоб спростити операції. SQL‑шар був «портативним». Схема була «та сама». План rollout — dual‑write і переключення читань.

У staging усе було добре. У продакшені один ендпоінт почав таймаутити. Той самий запит. Та сама WHERE‑умова. Інша реальність. PostgreSQL обрав послідовне сканування і hash join, що виглядало розумно на папері. Фактичні рядки далеко не відповідали оцінкам.

Корінь був буденний: у MySQL команда мала композитний індекс, що відповідав формі запиту. Під час міграції в PostgreSQL створили одно‑стовпчикові індекси, але пропустили композитний. Index merge і nested‑loop у MySQL маскували помилку; Postgres хотів індекс, що підтримував і ключ з’єднання, і часовий фільтр. Планувальник Postgres також недооцінив селективність часового предиката, бо розподіл мав «обрив» (робота з retention нещодавно заповнила тиждень даних).

Виправлення не було героїчним: побудували потрібний композитний індекс concurrently, запустили ANALYZE і додали запобіжник: будь‑який запит, що JOIN‑ить і фільтрує, має пройти рев’ю індексів. Також додали розширені статистики для корельованих стовпців. Плани стабілізувались, і ендпоінт перестав видавати симптоми DoS.

2) Оптимізація, що відкотилась: «Давайте примусимо планувальник»

Інша команда мала повільний аналітичний запит у PostgreSQL. Хтось виявив, що вимкнення nested loops пришвидшило запит на їхньому ноутбуці. Вони додали SET enable_nestloop=off у налаштування сесії застосунку. Здавалося кмітливо. Але це пастка.

Тиждень виглядав як виграш. Потім трафік змінився: у одного клієнта набір даних був значно менший за середній, і nested loops були правильним вибором. З вимкненими nested loops Postgres обрав hash join і великі сорти, пам’ять сплинула, система стала виливатись на диск, латентність I/O підскочила, і незв’язані запити сповільнились. «Фікс» перетворив уподобання одного запиту на глобальну політику для пулу з’єднань.

Її відкотили і зробили нудну роботу: покращили статистику, додали індекс і переписали запит, щоб бути вибірковішим раніше. Урок: підказки плану (або псевдо‑підказки через session settings) — останній засіб. Вони швидко старіють. Дані змінюються. Навантаження змінюється. Ваша кмітливість не переживе наступний квартал.

3) Нудна, але правильна практика, що врятувала день: «Виміряти, потім змінити одне»

Платіжна система запускала і MySQL, і PostgreSQL для різних сервісів. Після деплойменту латентність MySQL підскочила для набору запитів, що зазвичай виконувались за <50ms. Миттєва реакція — настроїти buffer pool і звинуватити мережу.

On‑call дотримався ранкбуку: перевірити блокування, підтвердити насичення I/O, захопити плани, порівняти до/після. Дістали slow query log і побачили той самий запит тепер робить «Using temporary; Using filesort». Потім перевірили diff схем: міграція видалила композитний індекс і замінила його двома одно‑стовпчиковими, вважаючи, що «оптимізатор зможе їх об’єднати». Іноді може. Цього разу — ні.

Відновили композитний індекс, підтвердили через EXPLAIN, що план повернувся, і спостерігали відновлення латентності. Жодних драматичних трюків. Ніякої рулетки параметрів. Просто докази, одна зміна і план відкату. Це не гламурно, але і виплата зарплат теж не гламурна.

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

• Симптом: Postgres використовує Seq Scan, MySQL використовує індекс і швидкий.
  Корінь: Відсутній композитний індекс у Postgres, або предикат не sargable через приведення/функції.
  Виправлення: Створіть композитний індекс, що відповідає join + filter; забезпечте порівняння однакових типів; розгляньте індекс виразу.
• Симптом: Postgres оцінює 5k рядків, фактично читає 500k; план жахливий.
  Корінь: Стара/недостатня статистика; корельовані стовпці; скійований розподіл.
  Виправлення: ANALYZE; підвищити stats target; додати розширені статистики; розглянути партиціонування по часу або по tenant.
• Симптом: «Index‑only scan», але все одно повільно; багато heap‑fetchів.
  Корінь: Visibility map не встановлено через відставання vacuum; часті оновлення/видалення.
  Виправлення: Налагодити autovacuum і терміни транзакцій; виконати технічний vacuum; зменшити churn або змінити дизайн оновлень.
• Симптом: План MySQL раптово змінюється між хорошим і поганим.
  Корінь: Різниця в перерахунку/персистентності InnoDB статистик; чутливість до параметрів; відсутність гістограми.
  Виправлення: Увімкнути persistent stats; додати гістограми для скійованих стовпців; стабілізувати з допомогою правильних композитних індексів.
• Симптом: Запит повільний тільки інколи; інакше добре.
  Корінь: I/O‑сплески під час чекпоінтів/флашів; евікція кешу; латентність сховища від шумних сусідів.
  Виправлення: Згладжування записного I/O (налаштування чекпоінтів, розміри redo), поліпшити клас сховища, зменшити робочий набір, додати кеш.
• Симптом: Читання блокуються або таймаутять під час деплойменту.
  Корінь: Блокування DDL / metadata locks; довгі транзакції тримають блоки.
  Виправлення: Планувати вікна для DDL; використовувати онлайн‑техніки зміни схеми; встановити таймаути; виправити транзакції застосунку.
• Симптом: Postgres гіршає протягом тижнів без змін коду.
  Корінь: Блоат таблиць/індексів і борг vacuum; довгоживучі транзакції запобігають прибиранню.
  Виправлення: Аудит autovacuum, управління транзакціями; періодичний reindex де потрібно; прибрати «idle in transaction».
• Симптом: MySQL CPU підскакує з «Sending data», низька активність диска.
  Корінь: Велике використання join buffer, неефективний порядок з’єднань, відсутній covering index або покрокові зчитування рядків.
  Виправлення: Додати covering/композитні індекси; переписати запит; перевірити порядок з’єднань через EXPLAIN ANALYZE; уникати функцій на індексованих стовпцях.

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

Коли запит швидкий в MySQL і повільний в PostgreSQL

1. Отримайте план з реальним виконанням: Postgres EXPLAIN (ANALYZE, BUFFERS). Визначте домінуючий вузол.
2. Порівняйте оцінки і фактичні значення: якщо відхилення >10× — виправляйте статистику перед іншим.
3. Перевірте індекси та їх форму: впевніться, що існують композитні індекси для join + filter. Postgres часто хоче композит там, де MySQL «відмивається» через index merge.
4. Підтвердіть sargability предикатів: немає приведень на індексованих стовпцях, немає WHERE date(created_at)=... без індексу виразу.
5. Перевірте vacuum/видимість: мертві кортежі, heap‑fetchі, довгі транзакції.
6. Перевірте виливання в пам’яті: сортування/хеші, що виливаються на диск; виправляйте індексацією або дизайном запиту перед підняттям пам’яті.
7. Тільки потім налаштовуйте ручки: work_mem, паралелізм, пороги autovacuum — на основі виміряних вузьких місць.

Коли запит швидкий в PostgreSQL і повільний в MySQL

1. EXPLAIN у MySQL: шукайте type=ALL, «Using temporary», «Using filesort».
2. Підтвердіть композитні індекси: bitmap‑скани Postgres можуть поєднувати індекси добре; MySQL часто потребує одного композитного індексу, щоб уникнути тимчасових таблиць.
3. Перевірте колації та типи даних: порівняння рядків і неявні конверсії також можуть відключати індекси у MySQL.
4. Перевірте InnoDB статистику і гістограми: скійовані стовпці без гістограм можуть вводити оптимізатор в оману.
5. Перевірте стан buffer pool: якщо читання зростають під час повільності, це I/O‑проблема, а не «поганий SQL».
6. Перевірте metadata locks: DDL чи довгі транзакції можуть призупиняти запити.

Зміни планувати безпечно

1. Змінюйте одну річ за раз (індекс, статистику, перепис запиту або конфіг).
2. Вимірюйте до/після з тими ж параметрами і репрезентативними даними.
3. Майте план відкату: видалити індекс concurrently (Postgres) або drop index (MySQL) після перевірки альтернатив.
4. Документуйте причину у міграції. Майбутнє «ви» заслуговує доказів, а не відчуттів.

FAQ

1) Чи PostgreSQL повільніший за MySQL?

Ні. Обидва можуть бути надзвичайно швидкими. PostgreSQL часто перемагає на складних запитах і багатій індексації; MySQL часто блискуче працює в простих OLTP‑патернах. Визначальний фактор — чи має планувальник правильні індекси і статистику для вашого розподілу даних.

2) Чому PostgreSQL робить послідовне сканування, коли індекс існує?

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

3) Чому MySQL обирає жахливий план, навіть якщо індекси є?

Статистика InnoDB може вводити в оману при скійованих даних без гістограм; деякі форми запитів штовхають MySQL у тимчасові таблиці і filesort; іноді правильний індекс — це композит, що відповідає WHERE + JOIN, а не два одно‑стовпчикові індекси.

4) Чи варто використовувати підказки, щоб змусити план?

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

5) Чи потребує PostgreSQL більше обслуговування, ніж MySQL?

Різний обсяг робіт. PostgreSQL потребує дисципліни щодо vacuum і уваги до довгих транзакцій. MySQL потребує уваги до поведінки purge/undo InnoDB, розміру buffer pool і операційних практик щодо DDL і metadata locks. Жодна з систем не є «налаштуй і забуть» у продакшені.

6) Чому підготовлені запити змінюють продуктивність у PostgreSQL?

Postgres може використовувати generic‑план для підготовлених запитів, який не адаптується під значення параметрів. Якщо одне значення параметра поширене й дуже селективне (або ні) — generic‑план може систематично помилятись. Іноді варто використовувати custom‑плани або уникати передчасних prepare.

7) Чому той самий запит поводиться по‑різному на репліці?

Задержка реплікації, різна «теплота» кешу, інше навантаження I/O і накладні витрати на відтворення/аплай можуть змінювати латентність. Також репліки часто мають інші налаштування (умисно чи випадково), особливо щодо надійності і пам’яті.

8) Чи варто партиціонувати таблиці, щоб виправити повільні запити?

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

9) Яка СУБД прогнозованіша для продуктивності запитів?

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

Висновок: що зробити наступного тижня

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

1. Зробіть плани видимими: захопіть EXPLAIN ANALYZE (і буфери в Postgres) для повільних випадків, а не для щасливого шляху.
2. Виправляйте форму, а не забобони: додайте композитний індекс, що відповідає join + filter; приберіть неявні приведення; освіжіть статистику; вирішіть борг vacuum/транзакцій.
3. Експлуатуйте базу як систему: перевіряйте латентність I/O, стан кешу, блокування і техобслуговування. Оптимізатор не ворог — він ваш працівник. Дайте йому точні дані і потрібні інструменти.

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