Питання на співбесіді: Індекси

Індекс - окрема відсортована структура (найчастіше B-дерево), яка зберігає значення колонки й посилання на рядки таблиці. Як предметний покажчик у книжці: щоб знайти сторінку, не треба гортати всю книгу.

Без індексу запит WHERE email = 'a@b.com' змушує базу переглянути всю таблицю (Seq Scan / full table scan). З індексом пошук займає кілька кроків по дереву навіть на мільйонах рядків.

CREATE INDEX users_email_idx ON users (email);

Ціна індексу:

• Запис сповільнюється: кожен INSERT, UPDATE індексованої колонки й DELETE змушує базу оновити ще й індекс. Десять індексів на таблиці - десять додаткових записів на кожну вставку.
• Місце на диску і в пам'яті.

Що індексувати: колонки з WHERE, JOIN і ORDER BY у частих запитах, зовнішні ключі. Не варто - колонки, за якими не шукають, і таблиці на кілька сотень рядків.

Важливо: індекс корисний, коли умова відсіює більшість рядків. Колонка is_active, де 95% значень true, мало що дасть - базі все одно читати майже всю таблицю.

Докладніше в документації: Індекси

Складений індекс (a, b, c) впорядковує рядки спершу за a, всередині однакових a - за b, далі за c. Як телефонний довідник: за прізвищем, потім за ім'ям.

CREATE INDEX orders_user_status_idx ON orders (user_id, status, created_at);

Правило лівого префікса: індекс допомагає, коли умова використовує колонки з початку:

• WHERE user_id = 5 - так.
• WHERE user_id = 5 AND status = 'paid' - так.
• WHERE user_id = 5 AND status = 'paid' ORDER BY created_at - так, і сортування береться з індексу.
• WHERE status = 'paid' - ні (або лише дуже повільним повним обходом індексу): шукати за ім'ям у довіднику, впорядкованому за прізвищем, не вийде.

Як обирати порядок:

1. Спершу колонки з рівністю (=), потім ті, що в діапазоні (>, BETWEEN) чи в ORDER BY. Після колонки з діапазоном наступні колонки для пошуку вже майже не працюють.
2. Серед колонок з рівністю - ті, що використовуються в більшості запитів.

Наслідок: індекс (user_id, status) робить окремий індекс на user_id зайвим - лівий префікс уже покриває такі запити. Зайвий індекс лише сповільнює запис.

Докладніше в документації: Багатоколонкові індекси

PostgreSQL має кілька видів індексів, кожен під свій тип запитів:

• B-tree (за замовчуванням) - рівність, діапазони (<, >, BETWEEN), сортування, LIKE 'початок%'. Покриває більшість випадків.
• Hash - лише рівність (=). З PostgreSQL 10 надійний (записується в WAL), але виграш над B-tree рідко помітний.
• GIN (Generalized Inverted Index) - для значень, що містять багато елементів: масиви, jsonb, повнотекстовий пошук (tsvector), триграми (pg_trgm). Шукає «рядки, де є цей елемент».
• GiST - для геометрії й діапазонів: «перетинається», «містить», «найближчий» (PostGIS, tsrange, exclusion constraints, триграми з пошуком найближчих).
• SP-GiST - для нерівномірно розподілених даних: IP-адреси, телефонні префікси, квадродерева.
• BRIN - крихітний індекс для дуже великих таблиць, де значення фізично впорядковані на диску (час вставки в журналі подій).

CREATE INDEX orders_user_idx ON orders (user_id);                       -- B-tree
CREATE INDEX products_tags_idx ON products USING gin (tags);            -- масив
CREATE INDEX docs_search_idx ON docs USING gin (to_tsvector('simple', body));
CREATE INDEX bookings_period_idx ON bookings USING gist (period);       -- tsrange
CREATE INDEX events_created_brin ON events USING brin (created_at);

Як обирати: почати з B-tree. Інші типи - коли запит використовує оператори, які B-tree не підтримує (@>, &&, @@, <->), або коли таблиця настільки велика, що розмір індексу стає проблемою (BRIN).

Нюанс GIN: швидкий на читання, але дорожчий на запис. Тому він накопичує зміни в «відкладеному списку» (fastupdate), який потім вливається в індекс - на таблицях з інтенсивним записом це варто враховувати.

Докладніше в документації: Типи індексів

Які індекси є:

-- у psql
\d orders

-- або запитом
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

Чи використовуються - статистика pg_stat_user_indexes, яку PostgreSQL збирає з моменту останнього скидання статистики:

SELECT relname AS table,
       indexrelname AS index,
       idx_scan AS scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

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

Перш ніж видаляти «невикористаний» індекс:

• Перевірити термін статистики: якщо її скинули вчора, щомісячний звіт ще не встиг скористатися індексом.
• Перевірити всі репліки: статистика збирається окремо на кожному сервері. Індекс може не використовуватися на primary, але бути критичним для звітів, що йдуть на репліку.
• Унікальні індекси й первинні ключі не видаляють, навіть з idx_scan = 0: вони забезпечують цілісність, а не швидкість.
• Видаляти через DROP INDEX CONCURRENTLY, щоб не блокувати таблицю.

Окремо корисно: seq_scan і seq_tup_read у pg_stat_user_tables показують таблиці, які часто читаються повністю, - кандидати на новий індекс.

Докладніше в документації: Статистика індексів

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

WHERE lower(email) = 'ivan@x.com'        -- індекс на email не використається
WHERE DATE(created_at) = '2026-10-04'    -- те саме
WHERE name LIKE '%олег%'                 -- невідомий початок рядка

Як виправити:

• Переписати умову, щоб колонка лишалася «голою»:

WHERE created_at >= '2026-10-04' AND created_at < '2026-10-05'

• Індекс за виразом (PostgreSQL; функціональний індекс у MySQL 8.0.13+):

CREATE INDEX users_email_lower_idx ON users (lower(email));

• LIKE 'олег%' (відомий початок) працює з B-деревом. У PostgreSQL з не-C локаллю для цього потрібен індекс з text_pattern_ops.
• LIKE '%олег%' - потрібен інший тип індексу: триграмний (pg_trgm + GIN у PostgreSQL) або повнотекстовий пошук (tsvector, FULLTEXT у MySQL), або окремий пошуковий рушій - Meilisearch, Elasticsearch.

Ще одна пастка - неявне приведення типів: порівняння рядкової колонки з числом (WHERE phone = 380501234567) змушує MySQL перетворювати кожне значення колонки, і індекс не працює.

Докладніше в документації: Індекси за виразами

Звичайно база знаходить у індексі посилання на рядок, а потім іде в саму таблицю по решту колонок. Якщо всі колонки, потрібні запиту, вже є в індексі, звертатися до таблиці не треба - це index-only scan, а індекс називають покривним.

-- Запит
SELECT status, total FROM orders WHERE user_id = 5;

-- Покривний індекс: user_id для пошуку, status і total - щоб не йти в таблицю
CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (status, total);

INCLUDE (PostgreSQL 11+) додає колонки лише в листки індексу: за ними не шукають, зате їх можна прочитати. У MySQL INCLUDE немає - колонки просто додають у кінець складеного індексу.

Нюанси:

• PostgreSQL через MVCC мусить перевірити, чи рядок видимий транзакції. Index-only scan справді не читає таблицю лише для сторінок, позначених у visibility map як «усі рядки видимі». Її оновлює VACUUM, тож на таблиці з частими змінами без вакууму виграш менший. У EXPLAIN ANALYZE це видно як Heap Fetches.
• InnoDB усі вторинні індекси вже містять первинний ключ, тож SELECT id ... WHERE email = ? за індексом на email - покривний автоматично.
• Широкий покривний індекс - це копія частини таблиці: більше місця, повільніший запис. Його роблять під конкретний частий запит, а не «про всяк випадок».

Докладніше в документації: Index-only scan і покривні індекси

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

Типові причини:

• Умова не відсіює більшість рядків. Якщо запит поверне 30% таблиці, послідовне читання сторінок підряд дешевше, ніж тисячі випадкових переходів індекс → таблиця. Так задумано.
• Маленька таблиця. Кілька сторінок простіше прочитати повністю.
• Застаріла статистика. Після масового імпорту чи видалення планувальник думає, що в таблиці 100 рядків, а їх мільйон. Рішення - ANALYZE orders (autovacuum робить це сам, але із запізненням).
• Умова не підходить до індексу: функція над колонкою (lower(email)), неявне приведення типів, LIKE '%текст', умова на другу колонку складеного індексу без першої.
• Параметризований запит з generic-планом, оптимальним «в середньому», але не для конкретного значення.
• Налаштування вартості: random_page_cost = 4 за замовчуванням розраховане на HDD. На SSD його зазвичай знижують до 1.1-1.5, і індекси стають «дешевшими» для планувальника.

Як розібратися:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';

Порівняти rows (оцінку) з actual rows. Якщо вони розходяться в рази - проблема в статистиці. Для перевірки гіпотези в сесії можна тимчасово заборонити послідовне читання: SET enable_seqscan = off; - і подивитися, наскільки «індексний» план насправді швидший. На проді так не залишають.

Нерівномірні дані: для колонок з перекосом (99% status = 'done') допомагає збільшити деталізацію статистики: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; і повторний ANALYZE.

Докладніше в документації: Статистика планувальника

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

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
-- майже те саме, що:
CREATE UNIQUE INDEX users_email_unique ON users (email);

Коли потрібен саме індекс:

• Частковий: унікальність серед частини рядків - CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL. Обмеження з WHERE не буває.
• За виразом: унікальність без урахування регістру - ON users (lower(email)).
• CONCURRENTLY: створити без блокування запису на великій таблиці.

Коли потрібне саме обмеження:

• Зовнішні ключі посилаються на PRIMARY KEY чи UNIQUE-обмеження (або унікальний індекс без умови).
• Відкладена перевірка DEFERRABLE INITIALLY DEFERRED - перевіряти унікальність у кінці транзакції, а не після кожного рядка (наприклад, при перестановці позицій).
• ON CONFLICT ON CONSTRAINT за іменем.

NULL і унікальність. За стандартом SQL NULL не дорівнює NULL, тож у колонці з UNIQUE може бути скільки завгодно рядків з NULL. Часто це і потрібно (необов'язковий email). Але інколи - ні: «у користувача може бути лише одна адреса без типу».

NULLS NOT DISTINCT (PostgreSQL 15+) змінює правило - NULL вважаються однаковими:

CREATE UNIQUE INDEX one_default_address ON addresses (user_id, type) NULLS NOT DISTINCT;

Тепер для одного user_id дозволено лише один рядок з type IS NULL.

Докладніше в документації: Унікальні індекси

Роздування (bloat) - індекс займає значно більше місця, ніж потрібно для живих даних. Причина - MVCC: UPDATE і DELETE залишають мертві версії рядків, а записи індексу, що на них вказували, звільняються вакуумом не завжди повністю. Сторінки індексу стають напівпорожніми.

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

Особливо схильні:

• індекси на колонках, які часто змінюються;
• таблиці-черги, де рядки постійно вставляють і видаляють;
• індекси на монотонно зростаючих значеннях з видаленням старих рядків (ліва частина дерева порожніє).

Як оцінити: розширення pgstattuple (pgstatindex('orders_created_idx') показує щільність листків) або запити-оцінки з PostgreSQL Wiki. Простий сигнал - індекс помітно більший за свіжостворений аналог.

Як виправити без простою:

REINDEX INDEX CONCURRENTLY orders_created_idx;   -- PostgreSQL 12+
REINDEX TABLE CONCURRENTLY orders;

CONCURRENTLY будує новий індекс поруч і підміняє старий, не блокуючи запис. Звичайний REINDEX блокує запис у таблицю на весь час перебудови.

Підводні камені: як і з CREATE INDEX CONCURRENTLY, команду не можна виконати в транзакції, а при збої може лишитися недійсний індекс (_ccnew), який треба видалити вручну.

Профілактика:

• Налаштований autovacuum для таблиць з частими змінами.
• fillfactor менше 100 для таблиць з частими оновленнями - щоб нові версії рядків вміщалися на ту саму сторінку (HOT-оновлення не чіпають індекси взагалі).
• Не індексувати колонки, які часто змінюються, без потреби.

Докладніше в документації: REINDEX

B-tree зберігає значення відсортованими. Тому запит «перші N за порядком» може взяти рядки прямо з індексу й зупинитися, не сортуючи всю таблицю:

CREATE INDEX posts_published_idx ON posts (published_at);

SELECT * FROM posts ORDER BY published_at DESC LIMIT 20;
-- Index Scan Backward: 20 рядків з кінця індексу, без сортування

B-tree можна читати в обох напрямках, тож для однієї колонки DESC в індексі не обов'язковий.

Коли напрямок у індексі важливий - змішане сортування кількох колонок:

SELECT * FROM products ORDER BY category_id ASC, price DESC LIMIT 50;

CREATE INDEX products_cat_price_idx ON products (category_id ASC, price DESC);

Індекс (category_id, price) для такого запиту не підійде: обхід у будь-якому напрямку дасть price в тому ж напрямку, що й category_id.

NULLS FIRST / NULLS LAST. За замовчуванням у PostgreSQL NULL більші за всі значення: при ASC вони в кінці, при DESC - на початку. Якщо запит пише ORDER BY published_at DESC NULLS LAST, індекс має збігатися:

CREATE INDEX posts_pub_idx ON posts (published_at DESC NULLS LAST);

Інакше планувальник змушений сортувати.

Разом з фільтром: WHERE user_id = ? ORDER BY created_at DESC LIMIT 10 найкраще обслуговує індекс (user_id, created_at): спершу рівність, потім колонка сортування.

Як перевірити: у EXPLAIN не має бути вузла Sort над великою кількістю рядків - лише Index Scan з Limit. Саме на цьому тримається keyset-пагінація.

Докладніше в документації: Індекси й ORDER BY

Частковий індекс (PostgreSQL) індексує лише рядки, що відповідають умові WHERE. Він менший, швидше оновлюється і краще тримається в пам'яті.

1. Запити завжди дивляться на малу частину таблиці:

-- 99% замовлень завершені, але черга обробки дивиться лише на pending
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';

Індекс займає частку від повного, і запит WHERE status = 'pending' ORDER BY created_at бере з нього кілька рядків.

2. Унікальність лише серед частини рядків - найкорисніше застосування:

-- Email унікальний лише серед не видалених (soft delete)
CREATE UNIQUE INDEX users_email_active_uniq ON users (email) WHERE deleted_at IS NULL;

-- Лише одна активна підписка на користувача
CREATE UNIQUE INDEX subscriptions_one_active ON subscriptions (user_id) WHERE active;

Без часткового індексу видалений користувач «займав» би email назавжди, і зареєструватися знову було б неможливо.

Обмеження:

• Планувальник використає індекс, лише якщо з умови запиту можна вивести умову індексу. WHERE status = 'pending' - так; WHERE status = $1 з параметром, невідомим на етапі планування, - не завжди.
• MySQL часткових індексів не має. Альтернативи - згенерована колонка, яка NULL для «неактивних» рядків (унікальний індекс пропускає NULL), або функціональний індекс.

Докладніше в документації: Часткові індекси

Звичайний CREATE INDEX у PostgreSQL бере блокування, яке забороняє запис у таблицю на весь час побудови. На таблиці в сотні мільйонів рядків це хвилини чи години простою для INSERT/UPDATE.

CREATE INDEX CONCURRENTLY будує індекс без блокування запису:

CREATE INDEX CONCURRENTLY orders_created_at_idx ON orders (created_at);

Ціна й підводні камені:

• Працює довше: два проходи по таблиці й очікування завершення всіх транзакцій, що вже йшли.
• Не можна в транзакції. У Laravel-міграції для цього потрібно вимкнути транзакцію міграції (public $withinTransaction = false;).
• Якщо побудова впала (наприклад, порушено унікальність), лишається індекс у стані INVALID. Він не використовується для читання, але сповільнює запис. Його треба знайти (pg_index.indisvalid = false), видалити DROP INDEX CONCURRENTLY і створити знову.
• Довга транзакція десь у системі змушує побудову чекати - перед операцією варто перевірити pg_stat_activity.

MySQL (InnoDB) з версії 5.6 будує більшість індексів online (ALGORITHM=INPLACE, LOCK=NONE), але на початку й наприкінці все одно потрібне коротке метаданих-блокування. Для великих таблиць часто використовують gh-ost чи pt-online-schema-change.

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

Докладніше в документації: CREATE INDEX CONCURRENTLY

BRIN (Block Range INdex) зберігає не кожне значення, а підсумок для діапазону сторінок таблиці: мінімум і максимум значення колонки в кожних, скажімо, 128 сторінках.

Запит WHERE created_at >= '2026-10-01' перевіряє підсумки й читає лише ті діапазони сторінок, де значення можуть бути, - решту пропускає.

CREATE INDEX events_created_brin ON events USING brin (created_at);
CREATE INDEX events_created_brin ON events USING brin (created_at) WITH (pages_per_range = 32);

Головна перевага - розмір. На таблиці в сотні гігабайтів B-tree займає десятки гігабайтів, а BRIN - мегабайти. Він майже не впливає на швидкість вставки.

Коли BRIN працює: значення корелюють з фізичним розташуванням рядків на диску. Ідеальний випадок - журнали, події, метрики, де рядки лише дописуються в кінець, а час вставки зростає. Тоді кожен діапазон сторінок містить вузький проміжок часу.

Коли не працює:

• Значення розкидані по таблиці випадково (наприклад, user_id у журналі подій) - кожен діапазон містить майже всі значення, і BRIN нічого не відсікає.
• Часті UPDATE чи видалення з подальшими вставками на звільнене місце руйнують кореляцію.
• Потрібен точковий пошук одного рядка - BRIN поверне діапазони сторінок, які доведеться перечитати.

Як перевірити придатність: correlation у pg_stats для колонки - близько до 1 чи -1 означає, що BRIN підійде.

Обслуговування: нові сторінки підсумовуються вакуумом або автоматично (autosummarize = on), або вручну brin_summarize_new_values(). Поки діапазон не підсумовано, його доводиться читати завжди.

Типова архітектура: BRIN за часом на великій таблиці подій + партиціонування за місяцями + B-tree лише на тих колонках, за якими справді шукають окремі записи.

Докладніше в документації: BRIN-індекси

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

HOT (Heap-Only Tuple) - оптимізація, коли оновлення не чіпає індекси взагалі. Нова версія кладеться на ту саму сторінку, а стара посилається на неї ланцюжком. Індекси продовжують вказувати на стару позицію й знаходять нову через ланцюжок.

Умови HOT:

1. Жодна індексована колонка не змінилася.
2. На тій самій сторінці є вільне місце для нової версії.

Чому зайвий індекс шкодить UPDATE: індекс на колонці updated_at чи views_count, яку змінює майже кожне оновлення, робить HOT неможливим для всіх таких оновлень. Кожен UPDATE ... SET views_count = views_count + 1 тепер пише в усі індекси таблиці й більше роздуває їх.

Як збільшити частку HOT:

• Не індексувати «гарячі» колонки, які часто змінюються, без явної потреби.
• fillfactor менше 100 - залишати на сторінках вільне місце для нових версій:

ALTER TABLE counters SET (fillfactor = 80);

(діє для нових сторінок; для наявних - після VACUUM FULL чи перебудови).

• Винести часто змінювані лічильники в окрему вузьку таблицю.

Як виміряти:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_percent
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

Низький відсоток HOT на таблиці з багатьма оновленнями - сигнал переглянути індекси й fillfactor.

Докладніше в документації: Heap-Only Tuples (HOT)

Кожен індекс уповільнює запис, займає пам'ять і диск, подовжує бекапи й вакуум. З роками в базі накопичуються індекси, створені «про всяк випадок» чи для запитів, яких уже немає.

1. Невикористані:

SELECT s.relname AS table, s.indexrelname AS index, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique           -- унікальні тримають цілісність
ORDER BY pg_relation_size(s.indexrelid) DESC;

2. Дублікати - однакові колонки в тому самому порядку:

SELECT indrelid::regclass AS table, array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey, indexprs::text, indpred::text
HAVING count(*) > 1;

3. Надлишкові за лівим префіксом. Індекс (user_id) зайвий, якщо є (user_id, created_at): другий обслуговує ті самі запити. Виняток - коли перший унікальний чи значно менший і використовується для гарячого запиту.

Перш ніж видаляти:

• Переконатися, що статистика збиралася достатньо довго (щомісячні звіти, сезонні задачі).
• Перевірити всі репліки - статистика окрема на кожному сервері.
• Перевірити, чи індекс не підтримує зовнішній ключ (каскадне видалення без нього стане повним переглядом таблиці).
• Видаляти через DROP INDEX CONCURRENTLY і мати скрипт для швидкого відновлення.

Інструменти: запити з PostgreSQL Wiki, pgstattuple, звіти в pganalyze, Percona Monitoring. Аналіз варто повторювати регулярно, а не раз на кілька років після проблем зі швидкістю.

Докладніше в документації: Статистика індексів