Базы данных

В реляционной модели данные лежат в отношениях со строгой схемой, а к ним прилагаются декларативный язык запросов и транзакции. Ценны тут не таблицы, а две вещи: ты можешь задать запрос, о котором не думал на этапе проектирования схемы, и сервер сам гарантирует целостность через констрейнты и ACID. За это платишь тем, что запись тяжело масштабировать горизонтально: джойны и транзакции через сеть между узлами стоят дорого.

Зонтичный термин NoSQL на собесе лучше определить так: «NoSQL — это не отсутствие SQL, а отказ от какой-то реляционной гарантии». Redis отказался от запросов не по ключу, MongoDB — от строгой схемы и (исторически) от кросс-документных транзакций, ClickHouse — от точечного UPDATE и от OLTP, Cassandra — от сильной консистентности. Взамен каждый получил своё: обычно линейное масштабирование или на порядок большую пропускную способность в своём сценарии.

Как выбирать

• Смотри на паттерн доступа, а не на объём. 500 ГБ прекрасно живут в PostgreSQL; 10 ГБ, к которым ходят 200 тыс. раз в секунду по одному ключу, — уже не живут.
• Запросы известны заранее и всегда по ключу → key-value.
• Запросы произвольные, нужны связи и отчёты → реляционная.
• Нагрузка состоит из агрегатов по сотням миллионов строк → колоночная.
• Данные образуют граф, и нужны обходы произвольной глубины → графовая.

Ответ на «можно ли всё одним классом»

Формально да: в PostgreSQL есть jsonb с GIN-индексами (документная модель), hstore и UNLOGGED-таблицы (почти key-value), расширения TimescaleDB и Citus, полнотекстовый поиск, PostGIS. Для 90 % продуктов «просто Postgres» будет правильным дефолтом. Это сильный ответ: он экономит команде операционную сложность. Но на конкретных нагрузках специализированный движок выигрывает на порядок: Redis отдаёт ключ за 50 мкс, ClickHouse считает агрегат по миллиарду строк за секунду. Поэтому зрелый продукт почти всегда держит несколько хранилищ, это и есть polyglot persistence: Postgres как источник истины, Redis как кэш, ClickHouse под аналитику, S3 под файлы.

Key-value

Redis, etcd, DynamoDB, Aerospike. Значение для БД непрозрачно, операция одна: «дай по ключу». Отсюда O(1) доступ и тривиальное шардирование: ключ хешируется, узел находится без координации. Сценарии: кэш, сессии, счётчики, rate limiter, распределённые локи, feature-флаги. А вот «найди все сессии пользователей из Москвы» так не спросишь: вторичный индекс придётся строить руками.

Документные

MongoDB, Couchbase, Elasticsearch. Значение хранится как JSON-документ, и БД умеет заглядывать внутрь: индексировать поля, фильтровать по вложенным путям. Схема гибкая, документы одной коллекции могут отличаться. Годятся для каталогов товаров с разнородными атрибутами, event store, CMS, поисковых индексов. Платишь тем, что целостность между документами поддерживаешь сам, а гибкая схема на второй год превращается в «в этой коллекции восемь разных форматов, и каждый парсер знает про них по-своему».

Колоночные

ClickHouse, Vertica, BigQuery, Druid. Данные лежат по колонкам, значения одной колонки идут подряд. Соседние значения похожи, так что данные сильно сжимаются, а с диска читаются только нужные запросу колонки. SELECT sum(amount) FROM events по миллиарду строк прочитает одну колонку, а не миллиард строк целиком. Сценарии: аналитика, логи, метрики, витрины. Точечный UPDATE строки и OLTP с транзакциями сюда не ложатся.

Графовые

Neo4j, ArangoDB, JanusGraph. Рёбра хранятся как объекты первого класса с физическими указателями, поэтому переход «сосед соседа» стоит константу, а не джойн. Подходят для соцграфа, антифрода («связан ли этот аккаунт с заблокированным через 4 перехода»), рекомендаций, графов прав. В реляционной БД такой обход пишут рекурсивным CTE, и на глубине 5+ он взрывается.

Философия

PostgreSQL вырос из академического проекта: расширяемость (свои типы, операторы, индексные методы, языки функций), строгое следование стандарту, приоритет корректности над скоростью. MySQL создавался как быстрое хранилище для веба и годами доращивался до полноценной СУБД: подключаемые движки, изначально «прощающее» поведение при некорректных данных, огромная экосистема хостингов и репликации.

MVCC — самое важное различие

В PostgreSQL старые версии строк лежат в самой таблице. UPDATE всегда вставляет новую версию кортежа и помечает старую. Отсюда растёт целый пласт эксплуатации, которого в MySQL нет: VACUUM, autovacuum, table bloat, HOT-обновления, transaction ID wraparound. Зато откат транзакции в PG почти бесплатен: новые версии просто никто не увидит.

В InnoDB строка обновляется на месте, а старая версия уезжает в undo-лог. Поэтому чтение «в прошлое» собирает строку по цепочке undo-записей (долгие транзакции удлиняют цепочку и замедляют чтения), а откат большой транзакции стоит дорого: изменения надо физически откатывать. Undo при этом чистит отдельный purge-поток.

Физическое хранение

PostgreSQL хранит таблицу как heap: строки лежат в файле в произвольном порядке, вторичный индекс хранит ctid (номер страницы + смещение) и ведёт прямо к версии. В InnoDB таблица сама и есть clustered index, B-tree по первичному ключу: строки лежат в листьях в порядке PK, а вторичный индекс хранит значение PK. Поэтому поиск по вторичному индексу всегда спускается по дереву дважды. На практике в MySQL PK должен быть коротким и монотонным (случайный UUID раздувает каждый вторичный индекс и рвёт страницы при вставке), а в PostgreSQL размер PK на вторичные индексы не влияет.

Строгость типов и мелочи

• PG падает с ошибкой на 'abc'::int, на переполнении int и на вставке слишком длинной строки в varchar(10). MySQL исторически усекал и выдавал warning; strict mode (дефолт с 5.7) это исправил, но «мягкое» наследие встречается до сих пор.
• Уровень изоляции по умолчанию: PG — READ COMMITTED, MySQL/InnoDB — REPEATABLE READ.
• DDL в транзакции: PG умеет откатывать CREATE TABLE, MySQL — нет (DDL делает неявный commit). Это критично для миграций: в PG упавшая миграция откатывается целиком.
• Типы: в PG массивы, jsonb с GIN, диапазоны, PostGIS, свои типы.

• DDL — CREATE, ALTER, DROP, TRUNCATE, COMMENT: структура и системный каталог.
• DML — INSERT, UPDATE, DELETE, MERGE: содержимое, построчно. SELECT иногда выделяют в DQL.
• DCL — GRANT, REVOKE: права.
• TCL — BEGIN, COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION: границы транзакции.

Механика TRUNCATE

DELETE идёт по строкам: на каждую пишет запись в WAL, помечает версию мёртвой, вызывает триггеры. Индексы при этом не трогаются, а место не освобождается: мёртвые версии и записи в индексах потом уберёт VACUUM. TRUNCATE вместо этого создаёт для таблицы новый пустой файл и меняет relfilenode в pg_class, а старый файл выбрасывает целиком. Ни одной построчной операции, ни одной записи в WAL на строку. А раз меняется запись об объекте в каталоге, операция относится к определению данных.

Практические следствия

• Работает за доли секунды независимо от размера таблицы и сразу освобождает диск.
• Берёт ACCESS EXCLUSIVE, а она блокирует даже SELECT. На проде это может встать в очередь за долгим чтением и «застопорить» таблицу целиком.
• Не вызывает BEFORE/AFTER DELETE-триггеры, только TRUNCATE-триггеры. Мягкое удаление и аудит на триггерах будут молча пропущены.
• Падает с ошибкой, если на таблицу ссылаются по FK: нужен CASCADE.
• RESTART IDENTITY сбрасывает счётчики последовательностей.

Как отвечать про выбор

• Часть строк удаляют только через DELETE, причём батчами: одна транзакция на 10 млн строк держит блокировки, раздувает WAL и оставляет гору мёртвых версий.
• Очистить таблицу целиком (тестовое окружение, перезалив справочника) — TRUNCATE, но помни про ACCESS EXCLUSIVE.
• Сущность из схемы убирает DROP, в миграции с IF EXISTS.
• «Почти всё удалить, чуть-чуть оставить» выгоднее без DELETE: «создать новую таблицу с нужным подмножеством → переименовать» дешевле и не оставляет bloat.

-- Удаляем батчами: транзакции короткие, autovacuum успевает
DELETE FROM events
WHERE ctid IN (
  SELECT ctid FROM events
  WHERE created_at < NOW() - INTERVAL '90 days'
  LIMIT 10000
);
-- в цикле, пока затронуто больше 0 строк, с паузой между итерациями

PRIMARY KEY задаёт минимальный набор колонок, который однозначно идентифицирует строку, и в таблице такой ключ один. PostgreSQL под него автоматически создаёт уникальный B-tree индекс (иначе проверять уникальность на каждой вставке пришлось бы сканом). UNIQUE отличается от PK тем, что их может быть несколько и они допускают NULL.

FOREIGN KEY требует, чтобы «значение этой колонки существовало в другой таблице». Проверка идёт в обе стороны: при INSERT/UPDATE ребёнка СУБД ищет родителя, при DELETE/UPDATE родителя смотрит, не осиротеют ли дети. Технически это системные триггеры.

Почему отсутствие индекса на FK так больно

1. JOIN. Соединение от родителя к детям идёт по неиндексированной колонке: планировщик вынужден брать Hash Join с полным сканом дочерней таблицы либо Nested Loop с Seq Scan внутри, а это уже катастрофа.
2. DELETE родителя. Чтобы проверить ON DELETE RESTRICT/CASCADE, СУБД выполняет SELECT 1 FROM child WHERE fk = $1. Без индекса это полный скан дочерней таблицы на каждую удаляемую строку. Классический инцидент: «удаление одного пользователя занимает 4 минуты».
3. То же самое при UPDATE ключевой колонки родителя.

CREATE TABLE orders (
    id          bigserial PRIMARY KEY,                     -- индекс есть
    customer_id bigint NOT NULL REFERENCES customers(id)   -- индекса нет
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);  -- заводим сами

Стоит ли вообще использовать FK

Минусы у FK есть: он замедляет запись (проверка на каждую строку), мешает шардированию (родитель и ребёнок на разных узлах) и усложняет массовые загрузки. Но это единственная гарантия целостности, которую нельзя обойти кривым скриптом, забытым условием или второй версией сервиса. Поэтому в OLTP-монолите FK держат почти всегда, а выключают точечно: в шардированных таблицах, в аналитических витринах и на время массовой загрузки (ALTER TABLE ... DROP CONSTRAINT → загрузка → добавить обратно NOT VALID и потом VALIDATE).

col = NULL честно возвращает UNKNOWN для каждой строки, включая строки, где col IS NULL: неизвестное не равно неизвестному, ведь это могут быть разные неизвестные. Сравнивать нужно через IS NULL, IS NOT NULL, IS [NOT] DISTINCT FROM.

Агрегаты

• COUNT(*) считает строки, NULL не важен.
• COUNT(col) считает только строки с col IS NOT NULL. Разница между ними сразу показывает, насколько заполнена колонка.
• SUM, AVG, MIN, MAX игнорируют NULL. AVG делит на количество НЕ-NULL, а это не то же самое, что «считать NULL нулём».
• На пустом наборе SUM возвращает NULL, а не 0, и в отчётах вылезают NULL. Лечится COALESCE(SUM(x), 0).
• GROUP BY собирает все NULL в одну группу; DISTINCT и UNION тоже считают NULL равными друг другу. Это исключения из общего правила.

Неравенство и JOIN

Самая тихая ловушка: WHERE status <> 'ban' не вернёт строки, где status IS NULL. Люди ждут «все, кроме забаненных», а получают «все, у кого статус известен и не ban». Правильно писать WHERE status IS DISTINCT FROM 'ban'. В JOIN ... ON a.x = b.x строки с NULL в ключе не соединяются ни с чем: при INNER они исчезают, при LEFT дают NULL-хвост.

NOT IN — самая опасная конструкция

-- Если подзапрос вернул хотя бы один NULL, результат всегда пустой
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- раскрывается в id <> v1 AND id <> v2 AND ... AND id <> NULL
-- последний конъюнкт = UNKNOWN, а TRUE AND UNKNOWN = UNKNOWN → строка отброшена

-- NULL-безопасно и обычно быстрее (настоящий anti-join)
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

• INNER оставляет только совпавшие пары. Строк может стать и меньше (нет пары), и больше (несколько пар справа).
• LEFT OUTER — все строки левой таблицы; где пары нет, правая часть заполняется NULL.
• RIGHT OUTER работает зеркально. На практике его почти не пишут: читается тяжелее, проще переставить таблицы и написать LEFT.
• FULL OUTER — все строки обеих таблиц. Нужен для сверок «что есть здесь и нет там, и наоборот».
• CROSS даёт декартово произведение, без условия. Для генерации сеток (все дни × все товары) это законно, а вот случайный CROSS из-за забытого условия легко положит сервер.
• SELF JOIN — таблица с самой собой (сотрудник → менеджер).
• LATERAL — правая часть видит колонки левой: подзапрос выполняется для каждой строки слева. Отлично подходит для «top-N на группу».

-- 1) Anti-join через LEFT + IS NULL: соединяем и оставляем только «непришитые»
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- 2) NOT EXISTS: NULL-безопасно, планировщик строит настоящий Anti Join,
--    и он умеет останавливаться на первом совпадении
SELECT c.*
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 3) EXCEPT: работает, но требует одинаковых наборов колонок и убирает дубли
SELECT id FROM customers EXCEPT SELECT customer_id FROM orders;

-- 4) NOT IN: ловушка. Один NULL в orders.customer_id, и результат пуст
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

Nested Loop

Для каждой строки внешней выборки ищем совпадения во внутренней. Стоимость равна N × стоимость_поиска. Хорош, когда внешняя выборка маленькая (десятки строк), а по внутренней есть индекс: получается N быстрых спусков по B-tree. Катастрофа, когда внешних строк много или внутри Seq Scan. Единственный алгоритм, который умеет неэквисоединения (<, BETWEEN, функции) и обязательный для LATERAL.

Hash Join

Фаза build: по меньшей стороне строится хеш-таблица в памяти. Фаза probe: один проход по большей стороне с проверкой по хешу. Сложность O(N + M): лучший выбор для двух крупных таблиц, но только по равенству. Главный риск: если хеш не влезает в work_mem, PostgreSQL разбивает данные на batches и сбрасывает их на диск. В плане это видно как Batches: 16 вместо Batches: 1 у узла Hash, и запрос проседает в разы.

Merge Join

Обе стороны читаются в отсортированном по ключу порядке и сливаются одним проходом, как merge в сортировке слиянием. Дёшев, если сортировка достаётся даром (index scan по нужному порядку); иначе к стоимости добавляются две сортировки. Часто выигрывает на очень больших наборах, где хеш не влез бы в память.

Как это использовать на практике

1. Nested Loop с loops=200000 и Seq Scan внутри почти всегда просит индекс по колонке соединения внутренней таблицы.
2. При Hash Join с Batches > 1 подними work_mem (можно локально: SET LOCAL work_mem = '256MB') или уменьши объём данных до соединения.
3. Если rows и actual rows сильно расходятся, планировщик выбрал алгоритм по неверной оценке. Помогают ANALYZE, повышение default_statistics_target или расширенная статистика CREATE STATISTICS для коррелированных колонок.

Запрос пишется в одном порядке, а выполняется в другом: SELECT стоит первым, а выполняется почти последним. Из этой цепочки механически выводятся все правила видимости имён, зубрить их не надо.

Что следует

1. Алиас из SELECT недоступен в WHERE, GROUP BY и HAVING: на момент их выполнения SELECT ещё не отработал. В ORDER BY доступен: он идёт после. (PostgreSQL допускает алиас в GROUP BY как расширение стандарта, но опираться на это не стоит.)
2. Оконные функции считаются на шаге SELECT, поэтому их нельзя писать ни в WHERE, ни в HAVING: нужен подзапрос или CTE. Так что «топ-3 в группе» всегда пишется в два уровня.
3. WHERE дешевле HAVING. Всё, что можно отфильтровать до группировки, надо фильтровать до неё: меньше строк доедет до GROUP BY, меньше памяти на хеш-агрегат.
4. LIMIT применяется последним, после сортировки, так что «взять 10 строк» не значит «прочитать 10 строк».

-- Ошибка: cnt ещё не существует, а агрегат в WHERE запрещён
SELECT customer_id, count(*) AS cnt
FROM orders
WHERE cnt > 5
GROUP BY customer_id;

-- Правильно
SELECT customer_id, count(*) AS cnt
FROM orders
WHERE created_at >= '2024-01-01'   -- фильтр строк: дёшево, до группировки
GROUP BY customer_id
HAVING count(*) > 5                -- фильтр групп: только по агрегату
ORDER BY cnt DESC                  -- здесь алиас уже виден
LIMIT 10;

Скалярный подзапрос возвращает ровно одно значение и может стоять вместо выражения: SELECT (SELECT max(id) FROM orders). Если он вдруг вернёт больше одной строки, получишь ошибку времени выполнения, а не пустой результат; на этом ловят. Коррелированный использует колонки внешнего запроса, а значит, его нельзя вычислить один раз заранее.

-- Коррелированный: для каждой строки employees считаем среднее по её отделу
SELECT e.name, e.salary,
       (SELECT avg(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id) AS dept_avg
FROM employees e;
-- на 1 млн сотрудников подзапрос выполнится до 1 млн раз

-- Оконная функция: один проход, одна сортировка/хеш
SELECT e.name, e.salary,
       avg(e.salary) OVER (PARTITION BY e.dept_id) AS dept_avg
FROM employees e;

-- Или JOIN с предагрегатом: каждый отдел посчитан ровно один раз
SELECT e.name, e.salary, d.dept_avg
FROM employees e
JOIN (SELECT dept_id, avg(salary) AS dept_avg FROM employees GROUP BY dept_id) d
  ON d.dept_id = e.dept_id;

Когда планировщик спасает, а когда нет

PostgreSQL умеет декоррелировать часть подзапросов: EXISTS и IN обычно превращаются в Semi Join, NOT EXISTS — в Anti Join, и это работает быстро. Но декорреляция не срабатывает, если внутри есть LIMIT, ORDER BY с ограничением, агрегат по внешней колонке, volatile-функция или подзапрос стоит в списке SELECT. В плане это видно как SubPlan с большим loops, и это верный признак, что запрос пора переписать.

Что чем заменять

-- LATERAL: последний заказ каждого клиента, с попаданием в индекс
SELECT c.id, c.name, o.id AS last_order, o.created_at
FROM customers c
LEFT JOIN LATERAL (
    SELECT o.id, o.created_at
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.created_at DESC
    LIMIT 1
) o ON TRUE;
-- нужен индекс (customer_id, created_at DESC)

Зачем

• Читаемость: длинный запрос разбивается на именованные шаги вместо вложенности в пять уровней.
• Результат можно переиспользовать внутри одного запроса.
• Рекурсия: без неё иерархии и графы в SQL не обойти.
• DML-конвейеры: WITH ... AS (DELETE ... RETURNING *) INSERT ... переносит данные одним атомарным запросом.

Что изменилось в PG 12

До PostgreSQL 12 любой WITH был барьером оптимизации: он выполнялся целиком во временный набор, и предикаты внешнего запроса внутрь не проталкивались. Если снаружи стоял WHERE id = 5, а CTE выбирал миллион строк, сначала считался миллион, а потом отбрасывалось всё, кроме одной. С PG 12 CTE, который используется ровно один раз, не рекурсивен и не содержит DML и volatile-функций, по умолчанию инлайнится и ведёт себя как обычный подзапрос. Явно поведение задают через AS MATERIALIZED и AS NOT MATERIALIZED.

-- MATERIALIZED явно — для барьера оптимизации; CTE с двумя и больше ссылками PG 12+ материализует и сам
WITH heavy AS MATERIALIZED (
    SELECT ... FROM big_table WHERE ...
)
SELECT * FROM heavy a JOIN heavy b ON ... ;

-- Рекурсивный CTE: якорь UNION ALL шаг рекурсии
WITH RECURSIVE subs AS (
    SELECT id, name, manager_id, 1 AS depth, ARRAY[id] AS path
    FROM employees WHERE id = 1                  -- якорь
  UNION ALL
    SELECT e.id, e.name, e.manager_id, s.depth + 1, s.path || e.id
    FROM employees e
    JOIN subs s ON e.manager_id = s.id           -- шаг
    WHERE NOT e.id = ANY(s.path)                 -- защита от цикла в данных
      AND s.depth < 50                           -- страховка от бесконечности
)
SELECT * FROM subs ORDER BY depth, id;

От агрегата окно отличается так: GROUP BY превращает 1000 строк в 10, а окно оставляет все 1000 и каждой приписывает значение, посчитанное по её группе. PARTITION BY режет строки на окна, ORDER BY внутри OVER задаёт порядок внутри окна, ROWS/RANGE задают рамку (если есть ORDER BY, по умолчанию она идёт от начала окна до текущей строки, отсюда «нарастающий итог»).

-- Топ-3 товара по выручке внутри каждой категории
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 sales
    GROUP BY category_id, product_id          -- окно считается после агрегации
) t
WHERE rn <= 3
ORDER BY category_id, revenue DESC;

-- Интервал между заказами клиента
SELECT customer_id, created_at,
       created_at - LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS gap
FROM orders;

-- Именованное окно, если оно повторяется
SELECT customer_id, amount,
       sum(amount) OVER w AS running_total,
       ROW_NUMBER()  OVER w AS n
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at);

VIEW

При обращении планировщик подставляет тело представления в запрос и оптимизирует всё вместе (view expansion). Значит, данные всегда свежие, места не занимают, но каждая выборка стоит столько, сколько стоит исходный запрос. Вьюха инкапсулирует логику, служит слоем совместимости при переименовании колонок и разграничивает права («этой роли — только вьюху без персональных данных»). Но вьюхи поверх вьюх дают планы, которые невозможно читать, а тяжёлая вьюха тяжела на каждом обращении. Простая вьюха обновляема напрямую; сложная — только через INSTEAD OF-триггеры или CREATE RULE.

MATERIALIZED VIEW

Хранит результат физически, поддерживает свои индексы, читается как обычная таблица. Обновляется только явно:

• REFRESH MATERIALIZED VIEW mv берёт ACCESS EXCLUSIVE: на всё время обновления вьюха недоступна даже для чтения.
• REFRESH MATERIALIZED VIEW CONCURRENTLY mv не блокирует читателей, но требует уникального индекса на вьюхе и работает заметно дольше (обновление идёт через сравнение со старой версией).
• С WITH NO DATA вьюха создаётся пустой и до первого REFRESH не читается.

В PostgreSQL нет инкрементального обновления «из коробки»: REFRESH пересчитывает всё. Если данных много, обычно заводят расписание (pg_cron / джоб в приложении) или собственную «сводную таблицу», которую обновляют триггерами или батчами.

Формы своими словами

• 1НФ: в ячейке лежит одно атомарное значение, повторяющихся групп нет. Нарушение: phones = '+7999..., +7495...' одной строкой или колонки phone1, phone2, phone3. Лечится отдельной таблицей.
• 2НФ: 1НФ + каждый неключевой атрибут зависит от всего составного ключа, а не от его части. Нарушение: ключ (order_id, product_id), а в строке лежит order_date, который зависит только от order_id. Актуальна только при составном ключе.
• 3НФ: 2НФ + нет транзитивных зависимостей: неключевые атрибуты не зависят друг от друга. Нарушение: в orders лежат city_id и city_name, а имя зависит от id, не от заказа. Переименовали город, и надо обновлять миллион строк: часть обновится, а часть нет.

Мнемоника: «каждый неключевой атрибут зависит от ключа, от всего ключа и ни от чего, кроме ключа». Дальше идут НФБК, 4НФ, 5НФ; на собесе достаточно упомянуть, что они существуют и лечат более экзотические аномалии.

Что именно чинит нормализация

Не «красоту», а три аномалии: обновления (одно и то же имя города в миллионе строк обновится не везде), вставки (нельзя завести город, пока нет заказа в нём) и удаления (удалили последний заказ — потеряли факт существования города).

Когда денормализовать

1. Джойн доказанно стал узким местом: есть план и цифры, а не ощущение.
2. Дублируемые данные меняются редко или не меняются вовсе.
3. Есть механизм поддержания согласованности: триггер, материализованная вьюха, пересчёт по расписанию, событие в брокере.

• NOT NULL — самый дешёвый и самый недооценённый. Убирает целый класс багов с трёхзначной логикой. Ставь по умолчанию, ослабляй осознанно.
• UNIQUE реализуется уникальным индексом. Помни: несколько NULL допускаются (они «разные»); с PG 15 есть UNIQUE NULLS NOT DISTINCT.
• CHECK проверяет предикат на каждой строке. Именуй явно: имя приходит в текст ошибки, и по нему удобно ветвиться в коде и искать в логах. Не может ссылаться на другие таблицы и должен вести себя как IMMUTABLE: CHECK (created_at <= now()) PostgreSQL примет, но проверит только при записи, и восстановление из дампа может на нём упасть.
• EXCLUDE, ещё один недооценённый констрейнт PostgreSQL, требует: «никакие две строки не должны конфликтовать по оператору». Каноничный кейс: запрет пересечения интервалов броней, EXCLUDE USING gist (room_id WITH =, period WITH &&).

-- Частичный уникальный индекс: email уникален только среди неудалённых
CREATE UNIQUE INDEX users_email_uniq ON users (LOWER(email)) WHERE deleted_at IS NULL;

-- Именованный CHECK: имя попадёт в текст ошибки
ALTER TABLE accounts ADD CONSTRAINT accounts_balance_nonneg CHECK (balance >= 0);

-- NOT VALID: добавить мгновенно, старые строки проверить потом без тяжёлой блокировки
ALTER TABLE orders ADD CONSTRAINT orders_total_pos CHECK (total > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_pos;

-- Отложенная проверка: до COMMIT можно временно нарушить (циклические ссылки)
ALTER TABLE a ADD CONSTRAINT a_b_fk FOREIGN KEY (b_id) REFERENCES b(id)
  DEFERRABLE INITIALLY DEFERRED;

Перевод 100 рублей со счёта 1 на счёт 2 состоит из трёх операций: списание, зачисление, запись в журнал переводов.

A — Atomicity (атомарность)

Применяются либо все три операции, либо ни одной. Без атомарности сценарий «сервер упал между UPDATEами» оставляет деньги списанными, но не зачисленными — они исчезают из системы. Механизм: WAL плюс правило, что изменения становятся видимыми только после записи о коммите. Откат в PostgreSQL почти бесплатен: новые версии строк просто никто никогда не увидит, потому что их xmin принадлежит откаченной транзакции.

C — Consistency (согласованность)

Транзакция переводит базу из одного корректного состояния в другое: CHECK (balance >= 0) не нарушен, FK на счета целы, сумма денег в системе не изменилась. Без неё баланс уходит в минус. Но есть оговорка: СУБД проверяет только объявленные ей инварианты. Инвариант «сумма денег постоянна» ей неизвестен, и если разработчик забыл второй UPDATE, транзакция будет атомарной, изолированной, долговечной и при этом бизнес-несогласованной. Поэтому C часто называют «половина ответственности приложения».

I — Isolation (изоляция)

Параллельный отчёт «сколько всего денег в банке» не должен увидеть момент, когда со счёта 1 уже списали, а на счёт 2 ещё не зачислили, иначе он покажет на 100 рублей меньше. Без изоляции получаем весь набор аномалий: грязное чтение, потерянные обновления, фантомы. В PostgreSQL для чтения работают MVCC-снапшоты, для записи блокировки строк и SSI. Из всех букв настраивают только изоляцию: у неё четыре уровня, и это сознательный размен строгости на пропускную способность.

D — Durability (долговечность)

После того как клиент получил «перевод выполнен», выключение питания не должно его отменить. Для этого перед подтверждением коммита запись WAL сбрасывается на диск через fsync. Отсюда практические ручки: synchronous_commit = off даёт кратный прирост скорости записи ценой потери последних миллисекунд транзакций при аварии (база при этом остаётся согласованной — теряются целые транзакции, а не половинки), а synchronous_standby_names расширяет durability до «переживёт потерю всего сервера».

1. Грязное чтение (dirty read)

T1 списала 100 рублей и ещё не закоммитила. T2 читает баланс и видит списание. T1 делает ROLLBACK. T2 приняла решение по данным, которых никогда не существовало. Запрещено начиная с Read Committed; в PostgreSQL невозможно вообще ни на каком уровне.

2. Неповторяющееся чтение (non-repeatable read)

T1 читает баланс — 500. T2 обновляет его на 400 и коммитит. T1 читает тот же баланс ещё раз — 400. Один и тот же запрос в одной транзакции дал разные ответы. Ломает любой отчёт из нескольких запросов: итоговая строка не сходится с детализацией. Запрещено с Repeatable Read.

3. Фантомное чтение (phantom read)

T1 считает count(*) WHERE dept = 5 — 10. T2 вставляет новую строку с dept = 5 и коммитит. T1 повторяет запрос — 11. Отличие от предыдущей аномалии: изменилась не строка, а множество строк, попадающих под предикат. По стандарту разрешено на Repeatable Read, но в PostgreSQL на Repeatable Read фантомов нет — снапшот берётся один на транзакцию. В MySQL/InnoDB для этого используются gap locks.

4. Потерянное обновление (lost update)

T1 читает qty = 100, T2 читает qty = 100, T1 пишет 150 и коммитит, T2 пишет 130 и коммитит. Прибавка T1 бесследно исчезла, при этом обе транзакции завершились успешно и ошибок не было. Это самая частая аномалия в реальном коде, потому что паттерн «прочитал в приложении → посчитал → записал» пишут все. Лечится не уровнем изоляции, а одним из трёх: атомарным UPDATE x = x + n, SELECT FOR UPDATE или version-колонкой.

5. Аномалия записи (write skew)

Инвариант: на дежурстве должен остаться хотя бы один врач; сейчас дежурят двое. T1 читает «дежурных 2», решает, что можно уйти, и снимает с дежурства Аню. T2 параллельно читает «дежурных 2» и снимает Борю. Обе транзакции изменили разные строки, поэтому конфликта записи нет и обе коммитятся. Дежурных ноль — инвариант нарушен. Snapshot isolation (Repeatable Read) это не ловит принципиально; нужен SERIALIZABLE с SSI, либо явная блокировка того, что читали, либо материализация инварианта в одну строку-счётчик.

В стандарте классическая таблица содержит только первые три колонки: потерянное обновление и write skew в ANSI SQL-92 вообще не упомянуты, их добавили позже в статье «A Critique of ANSI SQL Isolation Levels» (Berenson et al., 1995). Там же появились термины snapshot isolation и write skew, поэтому «уровень стандарта» и «уровень PostgreSQL» не одно и то же.

Как это выглядит в коде

-- на транзакцию
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- или уже внутри, до первого запроса
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- посмотреть текущий
SHOW transaction_isolation;

tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelSerializable,
    ReadOnly:  true,   // read-only на Serializable дешевле: SSI почти не отслеживает такие транзакции
})

Почему READ UNCOMMITTED = READ COMMITTED

Грязное чтение возможно только там, где читатель может увидеть данные, изменённые незавершённой транзакцией. В PostgreSQL каждая версия строки помечена xmin, номером создавшей её транзакции, и правило видимости требует, чтобы эта транзакция была закоммичена. Незакоммиченная версия невидима технически, показать её просто нечем. Поэтому SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED принимается без ошибки (для совместимости со стандартом), но ведёт себя как Read Committed. Уровней у PostgreSQL фактически три.

Почему на REPEATABLE READ нет фантомов

RC и RR в PostgreSQL различаются моментом, когда берётся снапшот. На Read Committed снапшот берётся заново на каждый оператор: следующий SELECT увидит всё, что закоммитили соседи. На Repeatable Read снапшот берётся один раз, при первом операторе транзакции, и живёт до конца. У строки, вставленной и закоммиченной после этого момента, xmin больше границы снапшота, и она невидима, сколько раз запрос ни повторяй. Фантом не может появиться просто потому, что база для этой транзакции «застыла во времени». Стандарт фантомы на RR разрешает, PostgreSQL их запрещает: реализация здесь строже спецификации.

Что взамен появляется на RR

Запись. Если транзакция на Repeatable Read пытается обновить строку, изменённую и закоммиченную другой транзакцией после её снапшота, PostgreSQL не может корректно применить изменение (её снапшот описывает устаревший мир) и откатывает транзакцию с ERROR: could not serialize access due to concurrent update, SQLSTATE 40001. На Read Committed в этой же ситуации сработал бы EvalPlanQual: перечитал бы строку и заново проверил WHERE. Поэтому переход на RR всегда идёт в комплекте с ретраями.

SERIALIZABLE = SSI

С версии 9.1 PostgreSQL реализует настоящую сериализуемость через Serializable Snapshot Isolation: поверх обычных снапшотов движок отслеживает предикатные блокировки (SIREAD), то есть «что эта транзакция прочитала», и ищет в графе зависимостей опасную структуру из двух подряд идущих rw-конфликтов. Найдя её, откатывает одну из участниц с could not serialize access due to read/write dependencies among transactions. Читатели по-прежнему никого не блокируют: платишь не ожиданием, а вероятностью отката и памятью под отслеживание предикатов (max_pred_locks_per_transaction).

В PostgreSQL по умолчанию стоит READ COMMITTED: снапшот на каждый оператор. Компромисс «достаточно строго для большинства задач, минимум откатов». Глобально его почти никогда не меняют, уровень поднимают точечно на конкретную транзакцию.

В MySQL/InnoDB по умолчанию REPEATABLE READ, и дело не в заботе о строгости. Исторически MySQL использовал statement-based репликацию: на реплику передавался текст запроса, который там выполнялся заново. При Read Committed порядок применения операторов мог дать на реплике другой результат, поэтому потребовался более строгий уровень плюс gap locks — блокировки «промежутков» между значениями индекса, которые не дают вставить строку в диапазон, прочитанный чужой транзакцией. Gap locks и закрывают фантомы в InnoDB, и они же порождают знаменитые дедлоки MySQL на, казалось бы, невинных вставках.

Откуда берётся

• Repeatable Read: транзакция пытается обновить строку, изменённую и закоммиченную после её снапшота, и получает could not serialize access due to concurrent update.
• Serializable: SSI находит в графе зависимостей опасную структуру и отвечает could not serialize access due to read/write dependencies among transactions.
• Рядом стоит 40P01 deadlock_detected — причина другая, обработка та же.

Откат при этом полный: база осталась согласованной, ничего наполовину не применилось, повтор той же транзакции корректен по определению. Поэтому SSI и работает: движок обещает «либо результат будет как при последовательном выполнении, либо я откачу». За вторую половину обещания отвечает приложение.

Как это выглядит правильно

// Оборачиваем всю бизнес-операцию, а не отдельный запрос
err := WithRetry(ctx, pool, func(tx pgx.Tx) error {
    var seats int
    if err := tx.QueryRow(ctx,
        `SELECT count(*) FROM bookings WHERE flight_id = $1`, flightID).Scan(&seats); err != nil {
        return err
    }
    if seats >= capacity {
        return ErrNoSeats           // бизнес-ошибка, не ретраим
    }
    _, err := tx.Exec(ctx,
        `INSERT INTO bookings(flight_id, user_id) VALUES ($1, $2)`, flightID, userID)
    return err
})
// Письмо отправляем только здесь, после успешного коммита:
// внутри fn оно ушло бы столько раз, сколько было попыток.

Что должно быть в реализации ретрая

1. Повторять всю транзакцию с нуля — старый снапшот невалиден.
2. Экспоненциальная пауза с джиттером. Без разброса конкуренты синхронно повторят конфликт и снова столкнутся.
3. Ограничение числа попыток и осмысленная финальная ошибка.
4. Никаких внешних побочных эффектов внутри: ни писем, ни платежей, ни сообщений в Kafka. Только чистая работа с БД; всё остальное после коммита или через outbox.
5. Различать ретраибельные и нет. 23505 unique_violation или 23514 check_violation повторять бессмысленно, они просто повторятся.
6. Метрика на число ретраев. Растёт доля конфликтов, значит, горячая точка разрастается и пора менять схему (шардировать счётчик, перейти на атомарный UPDATE).

FOR UPDATE и его слабые формы

SELECT ... FOR UPDATE берёт эксклюзивную блокировку на каждую возвращённую строку до конца транзакции. Другие транзакции не смогут её обновить, удалить или тоже заблокировать; обычные SELECT при этом работают свободно — в PostgreSQL читатели никогда не ждут писателей. Есть более слабые режимы: FOR NO KEY UPDATE (берётся неявно обычным UPDATE неключевых колонок), FOR SHARE («пусть читают, но менять нельзя») и FOR KEY SHARE (запрещает только удаление и смену ключа; его и берёт проверка внешнего ключа).

SKIP LOCKED — очередь на таблице

UPDATE tasks SET status = 'processing', locked_at = now()
WHERE id IN (
    SELECT id FROM tasks
    WHERE status = 'new' AND run_after <= now()
    ORDER BY priority DESC, id
    LIMIT 10
    FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

Без SKIP LOCKED десять воркеров выстроились бы в очередь за одной и той же первой строкой — параллелизм нулевой. С ним каждый пропускает занятое и забирает свою пачку. Это стандартный способ сделать очередь без брокера: плюс — задачи ставятся транзакционно вместе с бизнес-данными (никаких «в базе есть, в очереди нет»), минус — нагрузка на БД и bloat от постоянных UPDATE статуса, поэтому на десятках тысяч задач в секунду нужен настоящий брокер.

NOWAIT

FOR UPDATE NOWAIT вместо ожидания сразу отдаёт ошибку 55P03 lock_not_available. Нужен там, где ждать хуже, чем отказать: интерактивное редактирование документа («сейчас редактирует другой пользователь»), административная операция, которую можно повторить. Родственная настройка — SET LOCAL lock_timeout = '2s': ждать, но не бесконечно.

Пессимистичная

Блокировка берётся до начала работы и держится до конца транзакции. Плюс: операция гарантированно проходит с первого раза, логика линейная, ретраи не нужны. Минусы: конкуренты стоят в очереди (пропускная способность по горячей строке падает до «одна транзакция за раз»), растёт риск дедлоков, и она принципиально не работает через «длинный» бизнес-процесс — блокировку нельзя держать, пока пользователь заполняет форму.

Оптимистичная через version-колонку

CREATE TABLE documents (
    id      bigint PRIMARY KEY,
    body    text   NOT NULL,
    version int    NOT NULL DEFAULT 1
);

-- 1) читаем без блокировки, отдаём пользователю body и version = 7
-- 2) через 5 минут он присылает изменения; пишем условно:
UPDATE documents
   SET body = $1, version = version + 1
 WHERE id = $2 AND version = $3;   -- $3 = 7

res, err := db.ExecContext(ctx,
    `UPDATE documents SET body=$1, version=version+1 WHERE id=$2 AND version=$3`,
    body, id, version)
if err != nil {
    return err
}
n, _ := res.RowsAffected()
if n == 0 {
    // Ноль строк = либо документа нет, либо version изменилась.
    // Это не ошибка БД, конфликт разбирает бизнес-логика:
    // перечитать, показать пользователю расхождение или смержить.
    return ErrConcurrentModification
}

Вместо целочисленной версии можно использовать updated_at (хуже: разрешение времени и переводы часов) или хеш содержимого (годится для HTTP ETag / If-Match: по сути та же оптимистичная блокировка, только вынесенная в протокол).

Табличные

Берутся автоматически под каждую команду. Запомни минимум: SELECT берёт ACCESS SHARE и конфликтует только с ACCESS EXCLUSIVE; INSERT/UPDATE/DELETE берут ROW EXCLUSIVE и не мешают друг другу на уровне таблицы (конфликты разрешаются построчно); VACUUM, ANALYZE и CREATE INDEX CONCURRENTLY берут SHARE UPDATE EXCLUSIVE и не мешают DML; ACCESS EXCLUSIVE (DROP, TRUNCATE, большинство ALTER TABLE, VACUUM FULL) блокирует вообще всё, включая SELECT.

Строчные

Их четыре: FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, FOR UPDATE. Хранятся они не в общей памяти, а прямо в заголовке версии кортежа (плюс структура multixact, когда строку держат несколько транзакций). Выходит, число строчных блокировок не ограничено: можно заблокировать миллион строк и не бояться исчерпать память блокировок. А вот табличных блокировок конечное число (max_locks_per_transaction).

Advisory

pg_advisory_lock(key) и pg_try_advisory_xact_lock(key) вешают замок по произвольному числу, ни с какой строкой не связанный. Удобен как распределённый мьютекс: «только один инстанс сервиса выполняет эту фоновую задачу». Транзакционная форма (_xact_) снимается автоматически при завершении транзакции, её и используй; обычная требует явного unlock и легко теряется.

-- Кто кого блокирует прямо сейчас: первый запрос при инциденте
SELECT pid, now() - xact_start AS tx_age, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by, left(query, 90)
FROM   pg_stat_activity
WHERE  backend_type = 'client backend' AND state <> 'idle'
ORDER  BY tx_age DESC;

-- Что именно удерживается и в каком режиме
SELECT l.pid, l.locktype, l.mode, l.granted, c.relname
FROM   pg_locks l LEFT JOIN pg_class c ON c.oid = l.relation
ORDER  BY l.granted, l.pid;

SELECT pg_cancel_backend(12345);     -- мягко: отменить запрос
SELECT pg_terminate_backend(12345);  -- жёстко: закрыть соединение

Как возникает

T1 обновляет строку A и получает на неё блокировку, затем хочет строку B. T2 в это же время обновила B и хочет A. Каждая ждёт ресурс, который держит другая, и получается цикл. Классический источник: перевод денег «со счёта X на счёт Y» и одновременно «с Y на X»; или два процесса, обновляющие набор строк в разном порядке (например, потому что один шёл ORDER BY id, а второй — ORDER BY created_at).

Как разрешает PostgreSQL

Транзакция, прождавшая блокировку дольше deadlock_timeout (по умолчанию 1 с), запускает детектор: тот строит граф «кто кого ждёт» и ищет в нём цикл. Если цикл нашёлся, одна из транзакций (обычно та, что запустила проверку) откатывается с ERROR: deadlock detected, SQLSTATE 40P01, а в лог пишется, какие процессы и запросы участвовали. Остальные продолжают работу. Проверка запускается по таймауту, а не при каждом ожидании, чтобы не жечь процессор — обычное ожидание блокировки ещё не дедлок.

Как проектировать

1. Единый порядок захвата. Главное правило: если операция трогает несколько строк, блокируй их в детерминированном порядке (по возрастанию первичного ключа), независимо от бизнес-направления операции.
2. Захватывать всё сразу одним SELECT ... WHERE id IN (...) ORDER BY id FOR UPDATE, а не последовательно по одной строке.
3. Короткие транзакции и никаких сетевых вызовов внутри — окно пересечения меньше.
4. Осторожно с ON CONFLICT и FK: параллельные вставки в таблицу с уникальным индексом и разным порядком значений тоже дают дедлоки.
5. Всё равно ретраить. Полностью исключить дедлоки в системе со сложными транзакциями нельзя, поэтому обработка 40P01 обязательна.

-- Плохо: порядок зависит от направления перевода
UPDATE accounts SET balance = balance - 100 WHERE id = $from;
UPDATE accounts SET balance = balance + 100 WHERE id = $to;

-- Хорошо: сначала захватываем оба счёта в фиксированном порядке
SELECT id FROM accounts WHERE id IN ($from, $to) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = $from;
UPDATE accounts SET balance = balance + 100 WHERE id = $to;

Пять последствий

1. Блокировка очистки. Autovacuum не имеет права удалить мёртвую версию строки, если она может быть нужна самому старому активному снапшоту. Одна транзакция, висящая часами, останавливает уборку по всей базе. Отсюда table bloat: таблица на 10 ГБ данных занимает 60 ГБ, индексы распухают, всё сканируется медленнее, кэш забит мусором.
2. Удержание блокировок. Все блокировки живут до конца транзакции. Если в начале был ALTER или FOR UPDATE, соседи будут ждать до самого COMMIT — а из-за честной FIFO-очереди за ними встанут и невинные SELECT.
3. Проблемы с репликами. При hot_standby_feedback = on долгий запрос на реплике удерживает xmin на мастере и мешает VACUUM уже там. При выключенном фидбеке запрос на реплике убьют с canceling statement due to conflict with recovery. Выбор между двумя неприятностями, и об этом полезно сказать вслух.
4. Transaction ID wraparound. Счётчик транзакций 32-битный; чтобы старые данные не «стали будущими», VACUUM замораживает старые кортежи. Самая старая транзакция задаёт горизонт заморозки, и если она живёт сутками, база приближается к аварийному режиму, когда PostgreSQL перестаёт принимать новые транзакции.
5. Больше конфликтов. Чем шире окно, тем выше вероятность дедлока и ошибки сериализации.

Особый случай: idle in transaction

Чаще всего транзакцию затягивает не тяжёлый запрос, а BEGIN, после которого приложение ушло делать HTTP-запрос, писать в Kafka или просто забыло закоммитить из-за пропущенного defer tx.Rollback(). Транзакция ничего не выполняет, но вредит точно так же. Это и есть главный аргумент против «сетевых вызовов внутри транзакции».

-- Диагностика
SELECT pid, state, now() - xact_start AS tx_age, now() - state_change AS in_state,
       left(query, 100)
FROM   pg_stat_activity
WHERE  xact_start IS NOT NULL
ORDER  BY xact_start
LIMIT  20;

-- Профилактика. SET — на сессию; на роль — ALTER ROLE … SET, на сервер — ALTER SYSTEM
SET idle_in_transaction_session_timeout = '60s';
SET statement_timeout = '30s';
SET lock_timeout = '3s';

Служебные поля версии

У каждого кортежа в заголовке есть xmin (xid транзакции, создавшей версию), xmax (xid транзакции, удалившей или заменившей её; 0, если версия актуальна), ctid (физический адрес: страница, смещение), плюс cmin/ cmax для видимости внутри собственной транзакции. Их можно прямо посмотреть: SELECT xmin, xmax, ctid, * FROM accounts; — отличная демонстрация на собесе.

Снапшот

Снапшот состоит из трёх частей: xmin (все транзакции старше уже завершены), xmax (всё новее ещё не начиналось) и xip, список активных транзакций на момент снимка. Версия видна, если её создатель закоммичен и не входит в «активные или будущие», и при этом её xmax либо нулевой, либо принадлежит транзакции, которая для нас ещё не завершилась.

Почему из этого следует отсутствие грязного чтения

У незакоммиченной версии xmin принадлежит активной транзакции — правило видимости отбрасывает её у всех, кроме самой этой транзакции. Показать грязные данные в PostgreSQL физически нечем, поэтому READ UNCOMMITTED совпадает с READ COMMITTED.

Чем платим

• Мёртвые версии копятся в таблице → нужен VACUUM, возможен bloat.
• Раздувание индексов: каждая новая версия строки (кроме HOT) добавляет записи во все индексы.
• count(*) не может быть мгновенным: видимость строки зависит от снапшота, поэтому приходится сканировать (спасает частично index-only scan через visibility map).
• Долгая транзакция вредит всей базе, удерживая горизонт очистки.

Пошагово

1. Движок находит текущую версию строки и берёт на неё блокировку.
2. Формирует новую версию целиком — со всеми колонками, даже неизменёнными.
3. Пишет её в ту же страницу, если есть место, иначе в другую.
4. Старой версии проставляет xmax = мой xid и связывает её ctid с новой.
5. Добавляет записи во все индексы, если это не HOT-обновление.
6. Пишет всё это в WAL (а после контрольной точки — ещё и полную страницу целиком).

Отсюда неочевидные следствия: обновление одного boolean в строке с jsonb на 500 КБ создаёт новую версию строки (правда, неизменённые TOAST-чанки переиспользуются); таблица, где 1000 раз в секунду инкрементируется счётчик, растёт со скоростью 1000 мёртвых версий в секунду; ROLLBACK ничего не откатывает физически — записанные версии просто остаются мусором.

HOT (Heap-Only Tuple)

Если новая версия помещается в ту же страницу и ни одна проиндексированная колонка не изменилась, PostgreSQL не трогает индексы вообще: индексная запись продолжает указывать на старую версию, а внутри страницы выстраивается HOT-цепочка, по которой движок доходит до актуальной. Так получается меньше записи в WAL, индексы не пухнут, а очистка цепочки может произойти прямо при обращении к странице, без полноценного VACUUM.

Что делает VACUUM

• Помечает место мёртвых версий свободным для повторного использования (файл при этом не уменьшается).
• Удаляет соответствующие записи из всех индексов.
• Обновляет visibility map, битовую карту «в этой странице все версии видны всем», без которой index-only scan теряет смысл: ему приходится ходить в таблицу.
• Замораживает старые кортежи, отодвигая угрозу transaction ID wraparound.
• Попутно (через autovacuum) запускается ANALYZE, обновляющий статистику.

Обычный VACUUM берёт SHARE UPDATE EXCLUSIVE и не мешает SELECT/INSERT/UPDATE. VACUUM FULL устроен совсем иначе: переписывает таблицу в новый файл, возвращает место ОС, но берёт ACCESS EXCLUSIVE, то есть таблица недоступна полностью. На проде вместо него используют pg_repack.

Почему возникает bloat

1. Долгая транзакция или idle in transaction удерживает горизонт, и VACUUM не имеет права удалять новые мёртвые версии.
2. Забытый replication slot держит xmin бессрочно. Причина самая коварная: реплики уже может не быть, а слот остался.
3. Дефолтный autovacuum_vacuum_scale_factor = 0.2 на большой таблице: уборка начнётся, когда мёртвых накопится 20 %.
4. Autovacuum душат настройками (cost_delay, мало воркеров) — он не успевает за нагрузкой.
5. Массовые UPDATE/DELETE одной транзакцией.

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC LIMIT 20;

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);  -- чистить чаще
ALTER TABLE orders SET (fillfactor = 85);                        -- больше HOT

Правило упреждающей записи

Любое изменение сначала описывается записью в WAL, и только потом может быть сброшено в файл данных. Страницы меняются в shared_buffers и остаются «грязными»; COMMIT ждёт fsync журнала, а не страниц. Если сервер упадёт, при старте PostgreSQL проиграет WAL с последней контрольной точки и восстановит все закоммиченные изменения.

Почему это ещё и быстро

В WAL пишут последовательно, в один файл. Изменённые страницы сбрасываются случайной записью по всему файлу таблицы. Разница на порядок даже на SSD и на два порядка на HDD. Плюс несколько транзакций объединяют свои fsync в один (group commit). Так что WAL отвечает и за надёжность, и за производительность записи.

Checkpoint

Контрольная точка сбрасывает все грязные страницы на диск и записывает в журнал отметку «до этого места всё гарантированно в файлах данных». Она определяет, откуда начнётся восстановление. Настраивается через checkpoint_timeout и max_wal_size: чем реже контрольные точки, тем меньше нагрузка на диск в обычном режиме, но тем дольше recovery после аварии и больше объём WAL. checkpoint_completion_target размазывает запись во времени, чтобы не было всплеска I/O. Отдельно запомни full_page_writes: первая модификация страницы после контрольной точки пишет в WAL всю страницу. Так база защищается от частично записанных страниц, и отсюда же пики объёма WAL сразу после checkpoint.

Связь с репликацией и бэкапами

Физическая репликация буквально передаёт поток WAL на реплику, где он проигрывается. Логическая декодирует тот же WAL в логические изменения строк (wal_level = logical). PITR строится на базовой копии и архиве WAL и позволяет восстановиться на любой момент времени. Один механизм закрывает три задачи, и это хороший способ показать системное понимание.

Строка в PostgreSQL не может пересекать границу страницы, а страница — 8 КБ. Как только версия строки перестаёт влезать примерно в 2 КБ, включается TOAST: движок берёт самые крупные колонки переменной длины и сначала пытается их сжать (по умолчанию pglz, с PG 14 доступен lz4). Если этого мало — выносит значение в таблицу pg_toast.pg_toast_NNNN, нарезая на чанки примерно по 2 КБ, а в самой строке оставляет указатель. Одно TOAST-значение ограничено 1 ГБ.

Почему это важно знать на практике

• Ленивое чтение. SELECT id, status по таблице с гигантским jsonb не читает TOAST вообще, а SELECT * вытащит и распакует всё. Это одна из самых конкретных причин не писать «звёздочку».
• Место. pg_relation_size не включает TOAST, а pg_total_relation_size включает. В расхождении этих цифр обычно и кроется ответ на «куда делось место».
• Обновления. Новая версия строки создаётся всегда, но неизменённые TOAST-чанки переиспользуются, а не копируются.
• Стратегии хранения задаются на колонку: EXTENDED (сжимать и выносить, дефолт), EXTERNAL (только выносить — быстрее substring()), MAIN (сжимать, выносить в крайнем случае), PLAIN (ничего).

pg_stat_activity — здесь и сейчас

SELECT pid, usename, state,
       now() - xact_start  AS tx_age,
       now() - query_start AS query_age,
       wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 90)
FROM   pg_stat_activity
WHERE  backend_type = 'client backend' AND state <> 'idle'
ORDER  BY tx_age DESC NULLS LAST;

Что смотреть: state = 'active' с большим query_age выдаёт тормозящие запросы, state = 'idle in transaction' показывает забытые транзакции, которые блокируют VACUUM, а непустой blocked_by означает цепочки блокировок; wait_event_type подскажет природу ожидания (Lock, IO, LWLock, Client). На аварийный случай есть pg_cancel_backend (отменить запрос) и pg_terminate_backend (убить соединение).

pg_stat_statements — накопленная картина

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows / GREATEST(calls, 1)          AS rows_per_call,
       shared_blks_read, shared_blks_hit,
       left(query, 100)
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

Расширение требует shared_preload_libraries = 'pg_stat_statements' и рестарта. Оно нормализует запросы: константы заменяются на $1, поэтому миллион однотипных запросов схлопывается в одну строку с большим calls.

Как читать

• Сортировать по total_exec_time, а не по среднему: запрос на 8 мс, вызываемый 40 тыс. раз в минуту, вреднее запроса на 3 секунды раз в час.
• Огромный calls при крошечном mean — почти наверняка N+1 из ORM.
• Большой rows_per_call значит, что запрос тащит в приложение лишнее.
• Если shared_blks_read сравним с hit, данные не помещаются в кэш и чтение идёт с диска; повод посмотреть индексы или shared_buffers.
• Большой stddev_exec_time — план нестабилен: разные параметры дают разные планы.
• pg_stat_statements_reset() перед экспериментом, чтобы видеть эффект изменения чисто.

Что физически лежит в индексе

В листьях B-tree лежат пары «ключ → ctid», где ctid хранит пару (номер страницы, номер слота внутри страницы), то есть физический адрес версии строки. Внутренние узлы содержат только разделители-ключи и ссылки на дочерние страницы. Вдобавок листья связаны в двусвязный список, поэтому индекс умеет отдавать данные уже отсортированными и обслуживать диапазонные условия BETWEEN, >, < одним проходом вправо по листьям.

Арифметика, которую хотят услышать

Страница 8 КБ, ключ bigint + ctid ≈ 16 байт с накладными расходами, и на страницу влезает порядка 400 записей. Три уровня дают 400³ ≈ 64 млн ключей, четыре — 25 млрд. Значит, чтобы найти конкретное значение в таблице на 100 млн строк, хватит 4 чтений страниц, причём верхние уровни почти всегда уже в shared_buffers, то есть реальных обращений к диску обычно 1–2. Против 20 ГБ последовательного чтения при Seq Scan.

Почему бинарное дерево проигрывает

• Ветвление 2 против ~400, отсюда высота 27 против 4.
• Узлы бинарного дерева разбросаны по произвольным местам, отсюда случайные чтения, самые дорогие для диска.
• B-tree сбалансировано по построению: все листья на одном уровне, худший случай равен среднему. У обычного BST при вставке возрастающих ключей (а это типичный id) дерево вырождается в список.

Что происходит при вставке

Значение попадает в нужный лист; если лист переполнен, случается split: страница делится пополам, разделитель поднимается в родителя, при переполнении родителя split идёт вверх, и в пределе растёт высота дерева. Отсюда и цена записи: вставка в индекс не сводится к «дописать в конец», это поиск + возможная перестройка страниц + запись в WAL.

Разбор по типам

• B-tree берёт всё, что имеет порядок: =, <, >, BETWEEN, IN, IS NULL, префиксный LIKE 'abc%', а также ORDER BY и MIN/MAX без сортировки. Уникальность и первичные ключи умеет только он.
• Hash понимает только =: ни сортировки, ни диапазонов, ни уникальности. С PG 10 он пишется в WAL (до этого не переживал крэш и не реплицировался), но выигрыш перед B-tree мал, и берут его редко — разве что для очень длинных строк, где хеш заметно компактнее.
• GIN (inverted index) нужен, когда в одном значении много элементов: массивы (@>, &&), jsonb (@>, ?), полнотекстовый поиск (tsvector @@ tsquery), подстроки через pg_trgm. Внутри B-tree по элементам, а в листьях сжатые списки ctid. Читается быстро, пишется медленно; это смягчают настройки fastupdate и gin_pending_list_limit.
• GiST — обобщённое дерево поиска для данных, где «ключом» служит область: геометрия PostGIS, диапазоны tsrange/int4range с оператором пересечения &&, ближайшие соседи (ORDER BY point <-> point), а также EXCLUDE-констрейнты (например, «брони не пересекаются»).
• SP-GiST делит пространство (quad-tree, k-d tree, radix): точки, IP-сети inet, префиксный поиск по строкам. Хорош там, где данные неравномерны.
• BRIN (Block Range INdex) хранит min/max по диапазону страниц (по умолчанию 128), и индекс на терабайтную таблицу занимает мегабайты. Работает, только если физический порядок строк коррелирует со значением колонки. Классический случай: created_at в append-only логах и метриках.

Как выбирать на собеседовании

Вопрос закрывает такой ответ: «сначала спрашиваю, какой оператор в WHERE. Оператор определяет класс индекса: под равенство и диапазон B-tree, под «содержит» GIN, под «пересекается» / «ближайший» GiST, под «строки идут по возрастанию и таблица огромная» BRIN». Проверить, что оператор реально поддерживается, можно через pg_amop или просто по EXPLAIN.

Почему не B-tree

Запрос «найди всё в радиусе 2 км» или «какие полигоны пересекают этот прямоугольник» не сводится к сравнению по одной оси. Отсортировать точки по x не помогает: близкие по обеим координатам точки окажутся в разных концах индекса. Нужна структура, которая группирует объекты по пространственной близости.

Как работает GiST на геометрии

В узлах хранятся ограничивающие прямоугольники (MBR, bounding box) дочерних объектов. Поиск спускается только в те ветки, чей прямоугольник пересекается с искомой областью. Получается R-tree, реализованное поверх обобщённого GiST. Результат приблизительный по bbox, поэтому PostGIS делает двухфазный поиск: индекс отдаёт кандидатов по bbox, затем точный предикат (ST_Intersects, ST_DWithin) отсеивает лишнее.

CREATE EXTENSION postgis;
ALTER TABLE places ADD COLUMN geom geography(Point, 4326);
CREATE INDEX places_geom_idx ON places USING GIST (geom);

-- Всё в радиусе 2 км: ST_DWithin умеет пользоваться индексом
SELECT id, name FROM places
WHERE  ST_DWithin(geom, ST_MakePoint($1, $2)::geography, 2000);

-- Ближайшие 10 (KNN, оператор расстояния идёт прямо в индекс)
SELECT id FROM places ORDER BY geom <-> ST_MakePoint($1, $2)::geography LIMIT 10;

Варианты

• SP-GiST (quad-tree) на чистых точках бывает быстрее и компактнее GiST.
• BRIN подойдёт гигантским таблицам, если данные физически сгруппированы по географии (например, загружены по регионам).
• Геохеш / H3 в отдельной колонке + обычный B-tree обходятся без PostGIS: координату кодируют в строку/число так, чтобы у близких точек был общий префикс, и ищут по префиксу. Работает, но на границах ячеек теряет близкие точки, их добирают из соседних ячеек.

Почему это следует из устройства

Ключи в индексе отсортированы сначала по a, внутри одинаковых a — по b, внутри одинаковых b — по c. Как телефонный справочник по «фамилия, имя, отчество»: всех Ивановых найти легко, а всех Петровичей — только полным перебором.

Правило порядка колонок

1. Сначала колонки с равенством (=, IN): они сужают поиск до непрерывного участка индекса.
2. Затем колонка диапазона (>, BETWEEN). Полезной она бывает только одна и только последней среди «поисковых».
3. Затем колонки сортировки, в том же направлении, что в ORDER BY.
4. Селективность внутри группы равенств вторична: она влияет на размер индекса и на то, сколько других запросов он покроет, но не на то, применим ли он вообще.

Почему индексу всё-таки обычно нужна таблица

В индексной записи нет информации о видимости: там нет xmin/xmax. Индекс не знает, видит ли текущая транзакция конкретную версию строки, поэтому обычный Index Scan обязан сходить в heap и проверить заголовок кортежа. Обойти это помогает visibility map — битовая карта, где взведённый бит означает «все версии на этой странице видны всем транзакциям». Если бит взведён, идти в heap не нужно.

Условия index-only scan

• Все колонки из SELECT, WHERE, ORDER BY есть в индексе.
• Свежий VACUUM: visibility map актуальна.
• В плане это видно как Heap Fetches: 0. Большое число здесь означает, что формально index-only scan выбран, а фактически мы всё равно ходим в таблицу.

INCLUDE против добавления колонки в ключ

-- Вариант 1: колонка в ключе участвует в поиске и сортировке, но раздувает внутренние узлы
CREATE INDEX i1 ON orders (customer_id, total);
-- Вариант 2: с INCLUDE total лежит только в листьях
CREATE INDEX i2 ON orders (customer_id) INCLUDE (total);

-- Для уникальных индексов разница такая:
CREATE UNIQUE INDEX u ON users (email) INCLUDE (name);
-- уникальность по email, а name просто «едет» в листе.
-- Если бы написали (email, name), уникальной была бы пара, а это другая семантика.

INCLUDE (с PG 11) выгоден, когда колонка нужна только для чтения: она не увеличивает внутренние узлы дерева, значит высота и ветвление не страдают, а типы могут быть даже такими, для которых нет операторов сравнения B-tree.

Частичный индекс

-- Мягкое удаление: 99 % запросов ходят только за живыми записями
CREATE INDEX users_active_idx ON users (created_at) WHERE deleted_at IS NULL;

-- Очередь: строк в статусе pending всегда мало, а таблица огромная
CREATE INDEX jobs_pending_idx ON jobs (run_at) WHERE status = 'pending';

-- Уникальность только среди живых: UNIQUE-констрейнтом не выразить, частичным индексом — да
CREATE UNIQUE INDEX users_email_uniq ON users (email) WHERE deleted_at IS NULL;

Планировщик использует частичный индекс, только если может доказать, что предикат запроса влечёт предикат индекса. То есть WHERE status = 'pending' попадёт, а WHERE status = $1 с параметром, как правило, нет: на момент планирования значение неизвестно (спасает generic vs custom plan, но полагаться на это нельзя).

Функциональный индекс

CREATE INDEX users_lower_email_idx ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);   -- попадёт

CREATE INDEX events_day_idx ON events ((created_at::date));
CREATE INDEX orders_meta_client_idx ON orders ((meta->>'client_id'));

Есть ограничение: выражение должно быть IMMUTABLE. Поэтому CREATE INDEX ... (created_at AT TIME ZONE 'UTC') пройдёт, а (now() - created_at) — нет: индекс, который меняется со временем, смысла не имеет.

Что происходит при создании констрейнта

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
-- PostgreSQL молча создаёт индекс users_email_key и привязывает его к констрейнту.
-- DROP INDEX users_email_key;  -- ошибка: индекс принадлежит констрейнту

Приём для прода

-- Добавить уникальность на живую таблицу без длинной блокировки:
CREATE UNIQUE INDEX CONCURRENTLY users_email_uniq ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_uniq;
-- Второй шаг берёт короткую блокировку и просто «усыновляет» готовый индекс.

Условие не подходит структуре

• Функция над колонкой: WHERE lower(email) = 'a@b.c', WHERE date(created_at) = '2024-01-01'. Индекс хранит значения колонки, а не результат функции. Лечится функциональным индексом или переписыванием условия в диапазон.
• Неявный каст: колонка varchar, а параметр приходит числом; или WHERE id::text = $1. Для индекса каст ничем не отличается от функции. Правильно приводить параметр к типу колонки, а не наоборот.
• LIKE '%abc%': B-tree ищет по префиксу, а его тут нет. Нужен pg_trgm + GIN. Даже LIKE 'abc%' в базе не с C-локалью не попадёт в индекс без text_pattern_ops.
• Не левый префикс составного индекса (см. вопрос про составные индексы).
• OR по разным колонкам: одним индексом его не покрыть; иногда выручает BitmapOr по двум индексам, иногда переписывание в UNION ALL.
• ORDER BY a ASC, b DESC при индексе (a, b): направления не совпали.

Индекс подходит, но он не выгоден

Если под условие попадает существенная доля таблицы (эмпирически больше 5–20 %), Index Scan проигрывает: он делает случайные чтения heap-страниц, а Seq Scan читает подряд, что на порядок дешевле, да ещё и пачками. Планировщик тут поступает правильно, и на собеседовании ждут ответа «здесь Seq Scan оптимален», а не «надо заставить индекс». Есть и промежуточный вариант, Bitmap Heap Scan: PostgreSQL собирает ctid в битовую карту, сортирует по номерам страниц и читает heap условно последовательно.

Планировщик ошибается

Оценки берутся из pg_statistic, который наполняет ANALYZE. После массовой загрузки, после создания индекса, после смены распределения данных статистика врёт, и в плане rows= и actual rows= расходятся на порядки. Лечение: ANALYZE table, поднять default_statistics_target (по умолчанию 100) для конкретной колонки, добавить CREATE STATISTICS для коррелированных колонок (город и страна, товар и категория).

Конкретная цена

• INSERT / DELETE трогают все индексы таблицы: поиск места + возможный split страницы + запись в WAL по каждому индексу.
• UPDATE, задевший индексируемую колонку, ломает HOT: вместо «новая версия в той же странице и индексы не трогаем» получаем записи во все индексы. Один лишний индекс по «горячей» колонке может удвоить стоимость обновления.
• WAL растёт → растёт трафик репликации, дольше идёт восстановление, больше нагрузки на диск.
• Место: индексы часто занимают больше самой таблицы. И они конкурируют с данными за shared_buffers и страничный кэш ОС — то есть вытесняют оттуда полезное.
• VACUUM обходит каждый индекс, чтобы вычистить ссылки на мёртвые версии; чем больше индексов, тем дольше и тяжелее autovacuum.
• Планировщик перебирает больше вариантов, и время планирования растёт (заметно на OLTP с тысячами простых запросов в секунду).

Как это звучит на собесе

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

-- 1. Ни разу не использованные, отсортированные по занимаемому месту
SELECT s.relname            AS tbl,
       s.indexrelname       AS idx,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS sz,
       s.idx_scan
FROM   pg_stat_user_indexes s
JOIN   pg_index i ON i.indexrelid = s.indexrelid
WHERE  s.idx_scan = 0
  AND  NOT i.indisunique       -- уникальные держат бизнес-инвариант
  AND  NOT i.indisprimary
ORDER  BY pg_relation_size(s.indexrelid) DESC;

-- 2. Полные дубликаты: одинаковый набор колонок в одном порядке
SELECT indrelid::regclass AS tbl, array_agg(indexrelid::regclass) AS dupes
FROM   pg_index GROUP BY indrelid, indkey, indpred, indexprs HAVING count(*) > 1;

-- 3. Когда сбросили статистику: без этого idx_scan = 0 ничего не значит
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

Что обязательно оговорить

• Статистика копится с момента последнего сброса и только на этом узле. На реплике запросы другие: индекс, «неиспользуемый» на мастере, может активно работать на реплике для отчётов. Проверять надо на всех узлах.
• Есть редкие, но критичные запросы: месячная выгрузка, ночной пересчёт, ручной поиск в поддержке. Наблюдать нужно хотя бы полный бизнес-цикл — месяц.
• Уникальные индексы и индексы под внешними ключами не удалять: они держат ограничения целостности, даже если idx_scan = 0. Кстати, колонка FK без индекса сама по себе беда: DELETE в родительской таблице будет делать Seq Scan по дочерней.
• Безопасного приёма вместо DROP в PostgreSQL штатно нет: сделать индекс невидимым для планировщика нельзя (в MySQL для этого есть ALTER TABLE ... ALTER INDEX ... INVISIBLE). Поэтому на практике индекс переименовывают и держат неделю, чтобы быстро вернуть, либо снимают DDL заранее.

Что происходит без CONCURRENTLY

Блокировка SHARE конфликтует с ROW EXCLUSIVE, который берут INSERT/UPDATE/DELETE. На таблице в 500 ГБ индекс строится десятки минут — и всё это время запись в таблицу стоит. Хуже того, ожидающие запросы выстраиваются в очередь за нашей блокировкой и блокируют даже чтения, которые сами по себе конфликта не имели бы.

Как работает CONCURRENTLY

1. Первый проход: строит индекс по снапшоту, параллельно записи продолжаются.
2. Ждёт завершения всех транзакций, начавшихся до первого прохода.
3. Второй проход: добирает строки, изменившиеся за время первого прохода.
4. Ждёт транзакции, которые могли не видеть индекс, и помечает его валидным.

Ограничения, о которых спрашивают

• Нельзя внутри транзакционного блока, так что миграция должна уметь выполнять такой шаг вне транзакции (в goose это -- +goose NO TRANSACTION, в golang-migrate — отдельный файл и x-no-transaction либо ручной запуск).
• Идёт в 2–3 раза дольше и сильнее нагружает диск.
• При сбое остаётся невалидный индекс: планировщик его не берёт, но на каждой записи он обновляется, то есть только вредит. Искать так: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;, лечить DROP INDEX CONCURRENTLY и повторной попыткой.
• Долгая транзакция блокирует завершение: если кто-то держит открытую транзакцию, CONCURRENTLY будет ждать её вечно. Перед запуском стоит проверить pg_stat_activity.
• Уникальный индекс при конфликте данных упадёт на втором проходе, когда всё время уже потрачено.

Что даёт каждый

• EXPLAIN выдаёт план и оценки планировщика: cost, ожидаемые rows, width. Ничего не выполняется, безопасно всегда.
• EXPLAIN ANALYZE добавляет actual time, actual rows, loops, Rows Removed by Filter. Только сравнив ожидание с фактом, можно понять, врёт ли статистика.
• BUFFERS показывает, сколько страниц реально прочитано и откуда: shared hit (из кэша), read (с диска), dirtied, written. Почти всегда это полезнее времени: время плавает от нагрузки, а число страниц — нет.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT TEXT) SELECT ...;

-- Пишущий запрос: выполнить и откатить
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'done' WHERE id = 42;
ROLLBACK;

Риски и тонкости

• Данные меняются по-настоящему. Классический инцидент на собеседовании и в жизни: «я просто посмотрел план» — а DELETE уже выполнился.
• Побочные эффекты не откатываются полностью: последовательности (nextval) не возвращаются назад, триггеры могут отправить письмо или записать во внешнюю систему.
• Долгий запрос будет выполнен целиком. Если он и так идёт 40 минут и держит блокировки, ANALYZE сделает это ещё раз. Ставь SET LOCAL statement_timeout.
• Оверхед измерения. Таймеры сами по себе замедляют выполнение, иногда заметно на узлах с миллионами итераций. Режим EXPLAIN (ANALYZE, TIMING OFF) считает строки, но не время.
• Параметры. План для PREPARE-запроса с generic plan может отличаться от плана, который ты получил, подставив константы руками. Смотреть надо на то, что реально исполняется, и тут поможет auto_explain.

Числа в узле

• cost=0.43..8.45 меряется в абстрактных «попугаях» планировщика, не в миллисекундах. Первое число оценивает получение первой строки, второе — всех. Разница важна: под LIMIT побеждает план с дешёвым стартом, даже если полная стоимость выше.
• rows=1000 даёт оценку числа строк по статистике pg_statistic.
• actual rows=98000 показывает факт. Из разъезда с оценкой растут почти все плохие планы.
• loops=500 говорит, сколько раз узел выполнялся. Ловушка: actual time и actual rows в плане показаны на одну итерацию. Реальное время узла = actual time × loops. Узел с «0.02 ms» и 500 000 loops работает 10 секунд.
• Rows Removed by Filter=49000: прочитали 50 000, отдали 1000. Прямое указание: нужен индекс по этому предикату (или частичный индекс).
• Heap Fetches считает, сколько раз Index Only Scan всё-таки полез в heap. Если не ноль, visibility map устарела и нужен VACUUM.
• Buffers: shared hit=12 read=8400 значит, что с диска поднято 8400 страниц (~66 МБ).
• Sort Method: external merge Disk: 240MB — сортировка не влезла в work_mem и ушла на диск. Часто самая дешёвая победа: поднять work_mem для конкретной сессии.

Порядок разбора

1. Найти узел с наибольшим собственным временем (общее минус время детей); на верхний узел не смотреть, он суммарный.
2. Сравнить rows и actual rows в этом узле и его детях.
3. Посмотреть, откуда пришло расхождение: устаревшая статистика → ANALYZE; коррелированные колонки → CREATE STATISTICS; выражение вместо колонки → функциональный индекс.
4. Проверить Rows Removed by Filter и Heap Fetches.
5. Посмотреть на тип соединения: Nested Loop с большим внешним набором почти всегда вырастает из недооценки строк.

Узлы доступа

Почему Seq Scan бывает правильным выбором

Случайное чтение страницы дороже последовательного: в модели планировщика это random_page_cost = 4.0 против seq_page_cost = 1.0. Если по индексу нужно вытащить 30 % строк, придётся прочитать 30 % страниц heap в случайном порядке плюс сам индекс, а это дороже, чем прочитать таблицу целиком подряд. Поэтому «планировщик игнорирует мой индекс» чаще всего означает «планировщик прав».

Проверить гипотезу можно принудительно: SET enable_seqscan = off; и сравнить планы. Это инструмент диагностики, а не решение, и на проде такие флаги не оставляют: они лишь показывают, во сколько планировщик оценивает альтернативу.

Шаг 0. Уточнить вопрос

• Тормозит всегда или стало? Если «стало» — что изменилось: релиз, объём данных, план, нагрузка, железо?
• Тормозит этот запрос или вся база? Если вся, смотреть надо не на запрос, а на pg_stat_activity, блокировки, диск, чекпоинты.
• Какая доля запросов страдает: p50 или p99? Деградация только хвоста часто означает блокировки или холодный кэш, а не план.

Шаг 1. Найти виновника объективно

-- Кто съедает базу суммарно (а не кто медленнее всех в единичном вызове)
SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- Что происходит прямо сейчас и кто кого ждёт
SELECT pid, state, wait_event_type, wait_event, now() - query_start AS dur, query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY dur DESC;

Шаг 2. План

Снять EXPLAIN (ANALYZE, BUFFERS) и искать три вещи: расхождение rows и actual rows, большие Rows Removed by Filter, узлы с высоким loops. На терабайтной таблице почти всегда обнаруживается либо Seq Scan, либо Nested Loop с сотнями тысяч итераций.

Шаг 3. Лечение по возрастанию цены

1. Статистика. ANALYZE; поднять default_statistics_target для колонки с перекошенным распределением; CREATE STATISTICS для коррелированных колонок (город и страна). Самое дешёвое и на удивление часто помогает.
2. Индекс. Прицельный: составной в правильном порядке, частичный под предикат, покрывающий с INCLUDE ради Index Only Scan. Только CREATE INDEX CONCURRENTLY. Помнить о цене записи.
3. Переписать запрос. Убрать функцию с колонки, заменить OR на UNION ALL, NOT IN на NOT EXISTS, OFFSET на keyset, убрать SELECT *, вынести тяжёлую агрегацию в отдельный проход.
4. Сузить данные. Нужны ли реально все 3 года? Обычно 95 % запросов идут за последний месяц, а холодные данные можно унести в архивную таблицу или в отдельное хранилище.
5. Партиционирование по времени. Даёт pruning для запросов с датой и, что не менее важно, снова делает обслуживание управляемым: VACUUM, REINDEX, удаление старого через DROP TABLE.
6. Денормализация / материализованное представление / инкрементальный счётчик — если запрос аналитический и считает агрегаты по всей истории.
7. Другое хранилище. Аналитике по миллиардам строк место в ClickHouse, а не в PostgreSQL. Сказать это прямо не стыдно, это сильный ответ.

Как выглядит

// N+1: 1 запрос за заказами + 100 запросов за клиентами
orders, _ := repo.ListOrders(ctx, 100)
for i := range orders {
    orders[i].Customer, _ = repo.GetCustomer(ctx, orders[i].CustomerID)
}

// Батч: 2 запроса всего
orders, _ := repo.ListOrders(ctx, 100)
ids := lo.Uniq(lo.Map(orders, func(o Order, _ int) int64 { return o.CustomerID }))
customers, _ := repo.GetCustomersByIDs(ctx, ids)   // WHERE id = ANY($1)
byID := make(map[int64]*Customer, len(customers))
for _, c := range customers { byID[c.ID] = c }
for i := range orders { orders[i].Customer = byID[orders[i].CustomerID] }

Как обнаружить

• Трассировка. В OpenTelemetry N+1 виден мгновенно: «лестница» из сотни коротких одинаковых спанов внутри одного HTTP-запроса. Самый убедительный способ.
• pg_stat_statements. Запрос с гигантским calls и микроскопическим mean_exec_time, но большим total_exec_time — классическая подпись N+1.
• Счётчик запросов на HTTP-запрос. Простейшая метрика db_queries_per_request с алертом на p99 > 20 ловит регрессии до прода.
• Тест. В интеграционном тесте посчитать число обращений к БД через обёртку над драйвером и зафиксировать ожидаемое: «список из 50 заказов = ровно 2 запроса».
• Косвенный признак: «эндпоинт линейно замедляется от размера страницы» — 20 элементов 40 мс, 100 элементов 200 мс.

Варианты лечения

-- OFFSET: стоимость растёт линейно с номером страницы
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;      -- прочитано 200 020 строк, отдано 20

-- Keyset: стоимость постоянна
SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ($1, $2)     -- курсор с прошлой страницы
ORDER BY created_at DESC, id DESC
LIMIT 20;                              -- прочитано 20 строк

CREATE INDEX posts_feed_idx ON posts (created_at DESC, id DESC);

Две проблемы OFFSET, а не одна

1. Скорость. Деградация линейная: последние страницы больших списков грузятся секундами и падают по таймауту.
2. Корректность. Между запросом страницы 1 и страницы 2 кто-то вставил запись в начало — и одна строка сдвинулась со страницы 1 на страницу 2, то есть пользователь увидит её дважды, а какую-то другую не увидит вовсе. При бесконечной прокрутке это заметный баг, и keyset его чинит бесплатно.

Обязательные детали реализации

• Тай-брейкер. Сортировка должна быть по уникальному набору колонок, иначе строки с одинаковым created_at будут теряться или повторяться. Всегда добавляй id последним.
• Кортежное сравнение. (created_at, id) < ($1, $2) сравнивает строки целиком, ровно как нужно, и PostgreSQL умеет использовать под это индекс. Разворачивать его в created_at < $1 OR (created_at = $1 AND id < $2) не нужно, план от этого только хуже.
• Индекс в том же порядке, включая направления DESC.
• Курсор — непрозрачный токен. Кодировать base64 и не обещать клиенту его структуру, иначе поменять сортировку станет ломающим изменением API.
• limit + 1 вместо отдельного COUNT(*), чтобы узнать, есть ли следующая страница.

• Index Only Scan становится невозможен. Даже идеально покрывающий индекс бесполезен: раз нужны все колонки, придётся идти в heap за каждой строкой. Это буквально разница между 1 чтением и 500.
• TOAST. Одна колонка jsonb или text на мегабайт, которая никому в этом запросе не нужна, превращает лёгкую выборку в чтение внешних чанков из TOAST-таблицы с разжатием.
• Трафик и память приложения. На выборке в 1000 строк разница между 4 нужными колонками и 40 измеряется десятками мегабайт по сети и в куче Go, а это ещё и давление на GC.
• Хрупкость. Кто-то добавил колонку — и rows.Scan с фиксированным набором приёмников падает: «expected 12 destination arguments, not 11». Хуже, если типы совпали и данные тихо разъехались по полям.
• Непрозрачность. По коду невозможно понять, какие поля реально используются; удалить колонку становится страшно, потому что неизвестно, кто её читает.

Отдельная история с SELECT count(*): он проверяет видимость каждой строки, поэтому на больших таблицах дорог всегда. Для «примерно сколько» есть reltuples из pg_class, для точного — отдельный счётчик.

Практические правила

• Батч 1000–10 000 строк. Если больше, получится долгая транзакция: блокировки, удержание горизонта VACUUM, откат всей работы при ошибке в конце.
• Загрузка «с нуля»: сначала данные, потом индексы и FK. Индекс на готовой таблице строится быстрее, чем поддерживается при вставке.
• Для идемпотентности (повторная доставка из брокера) есть ON CONFLICT DO NOTHING. С COPY он не работает, поэтому типичный компромисс: COPY во временную таблицу, затем INSERT ... SELECT ... ON CONFLICT из неё.
• При конкурентных апсертах сортируй строки батча по ключу: ON CONFLICT DO UPDATE берёт блокировку на конфликтующую строку, и разный порядок ключей в параллельных батчах даёт дедлоки.
• pgx.Batch нужен для pipelining разных запросов одним обменом, а не для массовой вставки одинаковых; для однородных строк CopyFrom быстрее.

Что реально даёт

• Pruning на чтении — но только для запросов с ключом партиционирования в WHERE.
• Мгновенное удаление старого. DROP TABLE events_2025_08 вместо DELETE на 300 млн строк, который создаст 300 млн мёртвых версий и вгонит autovacuum в многочасовую работу. Это главная причина партиционировать по времени.
• Управляемое обслуживание. VACUUM, ANALYZE, REINDEX идут по партициям независимо и параллельно, каждая укладывается в окно.
• Меньше индексы. У каждой партиции своё дерево индекса, пониже, и оно целиком помещается в кэш.
• Разные носители. Свежие партиции на NVMe, архивные — в дешёвый tablespace.

Ограничения, которые обязательно назвать

• Ключ партиционирования обязан входить в PRIMARY KEY и любой UNIQUE: глобального уникального индекса поверх всех партиций нет. Отсюда типичный PRIMARY KEY (id, created_at).
• Запросы без ключа партиционирования читают все партиции, и выходит хуже, чем без партиционирования.
• Функция над ключом (date_trunc('month', created_at) = ...) убивает pruning так же, как убивает индекс.
• Сотни партиций планировщик переваривает, тысячи — заметно удлиняют планирование.
• Нарезку будущих партиций надо автоматизировать (pg_partman, cron), иначе всё поедет в DEFAULT-партицию или вставка упадёт.
• Чтобы перевести существующую большую таблицу в партиционированную, нужна отдельная миграция без даунтайма (новая таблица + ATTACH старой как партиции, либо постепенное копирование с двойной записью).

-- Способ 1: NOT EXISTS, предпочтительный и самый читаемый
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Способ 2: LEFT JOIN + IS NULL, классический антиджойн
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;         -- фильтр только по NOT NULL-колонке правой таблицы

-- Способ 3: EXCEPT, когда нужны только идентификаторы
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

-- Ловушка, так не писать:
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);   -- пусто, если есть NULL

Построчно

• SELECT 1 в EXISTS ничего не вычисляет: планировщику важно только «есть ли хоть одна строка». Что писать, SELECT * или SELECT 1, дело вкуса, разницы в плане нет.
• Условие связи o.customer_id = c.id делает подзапрос коррелированным, и как раз поэтому PostgreSQL может превратить его в Anti Join: один проход вместо подзапроса на каждую строку.
• В варианте с LEFT JOIN фильтр WHERE o.id IS NULL должен указывать на колонку, которая в самой таблице orders не бывает NULL, обычно на первичный ключ. Если взять o.comment IS NULL, в выборку попадут ещё и клиенты с заказами без комментария.

Почему NOT IN ломается

SQL живёт в трёхзначной логике. id NOT IN (1, 2, NULL) раскрывается в id <> 1 AND id <> 2 AND id <> NULL. Последнее сравнение даёт UNKNOWN, а TRUE AND UNKNOWN = UNKNOWN, и строка не проходит фильтр WHERE. Итог: если в подзапросе есть хотя бы один NULL, результат пуст для всех строк, причём без ошибки и без предупреждения. А колонка orders.customer_id вполне может быть nullable (гостевые заказы).

Второй минус известен меньше: NOT IN с подзапросом PostgreSQL в Anti Join не превращает вовсе — мешает семантика NULL — и выполняет как подплан: хешированный, если результат влез в work_mem, иначе построчный. «Починить» можно через WHERE customer_id IS NOT NULL в подзапросе, но правильный вывод другой: для антиджойна используем NOT EXISTS, а NOT IN оставляем для списков констант.

-- 1. Найти: какие email задублированы и сколько раз
SELECT lower(email) AS email, count(*) AS n, min(id) AS keep_id, array_agg(id) AS all_ids
FROM users
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY n DESC;

-- 2. Посмотреть сами строки-дубликаты целиком
SELECT * FROM users
WHERE lower(email) IN (SELECT lower(email) FROM users GROUP BY lower(email) HAVING count(*) > 1)
ORDER BY lower(email), id;

-- 3. Удалить, оставив в каждой группе самую раннюю (по created_at, при равенстве по меньшему id)
DELETE FROM users u
USING (
    SELECT id,
           row_number() OVER (PARTITION BY lower(email) ORDER BY created_at, id) AS rn
    FROM users
) d
WHERE u.id = d.id AND d.rn > 1;

-- 4. Закрепить результат, чтобы проблема не вернулась
CREATE UNIQUE INDEX CONCURRENTLY users_email_uniq ON users (lower(email));

Построчно

• PARTITION BY lower(email) группирует по нормализованному значению. Дубликаты почти всегда выглядят как Ivan@mail.ru и ivan@mail.ru, и без lower() ты их не найдёшь.
• ORDER BY created_at, id внутри окна задаёт, какая запись выживет. Решает тут бизнес, а не техника: оставить самую старую (сохранить исходный id) или самую свежую (сохранить актуальные данные). Тай-брейкер id обязателен, иначе при одинаковом created_at выбор недетерминирован.
• DELETE ... USING даёт join в DELETE (синтаксис PostgreSQL). Подзапрос вычисляется один раз, а на больших объёмах это важно.
• Уникальный индекс строим по lower(email), чтобы он совпадал с логикой дедупликации, и с CONCURRENTLY, чтобы не блокировать запись на проде.

Альтернативы и почему они хуже

-- Через ctid: работает без суррогатного ключа, но некрасиво и небезопасно при UPDATE
DELETE FROM users a USING users b
WHERE a.email = b.email AND a.ctid > b.ctid;
-- ctid: физический адрес версии строки, он меняется при UPDATE и VACUUM FULL.
-- Годится только для одноразовой чистки, но не для хранения ссылок.

-- Через min(id): проще, но нельзя выбрать «оставить последнюю» и медленнее на больших таблицах
DELETE FROM users
WHERE id NOT IN (SELECT min(id) FROM users GROUP BY email);
-- Плюс здесь снова NOT IN: попади в список NULL, и не удалится ничего, причём без ошибки.

-- Самый безопасный вариант для очень больших таблиц: пересоздание
CREATE TABLE users_new AS
SELECT DISTINCT ON (lower(email)) * FROM users ORDER BY lower(email), created_at, id;
-- затем переключаем таблицы в одной транзакции

-- Вариант A: топ-3 категории всего, хватит LIMIT
SELECT c.id, c.name, sum(oi.price * oi.qty) AS revenue
FROM order_items oi
JOIN orders     o ON o.id = oi.order_id
JOIN categories c ON c.id = oi.category_id
WHERE o.status = 'paid'
  AND o.created_at >= date_trunc('month', now()) - interval '1 month'
  AND o.created_at <  date_trunc('month', now())
GROUP BY c.id, c.name
ORDER BY revenue DESC
LIMIT 3;

-- Вариант B: топ-3 категории в каждом регионе, нужно окно
WITH revenue AS (
    SELECT o.region_id,
           c.id   AS category_id,
           c.name AS category_name,
           sum(oi.price * oi.qty) AS revenue
    FROM order_items oi
    JOIN orders     o ON o.id = oi.order_id
    JOIN categories c ON c.id = oi.category_id
    WHERE o.status = 'paid'
      AND o.created_at >= date_trunc('month', now()) - interval '1 month'
      AND o.created_at <  date_trunc('month', now())
    GROUP BY o.region_id, c.id, c.name
),
ranked AS (
    SELECT *,
           row_number() OVER (PARTITION BY region_id ORDER BY revenue DESC, category_id) AS rn
    FROM revenue
)
SELECT region_id, category_id, category_name, revenue
FROM ranked
WHERE rn <= 3
ORDER BY region_id, rn;

Построчно

• Границы периода задавай полуинтервалом >= начало AND < следующее начало. Это единственный корректный способ: BETWEEN с '2026-07-31 23:59:59' теряет последнюю секунду, а с '2026-08-01' захватывает лишнюю строку ровно в полночь.
• Условие по дате ставь на колонку, а не на функцию от неё. WHERE date_trunc('month', o.created_at) = ... убивает индекс и, если таблица партиционирована, ещё и pruning.
• o.status = 'paid' нужен почти всегда: выручку считают по оплаченным, а не по созданным заказам. Скажи это вслух: на этом держится половина впечатления от ответа.
• row_number() нумерует внутри PARTITION BY region_id, сортировка по revenue DESC с тай-брейкером category_id для детерминизма.
• Фильтр WHERE rn <= 3 живёт во внешнем запросе: оконные функции вычисляются после WHERE, поэтому внутри того же уровня фильтровать по rn нельзя.

Альтернативы и почему они хуже

-- Вариант A: оконная функция. Один проход по orders.
WITH ranked AS (
    SELECT o.*,
           row_number() OVER (PARTITION BY o.customer_id
                              ORDER BY o.created_at DESC, o.id DESC) AS rn
    FROM orders o
)
SELECT id, customer_id, created_at, total
FROM ranked
WHERE rn <= 5
ORDER BY customer_id, rn;

-- Вариант B: LATERAL делает для каждого клиента точечный index scan на 5 строк.
SELECT c.id AS customer_id, o.id, o.created_at, o.total
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.id, o.created_at, o.total
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.created_at DESC, o.id DESC
    LIMIT 5
) o
ORDER BY c.id, o.created_at DESC;

-- Индекс, без которого ни один вариант не работает хорошо:
CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at DESC, id DESC);

Построчно

• PARTITION BY customer_id начинает нумерацию заново для каждого клиента.
• ORDER BY created_at DESC, id DESC ставит свежие первыми; id нужен как тай-брейкер, иначе два заказа с одинаковой меткой времени будут нумероваться случайным образом и результат станет нестабильным между запусками.
• WHERE rn <= 5 обязан быть уровнем выше: окно вычисляется после WHERE.
• В варианте B CROSS JOIN LATERAL позволяет подзапросу ссылаться на c.id из внешней таблицы, в этом и смысл LATERAL. Если нужны и клиенты без заказов, пиши LEFT JOIN LATERAL ... ON true.

Что выбрать: важная развилка

В цифрах: если у 1000 клиентов по 10 000 заказов, оконный вариант отсортирует 10 млн строк, чтобы выбросить 9 995 000. LATERAL с индексом (customer_id, created_at DESC) сделает 1000 index scan-ов по 5 строк. Выходят секунды против миллисекунд. Если же клиентов миллион и у каждого по 3 заказа, выигрывает окно: один проход дешевле миллиона nested loop.

-- Правильно: оконная функция, один проход по таблице
SELECT id, name, dept_id, salary, dept_avg
FROM (
    SELECT e.id, e.name, e.dept_id, e.salary,
           avg(e.salary) OVER (PARTITION BY e.dept_id) AS dept_avg
    FROM employees e
) t
WHERE salary > dept_avg
ORDER BY dept_id, salary DESC;

-- Так обычно пишут: коррелированный подзапрос
SELECT e.* FROM employees e
WHERE e.salary > (
    SELECT avg(salary) FROM employees WHERE dept_id = e.dept_id
);

-- Третий вариант: JOIN с агрегатом. Читается лучше окна, работает почти так же.
SELECT e.id, e.name, e.dept_id, e.salary, a.dept_avg
FROM employees e
JOIN (
    SELECT dept_id, avg(salary) AS dept_avg
    FROM employees GROUP BY dept_id
) a ON a.dept_id = e.dept_id
WHERE e.salary > a.dept_avg;

Построчно

• avg(salary) OVER (PARTITION BY dept_id) пишется без ORDER BY внутри окна. Добавишь ORDER BY и получишь не среднее по отделу, а нарастающее среднее: рамка по умолчанию станет «от начала раздела до текущей строки». Ошибка тихая и очень частая.
• Оконная функция не схлопывает строки: каждая строка сохраняется, к ней просто приписывается агрегат её раздела. Поэтому salary и dept_avg можно сравнить в одной строке.
• Фильтр снова уходит во внешний запрос (окно вычисляется после WHERE).
• Подзапрос можно оформить как CTE: так читается лучше, а в PostgreSQL 12+ он инлайнится и плана не портит.

Почему коррелированный подзапрос хуже

Формально современный планировщик PostgreSQL иногда умеет его развернуть, но полагаться на это нельзя: в общем случае подзапрос выполняется на каждую строку внешней таблицы. Для 100 000 сотрудников в 50 отделах это 100 000 агрегаций вместо 50. В плане это видно как SubPlan с большим loops, и время равно actual time × loops, а не тому, что показано в узле.

-- Вниз по дереву: все подчинённые сотрудника $1, на любую глубину
WITH RECURSIVE subordinates AS (
    -- Якорь: с кого начинаем
    SELECT e.id, e.name, e.manager_id, 1 AS depth,
           ARRAY[e.id] AS path
    FROM employees e
    WHERE e.id = $1

    UNION ALL

    -- Рекурсивная часть: те, чей менеджер уже найден на прошлом шаге
    SELECT e.id, e.name, e.manager_id, s.depth + 1,
           s.path || e.id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
    WHERE NOT e.id = ANY(s.path)    -- защита от цикла
      AND s.depth < 50              -- страховка на всякий случай
)
SELECT repeat('  ', depth - 1) || name AS tree, id, depth, path
FROM subordinates
ORDER BY path;

-- Вверх по дереву: вся цепочка руководителей конкретного сотрудника
WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE id = $1
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.lvl + 1
    FROM employees e JOIN chain c ON e.id = c.manager_id
    WHERE c.lvl < 50
)
SELECT * FROM chain ORDER BY lvl;

-- Число подчинённых на каждом уровне: тот же WITH RECURSIVE subordinates,
-- только финальный SELECT другой (отдельным запросом CTE не виден)
SELECT depth, count(*) FROM subordinates GROUP BY depth ORDER BY depth;

Как это выполняется на самом деле

1. Выполняется якорь, результат кладётся в рабочую таблицу и в итоговую.
2. Рекурсивная часть выполняется, но subordinates внутри неё означает только результат предыдущей итерации, а не весь накопленный результат. На этой тонкости и ловят.
3. Новые строки добавляются к итогу и становятся рабочей таблицей следующего шага.
4. Шаги повторяются, пока очередной не вернёт ноль строк.

Защита от циклов — три способа

• Массив пути path + WHERE NOT e.id = ANY(s.path) точнее всех: отсекает именно повторное посещение и заодно даёт готовый путь для сортировки дерева.
• Ограничение глубины WHERE depth < 50 — грубо, но надёжно, и защищает ещё и от «слишком глубокого» легального дерева.
• Синтаксис CYCLE (PostgreSQL 14+): ... CYCLE id SET is_cycle USING path. Это стандартный SQL, он делает то же самое декларативно.

-- Вариант 1: DENSE_RANK даёт второе по величине значение (правильный дефолт)
SELECT DISTINCT salary
FROM (SELECT salary, dense_rank() OVER (ORDER BY salary DESC) AS dr FROM employees) t
WHERE dr = 2;

-- Вариант 2: подзапрос с MAX работает без оконных функций, годится и для MySQL 5.x
SELECT max(salary) FROM employees
WHERE salary < (SELECT max(salary) FROM employees);

-- Вариант 3: коррелированный подзапрос ищет «значение, больше которого ровно одно значение»
SELECT DISTINCT e.salary FROM employees e
WHERE (SELECT count(DISTINCT e2.salary) FROM employees e2 WHERE e2.salary > e.salary) = 1;

-- Вариант 4: если нужна вторая строка, а не второе значение
SELECT id, name, salary
FROM (SELECT e.*, row_number() OVER (ORDER BY salary DESC, id) AS rn FROM employees e) t
WHERE rn = 2;

-- Для N-го места: dense_rank() = N. Второй вариант так не обобщается.

Разбор вариантов

Что проверяют на самом деле

• Уточняешь ли требования. Кандидат, который сразу пишет LIMIT 1 OFFSET 1, не задумываясь о дубликатах, отвечает хуже того, кто спросил «а если максимум встречается дважды?».
• Понимаешь ли разницу rank / dense_rank / row_number. Ради неё вопрос и задают.
• Что вернётся, если сотрудников меньше двух. Оконные варианты вернут ноль строк, вариант с max() — одну строку со значением NULL. Разница критична для кода: QueryRow().Scan() в первом случае отдаст sql.ErrNoRows, во втором — успешно просканирует NULL и упадёт, если приёмник int, а не sql.NullInt64.
• Зачем вообще запрет на LIMIT. Ограничение искусственное: его ставят, чтобы вытащить из тебя оконные функции. Скажи это прямо: «в продакшене я бы написал ORDER BY salary DESC LIMIT 1 OFFSET 1 с индексом по salary, но раз нельзя — вот вариант через dense_rank».

Аргументация выбора

• pgx нативно даёт бинарный протокол (меньше трафика и аллокаций), нативные типы PostgreSQL (jsonb, массивы, диапазоны, uuid без промежуточного []byte), CopyFrom, Batch, LISTEN/NOTIFY и типизированные ошибки pgconn.PgError с кодом и именем констрейнта.
• Цена: привязка к PostgreSQL и свой API — часть экосистемы (например, sqlmock) рассчитана на database/sql. Для меня это не проблема: интеграционные тесты я всё равно пишу на testcontainers с настоящей базой, а не на моках драйвера.
• sqlx разумно брать, если нужен стандартный интерфейс и переносимость: StructScan, Get, Select, sqlx.In закрывают 80 % боли database/sql почти без магии.
• GORM уместен там, где действительно много однотипного CRUD и мало сложных запросов. Проблемы: сгенерированный SQL непредсказуем и его надо логировать, Preload легко забыть и получить N+1, AutoMigrate на проде опасен (он не умеет откатываться и делает неожиданные ALTER).
• sqlc стоит упомянуть отдельно: SQL остаётся SQL-ом, но проверяется ещё при генерации и превращается в типизированные Go-функции. Это лучший из известных компромиссов между контролем и рутиной.

Почему дефолты плохие

• С MaxOpenConns = 0 при всплеске Go откроет столько соединений, сколько горутин пришло, упрётся в max_connections сервера и получит «FATAL: sorry, too many clients already». Причём упадёт не один этот сервис, а все, кто ходит в ту же базу, включая мониторинг.
• MaxIdleConns = 2 при MaxOpenConns = 50: как только нагрузка спадает, пул закрывает всё сверх двух, а на следующем всплеске снова устанавливает соединения — TCP + TLS + аутентификация, миллисекунды на каждое, и всё это в hot path. Симптом: MaxIdleClosed в DBStats растёт тысячами в минуту.

Как считать размер

1. Сверху: max_connections (обычно 100–200) минус резерв (superuser_reserved_connections, мониторинг, миграции, человек с psql), делить на число инстансов всех сервисов, ходящих в эту базу.
2. Снизу, по закону Литтла: нужное число = RPS × средняя длительность запроса. 400 RPS × 10 мс = 4 соединения на инстанс. Если получается 50, лечить надо длительность запросов.
3. Проверка здравого смысла: PostgreSQL хорошо работает, когда активных соединений порядка 2 × CPU. Сверх этого начинается деградация из-за конкуренции за LWLock и вытеснения кэшей, и пропускная способность падает, а не растёт.
4. MaxIdleConns = MaxOpenConns почти всегда подходит сервису под постоянной нагрузкой.
5. ConnMaxLifetime 30–60 минут обязателен, если между приложением и БД есть балансировщик, VIP или облачный прокси: без пересоздания соединения не переедут после failover и не перебалансируются.

Последовательность событий

1. Все MaxOpenConns соединений заняты.
2. Очередная горутина вызывает QueryContext и блокируется внутри database/sql в ожидании освобождения.
3. Растут WaitCount и WaitDuration.
4. Если у запроса есть context с таймаутом, по истечении вернётся context deadline exceeded. Если контекста нет, горутина будет ждать вечно.
5. Очередь растёт, горутины копятся, растёт потребление памяти, HTTP-хендлеры не отвечают, health-check падает, под перезапускается — и трафик уходит на соседние поды, которые падают следом. Каскад.

Типичные первопричины

• Забытый rows.Close() — соединение не возвращается в пул. Классика: return из середины цикла rows.Next() без defer rows.Close(). Симптом: InUse монотонно растёт до потолка, и всё залипает.
• Долгие транзакции: HTTP-вызов или запись в брокер внутри BeginTx/Commit. Соединение занято всё это время.
• Медленный запрос без индекса: 30 запросов по 2 секунды намертво выедают пул из 25.
• N+1 при росте страницы: 500 быстрых запросов вместо двух — и пул не справляется.
• Блокировки в БД: транзакции ждут друг друга, соединения заняты ожиданием.

Когда действительно нужен

• Много инстансов сервиса: 30 подов × 20 соединений = 600 при max_connections = 200.
• Kubernetes с автоскейлингом и serverless — число клиентов меняется на ходу, заранее поделить бюджет невозможно.
• Клиенты, которые не умеют пулить (короткоживущие процессы, cron-скрипты, PHP).
• Нужен единый шлюз для смены мастера при failover: приложения не переподключаются, pgbouncer переключает PAUSE/RESUME.

Почему ломаются prepared statements

PREPARE создаёт именованный план в конкретной серверной сессии. В transaction mode после COMMIT серверное соединение уходит другому клиенту, и следующий EXECUTE попадёт на другое соединение, где плана нет: «prepared statement "lrupsc_1_0" does not exist». Драйверы Go по умолчанию используют extended protocol с неявной подготовкой, поэтому проблема вылезает сразу, а не в экзотике.

Как обходят

• pgx: config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol (или QueryExecModeExec) — параметры отправляются вместе с запросом, именованных планов не создаётся. Защита от инъекций сохраняется, но каждый запрос планируется заново.
• pgbouncer 1.21+ умеет отслеживать protocol-level prepared statements (max_prepared_statements > 0) и переигрывать Parse на новом серверном соединении — самое удобное современное решение.
• Два пути к БД: API-трафик через pgbouncer, а фоновые воркеры с длинными транзакциями, LISTEN/NOTIFY и advisory-локами ходят напрямую.

Что ещё ломается в transaction mode

• SET / SET LOCAL: сессионные настройки утекают чужому клиенту или теряются. SET LOCAL внутри транзакции безопасен.
• Temp-таблицы: создаются в сессии, следующей транзакции их не видно.
• LISTEN/NOTIFY живёт только в сессии.
• Advisory locks уровня сессии (pg_advisory_lock): могут «залипнуть» на чужом соединении. Использовать транзакционные pg_advisory_xact_lock.
• Курсоры WITH HOLD вне транзакции.

// Уязвимо
q := "SELECT id, email FROM users WHERE name = '" + name + "'"
// name = "' OR '1'='1"                 → отдаст всю таблицу
// name = "' UNION SELECT id, password_hash FROM admins --"  → утечка
// name = "'; UPDATE users SET is_admin = true WHERE email='me@x.y'; --"

// Закрыто
rows, err := db.QueryContext(ctx, `SELECT id, email FROM users WHERE name = $1`, name)

Почему параметризация действительно защищает

В extended query protocol PostgreSQL сообщение Parse несёт текст запроса с плейсхолдерами, а Bind — значения. Сервер уже построил дерево разбора к моменту, когда увидел значение; подставить в него новый оператор невозможно физически. Защищает разделение каналов, а не экранирование или фильтрация. Поэтому «я экранирую кавычки» заведомо проигрывает: есть многобайтные кодировки, есть числовые контексты без кавычек, есть комментарии.

Динамическая сортировка и имена объектов

// ORDER BY $1 синтаксически валиден, но означает «сортировать по константе».
// Работать как задумано не будет. А склейка открывает дыру, причём blind-типа:
// злоумышленник читает данные посимвольно через CASE в ORDER BY,
// даже не видя текста ошибок.

var allowed = map[string]string{
    "date_desc": "created_at DESC, id DESC",
    "date_asc":  "created_at ASC, id ASC",
    "name":      "name ASC",
}
order, ok := allowed[req.Sort]
if !ok { order = "created_at DESC, id DESC" }
q := fmt.Sprintf(`SELECT id, name FROM users ORDER BY %s LIMIT $1 OFFSET $2`, order)

// Динамическое имя схемы (мультитенантность): квотируем идентификатор
tbl := pgx.Identifier{tenantSchema, "orders"}.Sanitize()
q := fmt.Sprintf(`SELECT id FROM %s WHERE created_at > $1`, tbl)

func (r *Repo) Do(ctx context.Context) (err error) {
    tx, err := r.db.BeginTx(ctx, nil)
    if err != nil { return fmt.Errorf("begin: %w", err) }
    defer func() {
        if p := recover(); p != nil { _ = tx.Rollback(); panic(p) }
        if err != nil { _ = tx.Rollback() }     // err здесь именованный результат
    }()

    if _, err = tx.ExecContext(ctx, q1, a...); err != nil { return err }
    if _, err = tx.ExecContext(ctx, q2, b...); err != nil { return err }

    if err = tx.Commit(); err != nil { return fmt.Errorf("commit: %w", err) }
    return nil
}

// SAVEPOINT: когда часть работы допустимо потерять, не теряя всю транзакцию
if _, err := tx.ExecContext(ctx, "SAVEPOINT sp1"); err != nil { return err }
if _, err := tx.ExecContext(ctx, riskyQuery); err != nil {
    _, _ = tx.ExecContext(ctx, "ROLLBACK TO SAVEPOINT sp1")   // транзакция снова рабочая
} else {
    _, _ = tx.ExecContext(ctx, "RELEASE SAVEPOINT sp1")
}

Разбор деталей

• Именованный возврат (err error) остаётся единственным способом дать defer увидеть ошибку, с которой функция выходит. Без него откат придётся ставить на каждом return, и однажды его забудут.
• Rollback после Commit вернёт sql.ErrTxDone. Это нормально, ошибку явно игнорируем.
• recover в defer нужен, чтобы паника не оставила транзакцию открытой (соединение зависнет в состоянии idle in transaction до таймаута). После отката панику пробрасываем дальше — глотать её нельзя.
• Только tx. Случайный вызов r.db.Exec внутри транзакции уйдёт по другому соединению: изменения окажутся вне транзакции, а при неудаче ещё и заблокируются на строке, которую держит собственная транзакция. Такую самоблокировку диагностируют часами.
• Isolation. &sql.TxOptions{Isolation: sql.LevelRepeatableRead} — и не забыть, что на Repeatable Read и Serializable нужен ретрай при 40001 (serialization failure).

Почему это вообще проблема

Транзакция принадлежит сценарию (создать заказ, списать со склада, положить событие в outbox), а репозитории пишутся по одной сущности. Значит, границей транзакции управляет слой выше репозитория, а репозитории должны уметь работать как внутри неё, так и вне. Если этого не решить, получается либо репозиторий, который сам открывает транзакцию (и тогда атомарности между сущностями нет), либо tx в каждой сигнатуре.

Аргументы против tx в context

• По сигнатуре Insert(ctx, user) error невозможно понять, участвует ли вызов в транзакции. Код перестаёт читаться локально.
• WithinTx легко забыть, и код будет работать, просто без атомарности. Такой баг находят в проде.
• context по соглашению Go отвечает за отмену, дедлайны и request-scoped данные, а не за внедрение зависимостей. Это прямо написано в документации context.WithValue.
• Тесты сложнее: для репозитория «в транзакции» нужно собрать правильный контекст.
• Вложенность: если WithinTx вызвать внутри WithinTx, наивная реализация откроет вторую транзакцию на другом соединении и заблокируется сама на себе. Нужно уметь распознать существующую и сделать savepoint.

Что важно понимать

• Отмена ≠ откат. Прерванный UPDATE откатывается не потому, что его отменили, а потому что транзакция не была зафиксирована. Если автокоммит и оператор успел завершиться до прихода отмены, изменения останутся.
• Задержка. Тяжёлая сортировка, длинный COPY и ожидание блокировки реагируют на отмену каждое в свой момент. Некоторые операции (например, fsync при коммите) вообще не прерываются.
• Соединение считается грязным. Go после отмены обычно закрывает соединение, а не возвращает в пул: неизвестно, в каком оно состоянии. При массовых таймаутах это даёт шторм переподключений, который добивает базу — типичная вторая волна инцидента.
• Отмена HTTP-запроса пользователем тоже отменяет контекст, а с ним и запрос в БД. Это хорошо для чтения и опасно для записи: сценарий «пользователь нажал Escape в середине оформления заказа» должен работать предсказуемо, поэтому для записи часто берут context.WithoutCancel (Go 1.21+) или отдельный контекст с собственным таймаутом.

rows, err := db.QueryContext(ctx, q, args...)
if err != nil { return nil, err }
defer rows.Close()          // (1) обязательно и сразу

var out []User
for rows.Next() {
    var u User
    if err := rows.Scan(&u.ID, &u.Name); err != nil { return nil, err }
    out = append(out, u)
}
if err := rows.Err(); err != nil {   // (2) обязательно после цикла
    return nil, fmt.Errorf("iterate rows: %w", err)
}
return out, nil

Детали, которые проверяют

• rows.Next() возвращает false в двух разных случаях: строки закончились или произошла ошибка (обрыв сети, серверная ошибка в середине выдачи, истёкший контекст, отвалившаяся реплика). Различить их можно только через rows.Err().
• Next(), дойдя до конца, сам вызывает Close(). Но при раннем return из цикла этого не произойдёт, поэтому defer rows.Close() нужен всегда.
• Повторный Close() безопасен, он идемпотентен.
• QueryRow не требует Close: Scan закрывает набор сам. Но именно поэтому QueryRow нельзя «отложить» — ошибка запроса приезжает только в Scan.
• Nested query trap: открыть rows и внутри цикла делать другой запрос через тот же *sql.DB значит занять два соединения на горутину одновременно. При пуле в 25 и 25 параллельных обработчиках получается дедлок на пуле: все держат по одному соединению и ждут второго. Лечится так: сначала вычитать результат в память, потом делать вложенные запросы (а обычно вложенный запрос вообще не нужен — это N+1).
• В pgx rows.Close() ничего не возвращает, а ошибку отдаёт rows.Err(); сегодня проще взять pgx.CollectRows с RowToStructByName, он сделает всё правильно за тебя.

Практический подход

• В слое репозитория живут sql.Null* или pgtype.*, в доменной модели — указатели или отдельное значение по умолчанию. Конвертирует маппер, и домен про SQL не знает.
• Часто чище всего убрать NULL в запросе: SELECT coalesce(middle_name, '') FROM users. Годится, когда пустая строка и NULL семантически неразличимы. Не годится, когда различимы: NULL «телефон не указан» и пустая строка «указан пустым» означают разные факты.
• Ещё лучше не заводить NULL в схеме там, где он не несёт смысла: NOT NULL DEFAULT ''. Каждый nullable-столбец навсегда добавляет ветвление в код.
• Помнить про агрегаты: sum() по пустому набору вернёт NULL, а не 0. Классический баг: SELECT sum(amount) FROM orders WHERE ... с приёмником int64 падает, когда заказов не нашлось. Лечится coalesce(sum(amount), 0).

// domain
var ErrNotFound = errors.New("not found")

// repository
func (r *Repo) GetUser(ctx context.Context, id int64) (*User, error) {
    var u User
    err := r.db.QueryRowContext(ctx,
        `SELECT id, email FROM users WHERE id = $1`, id).Scan(&u.ID, &u.Email)
    switch {
    case errors.Is(err, sql.ErrNoRows):       // pgx: errors.Is(err, pgx.ErrNoRows)
        return nil, fmt.Errorf("user %d: %w", id, domain.ErrNotFound)
    case err != nil:
        return nil, fmt.Errorf("get user %d: %w", id, err)
    }
    return &u, nil
}

// transport
if errors.Is(err, domain.ErrNotFound) {
    http.Error(w, "not found", http.StatusNotFound)
    return
}

Почему именно так

• Слои не должны знать про драйвер. Если хендлер проверяет sql.ErrNoRows, то при замене database/sql на pgx или переезде в другое хранилище придётся править транспорт.
• 404 против 500. Проброшенная как есть ошибка почти всегда превращается в 500 и в алерт, хотя ничего нештатного в «пользователь не найден» нет.
• errors.Is, а не ==. Ошибка почти всегда обёрнута через %w; сравнение на равенство перестанет работать при первом же добавлении контекста.
• У pgx своя pgx.ErrNoRows, и она не равна sql.ErrNoRows — типичная ловушка при миграции с lib/pq.
• Не для всех случаев это ошибка. Для «есть ли такой email» правильнее вернуть (bool, error) или (*User, error) с nil, nil, чем заставлять вызывающего ловить ошибку ради нормального ответа. Ошибка уместна там, где отсутствие записи действительно нарушает ожидания.

Что важно знать про инструменты

• goose: файлы с секциями -- +goose Up / -- +goose Down; поддерживает Go-миграции для сложной логики; есть -- +goose NO TRANSACTION — обязательно для CREATE INDEX CONCURRENTLY, который не работает внутри транзакции.
• golang-migrate: пары файлов *.up.sql / *.down.sql; понятие dirty state — если миграция упала посередине, база помечается грязной и дальнейшие запуски отказываются работать до force.
• Каждая миграция по умолчанию выполняется в транзакции. В PostgreSQL DDL транзакционен, и это большое преимущество: неудачная миграция откатывается целиком. Исключения — CREATE INDEX CONCURRENTLY, ALTER TYPE ... ADD VALUE (до PG 12), VACUUM.

Конфликты версий и как их гасить

• Одинаковые номера из двух веток лечатся переходом на timestamp-именование.
• Порядок при мерже. Даже с timestamp бывает так: ветку с более ранним timestamp смёржили позже. На проде она применится, а у разработчика, у которого более поздняя версия уже записана, — нет. goose умеет -allow-missing, у migrate этого нет; универсальное решение — перед мержем переименовать миграцию, чтобы она стала последней.
• Гонка при старте. Десять подов одновременно запускают миграции. Правильно — отдельная джоба или init-контейнер в единственном экземпляре. Инструменты берут advisory lock, но осознанно полагаться на это не стоит.
• Долгая миграция блокирует деплой. Тяжёлый backfill выносится из миграции в отдельную фоновую задачу, миграция остаётся быстрой и только меняет схему.
• Ревью миграций обязательно. Отдельным глазом смотреть на любой ALTER, на отсутствующий CONCURRENTLY и на lock_timeout.

-- Шаг 1 (мгновенно): nullable-колонка меняет только системный каталог
ALTER TABLE users ADD COLUMN phone text;

-- Шаг 2: релиз кода, который пишет phone. Старая версия кода тоже работает.

-- Шаг 3: backfill батчами, а не одним UPDATE
UPDATE users SET phone = derive(...)
WHERE phone IS NULL AND id BETWEEN $1 AND $2;   -- по 10k, с паузой, следя за lag

-- Шаг 4: NOT NULL без ACCESS EXCLUSIVE на весь скан
ALTER TABLE users ADD CONSTRAINT users_phone_nn CHECK (phone IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_phone_nn;   -- SHARE UPDATE EXCLUSIVE
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;      -- мгновенно (PG 12+)
ALTER TABLE users DROP CONSTRAINT users_phone_nn;       -- опционально

-- Индекс
CREATE INDEX CONCURRENTLY users_phone_idx ON users (phone);
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;  -- проверить успех

Что именно ломается при «прямом» подходе

• ADD COLUMN ... NOT NULL DEFAULT 'x' до PostgreSQL 11 переписывал всю таблицу под ACCESS EXCLUSIVE. С PG 11 значение по умолчанию хранится в каталоге и операция мгновенна, но знать историю надо: на PG 10 это десятки минут простоя.
• ALTER COLUMN ... SET NOT NULL напрямую запускает полный скан таблицы под ACCESS EXCLUSIVE, который блокирует даже SELECT.
• CREATE INDEX без CONCURRENTLY блокирует запись на всё время построения.
• Очередь блокировок. Самое коварное: ALTER, ожидающий ACCESS EXCLUSIVE, встаёт в очередь за долгим SELECT, а все запросы, пришедшие после него, встают уже за ним. Одна миграция при одном долгом отчёте кладёт весь сервис. Лечится SET lock_timeout = '3s' и ретраями.

Физическая (streaming)

• Реплика повторяет мастер блок в блок, поэтому версии PostgreSQL и архитектура обязаны совпадать.
• Минимальные накладные расходы: реплика просто применяет WAL.
• Нельзя выбрать часть данных, нельзя писать на реплике, нельзя иметь на ней свои индексы.
• Её ставят ради HA и failover, read-реплик и бэкапов без нагрузки на мастер.

Логическая

• CREATE PUBLICATION на источнике, CREATE SUBSCRIPTION на приёмнике; передаются строки, а не блоки.
• Работает между мажорными версиями, поэтому через неё обычно апгрейдят PostgreSQL с минимальным даунтаймом: поднимают новую версию подписчиком, ждут синхронизации, переключают трафик.
• Реплицировать можно выборочно: например, только справочники в аналитический контур или CDC в Kafka через Debezium/wal2json.
• Ограничения, которые обязательно назвать: не реплицируется DDL (схему надо накатывать на обе стороны руками), нужен первичный ключ или REPLICA IDENTITY FULL для UPDATE/DELETE, не синхронизируются последовательности, конфликты (например, дубль по уникальному ключу на подписчике) останавливают репликацию до ручного вмешательства.

Уровни synchronous_commit

Ключевые тонкости

• off не ломает базу. Вопреки частому заблуждению, теряются только последние транзакции: база остаётся целостной, потому что WAL всё равно пишется последовательно и восстановление корректно. Поэтому off годится для логов, метрик и импорта, где потеря секунды данных безразлична, а выигрыш в throughput кратный.
• Настраивается на транзакцию. SET LOCAL synchronous_commit = 'remote_apply' для перевода денег и 'off' для записи в журнал активности уживаются в одной базе.
• Кворум. С synchronous_standby_names = 'ANY 2 (r1, r2, r3)' мастер ждёт подтверждения любых двух из трёх: сохранность есть, а падение одной реплики запись не останавливает. Для прода это разумный компромисс.
• Опасность одной синхронной реплики: если она недоступна, мастер заблокирует запись до её возвращения. Поэтому синхронных реплик держат минимум две с кворумом ANY 1 или включают автоматический перевод в асинхронный режим.
• Гео-распределение. Синхронная реплика в другом регионе добавляет 50–100 мс к каждому коммиту, и обычно это неприемлемо. Типовая схема: синхронная в соседней AZ, асинхронная в другом регионе.

-- На мастере: кто отстаёт и насколько
SELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)   AS sent_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;

-- На реплике: лаг во времени
SELECT now() - pg_last_xact_replay_timestamp() AS lag;
SELECT pg_last_wal_replay_lsn();     -- нужен для ожидания собственной записи

Причины всплесков лага

• При массовой записи (backfill, миграция, импорт) WAL генерируется быстрее, чем применяется.
• Применение WAL на реплике однопоточное, и при большом объёме реплика физически не успевает за параллельной записью мастера.
• Долгий запрос на реплике конфликтует с применением: без hot_standby_feedback применение ждёт запрос до max_standby_streaming_delay, а потом его отменяет («canceling statement due to conflict with recovery»). С hot_standby_feedback = on конфликта нет, зато bloat растёт на мастере.
• Сеть между дата-центрами, диск реплики слабее мастера.
• VACUUM большой таблицы пишет гигабайты WAL за минуты.

Шаги failover

1. Обнаружение. Health-check не отвечает N раз подряд. С порогом ищут компромисс: слишком чувствительный даёт ложные срабатывания на сетевом моргании, слишком терпимый увеличивает RTO.
2. Выбор кандидата. Берут реплику с наибольшим применённым LSN: она потеряет меньше всего. Если есть синхронная реплика, выбирают её.
3. Fencing старого мастера делают до promote, а не после.
4. Promote. Реплика заканчивает восстановление и начинает принимать запись.
5. Переключение трафика: VIP, обновление DNS, PAUSE/ RESUME в pgbouncer, обновление service discovery.
6. Перестройка кластера на нового мастера. Остальные реплики просто переходят на его линию времени, а бывший мастер возвращают репликой через pg_rewind: у него могут быть «лишние» транзакции, до реплик не доехавшие.

Защита от split brain

• Кворум и внешний DCS. Решение принимает только часть кластера с большинством узлов (etcd/Consul/ZooKeeper). Поэтому узлов нужно нечётное число, а DCS должен быть отдельной, независимо доступной системой.
• Fencing / STONITH. Старый мастер физически изолируют: снимают VIP, останавливают сервис, блокируют порт, а в облаке отключают сетевой интерфейс. Только после этого повышают нового.
• Самодемоция. Узел, потерявший связь с DCS и не подтвердивший свою роль в течение TTL лидерской аренды, сам переводит себя в read-only. Так работает Patroni: лидерство там хранится как ключ с TTL, который надо продлевать.
• Единая точка входа. Клиенты ходят через прокси, знающий текущего мастера, и «второй мастер» просто не получает трафика.
• Синхронная репликация дополнительно гарантирует, что у нового мастера есть все подтверждённые коммиты.

Ключевые различия

• Что масштабируется. Партиционирование решает проблему размера таблицы и обслуживания, шардирование — проблему ресурсов сервера, когда одна машина не тянет запись, объём или память.
• Прозрачность. Партиции остаются деталью реализации: приложение работает с одной таблицей. Шарды входят в архитектуру: приложению нужно знать, куда идти.
• Транзакции. Между партициями работает обычный ACID, между шардами нужен 2PC или сага.
• JOIN. Между партициями он обычный. Между шардами остаётся scatter-gather с агрегацией в приложении или дублирование справочников на все шарды.
• Уникальность. Партиция требует ключ в PK. Шардирование вообще лишает глобального UNIQUE: чтобы email был уникален по всей системе, нужен либо отдельный сервис-реестр, либо шардирование именно по email.
• Эксплуатация. N шардов означают N кластеров, N наборов реплик, N бэкапов, N процедур failover. Сопровождение дорожает линейно.