data · advanced · 8d
Оптимизатор отчётной схемы
Отчётность — это место, где база либо оправдывает себя, либо нет: запросы широкие, таблицы большие, а «тормозит» — самый частый баг в продакшен-аналитике. Ты смоделируешь домен продаж и событий, напишешь честные медленные версии запросов для дашборда, а затем ускоришь их индексами и материализованными представлениями — и прочитаешь EXPLAIN ANALYZE, чтобы доказать ускорение, а не гадать о нём. Это сердце трека: превратить расплывчатое «отчёт подтормаживает» в измеренное, обоснованное изменение плана.
Результат
Самостоятельно развёрнутая база Postgres с засеянной отчётной схемой, файл аналитических запросов, у каждого из которых приложен вывод EXPLAIN ANALYZE «до/после», набор индексов и хотя бы одно материализованное представление с задокументированной стратегией обновления, а также короткая заметка, объясняющая каждое изменение плана и его причину.
Этапы
0/5 · 0%- 01Смоделируй отчётный домен
Прежде чем что-то оптимизировать, нужна форма, которую стоит оптимизировать. Смоделируй небольшой, но реалистичный домен продаж: customers, products, orders и order_lines с внешними ключами и ограничениями, которые держат данные честными. Отчётные схемы живут в напряжении: пережмёшь нормализацию — каждый запрос станет джойном из шести таблиц; денормализуешь слишком рьяно — обменяешь целостность записи на скорость чтения, которая, может, ещё и не нужна. Выбери осознанную середину, напиши DDL и добавь ограничения, которые позволят планировщику доверять данным. Схема, которой планировщик доверяет, — это схема, которую он может оптимизировать; груда nullable-столбцов без ключей — это то, о чём ему приходится гадать.
Критерии готовности- Существуют четыре связанные таблицы с первичными ключами, внешними ключами и хотя бы одним ограничением NOT NULL / CHECK, кодирующим настоящее бизнес-правило.
- Скрипт засева загружает хотя бы несколько сотен тысяч строк order_lines, чтобы планы запросов вели себя как в продакшене, а не как в игрушке.
- 02Сначала напиши медленные отчёты
Теперь напиши запросы, которые реально попросил бы дашборд, и напиши их намеренно наивно: выручка по месяцам, топ товаров по категориям, доля повторных клиентов, нарастающий итог продаж во времени. Используй GROUP BY, агрегаты и одну-две оконные функции — настоящий словарь отчётности. Запусти каждый и замерь время. Удержись от оптимизации: нельзя доказать ускорение, не имея медленной отправной точки, которую нужно обойти. Дисциплина здесь — зафиксировать честное начало, потому что «кажется быстрее» — это не инженерное утверждение, а число — да.
Критерии готовности- Минимум четыре аналитических запроса выполняются корректно и возвращают ожидаемую форму результата.
- У каждого запроса есть записанное базовое время и вывод EXPLAIN ANALYZE, снятые до любой настройки.
- 03Прочитай план, найди боль
EXPLAIN ANALYZE — единственный честный свидетель того, что Postgres действительно сделал. Научись его читать: замечай последовательные сканы по большим таблицам, строки, оценённые радикально иначе, чем реально вернулись, хеш-джойны и сортировки, сбрасываемые на диск. Разрыв между оценкой и реальностью обычно и есть место твоей боли — когда планировщик думает, что шаг вернёт 10 строк, а он возвращает 10 миллионов, он выбирает неверную стратегию для всего запроса. Для каждого медленного запроса выпиши самый дорогой узел и плохую оценку, которая им движет. Ты пока ничего не чинишь — ты ставишь диагноз, потому что починка без диагноза — просто удачная догадка.
Критерии готовности- Для каждого медленного запроса ты можешь назвать доминирующий по стоимости узел (seq scan, сортировка, hash join) и сходятся ли оценки с фактическими строками.
- Запуск ANALYZE по таблицам и повторное чтение плана показывают эффект свежей статистики на выбор планировщика.
- 04Индексируй, материализуй и докажи
Теперь заработай ускорение. Добавь индексы, которые запросили твои планы, — и только их, потому что каждый индекс это налог на каждую запись и кусок диска, за который ты платишь вечно. Композитный индекс с правильным порядком столбцов превращает последовательный скан в индексный; неверный порядок оставляет его неиспользованным. Для самых тяжёлых агрегатных отчётов построй материализованное представление, заранее считающее свёртку, и честно реши, как оно обновляется: по расписанию, конкурентно или по требованию — каждый выбор меняет свежесть на стоимость. Затем перезапусти EXPLAIN ANALYZE и положи «до» и «после» рядом. Победа засчитывается, только если план изменился, время упало, и ты можешь точно сказать почему.
Критерии готовности- Минимум два запроса показывают измеримо лучший план (index scan вместо seq scan или более дешёвый join) с записанным временем «до/после».
- Минимум одно материализованное представление обслуживает тяжёлый отчёт, и его стратегия обновления записана и обоснована.
- 05Защитись от регресса
Ускорение, которое тихо гниёт, хуже, чем его отсутствие, потому что кто-то на него полагается. Материализованные представления устаревают, статистика дрейфует после крупных загрузок, а раздувание от обновлений заставляет индексные сканы ползти. Встрой обслуживание, которое сохраняет твои победы: путь обновления для представления, ANALYZE после массовых загрузок, чтобы планировщик оставался честным, и проверку, что vacuum держит раздувание под контролем. Перезапусти эталонные запросы ещё раз и убедись, что планы, за которые ты бился, всё ещё те, что ты получаешь. Признак сеньора — не сделать быстро один раз, а сделать так, чтобы оставалось быстро, когда никто не смотрит.
Критерии готовности- Существует задокументированная процедура обслуживания (обновление представления + ANALYZE + проверка vacuum), и ты прогнал её один раз от начала до конца.
- Повторный прогон эталона после обслуживания подтверждает, что оптимизированные планы по-прежнему держатся.
Рубрика
| Джуниор | Миддл | Сеньор | |
|---|---|---|---|
| Чтение EXPLAIN ANALYZE | Запускает EXPLAIN и называет тип верхнего узла; замечает, есть ли seq scan или index scan. | Читает факт против оценки строк по каждому узлу, находит самый дорогой узел и объясняет, почему большой разрыв в оценке заставляет планировщик выбирать неверную стратегию join. | Замечает спилл на диск (Batches > 1), связывает плохие оценки с устаревшей статистикой и намеренно вызывает регресс плана удалением индекса — а затем читает новый план и точно объясняет, какой узел изменился и почему. |
| Проектирование индексов и covering-индексов | Добавляет однострочный индекс на очевидный столбец фильтра и подтверждает смену seq scan на index scan. | Выбирает порядок столбцов составного индекса по селективности ведущего столбца, добавляет covering-индекс для устранения обращений к куче в горячих запросах и использует частичный индекс на предикате с низкой кардинальностью, чтобы держать индекс компактным. | Аудитирует write amplification: подсчитывает, сколько индексов должен обновить INSERT, удаляет неиспользуемые и называет стоимость каждого индекса по диску и записи как явный компромисс, а не как послесловие. |
| Стратегия свежести материализованного представления | Создаёт материализованное представление и обновляет его вручную по запросу. | Выбирает между конкурентным и неконкурентным обновлением с обоснованием, планирует его под допустимое окно устаревания и добавляет ANALYZE после массовой загрузки, чтобы статистика оставалась честной. | Планирует на масштаб: называет стоимость обновления по мере роста таблицы, определяет, когда инкрементное обновление через дельта-таблицу дешевле полного REFRESH CONCURRENTLY, и встраивает проверку регресса, падающую в CI при деградации плана запроса представления. |
| Защита от регресса | Перезапускает запросы после изменений и отмечает, стало ли быстрее на ощупь. | Записывает вывод EXPLAIN ANALYZE и время «до/после» для каждой оптимизации и подтверждает, что планы держатся после запуска ANALYZE и VACUUM. | Автоматизирует проверку регресса: снимает последовательность узлов плана как отпечаток, громко падает при повторном появлении seq scan и привязывает проверку к шагу CI — превращая «отчёт ещё быстрый?» из человеческой оценки в машинное утверждение. |
Эталонный разбор (спойлер)
Почему EXPLAIN ANALYZE, а не EXPLAIN: EXPLAIN показывает прогноз планировщика; EXPLAIN ANALYZE выполняет запрос и фиксирует фактические строки и тайминг по каждому узлу. Разрыв оценка-факт — вот откуда начинается настоящая диагностика: недооценка строк в 1000× на узле nested loop — это симптом устаревшей статистики, а не ошибка запроса.
Порядок столбцов составного индекса: ведущий столбец обязан присутствовать в WHERE, иначе индекс вообще не используется. Ставьте сначала наиболее селективный предикат равенства, диапазонные — в конец. Covering-индекс добавляет каждый столбец, который SELECT проецирует, чтобы Postgres мог ответить только из индекса — сэкономив обращение к куче на каждую возвращаемую строку.
Компромисс свежести материализованного представления: REFRESH MATERIALIZED VIEW блокирует представление (читатели ждут) до окончания перестройки; REFRESH CONCURRENTLY избегает блокировки, но требует уникального индекса и занимает примерно вдвое больше. Ни то ни другое не является инкрементным — таблица перестраивается целиком. При значительном числе строк только ручное дельта-слияние позволяет уложить обновление в бюджет по задержке.
Регресс плана после массовых загрузок: статистика в Postgres — выборочная, а не точная. После массового INSERT оценки числа строк планировщиком могут расходиться на порядок, пока не запустится ANALYZE. Классический продакшен-сбой: ночная загрузка удваивает таблицу; утренний отчёт попадает в индекс, который планировщик теперь пропускает, потому что устаревшая статистика говорит, что таблица маленькая, — возвращается seq scan.
Сделай по-сеньорски
- Добавь журнал аудита, осведомлённый о транзакциях: таблицу, фиксирующую, кто, что и когда изменил, записываемую в той же транзакции, что и изменение, чтобы откатанное обновление не оставляло осиротевшую строку аудита. Явно рассуди об изоляции — на READ COMMITTED против REPEATABLE READ, — чтобы журнал никогда не противоречил данным, которые он берётся описывать.
- Разбей журнал аудита (или факт-таблицу orders) по времени с помощью декларативного range-партиционирования, чтобы старые партиции можно было дёшево отцеплять и архивировать, а запросы с фильтром по дате обрезались до одной партиции вместо скана всей истории.
- Поставь проверку регресса на скрипт: снимай сигнатуру плана и время каждого запроса, громко падай, если план снова сваливается в последовательный скан, и преврати «отчёт ещё быстрый?» в вопрос, на который может ответить CI.