Оптимізація індексів бази даних для веб-додатків
Індекс — найшвидший спосіб прискорити запит. І одночасно найшвидший спосіб сповільнити запис, займати десятки гігабайт і ввести планувальника в оману. Розберемо, які індекси додавати, а які видаляти.
Типи індексів у PostgreSQL
B-tree — дефолт, підходить для рівності, діапазонів, ORDER BY, LIKE 'prefix%'.
GIN — для масивів, JSONB, tsvector (повнотекстовий пошук), оператори @>, ?, @@.
GiST — для геометричних типів, range типів, повнотексту (альтернатива GIN з меншим розміром, повільніше при побудові).
BRIN — для дуже великих таблиць із корельованими даними (метрики за часом, логи). Мінімальний розмір, повільніше GIN/B-tree при пошуку.
Hash — тільки для рівності (=). Рідко потрібен, B-tree зазвичай краще.
Обов'язкові індекси
Усі FK-колонки — без них DELETE/UPDATE батьківського запису викликає sequential scan дочірної таблиці:
-- PostgreSQL автоматично індексує PK, але не FK
CREATE INDEX idx_products_category_id ON products (category_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);
CREATE INDEX idx_comments_post_id ON comments (post_id);
CREATE INDEX idx_comments_user_id ON comments (user_id);
Унікальні обмеження — автоматично створюють індекс:
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);
ALTER TABLE products ADD CONSTRAINT uq_products_slug UNIQUE (slug);
Складені індекси
Порядок колонок критичний. Правило: equality спочатку, range/sort в кінці.
-- Запит: WHERE status = 'published' AND created_at > '2024-01-01' ORDER BY created_at DESC
-- Правильний складений індекс:
CREATE INDEX idx_products_status_created ON products (status, created_at DESC);
-- Неправильно: range умова спочатку — індекс частково використовується
CREATE INDEX idx_products_created_status ON products (created_at, status); -- гірше
Перевірка: EXPLAIN (ANALYZE, BUFFERS) SELECT ... WHERE status = 'published' ORDER BY created_at DESC LIMIT 20;
Очікуємо: Index Scan using idx_products_status_created з rows removed by filter: 0 (ідеально) або мінімальна кількість видалених рядків.
Часткові індекси
Частковий індекс покриває підмножину рядків — менший розмір, швидша побудова, кращий selectivity:
-- Індекс тільки для опублікованих продуктів
CREATE INDEX idx_products_published_created
ON products (created_at DESC)
WHERE status = 'published';
-- Індекс для незавершених замовлень (їх мало)
CREATE INDEX idx_orders_pending
ON orders (user_id, created_at DESC)
WHERE status IN ('pending', 'processing');
-- Для м'якого видалення: індекс по активним записам
CREATE INDEX idx_users_active_email
ON users (email)
WHERE deleted_at IS NULL;
Частковий індекс використовується тільки коли WHERE умова запиту включає умову індексу.
Покриваючі індекси (INCLUDE)
PostgreSQL 11+ підтримує INCLUDE — додає колонки до leaf-сторінок без впливу на порядок:
-- Запит: SELECT id, title, price FROM products WHERE status = 'published' ORDER BY created_at DESC
-- Покриваючий індекс — не потрібен heap fetch
CREATE INDEX idx_products_published_cover
ON products (status, created_at DESC)
INCLUDE (id, title, price);
Запит виконується через Index Only Scan — дані беруться прямо з індексу, без читання heap.
GIN для JSONB
-- specs: {"ram": "16GB", "storage": "512GB", "os": "linux"}
CREATE INDEX idx_products_specs ON products USING GIN (specs);
-- Пошук за наявністю ключ-значення
SELECT * FROM products WHERE specs @> '{"os": "linux"}';
-- Пошук за наявністю ключа
SELECT * FROM products WHERE specs ? 'ram';
-- GIN з jsonb_path_ops — тільки для @>, менший розмір
CREATE INDEX idx_products_specs_path ON products USING GIN (specs jsonb_path_ops);
Виявлення невикористовуваних індексів
-- Індекси, які ніколи не використовувалися (з моменту останнього pg_stat_reset)
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
Індекси з idx_scan = 0 — кандидати на видалення. Виключення: унікальні індекси (потрібні для перевірки обмежень при INSERT).
Виявлення дублюючих індексів
SELECT
t.relname AS table_name,
ix1.relname AS index1,
ix2.relname AS index2,
array_to_string(a1.attnames, ', ') AS columns1,
array_to_string(a2.attnames, ', ') AS columns2
FROM pg_index i1
JOIN pg_index i2 ON i1.indrelid = i2.indrelid AND i1.indexrelid < i2.indexrelid
JOIN pg_class t ON t.oid = i1.indrelid
JOIN pg_class ix1 ON ix1.oid = i1.indexrelid
JOIN pg_class ix2 ON ix2.oid = i2.indexrelid
CROSS JOIN LATERAL (
SELECT array_agg(a.attname ORDER BY ordinality) AS attnames
FROM unnest(i1.indkey) WITH ORDINALITY AS u(attnum, ordinality)
JOIN pg_attribute a ON a.attrelid = i1.indrelid AND a.attnum = u.attnum
) a1
CROSS JOIN LATERAL (
SELECT array_agg(a.attname ORDER BY ordinality) AS attnames
FROM unnest(i2.indkey) WITH ORDINALITY AS u(attnum, ordinality)
JOIN pg_attribute a ON a.attrelid = i2.indrelid AND a.attnum = u.attnum
) a2
WHERE i1.indkey[0] = i2.indkey[0] -- перша колонка збігається
ORDER BY t.relname;
Створення індексів без блокування
В production додаємо індекси тільки через CONCURRENTLY:
CREATE INDEX CONCURRENTLY idx_products_new ON products (new_column);
Недоліки CONCURRENTLY: займає більше часу (два проходження), не можна в транзакції. Але не блокує INSERT/UPDATE/DELETE під час побудови.
Bloat та rebuild
З часом індекси фрагментуються. Перевіряємо вздування:
-- через pgstattuple розширення
SELECT * FROM pgstattuple('idx_products_status_created');
-- dead_tuple_percent > 20% — пора REINDEX
-- Перебудова без блокування (PG 12+)
REINDEX INDEX CONCURRENTLY idx_products_status_created;
Графіки
Аудит індексів існуючого проекту (невикористовувані, дубліки, відсутні FK-індекси, рекомендації): 1 день. Розробка та додавання оптимальних індексів під конкретний набір запитів: 1–2 дні.