PostgreSQL Middle+ · EXPLAIN, шардинг, репликация

Дашборд BatSchool тормозит. SELECT по homework_submissions с двумя JOIN и фильтром по metadata->>'mentor' стал отвечать за 4 секунды вместо 40 миллисекунд. Менти в чате пишут “ничего не открывается”. Ты лезешь в Postgres и видишь: тот же самый запрос, что вчера летал по индексу, сегодня устроил seq scan по миллиону строк. Что произошло? Где смотреть? Какую кнопку нажать, чтобы вернуть скорость?

Типичная ситуация для Middle+. Решаешь её тремя инструментами: чтением плана запроса, осознанным выбором индекса и пониманием, на каком пределе одной БД ты сейчас стоишь. Этот урок про инструменты, которыми Middle+ держит Postgres под нагрузкой.

Без глубинных внутренностей (этим займёмся в уроке 04), но достаточно, чтобы принимать осознанные решения и читать чужие схемы. После фундамента из урока 01 и транзакций из урока 02 добавим к ним планировщик, расширенный SQL (CTE, оконные функции), углублённые индексы и три темы, без которых разговор про “большой Postgres” не идёт: CAP, шардирование и репликация.

К концу занятия ты сможешь:

прочитать EXPLAIN ANALYZE снизу вверх, отличить хороший план от плохого по трём метрикам;
написать рекурсивный CTE для иерархии (дерево разделов или тред комментариев) и лидерборд оконными функциями;
выбрать тип индекса под конкретный запрос: B-tree, GIN, BRIN, partial, expression или covering;
объяснить, почему Postgres это CP, а не CA, и какие компромиссы это накладывает;
набросать shard-стратегию и понять, какие фичи Postgres из-за неё теряются;
развернуть streaming-реплику и осознанно решить, отдавать ли на неё read-нагрузку.

0. Что считает планировщик

Когда ты пишешь SELECT ..., Postgres не выполняет его буквально. Сначала он строит дерево возможных планов, оценивает стоимость каждого по своей внутренней модели и выбирает самый дешёвый. Это cost-based optimizer: дешёвое не значит “быстрое в миллисекундах”, а “у меня в табличке стоимостей получилось меньше всех”.

Откуда берётся стоимость

Стоимость, это абстрактное число в единицах “прочитать одну последовательную страницу с диска”. Оно складывается из эмпирических констант:

Константа	Значение по умолчанию	Что значит
`seq_page_cost`	1.0	страница, прочитанная последовательно
`random_page_cost`	4.0	страница, прочитанная случайным IO (важно для index scan)
`cpu_tuple_cost`	0.01	обработать одну строку
`cpu_index_tuple_cost`	0.005	обработать одну запись индекса
`cpu_operator_cost`	0.0025	вычислить выражение в WHERE

Для SSD часто опускают random_page_cost до 1.1 (рандом дёшев), для HDD оставляют 4. Это одна из тех настроек, которую DBA крутит после миграции на новые диски, и которая может сразу переключить кучу запросов с seq scan на index scan.

Статистика, чем питается планировщик

Чтобы оценить, “сколько строк вернёт WHERE state = 'done'”, Postgres держит pg_stats: на каждую колонку он хранит топ-N частых значений, гистограмму, количество уникальных, долю NULL. Эту статистику собирает ANALYZE (явный или внутри autovacuum).

ANALYZE homework_submissions;

SELECT attname, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'homework_submissions' AND attname = 'state';

Когда статистика устаревает, планировщик ошибается. Классическая ситуация: ты прогнал миграцию, которая добавила в таблицу миллион новых строк, autovacuum ещё не успел, планировщик думает, что таблица маленькая, и выбирает Nested Loop вместо Hash Join. Запрос, который должен идти секунду, идёт час. Лечение, ручной ANALYZE после массовых импортов.

Селективность

Из статистики планировщик считает селективность каждого фильтра. На её основе решает: брать индекс или нет, в каком порядке делать JOIN.

Полезное правило: если фильтр оставляет больше 10-15% строк, индекс часто проигрывает sequential scan. Это контринтуитивно (“зачем тогда индекс?”), но связано с тем, что index scan делает много рандомных IO, а seq scan читает страницы подряд и пользуется prefetch. Поэтому индекс на колонку с двумя значениями (is_active true/false) обычно бесполезен без partial.

Что планировщик НЕ знает

Корреляции между колонками. Если ты фильтруешь WHERE city = 'Moscow' AND country = 'Russia', планировщик считает их селективности независимо и перемножает (один Москвич из миллиона москвичей в России), что даёт катастрофически заниженную оценку. Лечится extended statistics через CREATE STATISTICS.
Реальное содержимое функций. WHERE my_func(x) = 1 для планировщика непрозрачно, он подставит селективность по умолчанию (обычно 0.005).
Что лежит в кэше. План одинаков для холодного и тёплого buffer cache, хотя реальное время отличается в 100 раз.

Это причина, по которой EXPLAIN ANALYZE важнее EXPLAIN: только реальное выполнение покажет, ошибся ли планировщик и насколько.

1. EXPLAIN ANALYZE построчно

EXPLAIN показывает план без выполнения, EXPLAIN ANALYZE фактически прогоняет запрос и добавляет реальные числа. EXPLAIN (ANALYZE, BUFFERS) дополнительно показывает IO. Это твой главный диагностический инструмент.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, COUNT(h.id) AS done
FROM users u
JOIN homework_submissions h ON h.user_id = u.id
WHERE h.state = 'accepted' AND h.created_at > now() - interval '30 days'
GROUP BY u.email
ORDER BY done DESC
LIMIT 10;

Типичный вывод (упрощённый):

Limit  (cost=4523.10..4523.13 rows=10 width=40) (actual time=78.4..78.5 rows=10 loops=1)
  Buffers: shared hit=1240 read=312
  ->  Sort  (cost=4523.10..4548.20 rows=10042 width=40) (actual time=78.4..78.4 rows=10 loops=1)
        Sort Key: (count(h.id)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=4200.10..4300.52 rows=10042 width=40) (actual time=70.1..73.2 rows=8451 loops=1)
              Group Key: u.email
              ->  Hash Join  (cost=120.00..3850.00 rows=70020 width=32) (actual time=2.4..52.1 rows=68913 loops=1)
                    Hash Cond: (h.user_id = u.id)
                    ->  Index Scan using h_state_created_idx on homework_submissions h
                          (cost=0.42..3120.00 rows=70020 width=12)
                          (actual time=0.05..38.1 rows=68913 loops=1)
                          Index Cond: ((state = 'accepted') AND (created_at > now() - '30 days'::interval))
                          Buffers: shared hit=1100 read=312
                    ->  Hash  (cost=80.00..80.00 rows=8000 width=28) (actual time=2.2..2.2 rows=8451 loops=1)
                          ->  Seq Scan on users u  (cost=0.00..80.00 rows=8000 width=28)
                                Buffers: shared hit=140
Planning Time: 0.42 ms
Execution Time: 78.6 ms

Переключатель сверху меняет план: “без индекса” (тот же запрос, но Seq Scan по миллиону строк) и “с индексом” (тот же план, что в листинге выше). Клик по узлу открывает детали справа: cost, реальное rows, время, buffers, что именно посмотреть. Цифра слева у каждого узла, это порядок физического выполнения снизу вверх.

Как читать дерево

Главное правило: читай снизу вверх и изнутри наружу. Самые вложенные узлы запускаются первыми, их результат подаётся на родителя.

В примере выше:

Seq Scan on users прочитает все 8000 пользователей в память.
Hash построит из них хэш-таблицу id -> row.
Index Scan on homework_submissions найдёт все accepted-сдачи за 30 дней через индекс (state, created_at).
Hash Join для каждой сдачи проверит, есть ли её user_id в хэше пользователей. Возьмёт совпадающую строку.
HashAggregate сгруппирует результат по email и посчитает COUNT.
Sort + Limit возьмут top-10 по убыванию.

Три цифры, на которые смотришь первым делом

Для каждого узла Postgres выводит (cost=A..B rows=N width=W) (оценка планировщика) и (actual time=A..B rows=N loops=L) (реальное выполнение).

Что сравниваешь	Что значит большой разлёт
`cost` рядом с другими узлами	где Postgres ожидает потратить больше всего
`actual time` рядом с `cost`	насколько планировщик угадал
`rows` оценка vs actual	главный сигнал плохой статистики
`loops` > 1	узел выполняется внутри Nested Loop, реальное время = `actual * loops`
`Buffers: shared read=N`	сколько страниц пришло с диска (то, чего нет в shared_buffers)

Когда видишь rows=70020 в плане и rows=68913 в actual, это нормальный разлёт. Когда видишь rows=100 и rows=1000000, у тебя горит статистика, и план выбран неправильно. Это первый красный флаг.

Основные операторы

Узел	Когда видишь	На что смотреть
`Seq Scan`	full scan таблицы	размер таблицы и доля строк, что прошли фильтр
`Index Scan`	поиск по индексу с возвратом к heap	`Index Cond` (что использовано из индекса) и доля сматченного
`Index Only Scan`	поиск только по индексу, без обращения к heap	требует `covering index` или visibility map
`Bitmap Index Scan` + `Bitmap Heap Scan`	средняя селективность, индекс полезен, но строк всё ещё много	bitmap собирает позиции, потом за один проход читает страницы
`Nested Loop`	мало строк во внешней стороне	если loops большое и actual внутри большое, плохо
`Hash Join`	большие таблицы по равенству	дешевле для больших объёмов, нужен memory для хэша
`Merge Join`	обе стороны уже отсортированы (или есть индекс)	редко, но эффективно на огромных диапазонах
`Sort`	требуется ORDER BY или сортировка для merge	`Sort Method: external merge Disk: N` -> сортировка ушла на диск, плохо
`HashAggregate` / `GroupAggregate`	GROUP BY	хэш быстрее, но требует памяти
`Materialize`	результат буферизуется для повторного прохода	обычно безобиден, но иногда сигнал ошибки в плане

BUFFERS, или почему один и тот же запрос идёт по-разному

shared hit = страницы найдены в Postgres-овском кэше, shared read = пришлось читать с диска. На “холодном” запросе read большое, после пары прогонов всё в hit. Поэтому, отлаживая запрос, прогоняй его несколько раз и смотри стабильный результат, а не первый прогон.

Что должно тебя насторожить

Seq Scan на большой таблице с селективным фильтром: значит индекса либо нет, либо планировщик не считает его выгодным (часто потому, что не дали ANALYZE).
actual time верхнего узла резко больше суммы детей: значит время уходит на сам узел (HashAggregate с переполнением памяти, Sort на диск, Nested Loop с большим loops).
Filter: под узлом, а не Index Cond:: значит фильтр применился после чтения, а индекс не покрыл колонку.
Rows Removed by Filter: N, где N в разы больше returned rows: значит читаешь много лишнего, нужен более точный индекс или partial.
Sort Method: external merge Disk: 200MB: не хватило work_mem, сортировка ушла на диск, дико медленно.

`auto_explain`, или EXPLAIN из прода

Логировать каждый медленный запрос в проде стоит почти ничего. Достаточно подключить расширение в postgresql.conf:

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_nested_statements = true

Любой запрос дольше 500 мс попадает в лог с полным EXPLAIN (ANALYZE, BUFFERS). Это спасает на инцидентах, где ты не можешь воспроизвести запрос локально, но он висит на проде.

Визуальные читалки

Текстовое дерево читать в живом инциденте больно, помогают онлайн-визуализаторы: explain.dalibo.com, explain.depesz.com, tatiyants.com/pev. Скармливаешь им вывод EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), получаешь подсвеченное дерево с тяжёлыми узлами. На ревью PR такая картинка проще, чем разбирать текст вручную.

2. CTE и RECURSIVE

CTE позволяет дать подзапросу имя и обращаться к нему дальше. Это в первую очередь инструмент читаемости, во вторую, способ написать рекурсию.

Обычный CTE

WITH recent_subs AS (
  SELECT user_id, COUNT(*) AS cnt
  FROM homework_submissions
  WHERE created_at > now() - interval '30 days'
  GROUP BY user_id
),
active_users AS (
  SELECT u.id, u.email
  FROM users u
  WHERE u.last_seen > now() - interval '7 days'
)
SELECT a.email, r.cnt
FROM active_users a
JOIN recent_subs r ON r.user_id = a.id
ORDER BY r.cnt DESC;

Без CTE этот запрос превратился бы в матрёшку подзапросов. С CTE это три читаемые секции с понятными именами.

Materialized vs inline

До PostgreSQL 12 любой CTE был “оптимизационным барьером”: результат вычислялся целиком и складывался в memory/disk, потом снаружи использовался как готовая таблица. Это часто мешало планировщику дотянуть фильтр снаружи в подзапрос.

С PG12 CTE по умолчанию inline: планировщик подставляет тело CTE в основной запрос и оптимизирует целиком. Поведение можно контролировать вручную:

WITH heavy_calc AS MATERIALIZED (
  SELECT ... -- большой агрегат, который нужен два раза в запросе ниже
)
SELECT * FROM heavy_calc WHERE x = 1
UNION ALL
SELECT * FROM heavy_calc WHERE x = 2;

MATERIALIZED форсирует кэширование (полезно, если CTE используется два раза и считать его дважды дороже). NOT MATERIALIZED форсирует inline (полезно, если ты знаешь, что планировщик иначе ошибётся).

WITH RECURSIVE

WITH RECURSIVE, ради чего и стоит знать CTE в BatSchool. Через него выражаются деревья: разделы курса, треды комментариев, иерархия организаций.

Допустим, у нас есть таблица разделов с самоссылкой:

CREATE TABLE sections_tree (
  id bigserial PRIMARY KEY,
  title text NOT NULL,
  parent_id bigint REFERENCES sections_tree(id)
);

INSERT INTO sections_tree (title, parent_id) VALUES
  ('Databases', NULL),                   -- 1
  ('PostgreSQL', 1),                     -- 2
  ('EXPLAIN', 2),                        -- 3
  ('Sharding', 2),                       -- 4
  ('Redis', 1);                          -- 5

Хотим вытащить всё поддерево, начиная с “PostgreSQL”:

WITH RECURSIVE subtree AS (
  -- anchor: корень поддерева
  SELECT id, title, parent_id, 1 AS depth
  FROM sections_tree
  WHERE title = 'PostgreSQL'

  UNION ALL

  -- recursive step: добавляем детей того, что уже в subtree
  SELECT s.id, s.title, s.parent_id, st.depth + 1
  FROM sections_tree s
  JOIN subtree st ON s.parent_id = st.id
)
SELECT * FROM subtree ORDER BY depth, title;

Результат:

 id |   title    | parent_id | depth
----+------------+-----------+------
  2 | PostgreSQL |         1 |     1
  3 | EXPLAIN    |         2 |     2
  4 | Sharding   |         2 |     2

Recursive step выполняется итеративно: на первом проходе ищет детей PostgreSQL, на втором, детей этих детей, и так пока на очередном шаге не вернётся пустой результат.

Подводные камни рекурсии

Циклы. Если в данных образовался цикл (A -> B -> A), запрос будет работать вечно. Защита, держать список уже посещённых:

WITH RECURSIVE walk AS (
  SELECT id, parent_id, ARRAY[id] AS path
  FROM sections_tree WHERE id = 2
  UNION ALL
  SELECT s.id, s.parent_id, w.path || s.id
  FROM sections_tree s
  JOIN walk w ON s.parent_id = w.id
  WHERE NOT s.id = ANY(w.path)
)
SELECT * FROM walk;

Глубина. Каждая итерация делает отдельный запрос к таблице. На глубоких деревьях (десятки уровней) тормозит. Альтернатива на больших данных: материализованная колонка path.
SEARCH и CYCLE (PG14+): синтаксис для упорядочивания обхода (DFS/BFS) и явного детектирования циклов:

WITH RECURSIVE walk AS (...)
SEARCH BREADTH FIRST BY id SET ord
CYCLE id SET is_cycle USING path
SELECT ... FROM walk;

Где брать в BatSchool

Треды комментариев под уроком: parent_comment_id, рекурсивный обход для рендера.
Дерево разделов с подсчётом числа уроков в поддереве.
Зависимости между уроками (prerequisites), обход в ширину для построения “что разблокировано”.

Если граф уходит больше, чем на 4-5 уровней и запросы по нему частые, рекурсивный CTE заменяют материализованным closure table или ltree.

3. Оконные функции

Оконные функции считают значение по группе строк, но не схлопывают результат, в отличие от GROUP BY. Это инструмент для лидербордов, дельт, кумулятивных сумм, скользящих средних.

Базовый синтаксис

SELECT user_id,
       created_at,
       COUNT(*) OVER ()                               AS total_subs,
       COUNT(*) OVER (PARTITION BY user_id)           AS subs_by_user,
       ROW_NUMBER() OVER (PARTITION BY user_id
                          ORDER BY created_at)        AS n_in_user
FROM homework_submissions;

Элемент	Что делает
`OVER ()`	окно = вся выборка
`PARTITION BY x`	окно = строки с тем же x
`ORDER BY x`	порядок внутри окна (нужно для ранжирующих, LAG/LEAD, кумулятивных)
`ROWS BETWEEN ...`	задаёт frame явно (по индексу строки)
`RANGE BETWEEN ...`	задаёт frame по значениям ORDER BY (полезно для дат)

Ранжирующие функции

Функция	Что возвращает	Пример: значения 100, 100, 90, 80
`ROW_NUMBER()`	уникальный номер	1, 2, 3, 4
`RANK()`	ранг с пропусками	1, 1, 3, 4
`DENSE_RANK()`	ранг без пропусков	1, 1, 2, 3
`NTILE(n)`	номер квартиля или децили	1, 1, 2, 2 (для n=2)

Пример: топ-3 ментора по числу принятых ДЗ в каждом разделе:

WITH stats AS (
  SELECT l.section,
         h.reviewed_by  AS mentor,
         COUNT(*)        AS accepted
  FROM homework_submissions h
  JOIN lessons l ON l.id = h.lesson_id
  WHERE h.state = 'accepted'
  GROUP BY l.section, h.reviewed_by
)
SELECT section, mentor, accepted, rk
FROM (
  SELECT section, mentor, accepted,
         DENSE_RANK() OVER (PARTITION BY section ORDER BY accepted DESC) AS rk
  FROM stats
) ranked
WHERE rk <= 3
ORDER BY section, rk;

LAG и LEAD

LAG(x, n) берёт значение x из строки на n позиций назад внутри окна, LEAD(x, n), на n вперёд. Классика для дельт:

SELECT month,
       accepted,
       LAG(accepted) OVER (ORDER BY month)                       AS prev_month,
       accepted - LAG(accepted) OVER (ORDER BY month)            AS delta,
       100.0 * (accepted - LAG(accepted) OVER (ORDER BY month))
            / NULLIF(LAG(accepted) OVER (ORDER BY month), 0)     AS pct_change
FROM (
  SELECT date_trunc('month', created_at)::date AS month,
         COUNT(*) FILTER (WHERE state = 'accepted') AS accepted
  FROM homework_submissions
  GROUP BY 1
) m
ORDER BY month;

Получишь таблицу: месяц, число accepted в этом месяце, в прошлом, дельта в штуках и в процентах. Без оконных функций это писалось бы либо двумя JOIN, либо процедурно на стороне приложения.

Frame (ROWS BETWEEN)

Frame определяет, какие строки попадают в окно для текущей строки. По умолчанию (если есть ORDER BY) frame = RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, то есть “все строки от начала окна до текущей включительно”. Это даёт кумулятивную сумму:

SELECT month,
       accepted,
       SUM(accepted) OVER (ORDER BY month) AS cumulative
FROM monthly_stats
ORDER BY month;

Скользящее среднее за 3 месяца:

SELECT month,
       AVG(accepted) OVER (
         ORDER BY month
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rolling_3m
FROM monthly_stats;

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, всё окно (полезно для процентов от общего).

Производительность

Оконные функции в Postgres исполняются после WHERE, GROUP BY, HAVING и до ORDER BY/LIMIT. Поэтому отфильтровать по результату оконной функции напрямую нельзя:

-- НЕ работает
SELECT ... FROM stats WHERE ROW_NUMBER() OVER (...) <= 3;

-- работает: обёртка через подзапрос/CTE
SELECT * FROM (
  SELECT ..., ROW_NUMBER() OVER (...) AS rn FROM stats
) t
WHERE rn <= 3;

Запросы с оконными часто требуют сортировки внутри планировщика (Sort или индекс, совпадающий с PARTITION BY ... ORDER BY ...). Если у тебя огромный лидерборд, индекс (section, accepted DESC) сильно ускорит ранжирование.

дальше после входа

Урок целиком открыт всем, кто вошёл. Войди по своему email, чтобы читать дальше.

Вход пока по приглашению. Если хочешь учиться, напиши Vassiliy.

войти