Настройка индексов базы данных для оптимизации веб-приложения

Настройка индексов базы данных для оптимизации веб-приложения

Индекс — самый быстрый способ ускорить запрос. И одновременно самый быстрый способ замедлить запись, занять лишние десятки гигабайт и ввести в заблуждение планировщика. Разберём, какие индексы добавлять, а какие удалять.

Типы индексов в PostgreSQL

B-tree — дефолт, подходит для равенства, диапазонов, ORDER BY, LIKE 'prefix%'.

GIN — для массивов, JSONB, tsvector (full-text search), операторы @>, ?, @@.

GiST — для геометрических типов, range types, full-text (альтернатива GIN с меньшим размером, медленнее по build).

BRIN — для очень больших таблиц с коррелированными данными (метрики по времени, логи). Минимальный размер, медленнее GIN/B-tree по lookup.

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 first, range/sort last.

-- Запрос: 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 (идеально) или минимальным числом удалённых строк.

Partial indexes

Partial index покрывает подмножество строк — меньше размер, быстрее build, лучше 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');

-- Для soft delete: индекс по активным записям
CREATE INDEX idx_users_active_email
    ON users (email)
    WHERE deleted_at IS NULL;

Partial index используется только когда WHERE условие запроса включает условие индекса.

Covering indexes (INCLUDE)

PostgreSQL 11+ поддерживает INCLUDE — добавляет колонки в leaf-страницы индекса без влияния на порядок:

-- Запрос: SELECT id, title, price FROM products WHERE status = 'published' ORDER BY created_at DESC
-- Covering index — не нужен 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 — кандидаты на удаление. Исключение: уникальные индексы (нужны для constraint проверки при 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 дня.