Индексы БД на собеседовании Data Engineer

Зачем спрашивают на собесе DE

DE отвечает за SLA пайплайна и read-perf аналитических запросов. На собесе обязательны вопросы: «когда B-tree, а когда hash», «зачем partial index», «почему запрос игнорирует индекс». Ответ «навешу индекс на все нужные колонки» — junior. Senior понимает, когда индекс вреден (write-heavy, низкая селективность).

Главная боль без понимания — DE накидал 10 индексов «на всякий случай», INSERT просел в 3 раза, никто не знает почему.

B-tree: дефолт и почему

B-tree (B+-tree) — сбалансированное дерево с упорядоченными ключами в листьях. Дефолт во всех реляционных БД (Postgres, MySQL, SQL Server, GP).

Что хорошо умеет:

• Точное равенство (WHERE id = 42)
• Диапазоны (WHERE created_at BETWEEN ... AND ...)
• <, >, <=, >=
• LIKE 'prefix%' (но не '%suffix')
• ORDER BY по индексированной колонке (без сортировки в плане)

Что нет:

• <> / !=
• LIKE '%middle%'
• Функции от колонки (WHERE LOWER(email) = ...) — нужен функциональный индекс
• Поиск в массивах, JSONB-полях

Сложность: O(log N) на поиск/вставку. На миллиарде строк глубина дерева 4-5.

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Hash, GIN, GiST, BRIN

Hash — равенство и только. В Postgres до 9.6 был unsafe (не WAL-логировался), с 10+ — production-ready, но обычно B-tree не хуже.

GIN (Generalized Inverted Index) — для составных значений. Хранит обратный индекс: value → list of rows.

-- поиск по массивам
CREATE INDEX idx_tags ON posts USING GIN(tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql', 'analytics'];

-- JSONB
CREATE INDEX idx_payload ON events USING GIN(payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"type": "click"}';

-- Full-text search
CREATE INDEX idx_fts ON articles USING GIN(to_tsvector('russian', body));

GiST (Generalized Search Tree) — для геометрии (PostGIS), full-text, range types, kNN. Гибкий, обычно медленнее GIN на чтение, но быстрее на вставку.

BRIN (Block Range INdex) — для больших таблиц с физической упорядоченностью данных (например, append-only по created_at). Хранит min/max на диапазон блоков. Огромная экономия места: индекс на 1ТБ-таблицу = десятки МБ.

CREATE INDEX idx_events_brin ON events USING BRIN(created_at);

Условие: данные физически отсортированы по индексируемому столбцу. Иначе запрос читает почти всё.

Partial и covering индексы

Partial index — индекс с WHERE. Включает только подмножество строк.

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

-- индекс на «непрочитанные» уведомления
CREATE INDEX idx_notif_unread ON notifications(user_id, created_at DESC)
WHERE read_at IS NULL;

Зачем: 95% таблицы не нужно индексировать (deleted/archived/old) — partial экономит место и ускоряет вставку.

Covering index (INCLUDE) — добавить «не-ключевые» колонки прямо в индекс, чтобы запрос не ходил в heap.

CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 42;
-- Index Only Scan, без обращения к таблице

Postgres ≥ 11. До этого — ставили лишние колонки в ключ, что хуже (увеличивает дерево).

Композитные индексы и порядок колонок

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

Порядок колонок критичен. Этот индекс используется при фильтрации:

• WHERE user_id = ? — да
• WHERE user_id = ? AND status = ? — да
• WHERE user_id = ? AND status = ? AND created_at > ? — да
• WHERE status = ? — нет (не на первой колонке)
• WHERE user_id = ? AND created_at > ? — частично (по user_id, потом фильтр в строках)

Правило: колонки с равенством — впереди, диапазон — в конце. После колонки с диапазоном следующие колонки индекса для фильтрации не используются.

Когда индекс не помогает

Низкая селективность. Колонка с двумя значениями (is_active) — индекс редко полезен. Планировщик предпочтёт seq scan, особенно если выбирается > 10-20% строк.

Маленькая таблица. До нескольких тысяч строк seq scan быстрее.

Функция от колонки в WHERE.

-- индекс не используется
SELECT * FROM users WHERE LOWER(email) = 'a@b';

-- решение 1: функциональный индекс
CREATE INDEX idx_email_lower ON users(LOWER(email));

-- решение 2: переписать запрос
SELECT * FROM users WHERE email = 'a@b';  -- если данные уже в lower

Тип данных не совпадает. WHERE id = '42' (строка vs int) — implicit cast, индекс не используется. Всегда передавай корректный тип.

OR через несколько колонок. Зачастую планировщик не комбинирует индексы. Решение: UNION или bitmap index scan.

Statistics устарели. После массового UPDATE/INSERT — ANALYZE table_name, иначе планировщик ошибается.

Stale statistics. В Postgres pg_stat_user_indexes показывает использование индекса. Неиспользуемые → удалить.

Индексы в DWH: ClickHouse и Greenplum

ClickHouse — нет индексов в привычном смысле. Есть primary key, которая определяет физический порядок данных и работает как разреженный индекс (mark per granule, дефолт 8192 строки). Plus skip-indexes (minmax, set, bloom_filter) — позволяют пропускать гранулы.

CREATE TABLE events (...)
ENGINE = MergeTree()
ORDER BY (event_date, user_id);
-- ORDER BY это и есть «индекс» — primary key

Greenplum — поддерживает B-tree, GiST, GIN. Но в OLAP чаще критичнее distribution key и partitioning, чем индексы. Bitmap indexes в GP — для read-heavy фактовых таблиц с низкой кардинальностью.

В обоих случаях: индексы в DWH вторичны. Партиционирование, кластеризация, распределение — первичны.

Частые ошибки

Навесить индекс на всё. Каждый индекс — write penalty: INSERT/UPDATE/DELETE обновляют все индексы таблицы. На write-heavy таблице 10 индексов снижают throughput в разы.

Забыть WHERE для partial. Если планировщик не видит, что запрос подходит под условие индекса — индекс не используется.

Композитный индекс с неправильным порядком. (status, user_id) бесполезен для WHERE user_id = ?. Анализируй частоту запросов.

Индекс на UUIDv4 как clustered key. UUIDv4 случайны → write-amplification, разрастание индекса. Используй UUIDv7 / ULID или surrogate bigint.

Игнорировать ANALYZE. После bulk load статистика устарела, планировщик выбирает плохой план.

Использовать B-tree для JSONB. GIN с подходящим оператором (jsonb_path_ops) гораздо эффективнее.

Удалять индексы по интуиции. pg_stat_user_indexes.idx_scan — единственная правда. 0 за месяц → можно удалять.

Связанные темы

• EXPLAIN и план запроса для DE
• Партиционирование таблиц для DE
• SQL для Data Engineer: собеседование
• Транзакции и MVCC на собесе DE
• Подготовка к собесу Data Engineer

FAQ

Hash-индекс быстрее B-tree на равенство?

В теории — да (O(1) vs O(log N)), на практике — разница небольшая. B-tree универсальнее (range queries, ORDER BY), поэтому почти всегда выигрывает.

Сколько индексов оптимально на таблицу?

Зависит от профиля нагрузки. На OLTP-таблицу с тяжёлым write — 2-4 индекса. На отчётную / append-only — может быть 5-10. Главное правило — каждый индекс должен реально использоваться.

Что такое index bloat и как его лечить?

При UPDATE Postgres создаёт новые версии строк → индексы накапливают «мёртвые» ссылки. Лечение: REINDEX CONCURRENTLY (без блокировки) или pg_repack.

Можно ли индексировать выражение?

Да: CREATE INDEX idx_l ON t(LOWER(email)). Запрос должен использовать ровно то же выражение в WHERE.

Bloom filter index когда используется?

В CH — для skip-индексов на колонках с большим количеством уникальных значений и фильтрацией по равенству. В Postgres bloom extension — для запросов «равенство по нескольким колонкам без префикса».

Это официальная информация?

Нет. Статья основана на документации Postgres 14+, ClickHouse 23.x, Greenplum 7.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.