Індексація в MariaDB та PostgreSQL: чому «кращі практики» шкодять реальним навантаженням

Чому «кращі практики» в провадженні не працюють

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

«Додайте індекс для кожного WHERE» — класика. Це також спосіб перетворити робочу OLTP-систему на купу з посиленим записом. Кожен додатковий індекс — це ще одна структура для оновлення, ще набір сторінок, які треба зробити «брудними», ще одна причина реплікаційної затримки і ще один шанс для оптимізатора вибрати неправильний шлях.

Індексація в продакшені — це про обмеження. Скільки записів ви можете собі дозволити? Скільки пам’яті зарезервовано для кешування сторінок індексів? Як часто ваші дані змінюють форму (гарячі ключі, перекіс, спалахи)? Який ваш допуск щодо вікон обслуговування? PostgreSQL і MariaDB відповідають на ці питання по-різному, бо їхні внутрішні механізми та операційні інструменти різняться.

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

Цитата, яку варто прикріпити на монітор: Надія — не стратегія. —Gene Kranz. Індексація без вимірювань — це надія з додатковими кроками.

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

Факти та історія, що досі мають значення

Кілька контекстних пунктів, які здаються академічними, поки не пояснять ваш інцидент:

1. PostgreSQL рано успадкував MVCC (multi-version concurrency control), отже мертві кортежі і vacuum — це частина історії індексації, а не примітка.
2. InnoDB давно став дефолтним рушієм MySQL, і MariaDB здебільшого слідує тій самій InnoDB-лінії — тож дизайн вторинних індексів успадковує реальність «кластеризованого первинного ключа».
3. GIN та GiST в PostgreSQL були створені для складних типів даних і патернів пошуку; вони потужні, але вводять динаміку обслуговування та «блоату», яку звичайний прихильник B-tree забуває.
4. MariaDB/MySQL історично покладалися на «index condition pushdown» і евристики оптимізатора; вони хороші, поки не стають такими, і тоді єдина істина — фактичний план.
5. PostgreSQL додав INCLUDE-колонки для B-tree індексів (поведінка, схожа на covering) відносно пізно у порівнянні з деякими рушіями; це змінило підхід до побудови «покриваючих» індексів.
6. Листові записи вторинних індексів InnoDB зберігають первинний ключ у своїх листових сторінках. Ця одна деталь пояснює шокуючу частину росту простору й дрейф кешу, коли ваш первинний ключ широкий.
7. PostgreSQL використовує visibility maps, щоб зробити можливими index-only scans; якщо vacuum не встигає, ваш «index-only» скан стає «індекс плюс таблиця».
8. MySQL/MariaDB давно підтримували «невидимі індекси» (in MySQL), а MariaDB має подібні можливості через перемикачі оптимізатора та підказки індексів; можливість безпечно тестувати видалення індексу — це операційне золото.

Скільки насправді коштують індекси (і хто платить)

1) Посилення записів: кожен INSERT/UPDATE платить оренду

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

MariaDB (InnoDB) оновлює структури B-tree і пише redo/undo логи. PostgreSQL записує нові версії кортежів (MVCC) і оновлює індекси для нового кортежу; старі версії лишаються, поки vacuum їх не прибере. Різні механізми, та сама мораль: більше індексів — більше хаосу.

2) Тиск на кеш: індекси конкурують з тим, що вам дійсно потрібно

Індекси не «безкоштовні при читанні». Це структури даних у пам’яті й на диску. Якщо ваш buffer pool (MariaDB) або shared_buffers + OS cache (PostgreSQL) не вміщують робочий набір, ви отримуєте IO-хвилювання. IO-хвилювання перетворює «кращий індекс» на «чому ми витрачаємо 4000 IOPS у просте?»

3) Ризик планування: оптимізатор може обрати неправильний «хороший» план

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

4) Обслуговування: блоат, фрагментація і міф «налаштував і забув»

У PostgreSQL блоат часто виникає через мертві кортежі й записи індексів, що не відразу звільняються. Vacuum пом’якшує це, але vacuum потребує ресурсів і налаштувань. MariaDB/InnoDB може страждати від фрагментації й проблем зі сплітами сторінок, а «OPTIMIZE TABLE» не треба запускати бездумно у великому масштабі.

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

Де MariaDB і PostgreSQL різняться в поведінці індексів

Кластеризований первинний ключ проти реальності heap-таблиці

InnoDB (MariaDB) зберігає дані таблиці, кластеризовані за первинним ключем. Листові записи вторинного індексу включають первинний ключ, який використовується як «вказівник рядка». Це робить ширину та випадковість PK дуже важливими. Широкий PK роздуває кожен вторинний індекс. Випадковий PK підсилює спліти сторінок і знижує локальність.

PostgreSQL використовує heap-таблиці; індекси вказують на місця в кортежах (TID). Ваш первинний ключ фізично не кластеризує таблицю, якщо ви явно не виконаєте CLUSTER (і не приймаєте його наслідки з обслуговуванням). Це робить деякі аргументи про «локальність PK» слабшими в Postgres, але вводить інші питання, як-от HOT-оновлення й фрагментацію heap.

Покриваючі індекси: «INCLUDE» проти «просто додати колонки»

У MariaDB «покриваючий індекс» зазвичай досягається шляхом розміщення потрібних колонок у ключі індексу. Це збільшує розмір індексу і може погіршити вартість записів. У PostgreSQL ви можете використовувати INCLUDE, щоб додати неклітинні колонки, дозволяючи index-only scans, зберігаючи порядок індексу визначеним меншим числом ключів. Це не магія; включені колонки все ще займають місце і потребують обслуговування, але у вас більше точного контролю.

Index-only scans — умовні, а не гарантовані

Index-only scans у PostgreSQL вимагають, щоб біти visibility map були встановлені, що залежить від vacuum. Якщо таблиця часто оновлюється, visibility map може відстати, і Postgres все одно звертатиметься до heap. У MariaDB «покриття» означає, що рушій може уникнути звернень до таблиці, якщо всі потрібні колонки в індексі, але ви все одно платите більшими індексами й тиском на кеш.

Статистики та різні режими помилок оцінки

Обидва рушії можуть помилятися з оцінками. PostgreSQL дає більше налаштувань (default_statistics_target, цільові статистики по колонках, розширені статистики). MariaDB покладається на персистентні статистики і гістограми у новіших версіях, але шлях до «виправлення оцінок» часто виглядає як «ANALYZE і моліться», плюс обережний дизайн індексів і підлаштування запитів.

Конкуренція та ритми обслуговування різняться

У Postgres vacuum — частина стійкої операції. В InnoDB більше фокусу на розмірі buffer pool, redo логах і уникненні патологічного обертання сторінок. Обидва вимагають рутинного обслуговування; вони просто по-різному відмовляють, якщо ви його ігноруєте.

Шаблони навантаження, які роблять поради шкідливими

Шаблон A: «Системи з великою часткою читань», що таємно мають багато записів

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

Шаблон B: Колонки з високою кардинальністю проти низької кардинальності

Індексування булевого чи дрібного enum може бути корисним у Postgres з partial indexes, але в MariaDB індекс з низькою кардинальністю можуть ігнорувати або він може спричинити марну роботу. Порада «індексувати все в WHERE» ігнорує селективність.

Шаблон C: Міф про порядок колон у складному індексі

Так, порядок у composite index має значення. Ні, немає універсального порядку. «Поставте найселективнішу колонку першою» часто неправильно, коли запит використовує range-умови, ORDER BY або потребує index-only покриття. У MariaDB правила leftmost prefix важливі. У Postgres планувальник гнучкіший, але все одно обмежений тим, як індекс може бути використаний.

Шаблон D: Надіндексація через ORM

ORM генерують запити з непослідовними предикатами і багатьма опціональними фільтрами. Команди реагують, додаючи «підтримуючі» індекси для кожної перестановки. Так ви отримуєте 25 індексів на таблицю з 10 колонками.

Шаблон E: Сортування й пагінація, що карають вас

OFFSET/LIMIT пагінація разом з ORDER BY по неіндексованих колонках — повільне лихо. «Додати індекс на колонку сортування» допомагає, поки ви не додасте WHERE, що змінило найкращий індекс. Тоді потрібен складаний індекс, що відповідає фільтру + сорту, або ключова пагінація. Тут «краща практика» — не «додати індекс», а «змінити запит».

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

Коли затримка зросла і люди оновлюють Slack, ніби це сторінка статусу, цей порядок зазвичай найшвидше знаходить винуватця.

Перший крок: підтвердьте, чи ви завантажені по CPU, IO чи блокуванням

• CPU-bound: високий CPU, низький IO wait, запити споживають цикли; плани часто показують дорогі з’єднання, сортування або функції.
• IO-bound: високі read/write IOPS, високий await; промахи кеша буфера; робочий набір індексів/таблиці не вміщується.
• Lock-bound: потоки чекають; графи блокувань; довгі транзакції; autovacuum блокується (Postgres) або metadata locks (MariaDB).

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

«Найповільніший запит» за часом на виклик часто є хибним слідом. Топ‑запит за загальним часом — той, що платить вашу іпотеку.

Третій крок: звірте плани з реальністю

В Postgres порівнюйте EXPLAIN (ANALYZE, BUFFERS) з оцінками. В MariaDB інспектуйте EXPLAIN, handler read counts і використання індексів. Шукайте скани, яких ви не очікували, filesorts, тимчасові таблиці та вкладені цикли, які мали бути hash join (Postgres) або мали бути обмежені раніше.

Четвертий крок: перевірте стан індексів і обслуговування

У Postgres: активність vacuum, мертві кортежі, індикатори блоату, visibility map. В MariaDB: hit rate buffer pool, поведінка change buffer, ріст розміру індексу та чи не роздуває ваш вибір PK кожен вторинний індекс.

П’ятий крок: тільки потім створюйте або видаляйте індекси

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

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

Це реальні завдання для продакшну: виконайте команду, інтерпретуйте вивід і прийміть конкретне рішення. Команди показані так, ніби ви на сервері з інструментами клієнта.

Завдання 1 (PostgreSQL): Знайдіть найбільших споживачів часу за загальним часом

cr0x@server:~$ psql -d appdb -c "SELECT query, calls, total_exec_time::numeric(12,1) AS total_ms, mean_exec_time::numeric(10,2) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
                               query                                | calls |  total_ms  | mean_ms
--------------------------------------------------------------------+-------+------------+---------
 SELECT ... FROM orders WHERE account_id=$1 AND status=$2 ORDER BY... | 98234 |  8543210.5 |   86.97
 UPDATE inventory SET qty=qty-$1 WHERE sku=$2                         | 45012 |  3011220.2 |   66.90
 SELECT ... FROM events WHERE created_at >= $1 AND tenant_id=$2        | 12033 |  2210098.7 |  183.67

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

Рішення: Оптимізуйте цей топовий запит першочергово. Не ганяйтеся за рідкісним звітом на 30 секунд, якщо він не впливає на SLA.

Завдання 2 (PostgreSQL): Порівняйте оцінки з реальністю за допомогою buffers

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE account_id=42 AND status='open' ORDER BY created_at DESC LIMIT 50;"
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..102.55 rows=50 width=128) (actual time=1.212..12.844 rows=50 loops=1)
   Buffers: shared hit=120 read=480
   ->  Index Scan Backward using idx_orders_account_created on orders  (cost=0.42..10523.31 rows=5200 width=128) (actual time=1.210..12.832 rows=50 loops=1)
         Index Cond: (account_id = 42)
         Filter: (status = 'open'::text)
         Rows Removed by Filter: 940
         Buffers: shared hit=120 read=480
 Planning Time: 0.290 ms
 Execution Time: 12.930 ms

Що це означає: Індекс допомагає з ORDER BY/LIMIT, але фільтр по status відкидає багато рядків. Buffers показують реальні зчитування: ви звертаєтеся до великої кількості сторінок.

Рішення: Розгляньте composite index на (account_id, status, created_at DESC) або partial index на open-статус, якщо він стабільний. Partial індекси тут добре працюють.

Завдання 3 (PostgreSQL): Визначте невикористані індекси (обережно)

cr0x@server:~$ psql -d appdb -c "SELECT relname AS table, indexrelname AS index, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes JOIN pg_class ON pg_class.oid=relid WHERE idx_scan=0 ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;"
  table   |         index          | idx_scan |  size
----------+------------------------+----------+--------
 events   | idx_events_payload_gin |        0 | 2048 MB
 orders   | idx_orders_status      |        0 |  512 MB

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

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

Завдання 4 (PostgreSQL): Знайдіть сигнали блоату індексу через мертві кортежі

cr0x@server:~$ psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, (n_dead_tup::numeric/(n_live_tup+1))*100 AS dead_pct FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
  relname  | n_live_tup | n_dead_tup | dead_pct
-----------+------------+------------+----------
 events    |   80000000 |   22000000 |  27.50
 orders    |    1200000 |     210000 |  17.50

Що це означає: Багато мертвих кортежів означає тиск vacuum і потенційний блоат індексу. Це може руйнувати index-only scans і надувати IO.

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

Завдання 5 (PostgreSQL): Перевірте активність autovacuum і блокування

cr0x@server:~$ psql -d appdb -c "SELECT pid, now()-xact_start AS xact_age, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state<>'idle' ORDER BY xact_start NULLS LAST LIMIT 8;"
 pid  |  xact_age  | wait_event_type |     wait_event     |                       query
------+------------+-----------------+--------------------+---------------------------------------------------
 9123 | 02:41:10   | Lock            | relation           | VACUUM (ANALYZE) events
 7331 | 02:42:55   |                 |                    | BEGIN; SELECT ... FROM events FOR UPDATE;

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

Рішення: Виправте патерни транзакцій у застосунку. Додайте statement timeouts, скоротіть транзакції і уникайте idle-in-transaction сесій.

Завдання 6 (PostgreSQL): Підтвердіть, чи index-only scans справді відбуваються

cr0x@server:~$ psql -d appdb -c "SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes JOIN pg_class ON pg_class.oid=relid WHERE relname='orders' ORDER BY idx_scan DESC;"
 relname | idx_scan | idx_tup_read | idx_tup_fetch
---------+----------+--------------+--------------
 orders  |  1200000 |     98000000 |     97000000

Що це означає: idx_tup_fetch майже дорівнює idx_tup_read, що вказує на багато звернень до heap. Index-only scans мало вам допомагають.

Рішення: Покращіть ефективність vacuum і розгляньте, чи індекс можна зробити покриваючим за допомогою INCLUDE (Postgres), або змініть форму запиту.

Завдання 7 (MariaDB): Знайдіть топові запити за загальним часом (Performance Schema)

cr0x@server:~$ mariadb -e "SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1000000000000,2) AS total_s, ROUND(AVG_TIMER_WAIT/1000000000000,4) AS avg_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 3\G"
*************************** 1. row ***************************
DIGEST_TEXT: SELECT * FROM orders WHERE account_id = ? AND status = ? ORDER BY created_at DESC LIMIT ?
COUNT_STAR: 98234
total_s: 8543.21
avg_s: 0.0869

Що це означає: Той самий принцип: загальний час виявляє справжнього «хулігана» запитів.

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

Завдання 8 (MariaDB): Поясніть запит і дивіться на filesort/temp

cr0x@server:~$ mariadb -e "EXPLAIN SELECT * FROM orders WHERE account_id=42 AND status='open' ORDER BY created_at DESC LIMIT 50\G"
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: idx_orders_account_created,idx_orders_status
key: idx_orders_account_created
key_len: 8
ref: const
rows: 5200
Extra: Using where; Using filesort

Що це означає: «Using filesort» означає, що рушій очікує сортування замість читання рядків у потрібному порядку з індексу. Часто це означає, що ваш індекс не збігається з ORDER BY після фільтрації.

Рішення: Розгляньте індекс, що збігається і з фільтром, і з порядком сортування: (account_id, status, created_at). Перевірте кардинальність; не додавайте індекси бездумно.

Завдання 9 (MariaDB): Виміряйте використання індексів за лічильниками Handler (швидкий тест)

cr0x@server:~$ mariadb -e "SHOW GLOBAL STATUS LIKE 'Handler_read%';"
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Handler_read_first    | 182349    |
| Handler_read_key      | 982341234 |
| Handler_read_next     | 774123998 |
| Handler_read_rnd_next | 889120044 |
+-----------------------+-----------+

Що це означає: Високий Handler_read_rnd_next зазвичай вказує на багато сканувань таблиці (або індексних сканів, що поводяться як скани). Високий Handler_read_next вказує на range-скани. Це грубо, але швидко.

Рішення: Якщо скани стрімко зростають, ідентифікуйте запити та відсутні/невикористані індекси — або прийміть, що ви IO-bound і потрібні зміни запитів чи партиціювання.

Завдання 10 (MariaDB): Подивіться розміри індексів і виявляйте наслідки «PK занадто широкий»

cr0x@server:~$ mariadb -e "SELECT table_name, index_name, ROUND(stat_value*@@innodb_page_size/1024/1024,1) AS index_mb FROM mysql.innodb_index_stats WHERE database_name='appdb' AND stat_name='size' AND table_name='orders' ORDER BY index_mb DESC;"
+------------+---------------------------+----------+
| table_name | index_name                | index_mb |
+------------+---------------------------+----------+
| orders     | PRIMARY                   |  820.0   |
| orders     | idx_orders_account_status |  740.0   |
| orders     | idx_orders_created        |  610.0   |
+------------+---------------------------+----------+

Що це означає: Вторинні індекси можуть бути майже такими ж великими, як PRIMARY, бо вони несуть PK у листових записах. Якщо ваш PK — великий UUID-рядок, ви за все це платите всюди.

Рішення: Розгляньте компактні PK (цілочисельні сурогатні ключі) або бінарне зберігання UUID там, де це доречно. Перегляньте, чи всі вторинні індекси справді потрібні.

Завдання 11 (PostgreSQL): Протестуйте новий індекс, не блокуючи записи (concurrently)

cr0x@server:~$ psql -d appdb -c "CREATE INDEX CONCURRENTLY idx_orders_account_status_created ON orders (account_id, status, created_at DESC) INCLUDE (total_amount);"
CREATE INDEX

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

Рішення: Використовуйте concurrently у продакшні, якщо тільки ви не в контрольованому вікні технічного обслуговування і не погоджуєтеся на блокування. Потім знову виконайте EXPLAIN (ANALYZE), щоб підтвердити зміни плану.

Завдання 12 (MariaDB): Додайте індекс онлайн (InnoDB) і перевірте алгоритм

cr0x@server:~$ mariadb -e "ALTER TABLE orders ADD INDEX idx_orders_account_status_created (account_id, status, created_at), ALGORITHM=INPLACE, LOCK=NONE;"
Query OK, 0 rows affected (12 min 31.44 sec)

Що це означає: In-place, lock-none зазвичай означає менш руйнівний DDL, але операція все одно споживає IO і може погіршити затримку під час побудови.

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

Завдання 13 (PostgreSQL): Знайдіть дублікати або зайві індекси

cr0x@server:~$ psql -d appdb -c "SELECT i1.relname AS index1, i2.relname AS index2, pg_get_indexdef(i1.oid) AS def1, pg_get_indexdef(i2.oid) AS def2 FROM pg_class i1 JOIN pg_index ix1 ON ix1.indexrelid=i1.oid JOIN pg_class i2 ON i2.relkind='i' JOIN pg_index ix2 ON ix2.indexrelid=i2.oid WHERE ix1.indrelid=ix2.indrelid AND i1.oid<>i2.oid AND ix1.indkey=ix2.indkey LIMIT 3;"
  index1              | index2                 | def1                                   | def2
----------------------+------------------------+----------------------------------------+----------------------------------------
 idx_orders_account   | idx_orders_account_v2  | CREATE INDEX ... (account_id)          | CREATE INDEX ... (account_id)

Що це означає: Два індекси з ідентичними ключами зазвичай надлишкові, якщо один не є partial, з іншим opclass або з різним предикатом.

Рішення: Видаліть надлишковий після перевірки залежностей і використання. Менше індексів — менше вартості записів і роботи vacuum.

Завдання 14 (MariaDB): Переконайтеся, що оптимізатор обирає ваш індекс

cr0x@server:~$ mariadb -e "EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE account_id=42 AND status='open' ORDER BY created_at DESC LIMIT 50\G"
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "orders",
      "access_type": "range",
      "possible_keys": ["idx_orders_account_status_created"],
      "key": "idx_orders_account_status_created",
      "rows_examined_per_scan": 60,
      "filtered": 100,
      "using_filesort": false
    }
  }
}

Що це означає: Рушій обрав composite index, очікує невелику кількість рядків на скан і без filesort. Це те, що ви хотіли.

Рішення: Впроваджуйте, а потім слідкуйте за затримкою записів, реплікаційною затримкою й промахами buffer pool. «Перевага для читання», що спричиняє «втрату для запису», — все одно втрата.

Три короткі історії з полі бою індексації

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

Компанія працювала мульти‑тенант SaaS на MariaDB з InnoDB. Новий сервіс запустили з таблицею, де як первинний ключ використовували рядковий UUID. Логіка була стандартною: глобально унікальні ID спрощують злиття й уникають координації. Вони читали блог-пости. Вони мали наліпки.

За тиждень затримка записів почала зростати. Не стрибок, а тенденція. On-call побачив збільшення IO і hit rate buffer pool, що ніби повільно здався. Реплікаційна затримка стала з’являтися під час піків, потім затримувалася після їх завершення.

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

Вирішили нудно, але правильно: ввели цілий сурогатний ключ (integer) як первинний, а UUID залишили як унікальний вторинний ключ для зовнішніх посилань. Міграція не була приємною, але система припинила «кровотечу». Команда винесла правило в онбординг: в InnoDB ваш первинний ключ — це рішення щодо зберігання, а не тільки ідентифікації.

Міні-історія 2: Оптимізація, що дала зворотний ефект (мания covering index)

Інша організація використовувала PostgreSQL для платформи інжестингу подій. Інженер почав кампанію index-only scan. Логіка була зрозумілою: зменшити fetch до heap, зробивши індекси «покриваючими» для поширених запитів. Вони додали кілька мультиколонних B-tree індексів з INCLUDE-колонками, і латентність у staging покращилася.

У продакшні все було добре місяць. Потім збільшився обсяг інжесту, оновлення стали частішими (зміни статусів і збагачення), і autovacuum почав відставати. Біти visibility map не встигали ставитись, і index-only scans тихо перетворилися на index scans плюс heap fetches. Тим часом більші індекси збільшили тиск на vacuum і контрольні точки.

Симптоми проявилися як періодичні спайки латентності й IO-штормів. Іронія була болюча: проект «index-only» збільшив обсяг даних, які треба vacuum-ити й кешувати, що ускладнило підтримання передумови index-only (видимість).

Виправлення не полягало в «додаванні ще індексів». Вони відкотили найширші INCLUDE-стовпці, розділили навантаження партиціюванням найгарячішої таблиці подій і налаштували autovacuum для кожної партиції. Index-only scans повернулися там, де вони мали сенс, і система перестала коливатися між «швидко» і «вогнем».

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

Фінтех‑команда використовувала MariaDB і PostgreSQL у різних сервісах. У них була тьмяна, але корисна звичка: кожна зміна схеми/індексу мала план відкату, і вони ставилися до змін індексів як до деплою аплікації. DBA не намагався бути важким; він намагався зберегти вихідні дні вихідними.

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

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

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

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

1) Записи раптово сповільнились після «оптимізації читання»

• Симптом: Зростає затримка INSERT/UPDATE, збільшується реплікаційна затримка, CPU і IO ростуть.
• Корінь: Надто багато вторинних індексів або новий широкий composite/covering індекс додано в гарячу таблицю.
• Виправлення: Видаліть надлишкові індекси; зберігайте лише ті, що підтримують топові запити. У Postgres віддавайте перевагу цільовим і partial індексам; в MariaDB тримайте PK компактним і не індексуйте «все підряд».

2) «Індекс існує», але запит все одно сканує або сортує

• Симптом: MariaDB EXPLAIN показує «Using filesort» або «Using temporary»; план Postgres показує Seq Scan або Sort.
• Корінь: Індекс не відповідає порядку предикатів (неправильний порядок у composite), або предикат використовує функції/касти, що заважають використанню індексу, або низька селективність робить скан дешевшим.
• Виправлення: Вирівняйте індекс з фільтром + сортуванням; перепишіть предикати, щоб вони були sargable (уникайте обгортання колонки у функцію); розгляньте partial index (Postgres) або обчислювані колонки (MariaDB).

3) Планувальник обирає жахливий план після росту даних

• Симптом: Запит, що був нормальним минулого тижня, тепер різко спадає; EXPLAIN показує nested loop на великих обсягах, неправильний порядок з’єднань або неправильний індекс.
• Корінь: Застарілі статистики, перекіс розподілу, параметрочутливі плани або відсутність розширених статистик (Postgres).
• Виправлення: Запустіть ANALYZE; збільшіть цілі статистик для перекошених колонок; використайте розширені статистики в Postgres; в MariaDB оновіть persistent stats/histograms де доступно й перевірте з JSON EXPLAIN.

4) Index-only scans в Postgres не залишаються index-only

• Симптом: Плани показують Index Only Scan, але heap fetches високі; продуктивність погіршується під час сплесків записів.
• Корінь: Visibility map не встановлено через відставання vacuum; часті оновлення інвалідовують all-visible біти.
• Виправлення: Налаштуйте autovacuum (scale factors, cost delay) для гарячих таблиць/партицій; зменшіть churn оновлень; перегляньте INCLUDE-блоат; партиціюйте гарячі дані.

5) Hit rate buffer pool у MariaDB раптово падає «нібито без причини»

• Симптом: Більше читань з диска, вища латентність, хоча мікс запитів схожий.
• Корінь: Робочий набір індексів виріс (нові індекси, PK розширився або експлозія кардинальності індексу) і більше не вміщується в пам’ять.
• Виправлення: Видаліть невикористані індекси; тримайте PK компактним; налаштуйте розмір buffer pool; переконайтесь, що ваші гарячі індекси вміщуються в пам’яті, а не холодні.

6) Піки очікування блокувань після додавання/видалення індексів

• Симптом: Тайм-аути блокувань, очікування DDL, блоковані писачі.
• Корінь: Неконкурентний DDL, довгі транзакції або зміни схеми, що конкурують з гарячим трафіком.
• Виправлення: У Postgres використовуйте CREATE INDEX CONCURRENTLY і DROP INDEX CONCURRENTLY; у MariaDB використовуйте ALGORITHM=INPLACE, LOCK=NONE, де підтримується; спочатку виправте довгі транзакції.

Контрольні списки / поетапний план

Практичний робочий процес індексації (підходить для обох рушіїв)

1. Виберіть ціль: оберіть топові запити за загальним часом, а не «найповільніший раз».
2. Отримаєте реальний план: Postgres: EXPLAIN (ANALYZE, BUFFERS). MariaDB: EXPLAIN FORMAT=JSON плюс статусні лічильники.
3. Класифікуйте біль: CPU vs IO vs блокування. Якщо ви не знаєте, ви не готові індексувати.
4. Запропонуйте найменший корисний індекс: не робіть його покриваючим «про всяк випадок».
5. Перевірте вплив на записи: чи таблиця гаряча? скільки записів/сек? скільки існуючих індексів?
6. Деплойте безпечно: Postgres — concurrently; MariaDB — in-place/online; стежте за реплікаційною затримкою.
7. Підтвердіть використання: переконайтесь, що індекс обирається і зменшує рядки/IO. Якщо не використовується — видаліть.
8. Виміряйте побічні ефекти: кеш буфера, vacuum, контрольні точки/redo, IO wait, очікування блокувань.
9. Переоцініть щоквартально: індекси не вічні; робочі навантаження змінюються.

Контрольний список дизайну індексу: що робити і що не робити

• Робіть: узгоджуйте composite індекси з найпоширенішими предикатами + порядком. В OLTP оптимізуйте головний шлях.
• Робіть: в Postgres використовуйте partial індекси для патернів «status=active», коли предикат стабільний і селективний.
• Робіть: в InnoDB тримайте первинний ключ компактним і уникайте випадкових широких PK, якщо вас хвилює розмір вторинних індексів.
• Не робіть: індексуйте колонки з низькою селективністю самі по собі і не очікуйте дива.
• Не робіть: додавати INCLUDE-колонки, поки не доведете, що heap fetches — вузьке місце і vacuum справляється.
• Не робіть: «виправляти» поганий запит, додаючи три індекси, якщо перепис запиту усуває потребу в сортуванні або скануванні.

Коли обирати тактики на кшталт MariaDB vs підхід Postgres

• Якщо PK-роздування вбиває вас: MariaDB/InnoDB часто виграє від редизайну PK; Postgres менш чутливий, але дисципліна ширини індексу все одно корисна.
• Якщо перекошені предикати вводять в оману планувальник: Postgres дає більше інструментів статистики; використовуйте їх перед додаванням надлишкових індексів.
• Якщо потрібні partial індекси і багатші типи індексів: Postgres зазвичай кращий набір інструментів.
• Якщо потрібна операційна передбачуваність для простих B-tree навантажень: MariaDB може бути прямо вперед — поки ви не надіндексуєте і buffer pool не стане полем бою.

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

1) Чи варто «індексувати кожний зовнішній ключ» в MariaDB і PostgreSQL?

Зазвичай так для продуктивності JOIN в OLTP, але не сліпо. В MariaDB відсутність FK-індексів може спричинити ампліфікацію блокувань і повільні DELETE/UPDATE. В Postgres JOIN-и можуть працювати й без них, але часто ви заплатите сканами. Перевіряйте по топовим запитам і фактичним планам.

2) Чи завжди composite індекс кращий за кілька одноколонних індексів?

Ні. Composite кращий, коли ваш запит використовує ці колонки разом у способі, який індекс може використати (фільтр + порядок або селективний префікс). Кілька одноколонних індексів все ще можуть бути корисними, і Postgres іноді поєднує їх (bitmap index scans), але це може перетворитися на додаткові звернення до heap.

3) Чому додавання індексу іноді робить запит повільнішим?

Бо планувальник обирає його і помиляється, або бо індекс спричиняє випадкові IO, гірші за послідовний скан. Також новий індекс може змінити форму плану (порядок з’єднань, nested loops), що виглядає дешевше в оцінках, але таким не є.

4) Чи покриваючі індекси завжди допомагають?

Ні. В MariaDB зробити індекс «покриваючим», додаючи колонки, збільшує розмір індексу і вартість записів. В Postgres INCLUDE може зменшити heap fetches, але лише якщо vacuum підтримує visibility maps. Покриття — інструмент, а не спосіб життя.

5) Як зрозуміти, що статистики Postgres брешуть?

Коли оцінені ряди сильно відрізняються від фактичних у EXPLAIN (ANALYZE), або коли план змінюється після незначних змін. Виправляйте через ANALYZE, цільові статистики по колонках і розширені статистики для корельованих колонок.

6) Чи можна просто примусово змусити використання індексу підказками?

У MariaDB це можна, і люди так роблять. В Postgres підказки не вбудовані (існують розширення). Примус планів — операційний борг: ви прив’язуєтесь до плану, що може стати неправильним при зміні даних. Краще зробіть правильний план природно найдешевшим через схему, статистики і форму запиту.

7) Який найшвидший безпечний спосіб видалити індекс?

Postgres: DROP INDEX CONCURRENTLY, щоб уникнути блокування писачів. MariaDB: видалення індексів зазвичай швидке, але все одно може блокувати метадані; робіть це в спокійні періоди і слідкуйте за реплікаційною затримкою або очікуваннями DDL.

8) Чому булевий/статусовий індекс іноді допомагає в Postgres, але не в MariaDB?

Postgres може використати partial індекси, наприклад WHERE status='open', і не індексувати решту, що підвищує селективність. MariaDB не має такого ж повноцінного partial-index механізму; індекс з низькою кардинальністю часто недостатньо селективний, щоб перемогти скан.

9) Чи є партиціювання рішенням проблем індексації?

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

10) Скільки індексів — «занадто багато»?

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

Наступні кроки, які ви реально можете зробити

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

1. Витягніть топові запити за загальним часом (pg_stat_statements або performance_schema digests). Складіть короткий список.
2. Для кожного запиту зафіксуйте реальний план (Postgres: ANALYZE+BUFFERS; MariaDB: JSON explain). Збережіть як артефакт.
3. Класифікуйте вузьке місце (CPU/IO/блокування). Якщо не вдається — додайте обсервабельність перед індексацією.
4. Видаліть один справді невикористаний або надлишковий індекс на гарячій таблиці, з планом відкату. Виміряйте затримку записів до/після.
5. Додайте один цільовий індекс онлайн/concurrent методом, потім доведіть, що він використовується і покращує потрібну метрику (не тільки бенчмарк).
6. Заплануйте перевірки стану обслуговування: здоров’я vacuum у Postgres, зростання buffer pool та розміру індексів у MariaDB.

Суть не в тому, щоб стати філософом індексів. Суть — зберегти затримки передбачуваними, витрати під контролем і ваш on-call рутинним. Рутинність — преміум‑план.