Модуль 2. PostgreSQL: фундамент, который нельзя пропускать

Цель модуля: разобрать внутренности PostgreSQL, на которых держится всё остальное — память и WAL, MVCC и autovacuum, индексы, планы выполнения, транзакции с блокировками, пулы соединений. Собрать SQL-арсенал (оконные функции, рекурсивные CTE, JSONB, полнотекстовый поиск) и научиться читать EXPLAIN (ANALYZE, BUFFERS) как диагноз. Без этого фундамента распределённые надстройки Модуля 3 — Citus, Patroni, CDC — бессмысленны: они ускоряют и защищают конкретные запросы и конкретные транзакции, а не «базу вообще».

Перед началом: Модуль 1 (карта курса), уверенный SQL (SELECT, JOIN, GROUP BY), Docker. Инструменты: docker-compose курса (профиль pg), psql, Go 1.22+ с github.com/jackc/pgx/v5. Трудоёмкость: ~12–14 ч (теория 6 ч · практика 5 ч · квиз 1 ч).

🔥 Инцидент: база, которая распухла за месяц

OrderFlow за месяц вырос вдвое: в orders уже несколько миллионов заказов, полезных данных — около 4 ГБ. А диск на мастер-PostgreSQL почти закончился: каталог кластера занимает 22 ГБ, и это не WAL и не бэкапы. Заодно просели времена ответа: карточка заказа, которая открывалась мгновенно, теперь подвисает на секунды, и операторы это чувствуют.

Дежурный смотрит в pg_stat_user_tables: n_dead_tup в восемь раз больше n_live_tup. Autovacuum формально работает — last_autovacuum свежий, — но за сутки не освобождает почти ничего. В pg_stat_activity находится виновник: сессия приложения в состоянии idle in transaction висит часами и держит xmin-горизонт. Мёртвые версии строк копятся быстрее, чем их успевают убирать, и таблица распухает.

Первое решение — подкрутить autovacuum и добавить диска — проблему не сняло: пока висит эта транзакция, чистка почти бесполезна. Нужно было не «ускорять базу вообще», а понять, как она устроена внутри.

Модуль разбирает фундамент PostgreSQL: MVCC и снапшоты, dead tuples и bloat, autovacuum и pg_repack, индексы, транзакции с блокировками и пулы соединений. А ещё — как читать EXPLAIN (ANALYZE, BUFFERS) как диагноз, а не как набор цифр.

📖 2.1. Архитектура и память: процессы, shared_buffers, work_mem, WAL, чекпоинты

Каждый коннект — это процесс

PostgreSQL — классическая «process-per-connection» система. На каждый коннект postmaster форкает отдельный backend-процесс со своей памятью (кэши каталогов, work_mem под узлы плана). Отсюда два следствия, которые определяют урок 2.7: коннект дешёвым не бывает (тысячи соединений = тысячи процессов = переключения контекста и гигабайты памяти ещё до запросов), и мультиплексировать толпу клиентских коннектов на малое число процессов PostgreSQL не умеет в принципе — в отличие от ClickHouse с его потоковой моделью.

Память: что сколько занимает

Параметр	Выделяется	Дефолт	Типично в проде
`shared_buffers`	один раз при старте, всем процессам	128 MB	~25% RAM
`work_mem`	на каждый узел сортировки/хэша каждого запроса	4 MB	16–64 MB, осторожно
`maintenance_work_mem`	VACUUM, CREATE INDEX, ALTER TABLE	64 MB	512 MB–1 GB
`wal_buffers`	буфер записей WAL	auto (до 16 MB)	дефолт
`effective_cache_size`	ничего не выделяет — подсказка планировщику	4 GB	50–75% RAM

Главная ловушка — work_mem. Это не «память на запрос», а память на узел плана: запрос с двумя сортировками и хэш-джойном спокойно возьмёт 3 × work_mem, а с параллелизмом — ещё умножит на число воркеров. Двадцать коннектов с work_mem = 64MB и тяжёлыми отчётами — верный способ познакомиться с OOM-killer'ом.

Второй слой — двойное кэширование: страницы лежат и в shared_buffers, и в page cache ОС. Поэтому shared_buffers не поднимают до 90% RAM: ОС-кэш нужен файлам, с которыми буферный пул не работает. Планировщику об этом рассказывает effective_cache_size (текущие значения всех параметров — в pg_settings).

WAL: журнал, на котором всё держится

Write-Ahead Log — append-only журнал, в который изменения попадают до изменения страниц данных. Коммит = запись и fsync записи WAL (synchronous_commit = on); сами страницы данных сбрасываются позже — bgwriter'ом и чекпоинтером. WAL — это одновременно: crash recovery, источник streaming-репликации (Модуль 3.2) и источник CDC через logical decoding (Модуль 3.3). Одна структура — три суперсилы.

synchronous_commit = off — коммит не ждёт fsync: быстрее, но последние миллисекунды коммитов могут пропасть при краше ОС (без порчи данных). Разрешённый трюк для не-критичных вставок, не для заказов.
wal_compression = on (PG15+: zstd) — сжимает full-page images, о них ниже.

Чекпоинты: цена честности

Чекпоинт сбрасывает все грязные буферы на диск. Зачем он нужен: ограничить время восстановления (replay WAL от последнего чекпоинта) и ограничить объём WAL. Срабатывает по двум причинам:

checkpoint_timeout (дефолт 5 мин) — по времени;
max_wal_size (дефолт 1 GB — на проде почти всегда поднимают до 4–16 GB) — по объёму записанного WAL.

Тонкость — full-page writes (full_page_writes = on): первую модификацию страницы после чекпоинта PostgreSQL пишет в WAL страницу целиком (защита от torn pages — обрыва записи на полдиска). Частые чекпоинты = больше full-page images = больше WAL и IO. Параметр checkpoint_completion_target = 0.9 размазывает сброс буферов почти на весь интервал и сглаживает IO-пилу.

sql
-- PG <= 16: статистика чекпоинтов (в PG17 вынесена в pg_stat_checkpointer)
SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint
FROM pg_stat_bgwriter;

Если checkpoints_req стабильно больше checkpoints_timed — чекпоинты «выдавливаются» объёмом WAL: поднимайте max_wal_size, иначе получите периодические провалы latency.

📖 2.2. MVCC и bloat: xid, снапшоты, dead tuples, autovacuum, pg_repack

UPDATE — это INSERT

PostgreSQL не перезаписывает строки на месте. Под MVCC каждая версия кортежа несёт xmin (xid создавшей транзакции) и xmax (xid удаляющей). UPDATE вставляет новую версию и помечает xmax'ом старую; DELETE просто ставит xmax. Читатели никогда не блокируют писателей — и наоборот. Цена этого комфорта: таблица хранит мусор.

Dead tuple — версия, которую уже не увидит ни один будущий снапшот. Место от неё переиспользуется только после VACUUM и обычно не возвращается ОС. UPDATE-heavy таблицы без полноценного vacuum растут — это и есть bloat.

Снапшот и xmin horizon

Транзакция работает со снапшотом: «все xid до такого-то — закоммичены, их эффекты видны». Vacuum не может чистить версии, видимые хоть одному живому снапшоту. Долгая транзакция (забытая сессия ORM в idle in transaction, prepared transaction, зависший logical slot, реплика с hot_standby_feedback = on) тянет за собой xmin horizon — и autovacuum беспомощно ходит по таблицам, не удаляя ничего:

sql
-- Кто держит горизонт и как давно
SELECT pid, usename, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

xid wraparound: ошибка, которая стоит даунтайма

xid — 32-битный счётчик (~4 млрд значений). PostgreSQL «замораживает» старые версии (freeze), чтобы счётчик можно было крутить по кругу. Если anti-wraparound vacuum не успевает, PostgreSQL сначала агрессивно форсирует vacuum, а затем отказывается принимать записи до ручного вмешательства. autovacuum_freeze_max_age (дефолт 200 млн транзакций) — не тюнинг-игрушка, а страховка от незапланированного простоя. «Отключим autovacuum, он мешает» — самая дорогая фраза, которую можно услышать на планёрке.

autovacuum: что реально крутят

Запуск по таблице: n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples. Дефолтный scale_factor = 0.2 означает: на таблице в 100 млн строк ждём 20 млн единиц мусора. Поэтому большие hot-таблицы настраивают потаблично:

sql
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- 1% вместо 20%
  autovacuum_vacuum_cost_limit   = 2000    -- агрессивнее дефолтного IO-бюджета
);

Autovacuum работает с cost-based throttling — специально «вежливый», чтобы не задушить прод; на гигантских таблицах дефолтный бюджет поднимают. log_autovacuum_min_duration = '1s' даёт минимальную наблюдаемость: кто, сколько и как долго чистил.

sql
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / GREATEST(n_live_tup, 1), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Когда bloat уже случился, таблицу пересобирают: pg_repack умеет это онлайн, без длинной ACCESS EXCLUSIVE-блокировки (детально — Модуль 3.4). VACUUM FULL в проде под нагрузкой — антипаттерн: он держит исключительную блокировку всё время работы.

📖 2.3. Индексы: B-tree, GIN, GiST, BRIN, hash — и три козыря B-tree

B-tree — дефолт и рабочая лошадка: равенство, диапазоны, LIKE 'префикс%', сортировка. Многостолбцовый индекс работает по правилу левого префикса: (user_id, created_at) обслужит условия по user_id и по user_id + created_at, но не по created_at отдельно.

Три козыря B-tree, о которых забывают:

INCLUDE — колонки-«груз»: не участвуют в поиске, но позволяют Index Only Scan (нужен актуальный visibility map — то есть vacuum);
partial — индекс под конкретный предикат (WHERE status = 'new'): крошечный и всегда «горячий»;
expression — по выражению (lower(email), attributes->>'brand'); планировщик найдёт его только при точном совпадении выражения в запросе.

sql
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
CREATE INDEX idx_orders_cover        ON orders (user_id, created_at DESC) INCLUDE (total);
CREATE INDEX idx_orders_new          ON orders (created_at) WHERE status = 'new';
CREATE INDEX idx_users_email_ci      ON users (lower(email));
CREATE INDEX idx_products_brand      ON products ((attributes->>'brand'));

Остальные типы — под задачи, которые B-tree не решает:

Тип	Механизм	Кейс	Цена записи
GIN	инвертированный индекс: контейнмент/поиск внутри значения	`jsonb @>`, массивы, tsvector	высокая (pending list, `fastupdate`)
GiST	обобщённое дерево: диапазоны, геометрия, KNN	`tsrange`, PostGIS, exclusion constraints	средняя
BRIN	min/max по диапазонам блоков	append-only таблицы по времени; размер — килобайты	почти нулевая
hash	хэш: только `=` (WAL-логируется с PG10)	точечный поиск по очень длинным строкам	низкая

sql
CREATE INDEX idx_products_attrs ON products USING gin (attributes jsonb_path_ops);
CREATE INDEX idx_products_fts   ON products USING gin (search_vec);
CREATE INDEX idx_events_brin    ON events USING brin (created_at);

BRIN эффективен, только пока физический порядок строк коррелирует со значением колонки (append-only по времени). «Вклинивающиеся» UPDATE/DELETE убивают эту корреляцию. GIN замедляет запись: bulk-load лучше делать без GIN и строить индекс после заливки.

Индекс — не бесплатный: каждый съедает IO на INSERT/UPDATE (новая версия строки инвалидирует записи во всех индексах таблицы — ещё одна причина bloat). Раз в квартал проверяйте мёртвые:

sql
SELECT s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

Уникальные индексы не удаляем (они про инварианты, не про скорость), остальное — кандидаты на вынос после проверки на репликах: счётчик idx_scan не агрегирует статистику со standby.

📖 2.4. SQL-арсенал: окна, рекурсия, JSONB, полнотекстовый поиск

Оконные функции: top-N per group и динамика

GROUP BY схлопывает строки, окна оставляют их и добавляют агрегат соседа. Топ-товаров в каждой категории, дельта к прошлому дню, нарастающий итог — всё это без self-join'ов:

sql
-- Топ-5 товаров по выручке в каждой категории
SELECT category_id, product_id, revenue
FROM (
  SELECT category_id, product_id, SUM(amount) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY category_id
                            ORDER BY SUM(amount) DESC) AS rn
  FROM order_items
  GROUP BY category_id, product_id
) t
WHERE rn <= 5;

-- Выручка по дням и дельта к предыдущему дню
SELECT day, revenue,
       revenue - LAG(revenue) OVER (ORDER BY day) AS delta
FROM (
  SELECT date_trunc('day', created_at)::date AS day,
         SUM(total) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
) d
ORDER BY day;

Фреймы (ROWS BETWEEN ...) дают нарастающие итоги и скользящие окна — пригодятся в дашбордах Модуля 7.

В ClickHouse (Модуль 4) оконные функции тоже есть, но тяжёлую аналитику мы унесём туда; в OLTP-запросах окна — удобство, а не замена индексам: под ними всё равно тот же aggregate over rows.

Рекурсивные CTE: деревья категорий

sql
WITH RECURSIVE cat_tree AS (
  -- якорь: корни
  SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  -- рекурсивный шаг: дети
  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || c.id
  FROM categories c
  JOIN cat_tree t ON c.parent_id = t.id
  WHERE NOT c.id = ANY(t.path)          -- защита от цикла в данных
)
SELECT repeat('  ', depth - 1) || name AS tree
FROM cat_tree
ORDER BY path;

Без path-защиты кривые данные (категория — предок самой себя) уведут запрос в вечный цикл. Рекурсивные CTE — не для миллионов строк: глубже 5–10 уровней и шире десятков тысяч узлов — думайте о материализованном пути (ltree) или денормализации.

JSONB: гибкость с индексом

sql
-- Достаём значения
SELECT attributes->>'brand' AS brand,
       attributes->'specs'->>'weight' AS weight
FROM products;

-- Контеймент: есть ли подмножество (использует GIN)
SELECT id, name FROM products
WHERE attributes @> '{"color": "red", "in_stock": true}';

-- Индекс под контеймент (jsonb_path_ops компактнее, но не поддерживает ?-операторы)
CREATE INDEX idx_products_attrs ON products USING gin (attributes jsonb_path_ops);

-- Часто запрашиваемое поле — вытащить в колонку
ALTER TABLE products
  ADD COLUMN brand text GENERATED ALWAYS AS (attributes->>'brand') STORED;

Честное правило: JSONB — для «широких хвостов» атрибутов, которые нельзя спроектировать заранее. Всё, что участвует в бизнес-инвариантах (сумма заказа, статус,FK), — обычные колонки с типами и constraint'ами. База, где «всё в JSONB», — это документоориентированная СУБД с SQL поверх, со всеми минусами обоих миров.

Полнотекстовый поиск: tsvector

sql
-- Generated-колонка + GIN: индексируется автоматически
ALTER TABLE products ADD COLUMN search_vec tsvector GENERATED ALWAYS AS (
  to_tsvector('russian',
    coalesce(name, '') || ' ' || coalesce(description, ''))
) STORED;

CREATE INDEX idx_products_fts ON products USING gin (search_vec);

-- Поиск с ранжированием (websearch-синтаксис: "беспроводные наушники" -светящиеся)
SELECT id, name,
       ts_rank(search_vec, query) AS rank
FROM products,
     websearch_to_tsquery('russian', 'беспроводные наушники') AS query
WHERE search_vec @@ query
ORDER BY rank DESC
LIMIT 20;

Для LIKE '%внутри%' GIN по tsvector не поможет — морфологии там нет. Это работа для триграмм:

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);
SELECT * FROM products WHERE name LIKE '%наушники%';

tsvector — про релевантность и морфологию, trigram — про «найди подстроку». Пока поисковый движок не нужен как продукт, PostgreSQL закрывает поиск каталога на ура.

📖 2.5. EXPLAIN (ANALYZE, BUFFERS): читать план как диагноз

EXPLAIN без опций — теоретический план с оценками. Рабочий режим:

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

text
Limit (actual time=0.31..0.34 rows=10 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using idx_orders_user_created on orders
        Index Cond: (user_id = 42)
        Buffers: shared hit=5
Planning Time: 0.21 ms
Execution Time: 0.39 ms

Правила чтения:

rows оценка vs actual rows — и умножайте на loops. actual time — среднее на одну итерацию; узел с loops=1000 отработал тысячу раз. Расхождение оценок в разы — протухшая статистика: ANALYZE table.
Buffers: shared hit vs shared read. read — страницы с диска (или из OS-кэша, но мимо PostgreSQL-пула). Много read на hot-запросе — раздуйте shared_buffers или добавьте индекс поменьше.
Heap Blocks: exact/lossy. lossy в Bitmap Scan — work_mem не хватило, битмап деградировал до «проверяй всю страницу»:

text
->  Bitmap Heap Scan on order_items (actual time=120..980 rows=912341 loops=1)
      Recheck Cond: (category_id = 7)
      Rows Removed by Index Recheck: 23411
      Heap Blocks: exact=12011 lossy=4096      -- <-- work_mem мал
      Buffers: shared hit=52000 read=8800

Index Only Scan — только если visibility map актуальна (таблица недавно vacuum'илась), иначе тот же Index Scan.
Sort Method: external merge — сортировка ушла на диск, снова work_mem.
JIT. PG11+ компилирует планы дороже jit_above_cost (дефолт 100000) — помогает длинным агрегатам, вредит OLTP: на точечных запросах JIT-трейс дороже самого запроса. Для OLTP часто ставят jit = off.

Стоимость (cost=0.43..12.5) — условные единицы (seq_page_cost = 1.0, random_page_cost = 4.0; на SSD его снижают до ~1.1, иначе планировщик боится индексов). План целиком удобнее разбирать в explain.depesz.com / explain.dalibo.com — но уметь глазами найти loops, rows и lossy должен каждый.

📖 2.6. Транзакции и блокировки: изоляция на живых примерах

Уровни изоляции: что реально видит транзакция

Уровень	Неповторяющееся чтение	Фантомы	Ошибка сериализации
Read Committed (дефолт)	да	да	нет
Repeatable Read	нет	нет (в PG)	да
Serializable	нет	нет	да

Живой пример №1 — Read Committed. Снапшот берётся на каждый statement:

sql
BEGIN;
SELECT sum(total) FROM orders WHERE created_at >= now() - interval '1 day';
-- → 1 000 000.  Параллельная транзакция коммитит оплаченный заказ.
SELECT count(*) FROM orders WHERE created_at >= now() - interval '1 day';
-- → уже учитывает новый заказ: два ответа «из разных баз»
COMMIT;

Отчёт, собранный из двух запросов в одной транзакции, видит несогласованные данные. Лечение — REPEATABLE READ: снапшот фиксируется на первом statement, и оба запроса увидят одно и то же.

Живой пример №2 — Repeatable Read и конкурентный UPDATE. Две транзакции читают одну строку, обе пытаются её обновить. Вторая получит ERROR: could not serialize access due to concurrent update — и должна уметь ретраить. Serializable идёт дальше (SSI, предикатные блокировки): ловит развивающиеся циклы read-write и ругается чаще. Правило курса: дефолтный Read Committed — для OLTP-операций; отчёты — в Repeatable Read; Serializable — только там, где без него инвариант не удержать, и только с retry-циклом.

Блокировки: очередь, которая вас убьёт

Ряд-level блокировки (UPDATE, SELECT ... FOR UPDATE) — понятная история. Опаснее табличные: каждый DDL (ALTER TABLE) берёт ACCESS EXCLUSIVE, а очередь запросов строится за ним. «Быстрый ALTER на 50 мс» в час пик останавливает всю таблицу: все SELECT'ы встают в очередь за ALTER и ждут его, а ALTER ждёт окончания уже выполняющихся запросов.

sql
-- Миграции: всегда с таймаутом на захват блокировки
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN IF NOT EXISTS currency text NOT NULL DEFAULT 'RUB';

Не взял блокировку за 3 секунды — откатился и не уронил прод. Ретрай позже. statement_timeout и idle_in_transaction_session_timeout выставляйте на уровне ролей — три строчки, спасающие от половины инцидентов:

sql
ALTER ROLE app SET statement_timeout = '5s';
ALTER ROLE app SET lock_timeout = '2s';
ALTER ROLE app SET idle_in_transaction_session_timeout = '30s';

Deadlocks и как их не создавать

text
T1: UPDATE orders SET status='paid' WHERE id=1;   -- держит строку 1
T2: UPDATE orders SET status='paid' WHERE id=2;   -- держит строку 2
T1: UPDATE orders SET status='paid' WHERE id=2;   -- ждёт T2
T2: UPDATE orders SET status='paid' WHERE id=1;   -- ждёт T1 → deadlock

PostgreSQL обнаружит цикл через deadlock_timeout (1 с) и откатит одну транзакцию (SQLSTATE 40P01). Лечение — профилактика: один и тот же порядок блокировок во всём коде (сортируйте id перед апдейтом батчем) и короткие транзакции. Приложение обязано уметь ретраить 40P01 и 40001.

Advisory locks: замки без таблицы

sql
SELECT pg_advisory_xact_lock(hashtext('nightly-reindex'));
-- держится до конца транзакции, не пишется на диск, не создаёт строки

Сессионные (pg_advisory_lock) живут до явного unlock — и именно они ломаются под transaction-пулом (урок 2.7). Для работы в пуле используйте pg_advisory_xact_lock.

SKIP LOCKED: очередь задач на чистом SQL

Классика outbox/воркеров: несколько воркеров, таблица задач, никто не должен драсться за одну строку:

go
const takeBatch = `
WITH picked AS (
    SELECT id FROM outbox
    WHERE status = 'new'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT $1
)
UPDATE outbox o
SET status = 'processing', locked_at = now()
FROM picked
WHERE o.id = picked.id
RETURNING o.id, o.payload`

// pgxpool: rows.Next()/Scan как обычно — CTE атомарна в одной транзакции,
// воркеры гарантированно получают непересекающиеся батчи.
rows, err := pool.Query(ctx, takeBatch, int32(50))

SKIP LOCKED пропускает строки, уже забранные другими воркерами: никакой дополнительной координации, очередь масштабируется горизонтально. Частый антипаттерн до её открытия — SELECT ... FOR UPDATE без SKIP LOCKED: воркеры строятся друг за другом и теряют throughput. Не забудьте partial-индекс WHERE status = 'new' — очередь должна быть крошечной.

📖 2.7. Пулы соединений: connection = процесс; PgBouncer и pgx

Почему нельзя «просто поднять max_connections»

Каждый коннект — процесс с памятью и кэшами каталогов: 10 000 коннектов — это 10 000 процессов, контекст-свитчи и деградация latency у всех, включая те 40 коннектов, которые реально работают. PostgreSQL не мультиплексирует коннекты внутри — поэтому мультиплексированием занимаются снаружи, в два слоя:

Приложение: pgxpool — пул на процесс приложения.
Инфраструктура: PgBouncer/Odyssey перед базой — общий пул на всё.

Формула честности: инстансов_приложения × размер_app_пула не должен превышать то, что PgBouncer готов принять; а default_pool_size PgBouncer — примерно «(ядра × 2) + диски» под OLTP. Толпа коннектов не ускоряет БД, она её топит.

Режимы PgBouncer

Режим	Коннект занят	Что ломается	Когда брать
session	до отключения клиента	почти ничего; экономия мала	админ-доступ, слушатели LISTEN/NOTIFY
transaction	на время транзакции	SET уровня сессии, LISTEN/NOTIFY, session advisory locks, temp tables, prepared statements (без 1.21+)	дефолт для прод-OLTP
statement	на один statement	любые мультистейтмент-транзакции	экзотика: автокоммит-only

Transaction-режим — сердце экономии: сотни клиентских коннектов крутятся на десятках серверных. Но серверный коннект после каждой транзакции может уйти другому клиенту — всё «сессионное» перестаёт иметь смысл.

Prepared statements через transaction-пул

Protocol-level prepared statements (Parse/Bind) привязаны к конкретному серверному коннекту. До версии 1.21 PgBouncer в transaction-режиме их не понимал вообще — и ломал половину драйверов, которые используют prepared statements по умолчанию. С 1.21 есть max_prepared_statements: PgBouncer запоминает, на каком серверном коннекте что подготовлено, и перемапит.

ini
; pgbouncer.ini — прод-минимум
[databases]
app = host=postgres port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 40            ; ~ (ядра * 2) под OLTP
reserve_pool_size = 5
server_reset_query = DISCARD ALL
max_prepared_statements = 200     ; PgBouncer >= 1.21

pgx по умолчанию (QueryExecModeCacheStatement) как раз использует protocol-level prepared statements с кэшем описаний. Варианты:

go
cfg, err := pgx.ParseConfig(dsn)
if err != nil {
    log.Fatal(err)
}

// PgBouncer >= 1.21 c max_prepared_statements: дефолтный режим работает.
// Старый PgBouncer в transaction-режиме — simple protocol:
cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

pool, err := pgxpool.NewWithConfig(context.Background(), cfg)

QueryExecModeSimpleProtocol медленнее (параметры интерполируются в текст — следите за типами), но честно работает через любой пулер. Выбор — осознанный: версия PgBouncer + режим пула + режим pgx согласованы все три.

Про альтернативу: Odyssey (Яндекс) — многопоточный пулер на десятки тысяч клиентских коннектов, именно он стоит перед Managed PostgreSQL в Яндексе. Для большинства проектов PgBouncer проще и достаточен; Odyssey — когда коннектов реально десятки тысяч и PgBouncer упирается в однопоточность.

И помните: пулинг не спасает от долгих транзакций — он лишь делит коннекты между клиентами. Порядок всегда один: короткие транзакции (урок 2.6) → пул на приложении → PgBouncer перед базой.

💻 Практика

Схема маркетплейса + оптимизация 5 запросов по pg_stat_statements.

Шаг 1. Инфраструктура. Поднимите PostgreSQL с предзагруженным pg_stat_statements:

yaml
services:
  postgres:
    image: postgres:16
    command: >
      postgres -c shared_preload_libraries=pg_stat_statements
               -c pg_stat_statements.track=all
    environment:
      POSTGRES_PASSWORD: course
    ports: ["5432:5432"]

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Шаг 2. Схема маркетплейса.

sql
CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE categories (
  id        int PRIMARY KEY,
  parent_id int REFERENCES categories(id),
  name      text NOT NULL
);

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category_id int NOT NULL REFERENCES categories(id),
  name        text NOT NULL,
  description text,
  price       numeric(10,2) NOT NULL,
  attributes  jsonb NOT NULL DEFAULT '{}',
  search_vec  tsvector GENERATED ALWAYS AS (
                to_tsvector('russian',
                  coalesce(name,'') || ' ' || coalesce(description,''))
              ) STORED,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users(id),
  status     text NOT NULL DEFAULT 'new',  -- new|paid|shipped|cancelled
  total      numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id    bigint NOT NULL REFERENCES orders(id),
  product_id  bigint NOT NULL REFERENCES products(id),
  category_id int NOT NULL,
  qty         int NOT NULL,
  amount      numeric(12,2) NOT NULL
);

Шаг 3. Данные (~500k товаров, 3M заказов, 6M позиций):

sql
INSERT INTO users (email)
SELECT 'u' || g || '@example.com' FROM generate_series(1, 100000) g;

INSERT INTO categories (id, parent_id, name)
SELECT g, CASE WHEN g % 7 = 0 THEN NULL ELSE g - (g % 7) END, 'cat-' || g
FROM generate_series(1, 70) g;

INSERT INTO products (category_id, name, description, price, attributes)
SELECT (g % 70) + 1, 'товар-' || g, 'описание товара ' || g,
       (g % 5000)::numeric / 10 + 10,
       jsonb_build_object('brand', 'brand-' || (g % 50),
                          'color', (ARRAY['red','green','black'])[1 + g % 3],
                          'in_stock', g % 4 <> 0)
FROM generate_series(1, 500000) g;

INSERT INTO orders (user_id, status, total, created_at)
SELECT (random() * 99999)::bigint + 1,
       (ARRAY['new','paid','paid','paid','shipped','cancelled'])[1 + floor(random() * 6)::int],
       (random() * 20000)::numeric(12,2),
       now() - (random() * 180 || ' days')::interval
FROM generate_series(1, 3000000) g;

INSERT INTO order_items (order_id, product_id, category_id, qty, amount)
SELECT o.id, (o.id * 2 + k) % 500000 + 1, ((o.id * 2 + k) % 500000) % 70 + 1,
       1 + (o.id % 3), (((o.id * 2 + k) % 500000) % 5000)::numeric / 10 + 10
FROM orders o CROSS JOIN (VALUES (0), (1)) AS k(k);

Шаг 4. Пять запросов — симптом → лечение. Каждый: запустите, найдите себя в pg_stat_statements, оптимизируйте, сбросьте статистику (SELECT pg_stat_statements_reset()), убедитесь в эффекте.

Фильтр по JSONB.

sql
SELECT id, name FROM products
WHERE attributes @> '{"brand": "brand-7", "color": "red"}';

Симптом: Seq Scan по 500k строк. Лечение: GIN jsonb_path_ops. Ожидание: Bitmap Index Scan за единицы миллисекунд.

Поиск подстроки.

sql
SELECT id, name FROM products WHERE name LIKE '%наушники%';

Симптом: Seq Scan. Лечение: pg_trgm + GIN gin_trgm_ops.

Топ-5 товаров по выручке в категории (окно из урока 2.4) по order_items. Симптом: Seq Scan + Sort по 6M строк — честно тяжёлый запрос. Лечение: индекс (category_id, product_id) INCLUDE (amount) переводит агрегат в Index Only Scan; для дашборда честнее MV — но это уже история про ClickHouse (Модуль 4+).

Выручка по дням.

sql
SELECT date_trunc('day', created_at)::date AS day, SUM(total)
FROM orders
WHERE status = 'paid' AND created_at >= now() - interval '30 days'
GROUP BY 1 ORDER BY 1;

Симптом: чтение всей таблицы ради 1/6 строк. Лечение: partial-индекс CREATE INDEX ON orders (created_at) WHERE status = 'paid'; на таблице в сотни миллионов строк — BRIN по created_at (урок 2.3).

Воркер очереди.

sql
-- 200 раз в секунду из приложения:
UPDATE orders SET status = 'processing'
WHERE id IN (
  SELECT id FROM orders WHERE status = 'new'
  ORDER BY created_at LIMIT 10
  FOR UPDATE SKIP LOCKED
) RETURNING id;

Симптом: каждый опрос — Seq Scan (статусных строк почти нет, но их надо найти). Лечение: partial-индекс WHERE status = 'new' — он почти пустой и всегда в кэше.

Шаг 5. Отчёт. Топ-5 по совокупному времени — ваш личный «рейтинг врагов»:

sql
SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Артефакт практики: таблица «до/после» (время, план, индекс) по всем пяти запросам. Она пригодится на собеседовании чаще, чем сертификат.

🧩 Quiz "Проверь себя"

1. Запрос WHERE attributes @> '{"color":"red"}' по jsonb-колонке. Какой индекс построить?

2. Приложение через PgBouncer в transaction-режиме делает pg_advisory_lock(key) в одной транзакции, а снимать планирует в следующей. Что произойдёт?

3. Запрос содержит две сортировки и хэш-джойн, work_mem = 64MB. Сколько памяти может занять выполнение такого запроса?

4. synchronous_commit = on. Что гарантированно записано в момент возврата COMMIT?

5. В плане «est rows=42, actual rows=912341», Heap Blocks: lossy=4096. Что это значит?

6. Какие приёмы реально помогают при раздутии (bloat) таблицы под нагрузкой? — выберите все подходящие

7. pgx по умолчанию использует protocol-level prepared statements. PgBouncer старше 1.21 в transaction-режиме. Что будет?

8. Воркер берёт задачи из таблицы outbox; воркеров много. Как забрать пачку без гонки за одни строки?

9. Транзакция в REPEATABLE READ дважды читает отчёт; между чтениями другая транзакция коммитит новые заказы. Что увидит первая?

10. Что реально ломается или вредит в PgBouncer transaction-режиме? — выберите все подходящие

11. CPU БД 90%, top-процесс — autovacuum worker. Что происходит и что крутить?

12. В базе висит транзакция «idle in transaction» уже 3 часа. Главный эффект?

13. Два UPDATE в разных транзакциях обновляют одни и те же две строки в разном порядке. Что произойдёт?

14. Зачем в CREATE INDEX ... (user_id, created_at) INCLUDE (total) нужна колонка total?

Разбор сценария: CPU 90%, autovacuum worker в топе

Вопрос из квиза — не теоретический, а классический прод-инцидент, поэтому разберём словами. Autovacuum worker на верхушке top — почти всегда симптом накопленного долга, а не «вредный процесс»: worker честно пытается разобрать бэклог dead tuples, который кто-то накопил. Первое действие — не душить autovacuum, а найти пациента: SELECT pid, backend_type, query, query_start FROM pg_stat_activity WHERE backend_type = 'autovacuum worker'; покажет, какую таблицу чистят. Дальше два типовых сюжета: (1) кто-то держал xmin-горизонт долгой транзакцией — vacuum ходил впустую, пока транзакция не ушла; ищем её в pg_stat_activity/pg_locks, убеждаемся, что idle_in_transaction_session_timeout выставлен; (2) на гигантской таблице сработал anti-wraparound vacuum — это обязательная работа, её нельзя «отключить», можно только не доводить до форса и ускорить потабличными autovacuum_vacuum_cost_limit/scale_factor. Если bloat уже в таблице — онлайн-пересборка pg_repack (Модуль 3.4), а не VACUUM FULL в час пик.

Второй сюжет из квиза — PgBouncer и prepared statements — запомните связкой трёх версий: режим пула × версия PgBouncer × DefaultQueryExecMode pgx. Дефолты pgx рассчитаны на PgBouncer 1.21+; со старым пулером простое QueryExecModeSimpleProtocol дешевле, чем недели охоты на плавающие «prepared statement does not exist».

✅ Итоги модуля

После прохождения ты должен уметь:

Шпаргалка модуля

Симптом	Первый подозреваемый	Что смотреть
Медленный точечный запрос	план ушёл в Seq Scan	`EXPLAIN (ANALYZE, BUFFERS)`
Таблица растёт, строк не прибавляется	bloat	`n_dead_tup`, `pgstattuple`
Autovacuum жрёт CPU	долгий xmin или гигантский долг	`pg_stat_activity`, `log_autovacuum_min_duration`
Периодические IO-пилы	частые чекпоинты	`pg_stat_bgwriter` (checkpoints_req)
OOM под нагрузкой	`work_mem` × узлы плана	план запроса, `pg_settings`
Тысячи коннектов	нет серверного пулера	PgBouncer transaction mode
Deadlock'и в логах	разный порядок блокировок	код: сортировка id, ретраи 40P01
Очередь задач душит БД	опрос без индекса/`SKIP LOCKED`	partial-индекс + `SKIP LOCKED`

🔗 Мостик к Модулю 3

Фундамент залит: вы читаете планы, понимаете MVCC и не боитесь блокировок. Дальше — то, ради чего курс называется «PostgreSQL + ClickHouse в распределённых системах»: расширения и плагины. Citus (шардирование), pg_partman (партиции), Patroni (HA и живой слот CDC), Debezium, pg_stat_statements, pg_repack, pgBackRest. Каждому — роль, код, лабораторная и честный вердикт «когда брать». Всё, что отточено в этом модуле, станет линзой, через которую мы оцениваем каждый плагин: что он делает с вашими запросами, транзакциями и коннектами.

← Модуль 1. Введение: три мира данных Модуль 3. Расширения и плагины PostgreSQL в распределённых системах ⭐ →