Акты 1–3 в глубину: схема, индексы и статистика планировщика

Команда добавляет колонку TEXT для email вместо CITEXT в День 0. Шесть месяцев и 100M строк спустя баг с регистром требует смены типа по всей таблице. Исходное решение по схеме — принятое за пять секунд — стоит трёх дней и окна обслуживания. Схема — единственный акт, где цена ошибки необратима.

Акт 1 — Схема: единственное необратимое решение

Неправильный тип (TEXT vs CITEXT для email) становится многодневной миграцией при 100M строк. Неправильный выбор ключа (составной натуральный ключ vs суррогатный BIGSERIAL) делает каждый join дорогим навсегда. Реляционная модель вознаграждает строгость с самого начала:

• NOT NULL по умолчанию — nullable колонки влекут app-level null-проверки там, где должны быть ограничения базы.
• FK-ограничения, если нет измеренного throughput pressure — ссылочная целостность предотвращает orphaned child rows, которые на масштабе дают неправильные результаты запросов.
• CHECK-ограничения для бизнес-инвариантов — прямой SQL update без них может создать невалидное состояние.
• Суррогатные ключи для внешних ссылок — id BIGSERIAL изолирует от изменений email; ретрофит суррогатных ключей при 1B строк требует полной перезаписи таблицы.

Ошибки схемы и escape hatches:

• Составной натуральный ключ (user_id, organization_id, email) как PK означает: каждый join несёт три колонки; каждый FK реплицирует три колонки. Исправление: суррогатный id дешевле, но ретрофит требует миграции с перезаписью таблицы.
• Отсутствующий FK на child table означает отсутствие ссылочной целостности; данные деградируют со временем. Исправление: добавить FK ретроактивно, но сначала исправить orphaned child rows.
• Отсутствующее CHECK-ограничение на domain-specific колонке (status должен быть одним из ACTIVE, PENDING, ARCHIVED) — приложение единственный guard. Исправление: добавить ограничение, вычистить невалидные строки.

Нарушай правила только с доказательствами — отключай FK на highest-write child tables, если стоимость FK trigger превышает write budget, но никогда как стилистическое предпочтение.

Акт 2 — Индексы: окупаются только на правильных колонках

B-tree на email разрешает WHERE email = ? за O(log N) page reads. B-tree на (org_id, created_at DESC) разрешает «50 последних событий для org» за два page read. GIN-индекс на prefs jsonb делает WHERE prefs @> '{"theme":"dark"}' поиском за 2 мс вместо 4 с seq-scan.

Забытая стоимость: каждый insert и update пишет во все индексы. Правило leading-column — самый нарушаемый принцип в дикой природе.

Ошибки выбора индексов:

• B-tree индекс на low-cardinality колонке (gender с 2 значениями, status с 5) почти всегда бесполезен — планировщик выбирает seq-scan, а индекс ест write throughput.
• Индекс на (a, b) не помогает запросам, фильтрующим только по b. Индекс (user_id, created_at), созданный для «событий пользователя», не помогает «последним событиям любого пользователя». Отдельный (created_at, user_id) удовлетворяет оба запроса, и планировщик выбирает его исходя из запроса.
• JSONB-индексы (GIN или GIST) мощные, но дорогие на writes — 100K inserts/s могут вдвое снизить write throughput при наивном индексировании JSONB-колонки.
• Partial indexes решают проблему write throughput для dimension tables: CREATE INDEX ... WHERE status != 'ARCHIVED' покрывает 5% активных строк за долю стоимости.

Цена ошибки здесь постоянная: legacy-приложения наследуют неверный порядок колонок, и любая попытка удалить индекс рискует регрессией в недокументированных code path.

Акт 3 — Статистика: планировщик выбирает планы из гистограмм

Postgres сэмплирует таблицу во время ANALYZE и строит гистограмму на каждую колонку. Планировщик использует гистограммы для оценки количества строк; cost model ранжирует планы. Когда статистика устаревает, оценки неверны.

Ошибка оценки в 1000× даёт план в 1000× медленнее: планировщик выбирает nested loop, ожидая 10 строк; реальная мощность — 10000 строк; цикл сканирует 10M index entries вместо одного hash join по 10K-строчной build side.

Защита:

• autovacuum_analyze_scale_factor = 0.05 (или меньше, 0.01) на busy tables — при дефолтном 0.1 таблица, растущая на 1M строк в день, имеет устаревшую статистику уже через часы.
• CREATE STATISTICS (dependencies) ON user_id, created_at FROM events учит планировщика корреляциям колонок (новые пользователи появляются в свежих данных; планировщик по умолчанию считает их независимыми).
• pg_stat_statements для поиска проблемных планов по total execution time.
• auto_explain.log_min_duration = '500ms' для triage хвостовой латентности.

Ловушка на масштабе: одна плохая оценка каскадирует. Планировщик кеширует plan IDs (pg_stat_statements.plan_id); человек должен найти и принудительно перепланировать, удалив и пересоздав prepared statement запроса, или выполнив DISCARD PLANS.