PostgreSQL Style Guide

PostgreSQL Style Guide для прикладного бэкенда: какие типы брать, как проектировать индексы, читать EXPLAIN и катить миграции без даунтайма. С кодами правил для AI-ревью.

Раздел собран из боевых решений, которые повторяются от сервиса к сервису: какой тип взять для денег, почему timestamp без зоны взрывается через полгода, чем uuid v7 лучше uuid v4 для btree-индекса, когда jsonb оправдан, а когда — флаг плохой схемы.

Каждое правило снабжено кодом (PG-T-001, PG-I-002, PG-M-003...) — на эти коды ссылаются скиллы при ревью схемы, миграций и планов запросов:

PG-T-* — типы (Types)
PG-I-* — индексы (Indexes)
PG-M-* — миграции (Migrations)
PG-E-* — EXPLAIN / runtime

Куда идти за конкретным вопросом

Вопрос	Статья
Какой тип взять для денег / id / счётчика	Числа и точность
`text` или `varchar(n)`, нужна ли длина	Строки
`timestamp` или `timestamptz`, как работают зоны	Время и таймзоны
UUID v4 или v7, почему случайные UUID — больно	UUID и идентификаторы
PG enum или ссылочная таблица или `CHECK`	Enum и boolean
Когда `jsonb` оправдан, когда нет	JSONB
Массив или отдельная таблица; интервалы и непересечения	Массивы и range-типы
Что точно делать не надо	Антипаттерны

Контекст применения

Подразумевается стек:

PostgreSQL 14+ (часть рецептов работает только начиная с 12 или 14 — будет помечено).
Java 21 + Spring Boot 3 + jOOQ как persistence-слой (почему jOOQ, а не JPA — отдельная тема).
Liquibase как инструмент миграций (db/changelog/*.xml или *.sql), но рецепты применимы к Flyway и сырому SQL.

Раздел не пытается заменить документацию PostgreSQL — он отвечает на «что брать в типичной CRUD/доменной задаче, чтобы не переписывать через год».

Структура

Раздел растёт волнами:

Типы (текущая волна) — фундамент. Что выбрать на этапе проектирования таблицы.
Индексы и EXPLAIN — что делать, когда запросы тормозят, и как читать план.
Миграции — как менять схему на живой БД.

Каждая волна закрывается отдельным AI-скиллом (ucp-pg-schema-review, ucp-pg-explain-review, ucp-pg-migration-review), который применяет правила из соответствующих статей.

Числа и точность

Числовых типов в PostgreSQL много, но в типичном бэкенде нужны три ответа: «чем считать id», «чем считать деньги», «чем считать метрики и проценты». Остальное — частные случаи.

1. Целые числа

PostgreSQL предлагает три целочисленных типа:

Тип	Размер	Диапазон
`smallint`	2 байта	−32 768 … 32 767
`integer` (`int`, `int4`)	4 байта	±2.1 миллиарда
`bigint` (`int8`)	8 байт	±9.2 квинтиллиона

`PG-T-010` — Для id таблицы — `bigint`. Без вариантов

integer кажется достаточным («у нас никогда не будет 2 миллиардов записей»), но через несколько лет один из таких аргументов всё равно перестаёт быть верным — а замена int → bigint на живой большой таблице болезненная: либо ALTER TYPE, переписывающий всю таблицу под ACCESS EXCLUSIVE lock, либо expand-contract через новую колонку. Стоимость превентивных 4 байт на строку — нулевая, стоимость миграции на бою — дни и инцидент.

`PG-T-011` — `smallint` оправдан только когда тип — нумератор фиксированной короткой шкалы

(например, год рождения автомобиля, день недели, часовой пояс). Для счётчиков, лимитов, остатков — integer или bigint, не smallint.

CREATE TABLE order_item (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    bigint    NOT NULL,
    quantity    integer   NOT NULL CHECK (quantity > 0),
    weight_g    integer   NOT NULL          -- граммы, не килограммы дробные
);

2. Генерация id: `IDENTITY` вместо `serial`

Исторически id-колонки оформляли через serial / bigserial:

CREATE TABLE foo (
    id bigserial PRIMARY KEY     -- не надо
);

`PG-T-012` — С PostgreSQL 10+ используем `GENERATED ALWAYS AS IDENTITY`, а не `serial` / `bigserial`

CREATE TABLE foo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

Отличия:

serial — это макрос: создаётся sequence и колонка int/bigint с DEFAULT nextval(...). Связь между sequence и колонкой не явная — это породило поколение проблем при pg_dump и при перепривязке последовательностей.
IDENTITY — стандарт SQL. Sequence привязана к колонке через каталог, при DROP TABLE уезжает вместе с таблицей. ALWAYS запрещает явные INSERT id = ? — это полезно: в обычной работе никто не должен подсовывать свои id.
BY DEFAULT (мягкая версия IDENTITY) — если иногда нужна явная вставка id (например, при миграции данных), но писать вручную каждый день не приходится.

Когда не годится: распределённые сервисы, которым нужен глобально уникальный id без обращения к одной БД. Тогда — UUID v7 (см. UUID).

3. Деньги: `numeric`, не `float`

`PG-T-013` — Деньги, проценты, налоги, тарифы — `numeric(p, s)`. Никогда не `real` / `double precision`

amount_total      numeric(15, 2) NOT NULL,    -- до 13 цифр до запятой, 2 после
exchange_rate     numeric(20, 8) NOT NULL,    -- курсы: 8 знаков после
discount_percent  numeric(5, 2)  NOT NULL CHECK (discount_percent BETWEEN 0 AND 100)

Причины:

float / double хранит число в двоичном виде, и не все десятичные значения представимы точно. Классический пример: 0.1 + 0.2 ≠ 0.3. Для денег это не «погрешность округления», это юридически неверный расчёт, который проявится через год при сверке с банком.
numeric хранит число как набор десятичных цифр — точно, без округления.
Производительность: numeric медленнее bigint на простых операциях, но для финансовых расчётов в OLTP это не узкое место.

Альтернатива: хранить деньги в копейках (целое в bigint). Вариант рабочий, но ограничен:

неудобно для систем с переменной точностью (BTC требует 8 знаков, валюты — 2–3);
требует дисциплины во всём приложении: один забытый делитель / умножитель портит данные.

Для типичного маркетплейса/SaaS-биллинга — numeric(p, s) со стандартной точностью валют.

`PG-T-014` — Тип `money` PostgreSQL не используем

Он привязан к глобальной локали сервера и не хранит код валюты. Для мультивалютной системы бесполезен, для одновалютной — numeric всё равно лучше.

4. Метрики, проценты, физические величины

`PG-T-015` — `real` (`float4`) и `double precision` (`float8`) применяем только когда уместна неточность

Допустимо:

временные ряды метрик (CPU usage, latency p95);
научные расчёты, где входы уже неточные (вес, температура, расстояние);
ML-фичи, embeddings.

Недопустимо:

деньги, учётные количества, баллы лояльности — всё, что должно сходиться при сравнении.

5. Boolean

`PG-T-016` — Boolean — это `boolean`

Не smallint 0/1, не varchar('Y'/'N'), не char(1) с CHECK.

is_active   boolean NOT NULL DEFAULT true,
is_deleted  boolean NOT NULL DEFAULT false

Подробнее — в статье Enum и boolean.

Строковые типы

В PostgreSQL три строковых типа — text, varchar(n), char(n). Внутри сервера они хранятся одинаково и работают одинаково быстро. Разница только в семантике ограничения длины.

1. По умолчанию — `text`

`PG-T-020` — Если у поля нет бизнес-причины ограничивать длину — берём `text`

CREATE TABLE customer (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name   text NOT NULL,
    email       text NOT NULL,
    bio         text
);

Аргументы:

text и varchar(n) хранятся одинаково (TOAST для длинных значений), скорость одинаковая. Длина — это CHECK-проверка перед записью, и больше ничего.
varchar(255) пришёл из MySQL/Oracle, где varchar(255) действительно отличался от text. В PostgreSQL это просто text с CHECK (length <= 255) и неудобный путь миграции, если завтра окажется 256.
Менять text на varchar(n) дешевле, чем расширять varchar(n) (последнее в старых версиях переписывало таблицу).

2. Когда длина оправдана

`PG-T-021` — Длину ставим, когда она — доменное правило, а не «техническое ограничение»

Хорошие случаи:

phone_e164      varchar(15)  NOT NULL,    -- E.164 ограничивает до 15 цифр
country_code    char(2)      NOT NULL,    -- ISO 3166-1 alpha-2: ровно 2 буквы
currency        char(3)      NOT NULL,    -- ISO 4217: ровно 3 буквы
inn             varchar(12)  NOT NULL,    -- ИНН ФЛ — 12, ЮЛ — 10

Плохие случаи:

full_name       varchar(255)             -- почему 255?
description     varchar(1000)            -- почему 1000?

Если завтра приходит русское имя в 4 слова с дефисами или клиент пишет описание длиннее лимита — будет 500-я ошибка на ровном месте. Берите text, валидация длины — на уровне приложения, если она нужна.

3. `char(n)` — почти никогда

char(n) — фиксированная длина с обязательным паддингом пробелами:

CREATE TABLE foo (code char(5));
INSERT INTO foo VALUES ('AB');
SELECT '[' || code || ']' FROM foo;   -- '[AB   ]'

`PG-T-022` — `char(n)` оправдан только для строго фиксированной длины из стандарта

— char(2) для ISO-страны, char(3) для валюты, char(36) под формат UUID-строки (хотя и это неправильно — см. UUID, нужен тип uuid).

Для всего остального — паддинг пробелами создаёт сюрпризы при LIKE, =, length() и при сериализации в JSON.

4. Case-insensitive: `citext` или функциональный индекс

Логины, email, теги часто сравниваются без учёта регистра. Два рабочих подхода:

Подход 1 — расширение citext:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE account (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  citext NOT NULL UNIQUE
);

INSERT INTO account (email) VALUES ('ivan@example.com');
SELECT * FROM account WHERE email = 'IVAN@EXAMPLE.COM';   -- найдёт

Подход 2 — обычный text + functional unique index:

CREATE TABLE account (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  text NOT NULL
);

CREATE UNIQUE INDEX account_email_lower_uk
    ON account (lower(email));

-- запросы должны явно использовать lower():
SELECT * FROM account WHERE lower(email) = lower('IVAN@EXAMPLE.COM');

`PG-T-023` — Для case-insensitive — `citext` либо `lower()` + functional index. Не `LOWER()` без индекса в каждом запросе

citext короче в коде, но:

не работает с типизированными драйверами JDBC «из коробки» (приедет как text, проверьте, что jOOQ генерирует);
усложняет миграцию, если решите переехать на другой движок.

text + functional index — более портируемый вариант, работает везде.

5. Энкодинг

`PG-T-024` — Кластер должен быть `UTF8`. Локально-зависимые сборки баз — наследие

SHOW server_encoding;   -- ожидаем UTF8

Это про инфраструктуру, не про DDL, но проверять стоит — иногда платформенные базы получают SQL_ASCII от старых deployment-скриптов, и потом эмодзи в комментариях покупателей ломают всё.

6. Длинные строки (TOAST)

PostgreSQL автоматически выносит длинные значения колонок в отдельное хранилище (TOAST). Порог — около 2 KB на запись. Это работает прозрачно: вы не управляете этим явно, но имеет смысл знать:

text с большими статьями/описаниями физически хранится отдельно от основной строки;
частое чтение «маленьких» колонок таблицы не задевает большие тексты;
EXPLAIN ANALYZE покажет ниже скорость, если запрос требует чтения toast-сегментов.

`PG-T-025` — Если в таблице есть и часто читаемый набор коротких полей, и редко-читаемый длинный текст (например, журнал событий с полем `body`), не разделяйте их на две таблицы преждевременно — TOAST уже это делает

Делите, только если измерения показывают узкое место.

Время и таймзоны

Время — самый частый источник тихих багов в продакшене. Заказ от 23:30 не попадает в дневной отчёт, события приходят «в будущем», cron срабатывает дважды. В 90% случаев виноват не код приложения, а тип колонки в БД.

1. Главное правило

`PG-T-030` — Для бизнес-времени — всегда `timestamptz`. Никогда `timestamp` (он же `timestamp without time zone`)

CREATE TABLE order_event (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at timestamptz NOT NULL,    -- правильно
    created_at  timestamp   NOT NULL     -- неправильно
);

2. Что хранится на самом деле

Главное недопонимание про PG: timestamptz не хранит зону. Он хранит UTC.

При записи: PG берёт значение, конвертирует в UTC по текущему TimeZone сессии, сохраняет как количество микросекунд от эпохи.
При чтении: PG берёт UTC, конвертирует обратно в TimeZone сессии, возвращает.

То есть timestamptz — это «timestamp в UTC + автоматическая конвертация на границе I/O». Зона нужна, чтобы PG знал, в какой зоне трактовать ваш ввод и в какой отдавать вывод.

timestamp — это просто число «год-месяц-день-час-минута-секунда» без привязки к зоне. PG считает его «локальным временем непонятно где». Если в одну колонку с разных серверов / клиентов / контейнеров приедут значения, получится мусор: 2026-05-07 12:00:00 от UTC-сервера и от MSK-клиента — это разные моменты, но в БД они одинаковые.

3. Откуда берутся проблемы

SET TIME ZONE 'Europe/Moscow';
INSERT INTO order_event (occurred_at) VALUES ('2026-05-07 14:00:00');
-- хранится как 2026-05-07 11:00:00+00 (UTC)

SET TIME ZONE 'UTC';
SELECT occurred_at FROM order_event;
-- вернёт 2026-05-07 11:00:00+00

SET TIME ZONE 'America/New_York';
SELECT occurred_at FROM order_event;
-- вернёт 2026-05-07 07:00:00-04

Один и тот же момент времени, отображается по-разному в зависимости от зоны сессии. Это правильно — это и есть смысл timestamptz.

С timestamp без зоны:

INSERT INTO bad (created_at) VALUES ('2026-05-07 14:00:00');
-- хранится буквально '2026-05-07 14:00:00' — без зоны

-- что это значит? UTC? MSK? Зона приложения? Зона клиента?
-- ответа нет, и через год никто уже не помнит, какая была договорённость.

4. JDBC и Java-сторона

`PG-T-031` — На Java-стороне используем `Instant` или `OffsetDateTime` для `timestamptz`. `LocalDateTime` — никогда

Колонка PG	Java тип	Корректно
`timestamptz`	`Instant`	да, рекомендуется
`timestamptz`	`OffsetDateTime`	да
`timestamptz`	`ZonedDateTime`	да, но избыточно
`timestamptz`	`LocalDateTime`	нет — потеряется зона на конверсии
`timestamp` (без TZ)	`LocalDateTime`	да (но сам тип нежелателен)
`date`	`LocalDate`	да
`time`	`LocalTime`	да

Instant — это «момент времени в UTC». Идеально ложится на timestamptz. jOOQ при включённой конфигурации генерирует Instant, JdbcTemplate тоже умеет.

Типичный баг: jOOQ сгенерил LocalDateTime для timestamptz-колонки → при чтении PG отдаёт UTC, JDBC-драйвер конвертирует в локальную зону JVM → получаем LocalDateTime без указания, в какой зоне он. На сервере с TZ=UTC и на ноутбуке разработчика с TZ=Europe/Moscow — разные значения.

// правильно
record OrderEventRow(long id, Instant occurredAt) {}

// неправильно
record OrderEventRow(long id, LocalDateTime occurredAt) {}

5. Когда `timestamp without time zone` всё-таки нужен

`PG-T-032` — `timestamp` (без TZ) допустим только для «локального времени без привязки к моменту»

— например, расписание открытия магазина, рабочее время, время вылета по локальному рейсу.

shop_opens_at   time NOT NULL,           -- 09:00, в любой локали
shop_closes_at  time NOT NULL,           -- 18:00
holiday_date    date NOT NULL,           -- 2026-01-01, в локальной таймзоне магазина
flight_local_departure timestamp NOT NULL  -- 14:00 по локальному рейса

В этих случаях зона хранится отдельно (например, в колонке timezone text NOT NULL), и приложение конвертирует при необходимости.

Это редкие случаи. Дефолт — timestamptz.

6. `now()`, `clock_timestamp()`, `statement_timestamp()`

`PG-T-033` — Знайте разницу:

Функция	Что возвращает
`now()` / `transaction_timestamp()`	время начала транзакции. Внутри одной транзакции возвращает то же самое.
`statement_timestamp()`	время начала текущего statement (одного `INSERT`/`UPDATE`/...).
`clock_timestamp()`	время в момент вызова. Каждое обращение даёт новое значение.

Для created_at / updated_at обычно нужен now() — тогда у всех строк, вставленных в одной транзакции, одна и та же отметка. Это полезно для отладки и аудита.

created_at timestamptz NOT NULL DEFAULT now()

Если делаете замер «сколько шёл цикл вставки 10 000 строк» внутри транзакции — берёте clock_timestamp().

7. Тестируемое время

`PG-T-034` — В приложении не вызывайте `Instant.now()` / `LocalDateTime.now()` напрямую. Заведите `DateTimeService` и моките его в тестах

Тривиальный сервис:

public interface DateTimeService {
    Instant now();
}

@Component
@Profile("!integration-test")
public class SystemDateTimeService implements DateTimeService {
    @Override public Instant now() { return Instant.now(); }
}

В тесте:

@MockitoBean DateTimeService dateTimeService;

@BeforeEach
void freezeTime() {
    when(dateTimeService.now()).thenReturn(Instant.parse("2026-05-07T12:00:00Z"));
}

Без этого тесты, где сравнивается время в БД с временем расчёта, становятся flaky на ровном месте: сравнение assertEquals(expected, actual) по Instant иногда отличается на 100 микросекунд.

То же самое применимо к now() в SQL-DEFAULT: для проверяемых сценариев лучше передавать значение из приложения, а не полагаться на DEFAULT now().

8. `INTERVAL` и арифметика

`PG-T-035` — Используйте `INTERVAL` для смещений в SQL, не вычисляйте секунды вручную

-- читаемо
SELECT * FROM session WHERE last_seen_at < now() - interval '15 minutes';

-- нечитаемо
SELECT * FROM session WHERE last_seen_at < now() - 900 * interval '1 second';

INTERVAL корректно учитывает летнее время, високосные секунды, разницу длительности месяцев.

9. Важная мелочь: `+infinity` и `-infinity`

PG поддерживает специальные значения:

INSERT INTO subscription (expires_at) VALUES ('infinity');
SELECT * FROM subscription WHERE expires_at > now();   -- найдёт

Полезно для «бессрочных» подписок, политик, ролей. Лучше, чем NULL или 9999-12-31 — выражает намерение явно и поддерживается арифметикой.

UUID и идентификаторы

UUID — стандартный выбор для распределённых сервисов: идентификатор можно сгенерить на клиенте до записи, нет конфликтов между сервисами, удобно ссылаться извне. Но как и любой инструмент, он бьёт, если применять без понимания.

1. Тип `uuid`, не `varchar(36)`

`PG-T-040` — UUID хранится в типе `uuid`. Никогда не `varchar(36)` / `char(36)` / `text`

-- правильно
CREATE TABLE customer (
    id   uuid PRIMARY KEY,
    name text NOT NULL
);

-- неправильно
CREATE TABLE customer_bad (
    id   varchar(36) PRIMARY KEY,
    name text NOT NULL
);

Что теряем при varchar(36):

Размер: uuid — 16 байт, varchar(36) — 36 байт + 1–4 байта overhead. На больших таблицах с FK это умножается на каждый индекс и каждую foreign key. Реальная экономия — десятки гигабайт на больших схемах.
Скорость: сравнение uuid — две операции int64. Сравнение varchar(36) — посимвольное.
Валидация: uuid проверяет формат на вставке. varchar(36) пропустит 'not-a-uuid'.
Регистр и канонический формат: varchar(36) хранит «как есть» — два UUID, отличающихся только регистром, считаются разными ключами. uuid нормализует.

2. v4 vs v7: тот же бит-формат, разная производительность

UUID v4 — это 122 случайных бита + версия. UUID v7 — это 48-битный timestamp + 74 случайных бита + версия.

`PG-T-041` — Для PK / FK берём UUID v7, не v4

Причина — устройство B-tree-индекса в PostgreSQL.

Что происходит с UUID v4

UUID v4 случаен — соседние генерируемые id попадают в случайные позиции индекса:

INSERT a1b2c3...   → страница 47
INSERT 4d5e6f...   → страница 9123
INSERT 9a8b7c...   → страница 218

Каждая вставка тащит из диска / прогревает в кеше новую страницу. Размер индекса растёт быстрее, чем должен (страницы заполняются на 30–60%, потому что btree разбивает их при вставках в середину). Кеш Postgres постоянно вытесняется. Чтения через PK медленнее, чем при последовательном id.

На небольших таблицах разницы не видно. На таблице 100M строк это 2–5x разница в скорости вставки и заметные просадки на хорошо прогретом кеше.

Что меняет UUID v7

Первые 48 бит UUID v7 — это unix-time в миллисекундах. Соседние по времени id физически близки в индексе:

2026-05-07T12:00:00.001 → 0190abcd-...
2026-05-07T12:00:00.002 → 0190abce-...
2026-05-07T12:00:00.003 → 0190abcf-...

Все попадают в одну страницу btree. Индекс растёт компактно (страницы заполняются на 90%+), кеш не вытесняется при последовательных вставках, чтение по диапазону «последние N заказов» становится range-scan, а не random-scan.

При этом сохраняются плюсы UUID:

глобальная уникальность без обращения к одной БД;
не угадывается извне (74 случайных бита достаточно);
генерится на клиенте до записи.

Цена перехода

Никакая. UUID v7 — это тот же тип uuid PostgreSQL, тот же 16-байтный формат. Меняется только генератор.

3. Где взять генератор UUID v7

В PostgreSQL 18 — встроенная функция uuidv7(). До 18 — расширение или генерация на клиенте.

В Java (рекомендуется — генерация на стороне приложения):

import com.github.f4b6a3.uuid.UuidCreator;

UUID id = UuidCreator.getTimeOrderedEpoch();   // UUID v7

Библиотека uuid-creator (или аналоги — java-uuid-generator, nanoid с другим форматом). Генерация на клиенте удобна тем, что id известен до записи — можно сразу публиковать события / возвращать клиенту, не дожидаясь ответа БД.

В PostgreSQL до 18:

Свой триггер с реализацией v7 (рецепты есть в публичных gist'ах). Менее удобно, чем клиентская генерация — теряется свойство «id известен до записи».

`PG-T-042` — Генерируем UUID v7 на стороне приложения

(если PG < 18 или если нужен id до commit). На стороне БД — только когда приложению он не нужен заранее.

4. UUID vs `bigint IDENTITY`: что выбирать

`PG-T-043` — `bigint IDENTITY` дешевле и быстрее. UUID — когда нужен по делу

UUID оправдан, когда:

Сервисов несколько, и id должен быть глобально уникальным без координации.
Id отдаётся наружу (публичный API, ссылка в письме) и не должен подсказывать порядок и объёмы (/order/12345 показывает «у нас примерно 12K заказов» — IDENTITY не подходит для публичных URL).
Запись и публикация события / возврат клиенту должны произойти до commit-а (id нужен сразу).

bigint IDENTITY оправдан, когда:

Один сервис, одна БД.
Внешний контракт не требует UUID.
Нужна простота в дебаге (увидеть id 1234 в логе и пойти по нему — проще, чем 0190abcd-1234-7890-...).

Обычная практика для маркетплейса:

CREATE TABLE order_doc (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- внутренний id
    public_id   uuid   NOT NULL UNIQUE DEFAULT gen_random_uuid(),  -- внешний id (можно UUID v7)
    ...
);

Внутренний bigint — для FK и быстрых джойнов. Публичный uuid — для API и URL.

5. UUID и FK

`PG-T-044` — При UUID-PK — обязательно индекс по FK с такой же `uuid`-колонкой

PostgreSQL не строит индекс по FK автоматически. На малых таблицах это не страшно, на больших — DELETE родителя уйдёт в seq-scan дочерней таблицы. См. composite индексы и FK (вторая волна раздела).

6. Что делать с уже существующими `varchar(36)`

Реально-боевая задача миграции:

Добавить новую колонку id_uuid uuid.
Backfill: UPDATE t SET id_uuid = id::uuid;
Создать индексы / FK на новую колонку (concurrent).
Переключить приложение читать новую колонку.
Дропнуть старую varchar(36) (отдельный релиз).

Это паттерн expand-contract — подробно в статье про миграции (третья волна).

Enum, boolean и перечисления

Перечислимые значения — статус заказа, тип уведомления, валюта, роль пользователя — в схеме можно описать тремя способами. Каждый имеет цену.

1. Boolean — это `boolean`

`PG-T-050` — Для двух значений «да/нет» — тип `boolean`. Не `smallint 0/1`, не `varchar('Y'/'N')`, не `char(1)` с `CHECK`

-- правильно
is_active   boolean NOT NULL DEFAULT true,
is_verified boolean NOT NULL DEFAULT false

-- неправильно — у каждого пять собственных «нет», и только тип boolean гарантирует одно
is_active   smallint NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
is_active   char(1)  NOT NULL DEFAULT 'Y' CHECK (is_active IN ('Y', 'N'))

Boolean занимает 1 байт, читается и пишется атомарно, не путает в SQL (WHERE is_active короче, чем WHERE is_active = 1), правильно мапится на Java boolean.

2. Три способа сделать перечисление

Допустим, у заказа есть статус: NEW, PAID, SHIPPED, DELIVERED, CANCELLED.

Вариант 1 — PG `ENUM`

CREATE TYPE order_status AS ENUM ('NEW', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED');

CREATE TABLE order_doc (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'NEW'
);

Плюсы:

Компактно (4 байта на значение).
Type-safety на уровне БД.
Хорошо смотрится в EXPLAIN, в WHERE status = 'PAID'.

Минусы:

Добавление нового значения дешёвое только в PG12+ (ALTER TYPE ... ADD VALUE). До 12 — пересоздание типа.
Удаление значения — нативно невозможно. Только пересоздание типа со всеми зависимостями (таблицы, default'ы, проверки) — большая миграция.
Изменение порядка значений — нативно невозможно, а порядок влияет на ORDER BY status.
Переименование значения возможно (ALTER TYPE ... RENAME VALUE), но в PG10+.
Между сервисами/репликами enum-ы должны совпадать — лишний источник миграционной координации.

Вариант 2 — reference table

CREATE TABLE order_status_dict (
    code        varchar(20) PRIMARY KEY,
    description text NOT NULL,
    sort_order  smallint NOT NULL,
    is_terminal boolean NOT NULL
);

INSERT INTO order_status_dict VALUES
    ('NEW',       'Создан',         10, false),
    ('PAID',      'Оплачен',        20, false),
    ('SHIPPED',   'Отправлен',      30, false),
    ('DELIVERED', 'Доставлен',      40, true),
    ('CANCELLED', 'Отменён',        50, true);

CREATE TABLE order_doc (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status varchar(20) NOT NULL REFERENCES order_status_dict(code) DEFAULT 'NEW'
);

Плюсы:

Добавление / переименование / удаление значений — обычные INSERT/UPDATE/DELETE. Миграция данных (UPDATE order SET status = 'CANCELLED' WHERE status = 'OLD_NAME') идёт без ALTER TYPE.
Дополнительные атрибуты (description, sort_order, is_terminal) — там же, рядом.
Удобно админке: справочник не требует деплоя.
Foreign key защищает от опечаток.

Минусы:

20 байт на значение (varchar(20)) против 4 у enum. Для редких таблиц — не важно.
При больших таблицах — лишний джойн на каждый SELECT. Часто решается денормализацией атрибутов в основную таблицу (если is_terminal нужен в каждом запросе — копировать).

Вариант 3 — `text` + `CHECK IN (...)`

CREATE TABLE order_doc (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status text NOT NULL DEFAULT 'NEW'
        CHECK (status IN ('NEW', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED'))
);

Плюсы:

Никаких типов. Миграция = ALTER TABLE ... DROP CONSTRAINT ... ADD CONSTRAINT ....
Не нужен справочник.

Минусы:

Нет foreign key, нельзя ссылаться из других таблиц по логике перечисления.
Дополнительные атрибуты (description, sort_order) надо тащить отдельно.
Большой CHECK IN (...) — некрасиво и не масштабируется (10+ значений выглядит плохо).

3. Когда что брать

`PG-T-051` — Правила выбора:

Случай	Выбор
Список значений известен, редко меняется, не нужны атрибуты	`ENUM`
Список значений может расти, изредка переименовываться, нужны атрибуты (порядок, описание, флаги)	reference table
Список валидируется только на уровне БД, простой, без атрибутов, до 5–7 значений	`CHECK IN (...)`
Часто меняется в админке	reference table

Практика:

Статусы доменных сущностей (order_status, payment_status) — обычно reference table. Они приобретают атрибуты со временем.
Технические перечисления (event_kind, notification_channel) — ENUM или CHECK IN.
Двузначные / трёхзначные коды (страна, валюта, язык) — отдельная reference table со стандартами (ISO 3166, 4217, 639).

4. Миграционные ловушки `ENUM`

`PG-M-020` — Добавление значения в enum (PG12+) — `ALTER TYPE ... ADD VALUE 'X'`. Делается мгновенно, но в отдельной транзакции

ALTER TYPE order_status ADD VALUE 'PARTIALLY_REFUNDED';

Важно: в той же транзакции, где добавили значение, его нельзя сразу использовать:

BEGIN;
ALTER TYPE order_status ADD VALUE 'PARTIALLY_REFUNDED';
INSERT INTO order_doc (status) VALUES ('PARTIALLY_REFUNDED');   -- ОШИБКА
COMMIT;

Это значит — миграция enum и миграция данных, использующих новое значение, должны быть в разных changeset'ах Liquibase.

`PG-M-021` — Переименование значения (PG10+):

ALTER TYPE order_status RENAME VALUE 'OLD_NAME' TO 'NEW_NAME';

`PG-M-022` — Удаление значения нативно невозможно. Замена enum реализуется так:

Создать новый тип (order_status_v2) с нужным набором значений.
Добавить временную колонку с новым типом.
Backfill: маппинг старых значений в новые.
Дропнуть старую колонку, переименовать новую.
Дропнуть старый тип.

Это две-три отдельные миграции с релизом приложения между ними. Дорого. Если предполагается, что значения будут уходить — берите reference table сразу.

5. Java-сторона: как маппить

`PG-T-052` — Для перечислений в Java — `enum`, не `String`

Для PG ENUM:

jOOQ умеет маппить — генерируется enum-класс по типу из БД.
Spring Data JPA тоже умеет (@Enumerated(EnumType.STRING)).

Для reference table / CHECK IN:

Колонка типа text/varchar, в Java мапится на свой enum через EnumType.STRING (или конвертер jOOQ).
Per feedback_jooq_only — вся persistence-логика через jOOQ-генерацию + явный конвертер enum ↔ text. Свой java-enum — единственное место, где значения перечисления зафиксированы в коде; БД выступает справочником.

6. Не путайте: status vs lifecycle

Не любое поле «один из набора» — это enum. Для статусов с переходами по правилам (NEW → PAID → SHIPPED, но не NEW → SHIPPED) одного типа недостаточно — нужна state machine в коде. Тип решает только «какие значения вообще валидны», переходы — отдельная логика. См. Уровень 1 UCP — пример state machine на статусах каталожной позиции.

JSONB — когда оправдан, когда нет

JSONB — мощный тип, который часто используют не по назначению: «сделаем jsonb, потом разберёмся». Через год это превращается в «реляционная БД с нетипизированной кашей внутри». Эта статья — про границу: где JSONB решает реальную задачу, где — анти-паттерн «всё равно мы пишем монгу».

1. `jsonb` vs `json`

`PG-T-060` — Всегда `jsonb`. `json` — почти никогда

	`json`	`jsonb`
Хранение	как текст, целиком	разобранное двоичное представление
Скорость чтения / поиска	медленно (парсинг при каждом обращении)	быстро (готовые ключи)
Сохраняет порядок ключей и пробелы	да	нет
Дубликаты ключей	сохраняет (последний выигрывает при чтении)	не сохраняет
Индексы (GIN)	нет	да

json оправдан только когда важно сохранить точное побайтовое представление документа — например, в журнале аудита, который должен быть бит-в-бит таким же, как пришёл. В 99% случаев нужен jsonb.

2. Главное правило: что класть в JSONB, а что в колонку

`PG-T-061` — Если по полю регулярно фильтруют, сортируют или джойнят — это колонка, не JSON-ключ

Контр-пример (плохо):

CREATE TABLE customer (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data jsonb NOT NULL
);

-- запросы:
SELECT * FROM customer WHERE data->>'email' = 'ivan@example.com';
SELECT * FROM customer WHERE data->>'status' = 'ACTIVE';
SELECT * FROM customer ORDER BY data->>'created_at' DESC;

Это не «гибкая схема», это «реляционная БД, которая делает вид, что она document store». Без специальных индексов — каждый запрос seq-scan по всем строкам.

Лучше:

CREATE TABLE customer (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      citext      NOT NULL UNIQUE,
    status     varchar(20) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata   jsonb       NOT NULL DEFAULT '{}'::jsonb     -- то, что действительно полиморфно
);

metadata — для редко-используемых, опциональных, специфичных под клиента/тип атрибутов. Email/status/created_at — колонки с индексами и типами.

3. Когда JSONB действительно оправдан

`PG-T-062` — Хорошие случаи:

Журнал аудита / содержимое события — пишем все поля события, не зная заранее, что в нём будет.

CREATE TABLE event_log (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    aggregate_id uuid       NOT NULL,
    event_type  varchar(50) NOT NULL,
    occurred_at timestamptz NOT NULL,
    payload     jsonb       NOT NULL    -- содержимое события — полиморфное по event_type
);

Полиморфные атрибуты товара — у обуви размер и материал, у электроники — мощность и тип разъёма. Ставить 200 nullable-колонок в product неудобно, делить на 50 таблиц — тоже.

CREATE TABLE product (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku           varchar(50) NOT NULL UNIQUE,
    name          text NOT NULL,
    category_code varchar(20) NOT NULL,
    price         numeric(15, 2) NOT NULL,
    attributes    jsonb NOT NULL DEFAULT '{}'::jsonb     -- {size: 42, material: 'leather'} / {power_w: 1500}
);

Конфиги и интеграционные настройки — каждый интеграционный канал имеет свой набор полей.

CREATE TABLE integration_channel (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kind    varchar(20) NOT NULL,    -- 'SMTP', 'TELEGRAM', 'WEBHOOK'
    config  jsonb NOT NULL           -- {host, port, username} / {bot_token, chat_id} / {url, secret}
);

Кеш-копии данных из внешних API — структура диктуется внешним сервисом, мы не можем её фиксировать колонками.

`PG-T-063` — Плохие случаи:

Все основные поля сущности в одном data jsonb («гибкая схема»).
Поле, по которому регулярно ищут/сортируют, лежит в JSONB.
JSONB используют, чтобы не писать миграции — добавили ключ в коде, БД не знает.

Последнее — самый коварный антипаттерн. Через полгода никто не помнит, какие ключи могут быть, какие обязательны, какие deprecated. Поверка схемы — на стороне приложения, и она расходится между сервисами.

4. Базовые операторы

`PG-T-064` — Запомнить четыре основных:

Оператор	Что делает	Возвращает
`->`	Доступ по ключу/индексу	`jsonb`
`->>`	Доступ по ключу/индексу	`text`
`#>`	Доступ по пути	`jsonb`
`#>>`	Доступ по пути	`text`

SELECT
    payload -> 'customer'             AS customer_obj,    -- jsonb
    payload ->> 'event_type'          AS type,            -- text
    payload #> '{order, items, 0}'    AS first_item,      -- jsonb
    payload #>> '{order, total}'      AS total_str        -- text
FROM event_log;

И операторы фильтрации:

-- содержит JSON (subset)
SELECT * FROM event_log WHERE payload @> '{"event_type": "ORDER_PAID"}'::jsonb;

-- содержит ключ
SELECT * FROM product WHERE attributes ? 'material';

-- содержит любой из ключей
SELECT * FROM product WHERE attributes ?| array['material', 'size'];

-- содержит все ключи
SELECT * FROM product WHERE attributes ?& array['material', 'size'];

5. Индексы по JSONB

`PG-T-065` — GIN-индекс с оператором `jsonb_path_ops` для поиска `@>`:

CREATE INDEX event_log_payload_gin
    ON event_log USING gin (payload jsonb_path_ops);

-- ускорит:
SELECT * FROM event_log WHERE payload @> '{"event_type": "ORDER_PAID"}';

jsonb_path_ops меньше и быстрее, чем дефолтный GIN, но поддерживает только @> (содержит). Для ? / ?| / ?& нужен дефолтный gin (column).

`PG-T-066` — Если запрос всегда по конкретному ключу — functional index, а не GIN

-- если регулярно ищем по 'event_type':
CREATE INDEX event_log_event_type
    ON event_log ((payload ->> 'event_type'));

SELECT * FROM event_log WHERE payload ->> 'event_type' = 'ORDER_PAID';

Functional index дешевле, чем GIN, и быстрее на конкретном ключе. GIN брать, когда фильтрация идёт по разным ключам или по содержимому вложенных объектов.

6. Размер и хранение

JSONB значения хранятся как «декомпозированное двоичное представление». Длинные значения уходят в TOAST (см. Строки).

`PG-T-067` — Не пишите в `jsonb` бинарь / большие тексты / base64

Для бинарей — bytea, для большого текста — text. JSONB не должен расти больше нескольких килобайт на запись.

7. Java-сторона: jOOQ + JSONB

jOOQ умеет маппить jsonb на JSONB (свой обёрточный тип) или на любой тип через converter. Типичная связка — Jackson:

@Component
public class JsonbConverter implements Converter<JSONB, JsonNode> {

    private final ObjectMapper mapper;

    @Override
    public JsonNode from(JSONB db) {
        try { return db == null ? null : mapper.readTree(db.data()); }
        catch (Exception e) { throw new IllegalStateException(e); }
    }

    @Override
    public JSONB to(JsonNode node) {
        try { return node == null ? null : JSONB.valueOf(mapper.writeValueAsString(node)); }
        catch (Exception e) { throw new IllegalStateException(e); }
    }
    // ...
}

Или жёсткий тип под конкретное содержимое JSONB:

public record EventPayload(UUID customerId, String eventType, Instant occurredAt, Map<String, Object> details) {}

Жёсткий тип возвращает контроль: видна структура, видно, что обязательно. Минус — нужно поддерживать Java-DTO синхронно с тем, что реально пишут в БД.

Массивы и range-типы

PostgreSQL умеет хранить в одной колонке несколько значений (массивы) и интервалы (range-типы). Это редкие, но иногда правильные инструменты — особенно когда альтернатива (отдельная таблица) усложняет схему ради формальности.

1. Массивы

CREATE TABLE article (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text   NOT NULL,
    tags  text[] NOT NULL DEFAULT '{}'
);

INSERT INTO article (title, tags) VALUES ('Заголовок', ARRAY['ddd', 'pg', 'архитектура']);

SELECT * FROM article WHERE 'pg' = ANY(tags);
SELECT * FROM article WHERE tags @> ARRAY['ddd'];     -- содержит все указанные
SELECT * FROM article WHERE tags && ARRAY['ddd'];     -- пересекается

Когда массив оправдан

`PG-T-070` — Массив уместен, когда:

Это простой набор скалярных значений (теги, разрешения, локали поддержки).
Размер ограничен небольшим числом элементов (десятки максимум).
Нет отдельной идентичности у элемента — никто не редактирует «третий тег», только заменяет весь набор.
Не нужно ссылаться на элементы из других таблиц.

Когда массив — антипаттерн

`PG-T-071` — Массив неуместен, когда:

У каждого элемента есть свои атрибуты — [{name, value, weight}, ...]. Это таблица, не массив.
Нужно ссылаться на конкретный элемент извне (article.tags[2] нестабильно при удалении).
Ожидается тысячи элементов на запись.
Элементы независимо обновляются разными запросами / транзакциями.

Контр-пример (плохо):

CREATE TABLE order_doc (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    items jsonb[]                -- массив из jsonb. Заводов нет, статусов нет, валидации нет.
);

Лучше:

CREATE TABLE order_doc (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE order_item (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id  bigint NOT NULL REFERENCES order_doc(id) ON DELETE CASCADE,
    sku       varchar(50) NOT NULL,
    quantity  integer NOT NULL CHECK (quantity > 0),
    price     numeric(15, 2) NOT NULL
);

GIN-индекс для массивов

CREATE INDEX article_tags_gin ON article USING gin (tags);

-- ускорит:
SELECT * FROM article WHERE tags @> ARRAY['ddd'];
SELECT * FROM article WHERE tags && ARRAY['ddd', 'pg'];
SELECT * FROM article WHERE 'ddd' = ANY(tags);

Java-сторона

jOOQ генерирует String[] / Integer[] для array-колонок. Можно прокидывать как List<String> через converter — обычно это удобнее в коде.

2. Range-типы

PostgreSQL имеет встроенные range-типы:

Тип	Содержит
`int4range`	интервал `integer`
`int8range`	интервал `bigint`
`numrange`	интервал `numeric`
`daterange`	интервал `date`
`tsrange`	интервал `timestamp` (без TZ)
`tstzrange`	интервал `timestamptz`

`PG-T-072` — Range-тип оправдан, когда сущность сама по себе — это интервал

Хорошие случаи:

Период действия тарифа / промокода / роли (с какого по какое действует).
Бронирование (дата-время заезда — выезда).
Цена с историей (когда стоила эту цену).
Возрастные ограничения (int4range(18, 65)).

Сравните «двух колонок» с «одной range-колонкой»:

-- две колонки
CREATE TABLE tariff_v1 (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    valid_from  timestamptz NOT NULL,
    valid_to    timestamptz                                       -- NULL = без ограничения
);

-- range
CREATE TABLE tariff_v2 (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    validity    tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)')
);

Что даёт range-тип

Атомарная семантика интервала — один тип со встроенным включающим/исключающим краем ([), (], [], ()).
Операторы пересечения:

-- две колонки — много кода и легко ошибиться:
SELECT * FROM tariff_v1
WHERE valid_from < '2026-05-07T12:00:00+00'
  AND (valid_to IS NULL OR valid_to > '2026-05-07T12:00:00+00');

-- range — кратко и однозначно:
SELECT * FROM tariff_v2
WHERE validity @> timestamptz '2026-05-07T12:00:00+00';

Оператор	Что делает
`@>`	range содержит точку или другой range
`&&`	два range пересекаются
`-\|-`	соприкасаются (соседние)
`<<` / `>>`	строго слева / справа

EXCLUDE constraint — гарантия непересечения (фундамент для бронирований).

`EXCLUDE` для непересечений

Это уникальная возможность PostgreSQL. На «двух колонках» сделать «брони не пересекаются» — только триггерами с риском состояния гонки. На range — одной декларацией:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id   bigint NOT NULL,
    period    tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

INSERT INTO booking (room_id, period) VALUES (1, '[2026-05-07 14:00, 2026-05-09 12:00)');
INSERT INTO booking (room_id, period) VALUES (1, '[2026-05-08 10:00, 2026-05-10 12:00)');
-- ERROR: conflicting key value violates exclusion constraint

`PG-T-073` — Если бизнес-задача — «эти интервалы для одного ключа не пересекаются», используем `EXCLUDE` constraint, а не приложение

Альтернатива на стороне приложения — гонка и двойная бронь при параллельных запросах. Ограничение на уровне БД защищает даже при одновременных вставках.

Открытые/закрытые края

Граничный синтаксис критичен:

[a, b) — a включён, b исключён (стандарт для дат-интервалов: с 1 марта по 31 марта = [2026-03-01, 2026-04-01)).
[a, b] — оба включены.
(a, b) — оба исключены.
[a, +infinity) — без верхней границы.

`PG-T-074` — По умолчанию используем `[)` — это даёт корректное `&&` без перекрытия концов

Иначе бронь «с 14:00 до 16:00» и «с 16:00 до 18:00» считаются пересекающимися.

Multirange (PG14+)

PG14 ввёл multirange — массив непересекающихся range:

CREATE TABLE schedule (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    avail  tstzmultirange NOT NULL    -- массив периодов доступности
);

Полезно для расписаний с несколькими окнами.

Java-сторона

jOOQ генерирует Range<T> (свой тип). Удобно обернуть в Java-record:

public record ValidityPeriod(Instant from, Instant to) {
    public boolean contains(Instant t) { return !t.isBefore(from) && t.isBefore(to); }
}

И converter Range<OffsetDateTime> ↔ ValidityPeriod.

Именование объектов в PostgreSQL

Конвенции именования — самая дешёвая и недооценённая дисциплина. Они не дают ускорения, но через два года команда легко читает чужой код, а DBA не путается в EXPLAIN. Эта статья — что назвать как и почему.

Правила пронумерованы кодами PG-N-NNN — на них ссылается скилл ucp-pg-schema-review.

1. Регистр и стиль

`PG-N-001` — `snake_case` для всего: таблиц, колонок, индексов, constraint'ов, sequence'ов, view'ов

-- правильно
CREATE TABLE order_doc (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now()
);

-- неправильно
CREATE TABLE OrderDoc (
    Id          bigint,
    customerId  bigint,
    "CreatedAt" timestamptz
);

`PG-N-002` — Никаких кавычек в идентификаторах

"OrderDoc" (с двойными кавычками) принуждает PG сохранить регистр и требует кавычек в каждом запросе. order_doc без кавычек — case-insensitive, удобно. Кавычки нужны только для конфликтных слов (см. §6).

2. Таблицы

`PG-N-010` — Существительные в единственном числе:

order_doc, customer, product, payment, не orders/customers/products.

Аргумент: ОДНА строка — это ОДНА сущность (SELECT * FROM customer WHERE id = 1 возвращает одного клиента). jOOQ-генерация даёт Customer класс из customer таблицы — натуральнее Orders из orders.

Это спорная конвенция (Hibernate/JPA-сообщество чаще предпочитает plural). Главное правило — выбрать одно и не смешивать. Если 80% таблиц singular, новые тоже singular.

`PG-N-011` — Junction-таблицы (many-to-many) — обе сущности в порядке:

order_item, customer_role, product_tag.

`PG-N-012` — Префикс домена для крупных схем

Если в одной БД живут несколько Bounded Context'ов — префикс схемы (order_*, catalog_*) или отдельный schema (order.doc, catalog.product). Без префиксов 200 таблиц одного public нечитаемо.

3. Колонки

`PG-N-020` — id-колонка таблицы — `id`. Не `customer_id` в таблице `customer`

CREATE TABLE customer (
    id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY  -- не customer_id
);

В FK уже видно из имени родителя: order.customer_id REFERENCES customer(id). Удвоение префикса избыточно.

`PG-N-021` — Foreign key column — `<parent>_id`

customer_id, order_id, product_id. Это автоматически документирует таблицу-родителя.

`PG-N-022` — Boolean-колонки — с префиксом `is_`/`has_`/`can_`

is_active   boolean NOT NULL DEFAULT true,
is_deleted  boolean NOT NULL DEFAULT false,
has_avatar  boolean NOT NULL DEFAULT false,
can_login   boolean NOT NULL DEFAULT true

Без префикса active boolean ↔ active enum трудно отличить в коде. is_active всегда читается как «да/нет».

`PG-N-023` — Время — глагол прошедшего времени + `_at` для timestamp, без — для дат:

created_at   timestamptz   -- момент создания
updated_at   timestamptz   -- последнее обновление
deleted_at   timestamptz   -- soft-delete
expires_at   timestamptz   -- момент истечения
published_at timestamptz   -- момент публикации (NULL = не опубликовано)
born_on      date          -- дата рождения (без времени)
hired_on     date

`PG-N-024` — Денежные колонки — `_amount` суффикс для сумм, `_price` для цен, `_rate` для курсов/процентов

total_amount      numeric(15, 2)  -- общая сумма
shipping_price    numeric(15, 2)  -- цена доставки
discount_rate     numeric(5, 4)   -- 0.1500 = 15%
exchange_rate     numeric(20, 8)  -- курс валют

`PG-N-025` — Длительность — `_seconds`/`_ms`/`_days`/`_hours` суффикс с явной единицей

ttl_seconds       integer
delivery_days     integer
session_timeout_ms bigint

Без явной единицы измерения через год никто не помнит, в чём delivery_time — секунды, минуты или дни.

`PG-N-026` — Перечисления — без префикса/суффикса:

status, type, kind, role, currency. В Java мапится на одноимённый enum.

`PG-N-027` — Опционально размер коллекции — `_count` суффикс:

view_count    integer NOT NULL DEFAULT 0
likes_count   integer NOT NULL DEFAULT 0
items_count   integer NOT NULL DEFAULT 0

4. Audit-колонки

`PG-N-030` — Стандартный набор для каждой бизнес-таблицы:

created_at   timestamptz NOT NULL DEFAULT now(),
updated_at   timestamptz NOT NULL DEFAULT now(),
created_by   bigint REFERENCES customer(id),    -- или uuid, если auth-user — UUID
updated_by   bigint REFERENCES customer(id),
version      bigint NOT NULL DEFAULT 0          -- для optimistic locking

Не обязательно все четыре сразу, но имена должны быть стандартными. version — для optimistic locking, created_by/updated_by — для аудита.

`PG-N-031` — Soft-delete — `deleted_at timestamptz` (NULL = не удалено), не `is_deleted boolean`

Время удаления — отдельная информация, которая часто нужна. is_deleted означает «когда-то удалили, момент потеряли».

-- хорошо
deleted_at  timestamptz  -- NULL = существует, иначе момент удаления

-- плохо
is_deleted  boolean      -- момент удаления потерян

Из deleted_at тривиально получить boolean: WHERE deleted_at IS NULL.

5. Индексы и constraints

`PG-N-040` — Префикс по типу — `ix_` / `uk_` / `ck_` / `fk_` + таблица + колонки:

Тип	Префикс	Пример
Обычный индекс	`ix_`	`ix_orders_customer_id`, `ix_orders_status_created_at`
Уникальный	`uk_`	`uk_customer_email`, `uk_product_sku`
Foreign key	`fk_`	`fk_order_item_order_id`, `fk_order_customer_id`
Check	`ck_`	`ck_order_total_positive`, `ck_age_range`
Primary key	`pk_`	`pk_customer` (обычно автоматически `<table>_pkey`)
Trigger	`tr_`	`tr_order_doc_audit`

`PG-N-041` — Индекс на одну колонку:

ix_<table>_<column>.

CREATE INDEX ix_order_customer_id ON order_doc (customer_id);

`PG-N-042` — Composite-индекс на несколько:

ix_<table>_<col1>_<col2> в порядке полей в индексе.

CREATE INDEX ix_order_status_created_at ON order_doc (status, created_at);

`PG-N-043` — Functional index — суффикс с операцией:

CREATE INDEX ix_account_email_lower ON account ((lower(email)));
CREATE INDEX ix_event_log_payload_gin ON event_log USING gin (payload jsonb_path_ops);

`PG-N-044` — Partial index — суффикс c фильтром:

CREATE INDEX ix_order_active_customer ON order_doc (customer_id) WHERE status IN ('NEW','PAID','SHIPPED');
-- альтернатива: ix_order_customer_active

`PG-N-045` — Constraint name на foreign key — `fk_<child>_<column>`

ALTER TABLE order_item
  ADD CONSTRAINT fk_order_item_order_id
  FOREIGN KEY (order_id) REFERENCES order_doc(id);

Без явного имени PG генерит order_item_order_id_fkey — длиннее и менее читаемо в логах ошибок.

`PG-N-046` — Check constraint — `ck_<table>_<rule>`:

CHECK (total_amount >= 0)                     -- автоген имени, плохо
CONSTRAINT ck_order_total_positive CHECK (total_amount >= 0)  -- хорошо
CONSTRAINT ck_product_stock_non_negative CHECK (stock >= 0)

В сообщении об ошибке CHECK constraint violated: ck_order_total_positive сразу понятно, какое правило сломано.

6. Зарезервированные слова

`PG-N-050` — Не используй имена-зарезервированные слова PG:

user, order, group, type, position, value, name, default, desc, asc, start, end, class.

Если бизнес-домен — «пользователь», бери customer/account/person. Если «заказ» — order_doc/purchase/shipment. Иначе каждый запрос — SELECT * FROM "user" с кавычками или магической ошибкой синтаксиса.

`PG-N-051` — `type`/`name`/`value` как имена колонок допустимы

, но используй с осторожностью. PG их не считает зарезервированными в контексте колонки, но в Java type/name могут конфликтовать с reflection / Jackson.

Полный список ключевых слов: SELECT * FROM pg_get_keywords() WHERE catcode IN ('R', 'T').

7. Длина имени и читаемость

`PG-N-060` — Лимит PostgreSQL — 63 символа

(NAMEDATALEN - 1). PG молча обрежет более длинное имя, что приводит к дубликатам (fk_very_long_table_referencing_another_long_table_some_column_id → коллизия).

`PG-N-061` — Целевая длина — до 30 символов

Имена в EXPLAIN, в логах ошибок, в Java-генерации (jOOQ создаст IxOrdersStatusCreatedAt класс — короткое имя проще читать).

`PG-N-062` — Сокращения — единые по проекту

Если решили usr вместо user — везде usr. Mix user/usr/account за год превращается в кашу.

8. Sequences и serial

`PG-N-070` — `GENERATED ALWAYS AS IDENTITY` сам генерирует sequence с именем `<table>_<column>_seq`

Не вмешивайся в это.

CREATE TABLE customer (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
-- автоматически: customer_id_seq

Использовать sequence напрямую — антипаттерн. nextval('customer_id_seq') пишут только при ручном импорте данных. В обычных вставках — INSERT INTO customer (name) VALUES (?) без id.

9. View и materialized view

`PG-N-080` — View — суффикс `_v` или префикс `v_`. Materialized view — `_mv`

CREATE VIEW customer_active_v AS SELECT * FROM customer WHERE deleted_at IS NULL;
CREATE MATERIALIZED VIEW order_stats_mv AS SELECT customer_id, count(*), sum(total_amount) FROM order_doc GROUP BY customer_id;

Не обязательно, но помогает в EXPLAIN сразу понять, что это не базовая таблица.

10. Анти-паттерны

PG-N-090 tbl_/t_ префикс на таблицах — избыточно (PG и так знает, что это таблица).

PG-N-091 <column>_<datatype> суффикс — name_varchar, created_timestamp — мусор. Тип в DDL, имя — про смысл.

PG-N-092 Зарезервированные имена в кавычках — "user", "order". Лечение — переименовать.

PG-N-093 deleted boolean без _at — теряется момент.

PG-N-094 data / info / details jsonb для основной модели — что в нём, никто не помнит. Имя должно говорить о содержимом: attributes, payload, metadata, config, extra.

PG-N-095 Сокращения по вкусу разработчика — cust_id, usr_nm, cnt. Полные имена короче не делают, читаются хуже.

PG-N-096 Регистр в идентификаторах в кавычках — "OrderDoc". Каждый запрос требует кавычек, мix с lowercase создаёт два разных объекта в одном пространстве.

Типы индексов в PostgreSQL

Когда говорят «индекс» — обычно подразумевают B-tree. Но PostgreSQL поддерживает шесть разных типов, и на специфических задачах правильный выбор сокращает запрос с минут до миллисекунд. Эта статья — про то, какой брать в каком сценарии.

1. Шесть типов

Тип	Когда брать	Что внутри
B-tree	По умолчанию: `=`, `<`, `>`, `BETWEEN`, `LIKE 'x%'`, `ORDER BY`	Сбалансированное дерево
Hash	Только `=` для крупных значений; почти всегда уступает B-tree	Хэш-таблица
GIN	Полнотекстовый поиск, JSONB, массивы, hstore	Inverted index
GiST	Геометрия (`PostGIS`), range-типы, kNN-поиск, EXCLUDE	Сбалансированное дерево по обобщённой метрике
BRIN	Очень большие таблицы с естественной упорядоченностью (временные ряды)	Block range — индекс по диапазонам страниц
SP-GiST	Неравномерные данные (фрактальные деревья, IP-префиксы)	Space-partitioned tree

2. B-tree — дефолт

`PG-I-020` — `CREATE INDEX` без указания типа = B-tree. Это правильный выбор в 80–90% случаев

CREATE INDEX ix_orders_created_at ON orders (created_at);   -- по умолчанию B-tree
CREATE INDEX ix_orders_created_at ON orders USING btree (created_at);  -- то же самое явно

B-tree поддерживает:

Все операторы сравнения: =, <, <=, >, >=, BETWEEN, IN.
LIKE 'prefix%' — префиксный поиск (но не LIKE '%suffix').
IS NULL / IS NOT NULL.
ORDER BY (включая DESC через index scan backward).
Уникальность (UNIQUE INDEX) и PRIMARY KEY.
Composite-индексы (см. Composite-индексы).

Для абсолютного большинства запросов на типичной CRUD/доменной задаче B-tree — единственный нужный тип.

3. Hash — почти никогда

`PG-I-021` — Hash-индекс почти всегда хуже B-tree. Не берите без явной причины

CREATE INDEX ix_account_email_hash ON account USING hash (email);

Теоретически hash быстрее B-tree на чистом = для очень длинных значений (когда сравнение строк дороже, чем хэш). На практике:

B-tree поддерживает не только =, но и сортировку, диапазоны, IS NULL.
Размер hash и B-tree сравнимы.
Производительность практически одинаковая.
До PG10 hash вообще не журналировался в WAL — был непригоден для прода.

Берите только при очень специфических нагрузочных тестах, которые показали выигрыш.

4. GIN — для составных значений

`PG-I-022` — GIN — единственный нормальный индекс для JSONB, массивов, полнотекстового поиска

-- JSONB
CREATE INDEX ix_event_payload ON event_log USING gin (payload jsonb_path_ops);

-- Массивы
CREATE INDEX ix_article_tags ON article USING gin (tags);

-- Полнотекстовый поиск
CREATE INDEX ix_post_search ON post USING gin (to_tsvector('russian', body));

Особенности:

Inverted index — для каждого «токена» (ключа JSONB / элемента массива / слова текста) хранится список строк, где он встречается.
Запись медленнее, чем B-tree — на каждое обновление надо разнести изменения по многим строкам индекса.
fastupdate — буфер pending-list ускоряет вставки за счёт замедления чтения, пока буфер не сольётся в основной индекс. По умолчанию включён.
Чтение быстрое — найти все строки с ключом «X» в JSONB за 1 lookup.

Подробнее по операторам в JSONB.

5. GiST — обобщённый индекс для нестандартных данных

`PG-I-023` — GiST — для range-типов, геометрии (PostGIS), полнотекста, kNN-поиска

-- range-типы (см. /pg/arrays-ranges/)
CREATE EXTENSION btree_gist;
CREATE TABLE booking (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

-- геометрия
CREATE INDEX ix_shop_location ON shop USING gist (location);   -- PostGIS
SELECT * FROM shop ORDER BY location <-> ST_Point(37.6, 55.7) LIMIT 10;

-- полнотекстовый — GIN обычно лучше, но GiST даёт ranking «на лету»
CREATE INDEX ix_post_search_gist ON post USING gist (to_tsvector('russian', body));

Особенности:

Сбалансированное дерево по обобщённой метрике — каждый узел описывает «что-то общее» для своих потомков.
Поддерживает kNN — ORDER BY x <-> point находит ближайших соседей через индекс.
EXCLUDE constraint — гарантирует непересечение range, geometry, etc.
Чтение медленнее GIN на типичных задачах JSONB/array, но GiST универсальнее.

Правило выбора между GIN и GiST для текста / массивов / JSONB:

GIN — быстрее на чтение, медленнее на запись, больший размер. Берём, когда таблица читается чаще, чем пишется.
GiST — быстрее на запись, медленнее на чтение, меньший размер. Берём при интенсивных вставках.

6. BRIN — для огромных таблиц с упорядоченностью

`PG-I-024` — BRIN оправдан на таблицах от десятков миллионов строк, где данные физически упорядочены по индексируемой колонке

Типичный сценарий — таблица событий / логов с timestamptz-колонкой, в которую только дописывают (append-only):

CREATE INDEX ix_event_log_at_brin ON event_log USING brin (occurred_at);

Особенности:

Block range index — хранит min/max значения для каждого диапазона страниц (по умолчанию 128 страниц).
Размер крошечный — десятки KB на таблицу в гигабайты, против гигабайтов у B-tree.
Запросы по диапазону работают быстро: «дай события за последний час» — BRIN сразу отбросит блоки, где max < now()-interval '1 hour'.
Точечный поиск (= ?) — не ускоряет.
Эффективен только при физической упорядоченности. Если строки вставляются не по возрастанию occurred_at, BRIN деградирует — таблица периодически нуждается в CLUSTER по этой колонке.

Подходит для:

Логи и события с автоматическим возрастающим timestamp.
Метрики (timestamp + значение).
Архивные таблицы с партиционированием по датам.

Не подходит для:

Обычных доменных таблиц с обновлениями.
Поиска по = в большой таблице (B-tree или GIN).

7. SP-GiST — редко

Space-partitioned GiST для неравномерно распределённых данных: префиксные деревья (IP-адреса, URL-пути), фрактальные структуры. Берётся, когда профайлер показывает, что обычный B-tree / GiST даёт плохую балансировку.

CREATE INDEX ix_request_url ON request USING spgist (url);
CREATE INDEX ix_visit_ip ON visit USING spgist (ip);   -- inet

В типичном бэкенде встречается крайне редко.

8. Сравнительная таблица: какой тип под какую задачу

Задача	Индекс
Поиск по `=` / `<` / `>` / `BETWEEN` обычной колонки	B-tree
Сортировка `ORDER BY`	B-tree
`LIKE 'prefix%'`	B-tree
`LIKE '%substring%'`	GIN с `pg_trgm`
Foreign key	B-tree
`UNIQUE` constraint	B-tree
Поиск по полю JSONB (`@>`, `?`)	GIN
Поиск элемента в массиве	GIN
Полнотекстовый поиск	GIN (если читаем чаще, чем пишем) или GiST
Геометрия (PostGIS), kNN	GiST
Range-типы (`tstzrange`, `int4range`)	GiST
`EXCLUDE` для непересечений	GiST + `btree_gist`
Огромная append-only таблица с timestamp	BRIN
IP-адреса с поиском по префиксу	SP-GiST
Точечный `=` по очень длинной строке	B-tree (Hash почти никогда не лучше)

9. `pg_trgm` — отдельно про `LIKE '%X%'`

`PG-I-025` — Для `LIKE '%substring%'` нужен GIN-индекс с расширением `pg_trgm`

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ix_customer_name_trgm
    ON customer USING gin (full_name gin_trgm_ops);

-- ускоряет:
SELECT * FROM customer WHERE full_name ILIKE '%иван%';
SELECT * FROM customer WHERE similarity(full_name, 'иванв') > 0.4;

pg_trgm разбивает строку на триграммы (тройки символов), индекс находит строки с пересечением триграмм. Полезно для нечёткого поиска, когда полнотекстовый избыточен.

10. Partial index — фильтр на индекс

`PG-I-026` — Если индекс по факту нужен только для подмножества строк — `WHERE`-условие в индексе

-- индексируем только активные заказы (90% таблицы — завершённые, не нужны)
CREATE INDEX ix_orders_active_customer
    ON orders (customer_id)
    WHERE status IN ('NEW', 'PAID', 'SHIPPED');

-- запрос должен включать то же условие, чтобы планировщик использовал индекс:
SELECT * FROM orders
WHERE customer_id = ?
  AND status IN ('NEW', 'PAID', 'SHIPPED');

Размер индекса — в разы меньше. Запись быстрее (индекс не обновляется на «не подходящих» строках).

Применимо к любому типу индекса (B-tree, GIN, GiST).

Composite-индексы и левый префикс

Самая частая ошибка с индексами — не «индекса нет», а «индекс есть, но не работает в этом запросе». Причина почти всегда — порядок полей в составном индексе.

1. Главное правило: левый префикс

Составной B-tree-индекс (a, b, c) физически отсортирован по a, внутри каждого значения a — по b, внутри каждого b — по c. Это телефонная книга по фамилии-имени-отчеству.

`PG-I-010` — Индекс работает только на левый префикс полей

Индекс (a, b, c):

Запрос	Использует индекс?
`WHERE a = ?`	да, эффективно
`WHERE a = ? AND b = ?`	да
`WHERE a = ? AND b = ? AND c = ?`	да
`WHERE a = ? AND c = ?`	по `a` — да, `c` доводит как `Filter`
`WHERE b = ?`	нет (или полный проход индекса)
`WHERE c = ?`	нет (или полный проход индекса)
`WHERE b = ? AND c = ?`	нет

Для WHERE b = ? или WHERE c = ? планировщик обычно выберет Seq Scan — индекс не подходит. В редких случаях — Index Only Scan, но это будет полный проход индекса (см. ниже).

2. Порядок написания в `WHERE` не важен

`PG-I-011` — Оптимизатор сам переставляет условия `WHERE`. Порядок в SQL — косметика

-- индекс (a, b, c)
WHERE a = 1 AND b = 2 AND c = 3   -- использует индекс
WHERE c = 3 AND b = 2 AND a = 1   -- использует индекс точно так же
WHERE b = 2 AND a = 1             -- использует индекс (как WHERE a = 1 AND b = 2)

В EXPLAIN:

Index Cond: ((a = 1) AND (b = 2) AND (c = 3))    -- ушли в индекс
Filter:     (c = 3)                                -- осталось пост-фильтром

Index Cond — то, что используется деревом для поиска. Filter — то, что проверяется уже после чтения строк.

3. Правила выбора порядка полей

`PG-I-012` — Первым ставим поле, по которому чаще всего идёт `WHERE` с равенством (`=`)

Это даёт максимум селективности через дерево.

`PG-I-013` — Поля с диапазонами (`>`, `<`, `BETWEEN`, `LIKE 'x%'`) — последними

После range-условия следующие поля индекса перестают использоваться как ключ:

CREATE INDEX ix_orders_status_created ON orders (status, created_at);

-- хорошо: status = (равенство) → created_at > (диапазон)
WHERE status = 'NEW' AND created_at > now() - interval '1 day'
-- Index Cond: (status = 'NEW') AND (created_at > ...)

CREATE INDEX ix_orders_created_status ON orders (created_at, status);
-- плохо: range по created_at отрубает использование status
WHERE status = 'NEW' AND created_at > now() - interval '1 day'
-- Index Cond: (created_at > ...)
-- Filter:     (status = 'NEW')   ← seq-фильтр после range-сканирования

`PG-I-014` — `ORDER BY` — порядок и направление полей должны совпадать с индексом, иначе сортировка делается отдельно

CREATE INDEX ix_msg_user_at ON messages (user_id, created_at DESC);

-- индекс используется и для фильтра, и для сортировки:
SELECT * FROM messages WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;

-- сортировка не «бесплатная»: индекс ASC по created_at, запрос DESC:
CREATE INDEX ix_msg_user_at_asc ON messages (user_id, created_at);
SELECT * FROM messages WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;
-- Index Scan Backward — работает, но только если все ORDER BY-поля идут в одной развертке.

4. Селективность — вторично

«Самое селективное поле — первым» — частый совет, который вводит в заблуждение. Правильнее: первым то, по чему чаще всего идёт =, и только потом — выбор по селективности среди равно-частых.

Пример: на orders (status, created_at, customer_id):

по status — равенство, низкая селективность (всего 5 значений), но фильтр почти в каждом запросе.
по customer_id — равенство, высокая селективность, но реже в WHERE.

Если 90% запросов — WHERE status = 'NEW' AND created_at > ? и только 10% — WHERE customer_id = ? — индекс (status, created_at) плюс отдельный (customer_id) лучше, чем один сложный (customer_id, status, created_at).

5. Дублирующиеся индексы

`PG-I-015` — Не создавайте `(a)`, если уже есть `(a, b, c)` — это покрывает запросы по `a`

Дубликаты:

занимают место;
замедляют INSERT/UPDATE/DELETE (каждая запись обновляет каждый индекс);
путают планировщика — он может выбрать неоптимальный.

Проверка:

SELECT indexrelname, indrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_index
JOIN pg_stat_user_indexes USING (indexrelid)
ORDER BY indrelid, indkey;

Глазами пройтись по группам с одинаковым indrelid и общим префиксом indkey — кандидаты на удаление.

6. Ловушка с `count(1)` и `Index Only Scan`

Запрос:

SELECT count(1) FROM orders WHERE created_at > now() - interval '7 days';

И индекс (status, created_at). По правилу левого префикса этот индекс не должен помочь — created_at второе поле, нет status в WHERE.

Тем не менее в EXPLAIN вы увидите:

Index Only Scan using ix_orders_status_created on orders
  Filter: (created_at > '2026-04-30')
  Rows Removed by Filter: 8420134
  Heap Fetches: 0

`PG-E-010` — Это не «индекс работает». Это `Index Only Scan` — планировщик выбрал полный проход индекса, потому что:

count(1) не требует данных из таблицы — нужен только факт строки.
Индекс содержит обе колонки (status, created_at) — можно ответить на запрос, не трогая heap.
Полный проход индекса дешевле полного Seq Scan по таблице (индекс физически меньше).

Признаки в плане:

Filter: (не Index Cond:) по основному фильтрующему полю — индекс не используется деревом.
Heap Fetches: 0 — таблица не читалась.
Rows Removed by Filter — большое число, потому что просканировали весь индекс.

`PG-E-011` — Если на этом запросе нужна реальная скорость — нужен индекс по `created_at` (или `(created_at, status)`)

Тогда план превратится в Index Scan с Index Cond: (created_at > ...) — будут читаться только нужные страницы индекса, а не все.

`PG-E-012` — Если `Heap Fetches > 0` на `Index Only Scan` — устаревший visibility map. Помогает `VACUUM`

Без актуального VM Index Only Scan превращается в обычный Index Scan с дёрганьем таблицы — может оказаться медленнее Seq Scan.

7. Покрывающий индекс (`INCLUDE`)

`PG-I-016` — Если по индексу часто читают одни и те же дополнительные колонки — `INCLUDE` ускоряет, не раздувая ключ

CREATE INDEX ix_orders_customer_inc
    ON orders (customer_id) INCLUDE (status, created_at, total);

-- запрос ниже выполняется как Index Only Scan, без обращения к таблице:
SELECT customer_id, status, created_at, total
FROM orders
WHERE customer_id = ?;

Колонки в INCLUDE не участвуют в дереве (не влияют на сортировку), но хранятся в листьях. Полезно для отчётов и денормализованных view.

8. Индексы на foreign key

`PG-I-017` — PostgreSQL не строит индекс по FK автоматически. Если по FK идут джойны / удаления родителя — индекс нужен

Без индекса: DELETE FROM parent WHERE id = ? запускает seq-scan по child-таблице, чтобы проверить ON DELETE правило. На большой child — это секунды-минуты блокировки.

CREATE TABLE order_item (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES order_doc(id) ON DELETE CASCADE
);

CREATE INDEX ix_order_item_order ON order_item (order_id);

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

`PG-I-018` — Если регулярно фильтруют по выражению (`LOWER(...)`, `COALESCE(...)`, `EXTRACT(...)`) — функциональный индекс

CREATE INDEX ix_account_email_lower ON account (lower(email));

-- запросы должны точно совпадать с выражением индекса:
SELECT * FROM account WHERE lower(email) = lower('IVAN@EXAMPLE.COM');

Альтернатива — citext (см. Строки).

10. Создание индекса в продакшене — `CONCURRENTLY`

`PG-I-019` — В продакшен-миграциях — всегда `CREATE INDEX CONCURRENTLY` и `DROP INDEX CONCURRENTLY`

Без CONCURRENTLY CREATE INDEX берёт SHARE lock на таблицу — блокирует все INSERT/UPDATE/DELETE до конца построения. На больших таблицах — десятки минут даунтайма.

CONCURRENTLY строит индекс в два прохода без блокировки записи. Минусы:

нельзя в транзакции (Liquibase: runInTransaction="false");
если индекс сломался при построении (INVALID), нужно явно дропнуть и повторить;
занимает в 2–3 раза больше времени, чем без CONCURRENTLY (но не блокирует).

Подробно — в статье про миграции (третья волна).

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

«Индекс есть, но запрос всё равно медленный» — почти всегда история про селективность. PostgreSQL не использует индекс, если считает, что seq-scan дешевле. Эта статья — как считать селективность, читать статистику и понимать решения планировщика.

1. Что такое селективность

`PG-I-030` — Селективность колонки = доля разных значений в таблице. Чем выше, тем эффективнее индекс по этой колонке

Формально: selectivity = distinct_values / total_rows.

Примеры:

Колонка	distinct	total	selectivity
`id` (PK)	1 000 000	1 000 000	1.0 — идеально
`email`	998 000	1 000 000	0.998 — почти идеально
`customer_id`	50 000	1 000 000	0.05 — средне
`status` (5 значений)	5	1 000 000	0.000005 — низкая
`is_deleted`	2	1 000 000	0.000002 — почти нулевая

`PG-I-031` — Индекс на низкоселективной колонке (`is_deleted`, `status`, `country` в одной стране) бесполезен

Планировщик увидит, что условие WHERE is_deleted = false отдаст 98% таблицы — выгоднее прочитать всю таблицу одним проходом, чем 98% случайных IO через индекс.

2. Как посмотреть селективность

SELECT
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    null_frac
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders';

Что важно:

n_distinct — оценка количества уникальных значений. Если положительное — это абсолютное число. Если отрицательное (например, -0.05) — это доля от строк (-0.05 = 5% уникальных, т.е. 50К на 1M).
most_common_vals — массив самых частых значений.
most_common_freqs — частоты этих значений (доли от 0 до 1).
null_frac — доля NULL.

Пример:

attname           | status
n_distinct        | 5
most_common_vals  | {DELIVERED,SHIPPED,NEW,CANCELLED,PAID}
most_common_freqs | {0.62, 0.18, 0.10, 0.06, 0.04}
null_frac         | 0

Видно: 62% строк — DELIVERED. Запрос WHERE status = 'DELIVERED' — индекс не поможет, планировщик пойдёт seq-scan. А вот WHERE status = 'PAID' (4% строк) — индекс может зайти.

3. Когда индекс точно поможет

`PG-I-032` — Запрос с равенством, отдающий < 5–10% таблицы, обычно идёт через индекс

Доля строк по условию	Индекс?
`< 1%`	Да, точно
`1–5%`	Скорее всего да
`5–20%`	Зависит от размера строки и кеша
`> 20%`	Скорее всего нет (seq-scan дешевле)
`> 50%`	Точно нет

Точные пороги определяются:

Размером строки (узкие строки → больше fits в один heap-блок → seq-scan дешевле).
effective_cache_size (косвенно — сколько данных PG считает в кеше).
Стоимостью random vs sequential IO (random_page_cost vs seq_page_cost).

`PG-I-033` — На SSD имеет смысл снизить `random_page_cost` с дефолтных 4.0 до 1.1

Дефолт рассчитан под HDD и часто заставляет планировщик предпочесть seq-scan, когда индекс действительно быстрее.

ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

4. Композитный индекс и селективность

Для индекса (a, b, c) селективность считается по левому префиксу:

WHERE a = ? — селективность колонки a.
WHERE a = ? AND b = ? — комбинированная селективность a × b (приближённо).

Пример: (status, created_at) на таблице 1M заказов.

WHERE status = 'DELIVERED' — 62% строк → индекс не пойдёт.
WHERE status = 'DELIVERED' AND created_at > now() - interval '1 day' — 62% × 0.5% = 0.3% → индекс пойдёт идеально.

`PG-I-034` — Композитный индекс может ожить там, где single-column бесполезен

Низкоселективное status сужает пространство только до 62% таблицы, range по created_at уже внутри этой выборки сужает до 0.3%. Однако нужно, чтобы оба условия были в WHERE.

5. Когда статистика врёт

Планировщик опирается на статистику, собранную через ANALYZE. Если статистика устарела или нерепрезентативна — план кривой.

`PG-I-035` — После массовой загрузки / `INSERT`-ов / `UPDATE`-ов — `ANALYZE` руками

ANALYZE orders;                    -- одна таблица
ANALYZE orders (status, created_at);  -- конкретные колонки
ANALYZE;                           -- весь schema

Autovacuum запускает ANALYZE сам, когда изменилось ~10% таблицы. На быстрых нагрузках этого может не хватить — autovacuum не успевает, статистика отстаёт.

`PG-I-036` — Если в данных есть редкие пиковые значения (бизнес-выбросы), увеличьте `default_statistics_target` для конкретной колонки

-- по умолчанию PG сэмплит 100 значений для most_common_vals
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Пример: колонка country_code в международном маркетплейсе. 99% — RU, остальные 1% — 50 разных стран. Без увеличения статистики PG не увидит мелкие страны в most_common_vals, селективность для них будет оценена неверно.

6. Планировщик: проверка через `EXPLAIN`

`PG-E-020` — `EXPLAIN ANALYZE` показывает реальное и оценочное число строк. Расхождение в 10x+ = криво посчитано

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'PAID';

Seq Scan on orders  (cost=0.00..18334.00 rows=400000 width=...) (actual time=0.012..89.123 rows=42137 loops=1)
  Filter: (status = 'PAID')
  Rows Removed by Filter: 957863

Здесь:

rows=400000 — оценка планировщика (оценил, что вернёт 400К).
actual rows=42137 — реальность (вернулось 42К).
Разница в 10x → планировщик ошибся → выбрал seq-scan, хотя был возможен Index Scan.

Решение: ANALYZE orders; (обновить статистику) → план должен пересчитаться.

7. Multi-column statistics (расширенная)

`PG-I-037` — Для коррелирующих колонок — `CREATE STATISTICS` с зависимостями

Пример: в orders колонки country_code и currency коррелированы (страна → валюта). Селективность WHERE country_code = 'RU' AND currency = 'RUB' равна селективности country_code = 'RU', а не произведению. PostgreSQL по умолчанию считает их независимыми и завышает селективность в 10–50 раз.

CREATE STATISTICS stats_orders_geo (dependencies, ndistinct)
    ON country_code, currency FROM orders;
ANALYZE orders;

После этого планировщик понимает зависимость и оценивает корректнее.

8. Чек-лист «индекс не работает»

Когда видишь медленный запрос с подходящим (на бумаге) индексом — пройти по списку:

EXPLAIN ANALYZE показывает Seq Scan вместо Index Scan?
Сравнить rows= оценку и actual rows=. Расхождение 10x+ → ANALYZE.
Посмотреть pg_stats для фильтрующей колонки — n_distinct, most_common_freqs. Низкая селективность → индекс не поможет, нужен другой подход.
Проверить random_page_cost — на SSD должен быть 1.1, не 4.0.
Проверить, что колонка в индексе действительно стоит первой в композитном (или хотя бы первой в левом префиксе по условию).
Для условий типа WHERE LOWER(email) = ... — нужен functional index.
Для частичных предикатов (WHERE deleted = false) — partial index.
Проверить корреляцию колонок — может нужны CREATE STATISTICS.

EXPLAIN ANALYZE — типы узлов и как читать план

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

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

EXPLAIN ANALYZE SELECT ...;            -- реально выполнит и покажет actual time / rows
EXPLAIN (ANALYZE, BUFFERS) SELECT ...; -- + сколько блоков прочитал из кеша / диска
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;  -- + полный список output-колонок
EXPLAIN (FORMAT JSON) SELECT ...;      -- структурированно, для tooling (PEV, depesz.com)

`PG-E-030` — Для диагностики всегда `EXPLAIN (ANALYZE, BUFFERS)`. Без `ANALYZE` это только оценка; без `BUFFERS` не видно, прочитан ли блок с диска или из кеша

⚠️ EXPLAIN ANALYZE выполняет запрос. Для INSERT/UPDATE/DELETE — оборачивать в транзакцию с ROLLBACK:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'X' WHERE id = 123;
ROLLBACK;

2. Как читать вывод

Hash Join  (cost=12.34..567.89 rows=1000 width=64) (actual time=1.234..56.789 rows=987 loops=1)
  Hash Cond: (a.id = b.a_id)
  Buffers: shared hit=1234 read=56
  ->  Seq Scan on a  (cost=...) (actual time=...) (rows=10000 loops=1)
  ->  Hash  (cost=...) (actual time=...) (rows=1000 loops=1)
        ->  Seq Scan on b  ...

Что значат поля:

cost=START..TOTAL — оценочная стоимость узла (в условных единицах). Не миллисекунды.
rows=N — оценка количества возвращаемых строк.
width=B — оценка размера строки в байтах.
actual time=START..TOTAL — реальное время в миллисекундах.
actual rows=N loops=M — реальное число строк × число запусков узла.
Buffers: shared hit=X read=Y — hit = из кеша, read = с диска. Чем больше read, тем больше IO.

`PG-E-031` — Главное — сравнить `rows=` (оценка) с `actual rows=`. Расхождение в 10x+ → статистика устарела (см. Селективность)

`PG-E-032` — Реальное время узла = `actual time` × `loops`

Если loops=10000 и actual time=0.5..1.2 — это 12 секунд внутри Nested Loop, не миллисекунды.

3. Узлы сканирования таблицы

Seq Scan

Seq Scan on orders  (cost=0.00..18334.00 rows=100000 width=64)
  Filter: (status = 'PAID')
  Rows Removed by Filter: 850000

Полный проход таблицы. Каждая строка проверяется на Filter.

Когда выбирается:

Нет подходящего индекса.
Запрос отдаёт большую долю таблицы (>20% обычно).
Таблица маленькая (планировщик считает, что оверхед индекса не окупится).

Когда плох:

На больших таблицах с селективным предикатом — Rows Removed by Filter гигантский.

Index Scan

Index Scan using ix_orders_status on orders
  Index Cond: (status = 'PAID')

Проход по B-tree-индексу + чтение строк из таблицы.

Index Cond: — условие, использованное для поиска по дереву.
Filter: — дополнительный фильтр после чтения строк.

`PG-E-033` — Если ваше условие в `Filter:`, а не в `Index Cond:` — индекс по этому полю не используется как ключ

Index Only Scan

Index Only Scan using ix_orders_status_created on orders
  Index Cond: (status = 'PAID')
  Heap Fetches: 12

Все нужные колонки есть в индексе → таблица не читается.

`PG-E-034` — `Heap Fetches > 0` означает, что visibility map устарел и пришлось дёргать таблицу. Помогает `VACUUM`

Bitmap Index Scan + Bitmap Heap Scan

Bitmap Heap Scan on orders
  Recheck Cond: (status = 'PAID')
  ->  Bitmap Index Scan on ix_orders_status
        Index Cond: (status = 'PAID')

Двухфазное:

Bitmap Index Scan — проходит индекс, строит битмап страниц с подходящими строками.
Bitmap Heap Scan — читает найденные страницы по порядку (sequential, не random).

Когда выбирается:

Условие отдаёт средне-большую долю строк.
Несколько индексов комбинируются (BitmapOr, BitmapAnd).

`PG-E-035` — Если `Recheck Cond:` показывает повторную проверку — это lossy bitmap (битмап вместил только страницы, не сами строки). На больших выборках теряется эффективность

4. Узлы соединения (Join)

Nested Loop

Nested Loop  (cost=...) (actual time=...) (rows=100 loops=1)
  ->  Index Scan on a  (rows=100)
  ->  Index Scan on b  (rows=1 loops=100)
        Index Cond: (b.a_id = a.id)

Для каждой строки внешнего отношения — поиск во внутреннем. Если внутреннее имеет индекс по join-ключу — очень эффективно для маленьких выборок.

Когда хорошо: outer rows × inner cost — мало (маленький outer, индекс на inner).

Когда плохо: outer rows × inner cost — много. Если видите loops=1000000 с actual time=0.5 на каждый — это 500 сек внутри Nested Loop. Лучше Hash Join.

Hash Join

Hash Join
  Hash Cond: (a.id = b.a_id)
  ->  Seq Scan on a
  ->  Hash
        ->  Seq Scan on b

Меньшее отношение полностью загружается в hash-таблицу в памяти, потом проход по большему с lookup-ом в hash.

Когда хорошо: меньшее отношение помещается в work_mem. Быстрее всего на больших соединениях без подходящих индексов.

`PG-E-036` — `Buckets: ... Batches: > 1` — hash не уместился в `work_mem`, использует диск. Увеличить `work_mem` для сессии

Merge Join

Merge Join
  Merge Cond: (a.id = b.a_id)
  ->  Sort
        ->  Seq Scan on a
  ->  Index Scan on ix_b_a_id

Оба отношения отсортированы по join-ключу, мерджатся как зипкой. Эффективен, если отсортированность бесплатна (есть индекс).

Когда хорошо: очень большие отношения, оба с индексами / уже отсортированы.

5. Сортировка и группировка

Sort

Sort  (cost=...) (actual time=12.345..56.789 rows=10000 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 8192kB

`PG-E-037` — `Sort Method: external merge Disk: ...kB` — сортировка не уместилась в память, ушла на диск. Увеличить `work_mem` или вынести сортировку в индекс

Альтернативы:

quicksort — в памяти, быстро.
top-N heapsort — для ORDER BY ... LIMIT N, держит в памяти только N лучших.

Aggregate / GroupAggregate / HashAggregate

HashAggregate
  Group Key: customer_id
  ->  Seq Scan on orders

Aggregate — простой count(*) / sum() без GROUP BY.
HashAggregate — GROUP BY через hash-таблицу в памяти. Быстро, не требует сортировки.
GroupAggregate — GROUP BY после Sort. Используется, когда HashAggregate не помещается в work_mem или нужен порядок.

Limit

Limit  (cost=...) (actual time=...) (rows=20)
  ->  Index Scan Backward on ix_orders_created_at

Останавливает выполнение, когда набралось N строк. Эффективен, если ниже — Index Scan (не нужно сортировать всё).

6. Прочие узлы

Узел	Что делает
Materialize	Кеширует результат подноды в памяти, чтобы не пересчитывать (часто внутри Nested Loop).
Subquery Scan	Обёртка над подзапросом.
CTE Scan	Чтение из CTE (с PG12+ часто `inline` без CTE Scan).
Append	Объединение результатов нескольких подзапросов (партиционирование, `UNION ALL`).
Gather / Gather Merge	Параллельный план — собирает результаты от worker-процессов.
Memoize (PG14+)	Кеширует результаты повторяющихся подзапросов внутри Nested Loop.
Result	Единичный результат без сканирования (например, `SELECT 1`).

7. Параллельный план

Gather  (cost=...) (rows=...)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders

PostgreSQL может разбить sequential scan / hash join / aggregate на несколько worker-процессов.

`PG-E-038` — Параллелизм оправдан на больших таблицах. На мелких — оверхед запуска worker-ов больше выигрыша

Управляется параметрами:

max_parallel_workers_per_gather (по умолчанию 2).
parallel_setup_cost / parallel_tuple_cost.
min_parallel_table_scan_size (по умолчанию 8MB) — таблица меньше → не параллелится.

8. Чек-лист «как читать план»

`PG-E-039` — Алгоритм:

Читать снизу вверх. Дерево плана: листья — самые внутренние операции, корень — финальный результат.
Найти узкое место по actual time. Узел с самым большим вкладом (actual time × loops минус время дочерних) — там фокус.
Сверить rows= и actual rows=. Расхождение → ANALYZE.
Проверить Buffers: read=. Большое read → IO-bound, нужен индекс или больше кеша.
Filter vs Index Cond. Если важное условие в Filter — индекс не используется как ключ.
Heap Fetches > 0 на Index Only Scan → VACUUM.
external merge Disk: или Batches > 1 в Hash Join → увеличить work_mem.
Nested Loop с большим loops → проверить, есть ли индекс на inner join-ключе. Если нет — Hash Join будет лучше.
Rows Removed by Filter миллионы → нужен индекс, который сузит выборку до фильтра.

9. Инструменты визуализации

Для сложных планов (5+ уровней вложенности) удобнее визуализаторы:

explain.depesz.com — кладёшь plan, получаешь раскрашенную таблицу с подсветкой проблем.
explain.dalibo.com (PEV2) — графическое дерево, открываешь в браузере, не отправляя план в чужие сервисы.
pg_stat_statements — расширение для агрегации статистики по запросам в проде. Показывает топ по total_time, mean_time, calls.

10. Когда EXPLAIN не помогает

EXPLAIN ANALYZE показывает то, что ОДИН запрос делает СЕЙЧАС. Не показывает:

Локскипы и блокировки — для них pg_locks + pg_stat_activity.
autovacuum / wraparound — для них pg_stat_user_tables.
Memory pressure — top / htop / pg_stat_database.
Replication lag — pg_stat_replication.

`PG-E-040` — EXPLAIN — про план одного запроса. Для проблем нагрузки и производительности кластера нужны pg_stat_*-views и метрики

Блокировки и как использовать их с jOOQ

PostgreSQL даёт три уровня блокировок: table-level (на DDL и LOCK TABLE), row-level (на строки через SELECT FOR ...), advisory (произвольные, на ваш bigint-ключ). В прикладной разработке нужны последние два. Эта статья — что и когда брать, как делать через jOOQ, и какие ловушки ждут на бою.

1. Базовая модель: что блокируется без вашего участия

`PG-L-001` — `UPDATE` / `DELETE` уже берут row-level lock автоматически

Параллельный UPDATE той же строки ждёт. Параллельный SELECT без FOR UPDATE — НЕ ждёт (читает старую версию через MVCC).

TX1: UPDATE orders SET status='PAID' WHERE id=42;
TX2: UPDATE orders SET status='CANCELLED' WHERE id=42;   ← ждёт, пока TX1 закоммитит
TX2: SELECT status FROM orders WHERE id=42;              ← НЕ ждёт, видит старую (committed)

Это «оптимистичная» картина MVCC: чтение никогда не ждёт запись. Поэтому фантомов между чтением и решением «надо ли менять» — навалом, и SELECT FOR UPDATE существует именно для них.

2. `SELECT FOR UPDATE` — главный инструмент

`PG-L-010` — Используй `SELECT FOR UPDATE`, когда читаешь строку, чтобы потом её изменить, и нужна гарантия, что между чтением и записью никто другой не вмешается

Классический сценарий — резервирование товара:

BEGIN;
SELECT stock FROM product WHERE id = 100 FOR UPDATE;  -- блокирует строку
-- решаем: stock=5, заказ на 3, ок, можем зарезервировать
UPDATE product SET stock = stock - 3 WHERE id = 100;
COMMIT;

Без FOR UPDATE два параллельных запроса прочитали бы stock=5, оба решили «можем», оба сделали UPDATE — продали 6 единиц при остатке 5.

`PG-L-011` — `FOR UPDATE` блокирует только до конца транзакции

Без транзакции (autocommit) лок отпускается сразу — толку нет. В Spring это значит: @Transactional обязателен.

3. Варианты `FOR ...` — что выбрать

Команда	Что делает	Когда брать
`FOR UPDATE`	Полная блокировка строки на запись	Резерв остатков, изменение статуса, любой UPDATE-after-SELECT
`FOR NO KEY UPDATE`	Слабее, не блокирует FK-операции	UPDATE полей, которые не PK/UK (HOT update friendly)
`FOR SHARE`	Несколько TX могут держать одновременно, но никто не может UPDATE	«Эта строка не должна измениться, пока я работаю» (редко нужно)
`FOR KEY SHARE`	Самый слабый	Удержать строку для FK-проверки

`PG-L-012` — В 95% случаев нужен `FOR UPDATE`. Остальные — оптимизации, не дёргай без понимания

4. `SKIP LOCKED` — очередь задач из таблицы

`PG-L-020` — `FOR UPDATE SKIP LOCKED` пропускает уже залоченные строки. Идеальный паттерн для очереди задач в БД

-- worker берёт следующую невзятую задачу
BEGIN;
SELECT id, payload
FROM task_queue
WHERE status = 'PENDING'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- (если получили строку)
UPDATE task_queue SET status = 'PROCESSING', worker_id = ? WHERE id = ?;
COMMIT;

-- (обработка)
-- ...

-- завершение
UPDATE task_queue SET status = 'DONE' WHERE id = ?;

Несколько worker-ов могут одновременно дёргать SELECT FOR UPDATE SKIP LOCKED — каждый получит свою строку, никто не ждёт. Это и есть простая распределённая очередь без Kafka/Redis.

`PG-L-021` — Используется в outbox-relay

: каждый instance сервиса берёт строки SKIP LOCKED, чтобы при многих инстансах не было дублирующей публикации.

5. `NOWAIT` — fail-fast вместо ожидания

`PG-L-030` — `FOR UPDATE NOWAIT` падает с ошибкой, если строка занята, вместо ожидания

SELECT * FROM order_doc WHERE id = ? FOR UPDATE NOWAIT;

Полезно, когда:

Контролируем таймауты на уровне приложения, не хотим зависать на дефолтном lock_timeout.
Лучше отказаться, чем ждать — например, в API-эндпоинте «ну, попробуйте через минуту».

6. jOOQ: как написать всё это правильно

`PG-L-040` — jOOQ умеет все варианты блокировок через DSL

// FOR UPDATE
ProductRecord product = ctx
    .selectFrom(PRODUCT)
    .where(PRODUCT.ID.eq(productId))
    .forUpdate()
    .fetchOne();

// FOR UPDATE SKIP LOCKED
List<TaskRecord> tasks = ctx
    .selectFrom(TASK_QUEUE)
    .where(TASK_QUEUE.STATUS.eq("PENDING"))
    .orderBy(TASK_QUEUE.CREATED_AT)
    .limit(10)
    .forUpdate()
    .skipLocked()
    .fetch();

// FOR UPDATE NOWAIT
ProductRecord product = ctx
    .selectFrom(PRODUCT)
    .where(PRODUCT.ID.eq(productId))
    .forUpdate()
    .noWait()
    .fetchOne();

// FOR NO KEY UPDATE
ctx.selectFrom(ORDER_DOC)
    .where(ORDER_DOC.ID.eq(orderId))
    .forNoKeyUpdate()
    .fetchOne();

`PG-L-041` — Lock запрос обязан быть внутри `@Transactional`-метода Spring

Иначе jOOQ откроет, выполнит, закроет соединение — лок отпустится мгновенно.

@Transactional
public void reserveStock(long productId, int quantity) {
    ProductRecord product = dsl
        .selectFrom(PRODUCT)
        .where(PRODUCT.ID.eq(productId))
        .forUpdate()
        .fetchOne();

    if (product == null) throw new ProductNotFoundException(productId);
    if (product.getStock() < quantity) throw new InsufficientStockException(productId);

    dsl.update(PRODUCT)
       .set(PRODUCT.STOCK, PRODUCT.STOCK.minus(quantity))
       .where(PRODUCT.ID.eq(productId))
       .execute();
}

7. Pessimistic vs Optimistic locking

`PG-L-050` — Два подхода. Выбирай по нагрузке и характеру конфликтов

Pessimistic (`SELECT FOR UPDATE`)

Берёшь лок заранее, никто другой не лезет.
Простой код.
При высокой конкуренции — очередь TX, троттлинг.

Optimistic (через `version`-колонку)

Не блокируешь заранее. На UPDATE проверяешь, что версия не изменилась с момента чтения.
Если изменилась — OptimisticLockException, перечитывай и повторяй.
Лучше масштабируется при низкой реальной конкуренции.

-- DDL
ALTER TABLE order_doc ADD COLUMN version bigint NOT NULL DEFAULT 0;

-- READ
SELECT id, status, version FROM order_doc WHERE id = ?;

-- UPDATE с проверкой версии
UPDATE order_doc
SET status = 'PAID', version = version + 1
WHERE id = ? AND version = ?;   -- если изменилось — 0 rows affected → конфликт

В jOOQ:

int updated = dsl.update(ORDER_DOC)
    .set(ORDER_DOC.STATUS, "PAID")
    .set(ORDER_DOC.VERSION, ORDER_DOC.VERSION.plus(1))
    .where(ORDER_DOC.ID.eq(orderId).and(ORDER_DOC.VERSION.eq(originalVersion)))
    .execute();

if (updated == 0) {
    throw new OptimisticLockException("order " + orderId + " modified concurrently");
}

`PG-L-051` — Optimistic для read-heavy, pessimistic для write-heavy и денежных операций

Когда конфликты редки (раз в 1000 запросов) — optimistic дешевле. Когда часто (десяток конкурентов на одну строку) — pessimistic не даёт «10 retry в цикле».

8. Advisory locks — блокировка не на строку, а на абстрактный ключ

`PG-L-060` — Advisory lock — собственная блокировка на произвольный bigint-ключ. Не привязан к таблице

Полезно, когда нужно «провести операцию X в одном экземпляре сервиса», но не на строку БД:

Запуск scheduled-job'а только одним инстансом из кластера.
Глобальная очередь.
Предотвращение двух одновременных миграций.

Два варианта:

-- сессионный (отпускается при отключении)
SELECT pg_advisory_lock(12345);

-- транзакционный (отпускается на COMMIT/ROLLBACK)
SELECT pg_advisory_xact_lock(12345);

Try-вариант (не ждёт):

SELECT pg_try_advisory_xact_lock(12345);  -- true/false

В jOOQ:

@Transactional
public void runIfNotAlreadyRunning(long jobKey, Runnable job) {
    Boolean acquired = dsl.select(
        DSL.function("pg_try_advisory_xact_lock", Boolean.class, DSL.val(jobKey))
    ).fetchOne(0, Boolean.class);

    if (Boolean.TRUE.equals(acquired)) {
        job.run();
    } else {
        log.info("job {} already running on another instance, skip", jobKey);
    }
}

`PG-L-061` — Используй два bigint-аргумента, если лок логически разделяется на две части:

pg_advisory_lock(class_id, object_id). Например, (1001, tenant_id) — лок на tenant_id в namespace «refresh-cache».

9. Deadlock — что это и как ловить

`PG-L-070` — Deadlock возникает, когда две транзакции берут локи в РАЗНОМ порядке

TX1: UPDATE orders WHERE id=1   -- блокирует строку 1
TX2: UPDATE orders WHERE id=2   -- блокирует строку 2
TX1: UPDATE orders WHERE id=2   -- ждёт TX2
TX2: UPDATE orders WHERE id=1   -- ждёт TX1
                                -- DEADLOCK

PostgreSQL детектит deadlock через deadlock_timeout (default 1s) и убивает одну из TX с ошибкой 40P01 deadlock detected.

`PG-L-071` — Лечится упорядочением блокировок. Всегда брать строки в одном и том же порядке

// плохо: порядок зависит от пользовательского ввода
public void transferMoney(long fromAccountId, long toAccountId, BigDecimal amount) {
    var from = lockAccount(fromAccountId);
    var to   = lockAccount(toAccountId);
    // если параллельно идёт обратный transfer (toId → fromId), будет deadlock
}

// хорошо: всегда блокируем в порядке возрастания id
public void transferMoney(long fromAccountId, long toAccountId, BigDecimal amount) {
    long firstId  = Math.min(fromAccountId, toAccountId);
    long secondId = Math.max(fromAccountId, toAccountId);
    var first  = lockAccount(firstId);
    var second = lockAccount(secondId);
    // оба перевода блокируют в одном порядке → нет deadlock
}

`PG-L-072` — Для deadlock`ов в коде Spring — обработай`org.springframework.dao.CannotAcquireLockException` (PG код 40P01) с retry

Это не баг, это нормальный исход в high-conc OLTP. 1–3 retry с экспоненциальной паузой обычно решают.

@Retryable(
    retryFor = CannotAcquireLockException.class,
    maxAttempts = 3,
    backoff = @Backoff(delay = 50, multiplier = 2)
)
@Transactional
public void transferMoney(...) { ... }

10. `lock_timeout` и `statement_timeout`

`PG-L-080` — Поставь `lock_timeout` для критичных операций — не жди вечно

SET LOCAL lock_timeout = '5s';
UPDATE order_doc ... ;

В Spring это можно через @Transactional-настройки или через entityManager.createNativeQuery("SET LOCAL lock_timeout = '5s'") в начале метода.

Особенно важно для миграций: ALTER TABLE берёт ACCESS EXCLUSIVE — если хоть кто-то читает таблицу с активной транзакцией, миграция заблокирована, а за ней — все новые запросы (из-за lock queue).

-- безопасный паттерн миграции
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN ...;
COMMIT;

11. Антипаттерны

PG-L-090 SELECT FOR UPDATE без транзакции — лок мгновенно отпускается, проверка бесполезна.

PG-L-091 SELECT FOR UPDATE со сложным WHERE, который индекс не покрывает — может залочить кучу строк через seq scan. Проверь EXPLAIN.

PG-L-092 Длинная транзакция с локом → блокирует всех ждущих и копит WAL (см. WAL §8). Лок брать как можно ближе к UPDATE, отпускать сразу.

PG-L-093 Pessimistic lock на каждое чтение — превращает БД в очередь. Используй optimistic, где конфликты редки.

PG-L-094 Брать локи в разном порядке в разных методах — deadlock. Зафиксируй правило (например, по возрастанию PK) и придерживайся.

PG-L-095 SELECT FOR UPDATE на таблице с миллионами строк без LIMIT — внезапно блокирует все строки. Всегда LIMIT N для очередей.

Уровни изоляции и аномалии

PostgreSQL поддерживает три уровня изоляции (стандарт SQL описывает четыре, READ UNCOMMITTED PG не реализует — для совместимости работает как READ COMMITTED). На уровне @Transactional Spring это значит, что вы выбираете компромисс между корректностью и производительностью.

Дефолт PG — READ COMMITTED, и в 95% случаев это правильный выбор. Эта статья — про оставшиеся 5%, когда нужен другой уровень.

Правила пронумерованы кодами PG-IS-NNN — на них ссылается скилл ucp-pg-runtime-review.

1. Что такое аномалии

Стандарт SQL описывает четыре аномалии:

Аномалия	Что	Пример
Dirty read	Видим uncommitted изменение чужой транзакции	TX1 пишет, TX2 видит до commit, TX1 откатилась
Non-repeatable read	Перечитал ту же строку — значение изменилось	TX1 читает price=100, TX2 update price=120 commit, TX1 перечитал — 120
Phantom read	Перечитал тот же `WHERE` — пришли новые строки	TX1 `SELECT count(*) WHERE status='NEW'` = 5, TX2 insert NEW commit, TX1 повторно — 6
Serialization anomaly	Параллельные TX в сумме нарушают инвариант, который каждая по отдельности соблюдает	Класс. пример: write skew

`PG-IS-001` — Уровни изоляции — это НЕ «больше = лучше». Каждый уровень — компромисс

: выше изоляция → меньше аномалий → больше блокировок и rollback'ов. Выбирай минимальный, который покрывает аномалии в твоём сценарии.

2. Три уровня PostgreSQL

Уровень	Dirty read	Non-repeat	Phantom	Serialization
`READ UNCOMMITTED` (PG = RC)	предотвращает	разрешает	разрешает	разрешает
`READ COMMITTED` (default)	предотвращает	разрешает	разрешает	разрешает
`REPEATABLE READ` (PG = snapshot)	предотвращает	предотвращает	предотвращает	разрешает
`SERIALIZABLE`	предотвращает	предотвращает	предотвращает	предотвращает

`PG-IS-002` — PostgreSQL `READ COMMITTED` строже, чем стандарт

Через MVCC dirty read невозможен в принципе.

`PG-IS-003` — PostgreSQL `REPEATABLE READ` строже, чем стандарт — реально это snapshot isolation

Phantom read предотвращён (стандарт это не требует на этом уровне).

`PG-IS-004` — Только PG `SERIALIZABLE` (через SSI — Serializable Snapshot Isolation) предотвращает все аномалии, включая write skew

3. READ COMMITTED — дефолт и почему

`PG-IS-010` — `READ COMMITTED` (RC) — дефолт PostgreSQL

Внутри одной транзакции каждый SELECT видит самое последнее committed состояние на момент СВОЕГО запуска (не на момент BEGIN).

TX1: BEGIN;
TX1: SELECT price FROM product WHERE id=1;   -- 100

TX2: BEGIN;
TX2: UPDATE product SET price = 120 WHERE id=1;
TX2: COMMIT;

TX1: SELECT price FROM product WHERE id=1;   -- 120 (non-repeatable!)
TX1: COMMIT;

`PG-IS-011` — Non-repeatable и phantom — разрешены

В TX1 ту же строку прочитали и получили разные значения. Кажется страшным, но в OLTP это редко проблема: один логический «запрос» обычно читает каждую строку один раз и не сравнивает потом.

`PG-IS-012` — `READ COMMITTED` минимизирует блокировки

Транзакции не блокируют друг друга на чтении, что даёт максимальный throughput.

Когда хватает RC (большинство кейсов):

Простые CRUD (создал заказ → ответил клиенту).
API-эндпоинты, которые читают и сразу отвечают.
Запросы с SELECT FOR UPDATE для read-modify-write — лок защищает от race condition.

4. REPEATABLE READ — snapshot на всю транзакцию

`PG-IS-020` — `REPEATABLE READ` (RR) фиксирует snapshot на момент первого запроса в транзакции

Все последующие SELECT видят то же состояние, сколько бы ни длилась транзакция.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- или: BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT count(*) FROM orders WHERE status='NEW';  -- 100
-- (другая транзакция вставила новые orders и закоммитила)
SELECT count(*) FROM orders WHERE status='NEW';  -- всё ещё 100 (phantom предотвращён)
COMMIT;

`PG-IS-021` — Когда RR оправдан:

Длинный отчёт, который перечитывает таблицы в нескольких запросах и должен видеть консистентный срез.
pg_dump для backup (использует RR).
Перенос данных по сложной логике, где половина source может измениться, пока пишешь destination.

`PG-IS-022` — На RR `UPDATE` той же строки, что изменилась после snapshot, упадёт с `40001 serialization_failure`

Java должен делать retry. Это плата за snapshot.

TX1 (RR): BEGIN;
TX1:      SELECT price FROM product WHERE id=1;   -- 100
TX2 (RC): UPDATE product SET price=120 WHERE id=1; COMMIT;
TX1:      UPDATE product SET price=110 WHERE id=1;
          ERROR: 40001 — could not serialize access due to concurrent update

`PG-IS-023` — На bulk-операциях RR может стать SLA-проблемой

Любая параллельная запись по тем же строкам = 40001 = retry. Не используй RR в hot path просто «на всякий случай».

5. SERIALIZABLE — full isolation через SSI

`PG-IS-030` — `SERIALIZABLE` гарантирует: любой результат параллельных транзакций — такой, какой получился бы, если бы они выполнялись последовательно

Это сильнее, чем RR.

`PG-IS-031` — Классический пример write skew, который RR пропускает, а SERIALIZABLE ловит:

Допустим, инвариант: «всегда хотя бы один врач на смене». В таблице 2 врача on-call.

TX1 (RR): BEGIN;
TX1: SELECT count(*) FROM doctors WHERE on_call=true;  -- 2
TX1: -- логика «можно уйти, остаётся хотя бы 1»
TX1: UPDATE doctors SET on_call=false WHERE id=1;

TX2 (RR): BEGIN;
TX2: SELECT count(*) FROM doctors WHERE on_call=true;  -- 2
TX2: UPDATE doctors SET on_call=false WHERE id=2;
TX2: COMMIT;

TX1: COMMIT;
-- инвариант нарушен: 0 врачей на смене

Под SERIALIZABLE одна из TX упадёт с 40001.

`PG-IS-032` — Когда нужен SERIALIZABLE:

Сложные инварианты, которые невозможно выразить через CHECK constraint или SELECT FOR UPDATE (например, инвариант на agregate уровне нескольких строк).
Финансовые расчёты с множественными правилами.
Где «правильность важнее throughput».

`PG-IS-033` — SERIALIZABLE дороже:

PG отслеживает зависимости между транзакциями (predicate locks).
Race-condition → откат с 40001 → retry на стороне приложения.
Под нагрузкой % rollback'ов растёт.

`PG-IS-034` — На большинстве OLTP — НЕ оправдан

Дешевле выразить инвариант через SELECT FOR UPDATE + ручной CHECK на тех же строках, оставив READ COMMITTED.

6. Spring — как задавать уровень

`PG-IS-040` — `@Transactional(isolation = Isolation.REPEATABLE_READ)` или `Isolation.SERIALIZABLE`

на методе.

@Transactional(isolation = Isolation.SERIALIZABLE)
public void releaseDoctorFromShift(long doctorId) {
    var onCallCount = doctorRepository.countByOnCallTrue();
    if (onCallCount <= 1) {
        throw new LastDoctorOnShiftException();
    }
    doctorRepository.setOnCallFalse(doctorId);
}

`PG-IS-041` — `Isolation.READ_COMMITTED` — дефолт. Не указывай явно

Пусть в коде явно стоит только то, что отличается от стандарта.

`PG-IS-042` — На SERIALIZABLE / REPEATABLE READ нужен retry на `CannotSerializeTransactionException` (PG код 40001):

@Retryable(
    retryFor = CannotSerializeTransactionException.class,
    maxAttempts = 3,
    backoff = @Backoff(delay = 50, multiplier = 2)
)
@Transactional(isolation = Isolation.SERIALIZABLE)
public void doWork() { ... }

3 retry с back-off обычно решают, потому что 40001 — почти всегда race, а не системная проблема.

7. Когда какой выбирать — практический алгоритм

`PG-IS-050` — Алгоритм выбора:

Это простой read-modify-write одной строки? → READ COMMITTED + SELECT FOR UPDATE.
Read-only отчёт по нескольким таблицам, нужна консистентность срез? → REPEATABLE READ.
Длинный bulk-перенос данных? → REPEATABLE READ (read-only часть) или READ COMMITTED с явной батчингом.
Сложный инвариант на нескольких строках/таблицах, который нельзя выразить через FOR UPDATE? → SERIALIZABLE + retry.
Запись денег / счетов? → READ COMMITTED + SELECT FOR UPDATE (упорядочить локи по id, см. Locks §9).

`PG-IS-051` — Когда сомнения — оставь дефолт

RC + правильные locks решает 95% задач. Поднятие уровня без понимания, какую конкретно аномалию ты предотвращаешь, — карго-культ.

8. Read-only транзакции

`PG-IS-060` — `@Transactional(readOnly = true)`

говорит JDBC и Spring, что транзакция не пишет. Эффект:

HikariCP может направить на read-replica (если настроено routing — см. Connection pool §6).
jOOQ/JPA пропускает flush.
На уровне PG — никакого effect автоматически. PG не знает.

`PG-IS-061` — Чтобы PG знал — `SET TRANSACTION READ ONLY`

в начале сессии. Spring это делает только если включено spring.jpa.properties.hibernate.connection.provider_disables_autocommit=true или вручную. Прирост перформанса небольшой — PG чуть оптимизирует MVCC.

9. `idle_in_transaction_session_timeout`

`PG-IS-070` — Серверная настройка `idle_in_transaction_session_timeout`

автоматически убивает транзакции, которые открыты, но idle > N. Защита от забытых транзакций.

SET idle_in_transaction_session_timeout = '30s';   -- в сессии
-- или в postgresql.conf:
idle_in_transaction_session_timeout = 30000  -- ms

На проде ставь 30–60 сек. Транзакция, которая идёт дольше — почти всегда баг (см. WAL §8).

10. Антипаттерны

PG-IS-080 @Transactional(isolation = Isolation.SERIALIZABLE) на каждом методе «на всякий случай» — % rollback'ов скакнёт, throughput упадёт.

PG-IS-081 Isolation.REPEATABLE_READ на коротком read-modify-write вместо SELECT FOR UPDATE — RR не подходит для этого, нужен лок.

PG-IS-082 Поднимать уровень изоляции, когда корень проблемы — отсутствие constraint'а в схеме. Если можешь выразить инвариант через CHECK или EXCLUDE — это лучше, чем SERIALIZABLE.

PG-IS-083 SERIALIZABLE без retry на CannotSerializeTransactionException — будут «случайные» ошибки в проде.

PG-IS-084 Долгие RR/SERIALIZABLE транзакции — каждая держит snapshot, мешает autovacuum (см. VACUUM §6).

Spring `@Transactional` — нюансы и антипаттерны

@Transactional — самая частая аннотация в Spring-приложениях и самый частый источник тонких багов. Эта статья — про то, что обычно неочевидно: propagation, self-invocation, что реально делает readOnly, когда rollback срабатывает.

Правила пронумерованы кодами PG-TX-NNN — на них ссылается скилл ucp-pattern-review (Spring-side) и ucp-pg-runtime-review (DB-side).

1. Где ставить `@Transactional`

`PG-TX-001` — Ставь на бизнес-методах service-уровня (UseCase / UseCaseHandler), не на репозиториях

// правильно
@Component
class CreateOrderUseCaseHandler {
    @Transactional
    public OrderId handle(CreateOrderUseCase uc) {
        var customer = customerRepository.findById(uc.customerId());
        var order = orderFactory.create(customer, uc.items());
        orderRepository.save(order);
        outboxRepository.publishEvent(new OrderCreated(order.id()));
        return order.id();
    }
}

Граница транзакции = граница бизнес-операции. Один UseCase = одна транзакция (R-HND-2 в use-case-pattern). На репозитории @Transactional избыточно.

`PG-TX-002` — Не ставь `@Transactional` на контроллере

— контроллер это HTTP, а не бизнес-операция. Если запрос делает несколько UseCase, каждый со своей TX.

2. Self-invocation — главная грабля

`PG-TX-010` — Spring `@Transactional` работает через AOP proxy. Вызов метода ВНУТРИ ТОГО ЖЕ КЛАССА не проходит через прокси, и `@Transactional` не срабатывает

@Component
class OrderService {
    public void processBatch(List<OrderId> ids) {
        for (var id : ids) {
            processOne(id);  // НЕ открывает транзакцию!
        }
    }

    @Transactional
    public void processOne(OrderId id) {
        // внутри без TX, потому что вызвано из processBatch() — не через прокси
    }
}

`PG-TX-011` — Решение — вынести метод с `@Transactional` в отдельный бин:

@Component
class OrderService {
    private final OrderProcessor processor;

    public void processBatch(List<OrderId> ids) {
        for (var id : ids) {
            processor.processOne(id);  // через DI — через прокси — TX открывается
        }
    }
}

@Component
class OrderProcessor {
    @Transactional
    public void processOne(OrderId id) { ... }
}

`PG-TX-012` — Альтернатива — через `TransactionTemplate` или `@Self` reference

, но это хак. Чистый путь — отдельный бин.

`PG-TX-013` — Метод с `@Transactional` должен быть `public`

Spring AOP proxy не видит package-private/protected/private. На private — ничего не открывается, тихо.

3. Propagation — что выбирать

`PG-TX-020` — `Propagation.REQUIRED` (default) — почти всегда правильно

Если транзакция уже есть — присоединиться к ней. Если нет — открыть новую.

`PG-TX-021` — `Propagation.REQUIRES_NEW` — приостанавливает текущую TX, открывает новую

Полезно для:

Аудит-логов, которые должны зафиксироваться даже если основная TX откатилась.
Записи в outbox/event log, которая идёт независимо от бизнес-результата.
Idempotency-trail — лог попыток.

@Transactional
public void processPayment(...) {
    auditService.logAttempt(...);   // должен записаться даже если processPayment упал
    paymentGateway.charge(...);
}

@Component
class AuditService {
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logAttempt(...) { ... }
}

`PG-TX-022` — `REQUIRES_NEW` берёт ОТДЕЛЬНОЕ соединение из пула

Текущее остаётся занятым (transaction suspended). На high-throughput это удваивает потребление пула.

`PG-TX-023` — `Propagation.NESTED` — savepoint внутри транзакции

Откат внутреннего savepoint оставляет внешнюю TX живой. PostgreSQL поддерживает savepoints, JDBC — тоже. Полезно для:

Опциональных шагов, где сбой одного не должен откатывать все.

@Transactional
public void importBatch(List<Item> items) {
    for (var item : items) {
        try {
            saveItem(item);   // NESTED — savepoint
        } catch (Exception e) {
            log.warn("skip {}: {}", item, e);
        }
    }
}

@Transactional(propagation = Propagation.NESTED)
public void saveItem(Item i) { ... }

`PG-TX-024` — `Propagation.MANDATORY` — обязана быть открытая TX, иначе exception

Полезно, когда метод НЕ должен вызываться без транзакции (например, помощник, ожидающий что вызывающий уже открыл TX).

`PG-TX-025` — `Propagation.NEVER` / `Propagation.NOT_SUPPORTED`

— почти не нужны в нормальном коде. Если в каком-то месте они кажутся правильными, скорее всего архитектура странная.

4. `readOnly = true` — что реально делает

`PG-TX-030` — `@Transactional(readOnly = true)` делает три вещи в Spring:

Connection.setReadOnly(true) — драйвер PostgreSQL передаёт это серверу как SET TRANSACTION READ ONLY.
Hibernate / JPA пропускает dirty-checking и flush — экономия CPU.
Routing DataSource (если настроен — см. Connection pool §6) направляет на read-replica.

`PG-TX-031` — На уровне jOOQ — почти ничего

jOOQ не имеет понятия dirty checking. setReadOnly(true) просто проброшено на JDBC. Если использует роутинг — поедет на реплику.

`PG-TX-032` — Используй `readOnly = true` для query-методов:

@Transactional(readOnly = true)
public List<OrderView> findOrdersByCustomer(long customerId) { ... }

`PG-TX-033` — Не используй `readOnly = true` на методах, которые могут открыть транзакцию для write через nested call

Контракт нарушится.

5. `rollbackFor` — что откатывает

`PG-TX-040` — По умолчанию Spring откатывает только `RuntimeException` и `Error`. Checked exceptions НЕ откатывают транзакцию — это исторический Spring-default

@Transactional
public void doWork() throws IOException {
    repo.save(...);
    if (cond) throw new IOException();  // commit пройдёт! Spring не откатит
}

`PG-TX-041` — Если код может бросить checked, и ты хочешь откат — `@Transactional(rollbackFor = Exception.class)`

@Transactional(rollbackFor = Exception.class)
public void doWork() throws IOException {
    repo.save(...);
    if (cond) throw new IOException();  // теперь rollback
}

`PG-TX-042` — Лучшее решение — не бросать checked exceptions из transactional-методов

Оборачивай в свои runtime-exceptions:

@Transactional
public void doWork() {
    try {
        externalCall();
    } catch (IOException e) {
        throw new ExternalCallFailedException(e);   // RuntimeException
    }
}

Кода больше, но контракт честный — все ошибки runtime, default rollback работает корректно.

`PG-TX-043` — `noRollbackFor` редко полезен

Возможный случай: бизнес-исключение, которое возвращается клиенту как 400 BAD REQUEST, но изменения в БД нужно сохранить. Обычно это запах архитектуры — лучше разделить операцию на две.

6. Транзакции и async

`PG-TX-050` — `@Async` метод запускается в другом потоке. Текущая транзакция НЕ передаётся

@Transactional
public void mainOp() {
    repo.save(...);
    asyncSomething();   // выполнится в другом потоке, БЕЗ TX, после return mainOp()
}

@Async
@Transactional
public CompletableFuture<Void> asyncSomething() { ... }   // открывает СВОЮ TX

`PG-TX-051` — `CompletableFuture.runAsync(() -> repo.save(...))` без `@Async` — не транзакционно

Сохранение либо упадёт (нет тред-локального TX), либо использует свою auto-commit транзакцию.

7. `@PostConstruct` / события

`PG-TX-060` — `@PostConstruct`-метод выполняется до полной инициализации бина — `@Transactional` не работает на нём

Если нужно что-то сделать в БД при старте, используй ApplicationReadyEvent:

@EventListener(ApplicationReadyEvent.class)
@Transactional
public void initData() { ... }

`PG-TX-061` — `@TransactionalEventListener`

— слушатель, привязанный к фазе транзакции (AFTER_COMMIT по умолчанию). Полезно для action-after-commit паттерна.

@Component
class OrderEventHandler {
    @TransactionalEventListener   // вызовется только после успешного COMMIT
    public void onOrderCreated(OrderCreated event) {
        emailService.send(...);
    }
}

В UseCase-паттерне это идиоматичный путь публиковать события «после save», без гонки.

8. Транзакция и внешние ресурсы

`PG-TX-070` — `@Transactional` не покрывает Kafka, HTTP, Redis, файлы

Внутри транзакции kafkaTemplate.send(...) отправит сообщение, и если транзакция откатилась — сообщение всё равно ушло.

`PG-TX-071` — Используй Outbox-паттерн

(см. Распределённые паттерны). В одной транзакции с бизнес-данными пиши в outbox-таблицу. Отдельный @Scheduled-relay читает outbox и шлёт в Kafka.

@Transactional
public OrderId createOrder(CreateOrderCommand cmd) {
    var order = orderRepo.save(new Order(...));
    outboxRepo.save(new OutboxEvent("OrderCreated", order.id(), payload));
    return order.id();
    // если упадёт после save и до save outbox — обе строки откатятся вместе
}

`PG-TX-072` — Spring `@Transactional` и Spring Kafka transactions — это разные транзакции

Связать их сложно (chained transactions / JTA / 2PC). На практике — Outbox.

9. Длительность транзакции

`PG-TX-080` — Транзакция должна жить

секунды**, не минуты.**

Анти-паттерн:

@Transactional
public void processOrder(OrderId id) {
    var order = orderRepo.find(id);
    paymentGateway.charge(order.total());   // HTTP, ~3 сек
    deliveryService.scheduleDelivery(order); // HTTP, ~2 сек
    order.confirm();
    orderRepo.save(order);
}
// 5 сек открытая транзакция — connection занят, autovacuum заблокирован

`PG-TX-081` — Решение — разделить:

public void processOrder(OrderId id) {
    var order = orderRepo.find(id);  // отдельная короткая TX
    paymentGateway.charge(order.total());
    deliveryService.scheduleDelivery(order);
    confirmOrder(id);  // отдельная короткая TX
}

@Transactional
private void confirmOrder(OrderId id) {
    var order = orderRepo.find(id);
    order.confirm();
    orderRepo.save(order);
}

С учётом self-invocation confirmOrder должен быть в отдельном бине (см. PG-TX-011).

`PG-TX-082` — На практике — Saga с компенсациями для долгих процессов

Каждый шаг — отдельная короткая транзакция, общая консистентность через компенсирующие действия.

10. Антипаттерны

PG-TX-090 @Transactional на private/protected/package-private методе — игнорируется (PG-TX-013).

PG-TX-091 @Transactional на методе, вызываемом из того же класса — не работает (PG-TX-010).

PG-TX-092 @Transactional вокруг внешнего HTTP/Kafka/S3 — соединение из пула удерживается всё время вызова.

PG-TX-093 rollbackFor = Exception.class глобально на каждом методе — лечение симптома, лучше переписать на runtime-exceptions.

PG-TX-094 @Transactional на контроллере — TX живёт до сериализации response в JSON, лишние операции в TX.

PG-TX-095 kafkaTemplate.send(...) внутри @Transactional — событие уйдёт даже при rollback. Нужен Outbox.

PG-TX-096 REQUIRES_NEW под high-load без понимания — удваивает потребление пула.

PG-TX-097 Propagation.SERIALIZABLE без retry на CannotSerializeTransactionException.

HikariCP и connection pooling

PostgreSQL держит один процесс ОС на каждое соединение. Тысяча соединений — тысяча процессов, каждый со своей памятью (~10 MB) и расходом на context switching. Кластер захлёбывается раньше, чем под бизнес-нагрузкой. Connection pool в приложении ограничивает число соединений, переиспользует их между запросами.

Эта статья — про HikariCP (стандарт в Spring Boot) и PgBouncer (внешний пул). Правила пронумерованы кодами PG-CP-NNN — на них ссылается скилл ucp-pg-runtime-review.

1. Размер пула — формула Wooldridge

`PG-CP-001` — Базовая формула: `connections = ((core_count × 2) + effective_spindle_count)`

Для современных SSD-серверов с N CPU-ядрами оптимум обычно 2N+1 = ~10–20 соединений.

Это контр-интуитивно. «Больше соединений = больше throughput» — миф. На самом деле:

Контекст-свитч между процессами PG имеет цену.
Lock contention растёт квадратично от числа конкурентов.
При размере пула 20 на 8-ядерном сервере throughput может быть выше, чем при 100.

`PG-CP-002` — Целевой размер пула на инстанс: 10–20

Если кажется, что нужно больше — сначала измерь, потом увеличивай. Часто узкое место — медленные запросы или долгие транзакции, не нехватка пула.

`PG-CP-003` — Бюджет соединений PostgreSQL = `max_connections` (default 100)

Раздели между всеми инстансами всех сервисов. На 10 инстансов сервиса по 20 соединений — уже 200 (превышает default). Решение — увеличить max_connections (до 300–500 разумно) или PgBouncer (см. §5).

2. HikariCP — основные параметры

`PG-CP-010` — Минимальная конфигурация в Spring Boot (`application.yml`):

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 20             # держи равным maximum — иначе pool ramp-up на холодную
      connection-timeout: 3000     # 3 сек ждать соединение
      idle-timeout: 600000         # 10 мин до закрытия idle (с min-idle = max не сработает)
      max-lifetime: 1800000        # 30 мин — обновлять соединения
      leak-detection-threshold: 60000  # 60 сек — алёрт на утечку
      validation-timeout: 5000
      auto-commit: false           # см. §3

`PG-CP-011` — `maximum-pool-size = minimum-idle`

— пусть пул всегда полный. Динамика «то 5, то 20» создаёт latency-всплески при ramp-up на пиках.

`PG-CP-012` — `connection-timeout: 3 сек`

— если за 3 секунды не дали соединение, лучше упасть, чем держать поток. На веб-фронте таймаут запроса обычно 30 сек, но 3 сек ожидания пула — индикатор перегрузки, нужна реакция.

`PG-CP-013` — `max-lifetime: 30 мин`

— каждое соединение через полчаса закрывается и пересоздаётся. Это защищает от утечек памяти на стороне PG (подросший work_mem, накопившиеся prepared statements) и от «тихой» поломки соединений за load balancer'ом.

Должен быть меньше, чем server-side idle_in_transaction_session_timeout и timeout-ы балансировщиков.

`PG-CP-014` — `leak-detection-threshold: 60 сек`

— HikariCP залогирует stack trace, если соединение не вернулось в пул за минуту. Признак забытого close(), @Transactional вокруг долгого HTTP-вызова, висящей транзакции.

`PG-CP-015` — Не отключай `leak-detection`!

Это бесплатный мониторинг утечек.

3. `auto-commit` — нюансы со Spring

`PG-CP-020` — `auto-commit = false` в Spring Boot — обычно правильно

Spring управляет транзакциями сам через @Transactional. Соединение берётся из пула, открывается транзакция, после метода — commit/rollback.

`PG-CP-021` — Если включить `auto-commit = true`:

каждый отдельный statement автокоммитится сразу. Spring ставит autocommit в false перед @Transactional методом и возвращает в true после. Это требует доп. round-trip к БД на каждый borrow и return соединения. На high-throughput заметная latency.

В Spring Boot по умолчанию auto-commit = true (HikariCP default). Меняешь в конфиге — но проверь, что код не делает явные setAutoCommit(false) без откатки.

4. Diagnostic — как понять «пул маленький / пул протекает»

`PG-CP-030` — Метрики HikariCP в Micrometer:

management:
  metrics:
    enable:
      hikaricp: true

Что мониторить:

hikaricp.connections.active — занято.
hikaricp.connections.idle — свободно.
hikaricp.connections.pending — потоки, ждущие соединение (если > 0 на постоянке — пул мал).
hikaricp.connections.usage — гистограмма «сколько секунд держали».
hikaricp.connections.timeout — счётчик таймаутов connection-timeout.

`PG-CP-031` — Если `pending > 0` стабильно — увеличь пул или сократи длительность транзакций

`PG-CP-032` — Если `usage` p99 > секунды — где-то долгие транзакции

Long @Transactional на HTTP-вызов, забытый commit, лок-wait.

`PG-CP-033` — Утечки соединений — `leak-detection-threshold` + alert на `hikaricp.connections.timeout`

5. PgBouncer — внешний пулер

`PG-CP-040` — PgBouncer оправдан, когда:

Много инстансов одного сервиса (десятки), и каждому нужны соединения.
Много разных сервисов на один кластер PG.
Serverless / lambda — частые коротко-живущие воркеры, каждому открывать соединение к PG дорого.
Нужно ограничить общее число соединений к PG ниже суммы всех app-pool'ов.

PgBouncer — лёгкий процесс, обслуживает тысячи клиент-соединений на маленьком числе real-соединений к PG.

5.1. Уровни pooling

`PG-CP-041` — PgBouncer поддерживает три уровня:

Уровень	Когда соединение возвращается в пул	Что не работает
`session` (default)	После disconnect клиента	Никаких ограничений
`transaction`	После каждого `COMMIT`/`ROLLBACK`	Prepared statements (на client side), session vars (`SET LOCAL` ok), advisory locks (sessionном scope), `LISTEN`/`NOTIFY`, `WITH HOLD` cursors
`statement`	После каждого SQL-statement	Транзакции из >1 statement, prepared statements, всё то же

`PG-CP-042` — `transaction` — обычный выбор

Даёт максимальную утилизацию пула при минимуме ограничений. Spring Boot + jOOQ нормально с ним работают.

`PG-CP-043` — `session` — когда нужны server-side prepared statements или `LISTEN`/`NOTIFY`

Менее эффективен, ближе к «никакого пулинга».

`PG-CP-044` — `statement` — почти никогда

Ломает даже очень простые транзакции.

5.2. Ограничения transaction-level

`PG-CP-045` — На `transaction` мode не работают server-side prepared statements

В Spring/JDBC по умолчанию HikariCP использует prepareThreshold > 0 — после 5 выполнений statement становится server-side prepared. Через PgBouncer этот state теряется.

Решение: на стороне приложения отключить server-side prepares для PgBouncer:

spring:
  datasource:
    hikari:
      data-source-properties:
        prepareThreshold: 0   # отключить server-side prepared

Или PgBouncer 1.21+ умеет prepared-statements caching сам. Проверь конкретную версию.

`PG-CP-046` — `SET` команды (без `LOCAL`) не работают

Любые SET work_mem = '64MB' теряются после COMMIT. Используй SET LOCAL внутри транзакции.

`PG-CP-047` — `LISTEN`/`NOTIFY` не работает на transaction mode

— connection не сохраняется между транзакциями. Если нужен PG NOTIFY — отдельный пул в session mode или другой механизм (Kafka, Redis pub/sub).

5.3. Размер пулов с PgBouncer

`PG-CP-050` — С PgBouncer пул в HikariCP может быть БОЛЬШЕ

Например, у каждого инстанса HikariCP пул 50, но PgBouncer держит к PG только 20 real-соединений. Эффект: 50 потоков могут одновременно «думать в коде», но к PG идёт максимум 20.

Соотношение: app_pool : pgbouncer_to_pg = 5:1 или больше.

# pgbouncer.ini
[databases]
mydb = host=postgres-master port=5432 dbname=mydb pool_mode=transaction

[pgbouncer]
pool_mode = transaction
default_pool_size = 20      # к PG
max_client_conn = 1000      # от приложений
reserve_pool_size = 5

6. Read-replica — отдельный пул

`PG-CP-060` — Если есть read-replica — отдельный DataSource + отдельный HikariCP пул

@Configuration
public class DataSourceConfig {
    @Bean @Primary
    @ConfigurationProperties("spring.datasource.master")
    public HikariConfig masterConfig() { return new HikariConfig(); }

    @Bean
    @ConfigurationProperties("spring.datasource.replica")
    public HikariConfig replicaConfig() { return new HikariConfig(); }

    @Bean @Primary
    public DataSource masterDataSource(HikariConfig masterConfig) {
        return new HikariDataSource(masterConfig);
    }

    @Bean
    public DataSource replicaDataSource(HikariConfig replicaConfig) {
        return new HikariDataSource(replicaConfig);
    }

    @Bean
    public DataSource routingDataSource(DataSource masterDataSource, DataSource replicaDataSource) {
        var routing = new TransactionRoutingDataSource();
        routing.setTargetDataSources(Map.of(
            DataSourceType.READ_WRITE, masterDataSource,
            DataSourceType.READ_ONLY,  replicaDataSource
        ));
        routing.setDefaultTargetDataSource(masterDataSource);
        return routing;
    }
}

С AbstractRoutingDataSource Spring может выбирать DataSource на основе TransactionSynchronizationManager.isCurrentTransactionReadOnly() — @Transactional(readOnly = true) отправляется на реплику, остальное — на мастер.

`PG-CP-061` — Read-replica запросы — eventual consistency

Replication lag — обычно миллисекунды, но при нагрузке может расти до секунд. Не используй реплику для read-after-write сценариев («только что создал, сразу читаю»).

7. Антипаттерны

`PG-CP-080` — Огромный пул (`maximum-pool-size = 200`)

— почти всегда пеcсимизация, не оптимизация. Сначала найди узкое место.

`PG-CP-081` — Разные пулы на одну БД для одного приложения

— каждый пул думает, что владеет всеми соединениями, в сумме перерасход.

`PG-CP-082` — `@Transactional` вокруг внешнего HTTP-вызова

— соединение из пула удерживается на всё время вызова. См. WAL §8 и PG-W-061.

`PG-CP-083` — Отключение `leak-detection-threshold`

— не «решение проблемы», а её сокрытие.

`PG-CP-084` — `auto-commit = true` глобально с явным `@Transactional`

— лишний round-trip на каждый borrow.

`PG-CP-085` — PgBouncer на `session` mode без явной причины

— теряется главный выигрыш (большая утилизация).

`PG-CP-086` — PgBouncer на `transaction` mode + server-side prepared statements

— теряется prepared cache, JDBC начинает дёргать PG на каждом запросе.

8. Чек-лист настройки

[ ] maximum-pool-size = minimum-idle = 10–20 на инстанс приложения.
[ ] connection-timeout = 3 сек.
[ ] max-lifetime = 30 мин (меньше LB-таймаута).
[ ] leak-detection-threshold = 60 сек включен.
[ ] HikariCP-метрики экспортированы в Micrometer.
[ ] Алёрт на hikaricp.connections.pending > 0 в течение минуты.
[ ] Алёрт на hikaricp.connections.timeout count > 0.
[ ] max_connections PG ≥ сумме всех пулов всех инстансов всех сервисов.
[ ] При наличии PgBouncer — выбран transaction mode (или явно обоснован session).
[ ] При PgBouncer + JDBC: prepareThreshold = 0 или PgBouncer 1.21+ с native prepared support.
[ ] Read-replica — отдельный пул, отдельный DataSource, routing через @Transactional(readOnly = true).

Миграции и breaking changes без даунтайма

Миграция БД — это не «выполнил SQL и пошёл дальше». На живой нагрузке ALTER TABLE берёт ACCESS EXCLUSIVE lock и блокирует всё — INSERT, UPDATE, SELECT. На таблице 100M строк это минуты-часы простоя. Эта статья — как менять схему так, чтобы прод не падал.

Правила пронумерованы кодами PG-M-NNN — на них ссылается скилл ucp-pg-migration-review.

1. Что такое breaking change

`PG-M-001` — Любая операция, которая ломает работу СТАРОЙ версии кода, — breaking change

Не-breaking (можно катить):

ADD COLUMN ... NULL (PG11+ с DEFAULT тоже).
ADD INDEX CONCURRENTLY.
CREATE TABLE.
CREATE INDEX CONCURRENTLY.
ADD CONSTRAINT ... NOT VALID.

Breaking (нужен expand-contract):

DROP COLUMN.
RENAME COLUMN.
ALTER TYPE (изменение типа).
ADD COLUMN ... NOT NULL без default (на больших таблицах).
DROP TABLE.
RENAME TABLE.
Удаление значения из enum.
Сужение CHECK-constraint (был >=0, стал >0).

`PG-M-002` — Правило N-1: миграция должна быть совместима с предыдущей версией кода

Деплой идёт в порядке: миграция → новый код. Между ними — окно, где старый код работает с новой схемой. Если миграция breaking, в этом окне старый код упадёт.

2. Expand-Contract — ключевой паттерн

`PG-M-010` — Любое breaking-изменение режется на 3+ релиза

Шаги:

Expand — добавили новое (старое работает).
Migrate data + dual-write в коде (пишем и в старое, и в новое).
Switch reads — читатели переключены на новое.
Contract — старое удалено.

Между шагами — отдельный релиз приложения.

Релиз 1 (expand):       миграция: добавить новую колонку/таблицу/индекс
                        код: пишет в старое, опционально dual-write в новое

Релиз 2 (migrate):       миграция: backfill старых данных в новое
                        код: dual-write обязателен; читатели всё ещё на старом

Релиз 3 (switch reads):  код: читатели переключены на новое
                        миграция: ничего

Релиз 4 (contract):      миграция: дропнуть старое
                        код: убрать упоминания старого

3 деплоя — не баг, а фича. Каждый релиз можно откатить независимо.

3. ALTER TABLE — что переписывает таблицу

`PG-M-020` — Что берёт `ACCESS EXCLUSIVE` (блокирует всё) и переписывает таблицу:

Операция	Перепис. таблицу	Lock
`ADD COLUMN` без `DEFAULT`	нет (PG11+)	ACCESS EXCLUSIVE кратко
`ADD COLUMN ... NULL DEFAULT 'X'`	нет (PG11+)	ACCESS EXCLUSIVE кратко
`ADD COLUMN ... NOT NULL DEFAULT 'X'`	нет (PG11+)	ACCESS EXCLUSIVE кратко
`ADD COLUMN ... NOT NULL` без `DEFAULT`	нет (но требует данных в колонке)	ACCESS EXCLUSIVE
`DROP COLUMN`	нет (помечает invisible)	ACCESS EXCLUSIVE кратко
`ALTER TYPE` (с приведением)	да, всю	ACCESS EXCLUSIVE минуты-часы
`SET NOT NULL`	нет, но проверяет каждую строку	ACCESS EXCLUSIVE
`ADD CONSTRAINT CHECK`	нет, но проверяет каждую строку	ACCESS EXCLUSIVE
`ADD CONSTRAINT FOREIGN KEY`	нет, но проверяет каждую строку	ACCESS EXCLUSIVE
`ADD CONSTRAINT ... NOT VALID`	нет, не проверяет	ACCESS EXCLUSIVE кратко

`PG-M-021` — `ACCESS EXCLUSIVE` lock встаёт в очередь после ВСЕХ ждущих и блокирует ВСЕХ новых

Если хоть один долгий SELECT идёт — миграция ждёт его, а за ней копится очередь новых запросов. Эффект — кратковременный stall на проде, даже если сама миграция занимает миллисекунды.

`PG-M-022` — `SET LOCAL lock_timeout = '3s'` в начале каждой миграции

Если за 3 секунды лок не получен — миграция упала, лучше повторить позже, чем заблокировать прод.

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE order_doc ADD COLUMN ...;
COMMIT;

4. Рецепты конкретных breaking changes

4.1. `ADD COLUMN NOT NULL`

`PG-M-030` — На PG11+ с `DEFAULT` — мгновенно. Без — нужен expand-contract

Хороший путь (PG11+):

ALTER TABLE order_doc ADD COLUMN priority integer NOT NULL DEFAULT 0;
-- мгновенно: PG не переписывает строки, читает default «виртуально»

Плохой путь до PG11 (или если default — функция типа gen_random_uuid()):

Шаги:

Релиз 1: ADD COLUMN priority integer NULL + код пишет значение в новых строках.
Релиз 2 (через время или миграция): UPDATE order_doc SET priority = 0 WHERE priority IS NULL; (батчами).
Релиз 3: ALTER TABLE ... ALTER COLUMN priority SET NOT NULL;.

`PG-M-031` — `SET NOT NULL` блокирует таблицу на проверку каждой строки. На большой таблице — минуты

Безопасный паттерн (PG12+):

-- 1. CHECK NOT VALID — мгновенно, не проверяет существующие строки
ALTER TABLE order_doc ADD CONSTRAINT ck_order_priority_not_null
    CHECK (priority IS NOT NULL) NOT VALID;

-- 2. VALIDATE — проверяет, но НЕ берёт ACCESS EXCLUSIVE (только SHARE UPDATE EXCLUSIVE)
ALTER TABLE order_doc VALIDATE CONSTRAINT ck_order_priority_not_null;

-- 3. SET NOT NULL — теперь дёшево, потому что PG знает из CHECK что null'ов нет
ALTER TABLE order_doc ALTER COLUMN priority SET NOT NULL;

-- 4. Опционально: дропнуть избыточный CHECK
ALTER TABLE order_doc DROP CONSTRAINT ck_order_priority_not_null;

4.2. `RENAME COLUMN`

`PG-M-040` — Невозможно одним коммитом без даунтайма

Старый код знает old_name, новый — new_name.

Шаги:

Релиз 1: ADD COLUMN new_name <type>; + dual-write в коде (UPDATE ... SET new_name = ?, old_name = ?).
Бэкфилл: UPDATE table SET new_name = old_name WHERE new_name IS NULL; (батчами).
Релиз 2: код читает из new_name, продолжает dual-write.
Релиз 3: код только new_name.
Миграция: ALTER TABLE ... DROP COLUMN old_name;.

Долго и нудно. Альтернатива — view-обёртка:

ALTER TABLE order_doc RENAME COLUMN old_name TO new_name;
CREATE VIEW order_doc_v AS SELECT *, new_name AS old_name FROM order_doc;
-- старый код через view, новый — через таблицу. Через релиз view дропнуть.

Работает только на чтение (INSERT/UPDATE через view с computed column не пройдут). Для read-only переходов — отличный путь.

4.3. `ALTER TYPE` (изменение типа колонки)

`PG-M-050` — `ALTER TABLE ... ALTER COLUMN ... TYPE ...` переписывает всю таблицу

На больших — часы под ACCESS EXCLUSIVE.

Безопасный паттерн (expand-contract):

Релиз 1: ADD COLUMN amount_v2 numeric(15, 2); + dual-write (amount, amount_v2).
Бэкфилл: UPDATE order_doc SET amount_v2 = amount WHERE amount_v2 IS NULL; батчами.
Релиз 2: код читает amount_v2, dual-write остаётся.
Релиз 3: код только amount_v2.
Миграция: DROP COLUMN amount; + RENAME COLUMN amount_v2 TO amount;.

Исключение — приводимые типы внутри одной семьи (int → bigint, varchar(50) → varchar(100), varchar → text). PG умеет без переписи в некоторых случаях:

varchar → text — мгновенно (одинаковое представление).
varchar(50) → varchar(100) — мгновенно (расширение).
varchar(100) → varchar(50) — переписывает (надо проверить).
int → bigint — переписывает.

4.4. `DROP COLUMN`

`PG-M-060` — `DROP COLUMN` дёшев (PG только помечает invisible), но требует expand-contract по коду:

старая версия упадёт на INSERT INTO ... (col, ...) VALUES (...).

Шаги:

Релиз 1: код перестал писать в колонку (только пишет, можно оставить NULL).
Релиз 2: код перестал упоминать колонку вообще.
Миграция: ALTER TABLE ... DROP COLUMN ...;.

4.5. `ADD FOREIGN KEY`

`PG-M-070` — `ADD CONSTRAINT FOREIGN KEY` проверяет каждую существующую строку под `ACCESS EXCLUSIVE`

На больших — больно.

Безопасный паттерн:

-- 1. Добавить как NOT VALID — мгновенно, проверяет только новые INSERT
ALTER TABLE order_item
  ADD CONSTRAINT fk_order_item_order_id
  FOREIGN KEY (order_id) REFERENCES order_doc(id)
  NOT VALID;

-- 2. VALIDATE отдельно — берёт SHARE UPDATE EXCLUSIVE (не блокирует чтение/запись)
ALTER TABLE order_item VALIDATE CONSTRAINT fk_order_item_order_id;

То же для UNIQUE через индекс:

-- 1. Создать unique index concurrently (без блокировки)
CREATE UNIQUE INDEX CONCURRENTLY uk_customer_email ON customer (email);

-- 2. Прицепить к констрейнту (мгновенно)
ALTER TABLE customer ADD CONSTRAINT uk_customer_email UNIQUE USING INDEX uk_customer_email;

4.6. Индексы

`PG-M-080` — В продакшен-миграциях — всегда `CREATE INDEX CONCURRENTLY` и `DROP INDEX CONCURRENTLY`

Без CONCURRENTLY:

CREATE INDEX берёт SHARE lock — блокирует INSERT/UPDATE/DELETE до конца построения. На big table — десятки минут даунтайма записи.
DROP INDEX берёт ACCESS EXCLUSIVE — блокирует и чтение тоже.

С CONCURRENTLY:

CREATE INDEX CONCURRENTLY — два прохода без блокировки. Минусы: нельзя в транзакции, в Liquibase runInTransaction="false".
Если упало — индекс остаётся INVALID, надо DROP INDEX и пересоздать.

<changeSet id="20260507-add-customer-status-index" author="vadim" runInTransaction="false">
    <sql>CREATE INDEX CONCURRENTLY ix_customer_status ON customer (status);</sql>
    <rollback>DROP INDEX CONCURRENTLY IF EXISTS ix_customer_status;</rollback>
</changeSet>

4.7. Удаление значения из enum

`PG-M-090` — Нативно невозможно. Замена enum через теневой тип

Шаги:

Создать новый тип: CREATE TYPE order_status_v2 AS ENUM ('NEW','PAID','SHIPPED');.
Добавить временную колонку: ALTER TABLE order_doc ADD COLUMN status_v2 order_status_v2;.
Бэкфилл: UPDATE order_doc SET status_v2 = status::text::order_status_v2; (для удаляемого значения — отдельно мапнуть в новое).
Релиз кода: dual-write обоих, читать новый.

Удалить старую колонку, переименовать новую:

ALTER TABLE order_doc DROP COLUMN status;
ALTER TABLE order_doc RENAME COLUMN status_v2 TO status;
DROP TYPE order_status;

`PG-M-091` — Добавление значения в enum (PG12+) — мгновенно (`ALTER TYPE ... ADD VALUE`)

, но новое значение нельзя использовать в той же транзакции:

BEGIN;
ALTER TYPE order_status ADD VALUE 'PARTIALLY_REFUNDED';
INSERT INTO order_doc (status) VALUES ('PARTIALLY_REFUNDED');  -- ОШИБКА
COMMIT;

В Liquibase это значит: ADD VALUE и использование — в разных changeset'ах.

`PG-M-092` — Переименование значения (PG10+):

ALTER TYPE order_status RENAME VALUE 'OLD' TO 'NEW';. Мгновенно. Но координация с кодом — N-1 правило.

4.8. `DROP TABLE` / `RENAME TABLE`

`PG-M-100` — Тривиально по DDL, но требует, чтобы вся версия кода уже не трогала таблицу

Шаги для drop:

Релиз 1: код перестал ссылаться на таблицу.
Релиз 2 (через несколько дней — на случай rollback): миграция DROP TABLE old_name;.

5. Long-running data migrations

`PG-M-110` — `UPDATE` миллионов строк одним statement — открывает гигантскую транзакцию, копит WAL, блокирует autovacuum

Делай батчами.

-- плохо: одна гигантская TX
UPDATE order_doc SET priority = 0 WHERE priority IS NULL;

-- лучше: батчи через cursor / LIMIT
DO $$
DECLARE
    rows_updated integer := 1;
BEGIN
    WHILE rows_updated > 0 LOOP
        UPDATE order_doc
        SET priority = 0
        WHERE id IN (
            SELECT id FROM order_doc
            WHERE priority IS NULL
            LIMIT 10000
        );
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        COMMIT;  -- между батчами
        PERFORM pg_sleep(0.1);  -- даём дыхание replica/autovacuum
    END LOOP;
END $$;

`PG-M-111` — На очень больших таблицах — отдельный backfill-job в коде

, не в миграции. Миграция должна выполниться за минуту максимум.

@Scheduled(cron = "0 * * * * *")
public void backfillPriority() {
    int updated;
    do {
        updated = jdbc.update("""
            UPDATE order_doc SET priority = 0
            WHERE id IN (
                SELECT id FROM order_doc
                WHERE priority IS NULL
                LIMIT 10000
                FOR UPDATE SKIP LOCKED
            )
            """);
    } while (updated == 10000);
}

SKIP LOCKED чтобы не конфликтовать с пользовательскими UPDATE-ами.

6. Rollback миграций — почему forward-fix лучше

`PG-M-120` — `down`-миграции (rollback) почти всегда не работают на проде

Liquibase <rollback> блок предполагает, что можно «открутить» изменение. На практике:

Если миграция применилась + работала с данными — rollback теряет данные (DROP COLUMN после backfill необратим).
Если миграция в составе нескольких — rollback одной разрушает консистентность.
Если уже видели pull request с rollback'ом — это значит «приходится крутить туда-сюда», что само по себе плохой знак.

`PG-M-121` — Реальный «откат» — это новая forward-миграция, отменяющая последствия

Если catastrophe — restore из backup, не rollback.

7. Координация с приложением

`PG-M-130` — Стандартный flow деплоя:

Миграция накатывается.
Один-два инстанса приложения обновляются (canary).
Через минут 5 — остальные инстансы.
Через час-сутки — следующая миграция, если есть «фаза 2».

`PG-M-131` — Миграция совместима с предыдущей версией кода (N-1)

Это значит:

ADD COLUMN всегда нullable (или с default).
Не добавлять NOT NULL constraint, пока старый код может писать строки без значения.
Не дропать колонки, пока в коде есть упоминания.

`PG-M-132` — CI-проверка совместимости

Запустить тесты предыдущей версии кода против новой схемы. Если упало — миграция не N-1.

8. Lint миграций

`PG-M-140` — `squawk` — линтер для миграций

Ловит:

ALTER TABLE ... ADD COLUMN с DEFAULT (для PG <11 — переписывает таблицу).
CREATE INDEX без CONCURRENTLY.
ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY без NOT VALID.
ALTER TABLE ... ALTER COLUMN ... TYPE (переписывает таблицу).
Renaming колонок/таблиц.
DROP COLUMN.

squawk db/changelog/v0042.sql

Подключи в pre-commit hook + CI.

9. Чек-лист на ревью миграции

Это transaction-safe? Можно ли откатить, не потеряв данные? (Обычно нет — спросить, если кажется надо.)
ALTER TABLE берёт ACCESS EXCLUSIVE? Если да — есть lock_timeout?
CREATE INDEX имеет CONCURRENTLY? Если да — runInTransaction="false" в Liquibase?
SET NOT NULL? Используется паттерн CHECK NOT VALID + VALIDATE + SET NOT NULL?
Foreign key? NOT VALID + отдельный VALIDATE?
Renaming колонки/таблицы? Расписан expand-contract на 3+ релиза?
UPDATE миллионов строк? Вынесено в backfill-job, не в миграцию?
DROP COLUMN? Подтверждено, что код не использует колонку?
ALTER TYPE? Переписывает таблицу — есть план через теневую колонку?
N-1 совместимость? Старая версия кода работает с новой схемой?

Полнотекстовый поиск (FTS)

PostgreSQL умеет полнотекстовый поиск из коробки: токенизация, словари (включая русский), стемминг, ранжирование. Для большинства проектов «поиск по статьям/товарам/контенту» PG FTS закрывает 90% задач без отдельного Elasticsearch.

Эта статья — что хватает, когда нужен ES, как настроить под русский язык. Правила пронумерованы кодами PG-FTS-NNN.

1. Когда PG FTS хватает

`PG-FTS-001` — PG FTS подходит для:

Корпоративный/админский поиск (≤ 10M документов, ≤ 100 запросов/сек).
Поиск товаров в e-commerce среднего масштаба.
Поиск по комментариям, тикетам, документации.
Когда не нужны: scoring алгоритмы (BM25), агрегации, фасетный поиск, geosearch с релевантностью.

`PG-FTS-002` — PG FTS не подходит:

≫ 10M документов с ranking-нагрузкой → Elasticsearch / Meilisearch.
Сложный fuzzy-поиск с typo tolerance → Elasticsearch с edge-ngram.
Multi-language с автоопределением → Elasticsearch.
Aggregations + facets + analytics → специализированный движок.

2. Базовые типы и функции

`PG-FTS-010` — `tsvector` — токенизированный документ. `tsquery` — запрос

-- toString → tsvector через словарь
SELECT to_tsvector('russian', 'Покупатели выбирают товары в каталоге');
-- 'выбира':2 'каталог':5 'покупател':1 'товар':3

SELECT to_tsquery('russian', 'покупатель & каталог');
-- 'покупател' & 'каталог'

-- match
SELECT to_tsvector('russian', 'Покупатели выбирают товары в каталоге')
       @@ to_tsquery('russian', 'покупатель & каталог');
-- t (true)

`PG-FTS-011` — `@@` — оператор поиска

tsvector @@ tsquery возвращает boolean.

3. Хранение `tsvector`

Два подхода:

`PG-FTS-020` — Generated column (рекомендуется, PG12+):

CREATE TABLE article (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title        text NOT NULL,
    body         text NOT NULL,
    search_doc   tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('russian', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('russian', coalesce(body, '')), 'B')
    ) STORED
);

CREATE INDEX ix_article_search_doc ON article USING gin (search_doc);

PG сам пересчитывает search_doc при каждом INSERT/UPDATE. setweight('A') для важных полей (title), 'B'/'C'/'D' для менее.

`PG-FTS-021` — Триггер (для PG <12):

CREATE TRIGGER article_search_update
BEFORE INSERT OR UPDATE ON article
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_doc, 'pg_catalog.russian', title, body);

`PG-FTS-022` — Хранить `tsvector` отдельно от текста, не вычислять на лету

Без stored column каждый запрос будет рассчитывать to_tsvector(...) @@ ... — без индекса, медленно.

4. Запросы

`PG-FTS-030` — Базовый поиск:

SELECT id, title, ts_rank(search_doc, q) AS rank
FROM article, to_tsquery('russian', 'покупатель & товар') q
WHERE search_doc @@ q
ORDER BY rank DESC
LIMIT 20;

`PG-FTS-031` — `plainto_tsquery` для пользовательского ввода — безопаснее:

SELECT * FROM article WHERE search_doc @@ plainto_tsquery('russian', $1);
-- 'купить новый товар' → 'купи' & 'новый' & 'товар'

`PG-FTS-032` — `websearch_to_tsquery` — синтаксис Google-style

(PG11+):

SELECT * FROM article WHERE search_doc @@ websearch_to_tsquery('russian', $1);
-- 'покупатель -ребёнок "новый каталог"'
-- → minus, фразы в кавычках

`PG-FTS-033` — Ranking — `ts_rank` или `ts_rank_cd`

(cover density). Для большинства проектов хватает ts_rank с весами через setweight.

5. Подсветка результатов

`PG-FTS-040` — `ts_headline` — подсветка совпадений в результате:

SELECT
    id,
    title,
    ts_headline('russian', body, q,
        'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MaxWords=20')
        AS snippet
FROM article, websearch_to_tsquery('russian', $1) q
WHERE search_doc @@ q
ORDER BY ts_rank(search_doc, q) DESC
LIMIT 20;

6. Конфигурации (словари)

`PG-FTS-050` — Встроенные конфигурации в PG:

simple — без стемминга, только нижний регистр.
english, russian, german, ... — стемминг по языку.

SELECT cfgname FROM pg_ts_config;

`PG-FTS-051` — Если нужно уточнить стемминг (бренды, термины) — кастомная конфигурация:

CREATE TEXT SEARCH DICTIONARY my_synonyms (
    template = synonym,
    synonyms = 'my_synonyms'   -- файл $SHAREDIR/tsearch_data/my_synonyms.syn
);

CREATE TEXT SEARCH CONFIGURATION ru_extended (COPY = russian);
ALTER TEXT SEARCH CONFIGURATION ru_extended
    ALTER MAPPING FOR word, asciiword
    WITH my_synonyms, russian_stem;

В файле my_synonyms.syn:

postgresql postgres
postgres postgres pg

`PG-FTS-052` — Stop words настраиваются через словарь

Дефолтный russian имеет встроенный список (предлоги, союзы).

7. Индексы — GIN vs GiST

`PG-FTS-060` — Для read-heavy — GIN. Для write-heavy — GiST

	GIN	GiST
Размер	больше	меньше
Скорость build	медленнее	быстрее
Скорость search	быстрее	медленнее
Скорость update	медленнее	быстрее
Lossy (false positive)	нет	да

В 95% случаев — GIN.

`PG-FTS-061` — `fastupdate` для GIN

— буфер pending-list ускоряет вставки. Default on. Для read-heavy с редкими вставками можно off:

ALTER INDEX ix_article_search_doc SET (fastupdate = off);

8. `pg_trgm` — триграммы для коротких фраз и опечаток

`PG-FTS-070` — `pg_trgm` дополняет FTS, когда нужны:

Поиск с опечатками (similarity() через триграммы).
LIKE '%substring%' с индексом.
Поиск по очень коротким значениям (имена, бренды), где FTS оверкилл.

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_customer_name_trgm
    ON customer USING gin (full_name gin_trgm_ops);

-- ускоряет:
SELECT * FROM customer WHERE full_name ILIKE '%иван%';
SELECT * FROM customer WHERE similarity(full_name, 'иванв') > 0.4
ORDER BY similarity(full_name, 'иванв') DESC LIMIT 10;

`PG-FTS-071` — Комбинируй FTS + триграммы:

FTS для длинного контента (тело статьи), pg_trgm для коротких полей с опечатками (имя автора, бренд).

9. Производительность и пагинация

`PG-FTS-080` — OFFSET + LIMIT для глубокой пагинации страдает

PG ranks ВСЕ совпадения, потом дропает offset. Для глубоких страниц — keyset pagination по rank+id:

SELECT id, title, ts_rank(search_doc, q) AS rank
FROM article, websearch_to_tsquery('russian', $1) q
WHERE search_doc @@ q
  AND (ts_rank(search_doc, q), id) < ($prev_rank, $prev_id)
ORDER BY rank DESC, id DESC
LIMIT 20;

`PG-FTS-081` — Использовать `ts_rank` в `WHERE` неэффективно

Считается на каждом совпадении. Лучше — limit'ить только в ORDER BY.

10. Антипаттерны

PG-FTS-090 Хранить to_tsvector(...) как stored function в WHERE — без индекса, медленно.

PG-FTS-091 Использовать simple конфигурацию для русского текста — без стемминга «покупатель» и «покупателя» — разные токены.

PG-FTS-092 Игнорировать веса setweight — все совпадения равны, ranking неточный.

PG-FTS-093 Использовать FTS для ≫10M документов с высокой нагрузкой ранжирования — придёт время Elasticsearch.

PG-FTS-094 Триграммы pg_trgm на полях > 100 символов — индекс распухает, медленно.

Materialized views и стратегии refresh

VIEW — это сохранённый SQL-запрос, который выполняется при каждом обращении. MATERIALIZED VIEW — это VIEW, чей результат сохранён физически на диске и обновляется по команде. По сути — закэшированный snapshot тяжёлого запроса.

Эта статья — когда MV оправдан, как обновлять без блокировки, чем отличается от Read Model в коде. Правила пронумерованы кодами PG-MV-NNN.

1. Когда MV оправдан

`PG-MV-001` — MV подходит для:

Тяжёлые агрегации, которые читают часто, а изменения видны с задержкой ОК (отчёты, dashboards).
Сложные join'ы по нескольким таблицам, где данные не критично актуальны.
Pre-computed search-индексы (см. FTS).
Денормализация для read-heavy нагрузки.

`PG-MV-002` — MV не подходит:

Real-time данные (refresh имеет латентность).
Часто меняющиеся данные, где cost refresh > выигрыш чтения.
Простые запросы без агрегаций — обычный VIEW или индекс.

2. Базовый паттерн

`PG-MV-010` — Создание:

CREATE MATERIALIZED VIEW order_stats_mv AS
SELECT
    customer_id,
    count(*) AS orders_count,
    sum(total_amount) AS total_spent,
    max(created_at) AS last_order_at
FROM order_doc
WHERE status != 'CANCELLED'
GROUP BY customer_id
WITH DATA;     -- (default) — сразу заполнить данными

WITH NO DATA — создать пустой, заполнить позже через REFRESH. Полезно для миграций.

`PG-MV-011` — Запрос — как к обычной таблице:

SELECT * FROM order_stats_mv WHERE customer_id = 42;

`PG-MV-012` — Индексы на MV — обязательны для частых WHERE:

CREATE INDEX ix_order_stats_customer ON order_stats_mv (customer_id);
CREATE UNIQUE INDEX uk_order_stats_customer ON order_stats_mv (customer_id);
-- UNIQUE-индекс нужен для CONCURRENTLY refresh (см. §4)

3. Стратегии refresh

`PG-MV-020` — Три варианта обновления:

REFRESH MATERIALIZED VIEW (полный, блокирующий)

REFRESH MATERIALIZED VIEW order_stats_mv;

Перевычисляет всё с нуля.
Берёт ACCESS EXCLUSIVE lock — блокирует SELECT во время refresh.
На большой MV (миллионы строк) — минуты блокировки.

`PG-MV-021` — Используй только если:

MV маленькая (< 100K строк).
Refresh идёт в окне обслуживания.
Нет онлайн-чтения во время refresh.

REFRESH MATERIALIZED VIEW CONCURRENTLY (без блокировки)

REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv;

Не блокирует SELECT.
Использует UNIQUE-индекс для diff'а — обновляет только изменившиеся строки.
Требует UNIQUE-индекс на MV (любая колонка/комбинация уникальная).
Медленнее обычного REFRESH (требует diff'а).

`PG-MV-022` — CONCURRENTLY — стандартный выбор для прод-MV

Инкрементальный refresh (через триггеры или ручной)

`PG-MV-023` — PG не поддерживает инкрементальный refresh из коробки

Когда нужен — варианты:

Расширение pg_ivm (incremental view maintenance).
Свой паттерн через триггеры на исходных таблицах + ручной UPDATE MV.
TimescaleDB continuous aggregates (если уже в стеке).

4. Когда refresh — крон, когда — реактивно

`PG-MV-030` — Cron / `@Scheduled` — для отчётов и дашбордов:

@Scheduled(fixedDelay = 300_000)   // каждые 5 минут
public void refreshOrderStats() {
    jdbc.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv");
}

Латентность 5 минут — приемлемо для аналитики.

`PG-MV-031` — Реактивно через триггер — когда задержка критична:

CREATE OR REPLACE FUNCTION refresh_order_stats()
RETURNS trigger AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_order_doc_refresh_stats
AFTER INSERT OR UPDATE OR DELETE ON order_doc
FOR EACH STATEMENT EXECUTE FUNCTION refresh_order_stats();

`PG-MV-032` — Триггер на каждое изменение — ОЧЕНЬ дорого

Refresh всей MV на каждый INSERT уничтожает throughput. Используй только когда:

Изменения редкие.
MV маленькая.
Альтернативы нет.

`PG-MV-033` — Лучше — debouncing через Redis/cron:

@Component
class OrderStatsRefresh {
    @EventListener
    public void onOrderChanged(OrderChangedEvent e) {
        redis.set("mv:order_stats:dirty", "1");
    }

    @Scheduled(fixedDelay = 60_000)
    public void refreshIfDirty() {
        if (redis.delete("mv:order_stats:dirty")) {
            jdbc.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats_mv");
        }
    }
}

Refresh не чаще раза в минуту, и только если что-то изменилось.

5. MV vs Read Model в коде

`PG-MV-040` — MV — это read model в БД. Read model в коде — отдельная таблица + код, обновляющий её

	MV	Read Model в коде
Логика обновления	SQL	Java code
Гранулярность	вся MV	можно по строкам
Латентность	секунды-минуты (refresh)	микросекунды (event-driven)
Сложность для разраба	низкая (один SQL)	высокая (eventual consistency)
Подходит для	отчёты, агрегации	CQRS write/read разделение

`PG-MV-041` — Простое правило выбора:

Запрос — SELECT ... GROUP BY сложный с JOIN'ами, читается часто, обновлять можно с задержкой → MV.
Read model нужна с минимальной задержкой, обновляется поэлементно при изменениях → отдельная таблица + event handler.

См. Уровень 2 UCP — Read Model в коде через UseCaseQuery.

6. Антипаттерны

`PG-MV-080` — `REFRESH MATERIALIZED VIEW` (без CONCURRENTLY) на проде

— блокирует всех читателей.

`PG-MV-081` — Триггер `AFTER ANY` с REFRESH

на горячей таблице — каждый INSERT триггерит full refresh.

`PG-MV-082` — MV без UNIQUE индекса

— нельзя CONCURRENTLY refresh, остаётся только blocking.

`PG-MV-083` — MV для real-time данных

— латентность refresh не покрывает требования к свежести.

`PG-MV-084` — MV вместо нормального индекса

— иногда «давай закэшируем SELECT WHERE foo = ?» решается обычным индексом по foo.

Партиционирование — когда, как, по какому ключу

Партиционирование — это разделение одной логической таблицы на несколько физических по правилу (диапазон дат, список значений, хэш). С точки зрения SQL это всё ещё одна таблица: SELECT FROM orders работает как раньше. Внутри PG читает только те партиции, которые подходят под WHERE.

Главный выигрыш — не «производительность сама по себе», а управляемость на больших объёмах: дроп старых данных мгновенный (DROP TABLE партиции), индексы строятся на одной партиции, vacuum работает на каждой отдельно, autovacuum не задыхается на 500-гигабайтной таблице.

Главный проигрыш — overhead планирования и сложность для маленьких таблиц. Партиции — это инструмент для больших таблиц.

1. Когда оправдано

`PG-P-001` — Партиционируй, если выполнено хотя бы одно:

Таблица > 50 GB (или > 100M строк) и продолжает расти.
Это time-series (события, метрики, логи) — естественное партиционирование по дате.
Старые данные регулярно удаляются по сроку (DELETE WHERE created_at < now() - interval '90 days' — мучительный seq scan, DROP TABLE партиции — мгновенный).
Multi-tenant с большими тенантами и желанием изолировать их per-tenant (хотя это спорный подход — см. §10).
Autovacuum не успевает на текущей таблице (видно по росту bloat и старому last_autovacuum).

`PG-P-002` — Партиционирование избыточно, если:

Таблица < 10 GB. Накладные расходы планировщика и управление партициями обойдутся дороже выигрыша.
Запросы редко включают потенциальный partition key в WHERE. Без partition pruning партиции только мешают.
Все партиции одного размера и читаются равномерно — это не партиции, это шардирование, и PG-партиции тут не помогут.

2. Жизненный цикл декларативной партиционированной таблицы

С PG10 партиционирование декларативное (раньше было через наследование — устарело).

-- родительская таблица (без данных)
CREATE TABLE event_log (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    payload     jsonb NOT NULL,
    PRIMARY KEY (id, occurred_at)              -- PK должен включать ключ партиционирования
) PARTITION BY RANGE (occurred_at);

-- партиции
CREATE TABLE event_log_2026_05 PARTITION OF event_log
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE event_log_2026_06 PARTITION OF event_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- INSERT идёт в родительскую, PG раскладывает по партициям
INSERT INTO event_log (occurred_at, payload) VALUES (now(), '{"k":"v"}');

-- SELECT с фильтром по ключу — partition pruning
SELECT * FROM event_log
WHERE occurred_at >= '2026-05-15' AND occurred_at < '2026-05-20';
-- читается ТОЛЬКО event_log_2026_05

`PG-P-003` — PK партиционированной таблицы обязан включать ключ партиционирования

Это ограничение PG (нельзя PRIMARY KEY (id) в partitioned table). Поэтому идиоматично: PRIMARY KEY (id, occurred_at) или PRIMARY KEY (tenant_id, id) для tenant-партиционирования.

3. Три типа партиционирования

RANGE (по диапазону)

`PG-P-010` — Самый частый и самый понятный

Подходит для:

Time-series по occurred_at/created_at.
Архивы по году/месяцу.
Числовые шкалы (например, id в диапазонах).

PARTITION BY RANGE (occurred_at);
PARTITION BY RANGE (created_at);

LIST (по списку значений)

`PG-P-011` — Когда есть фиксированный набор значений-категорий

CREATE TABLE order_doc (...) PARTITION BY LIST (region);

CREATE TABLE order_doc_eu  PARTITION OF order_doc FOR VALUES IN ('EU', 'UK');
CREATE TABLE order_doc_usa PARTITION OF order_doc FOR VALUES IN ('US', 'CA');
CREATE TABLE order_doc_apac PARTITION OF order_doc FOR VALUES IN ('JP', 'CN', 'AU');
CREATE TABLE order_doc_other PARTITION OF order_doc DEFAULT;

Удобно, когда регионы нужно физически разделить (compliance) или когда часть регионов горячая, а часть холодная.

HASH (по хэшу)

`PG-P-012` — Когда нужно равномерно распределить нагрузку без естественного ключа разбиения

CREATE TABLE user_event (...) PARTITION BY HASH (user_id);

CREATE TABLE user_event_p0 PARTITION OF user_event FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_event_p1 PARTITION OF user_event FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_event_p2 PARTITION OF user_event FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_event_p3 PARTITION OF user_event FOR VALUES WITH (MODULUS 4, REMAINDER 3);

HASH не даёт partition pruning по диапазону (WHERE user_id BETWEEN 100 AND 200 — все партиции). Помогает только при WHERE user_id = ? (одно значение → одна партиция).

`PG-P-013` — HASH-партиционирование оправдано редко

Чаще всего «нужно равномерно распределить» решается одной таблицей с правильными индексами. Real use case: очень большая таблица событий, где старые данные не дропаются, а PK-поиск идёт точечно.

4. Выбор ключа партиционирования

Самое важное решение — определяет, будет ли partition pruning работать.

`PG-P-020` — Ключ партиционирования должен быть в `WHERE` почти всех запросов

Правильно:

Time-series: ключ — occurred_at. Запросы почти всегда WHERE occurred_at > ? или WHERE occurred_at BETWEEN ? AND ?.
Multi-tenant: ключ — tenant_id. Каждый запрос работает в контексте одного тенанта.
Архив заказов: ключ — created_at.

Неправильно:

Партиционировать orders по status, если запросы ходят по customer_id. PG будет читать все партиции — overhead больше, чем выигрыш.
Партиционировать customer по country, если 90% работы внутри country = 'RU'. Все партиции, кроме одной — пустые. Это просто отдельная таблица для RU и неудобство.

`PG-P-021` — Если запросы делятся на две группы (по `customer_id` И по `created_at`) — выбирай тот ключ, который чаще в самых тяжёлых запросах

Композитный partition key возможен (PARTITION BY RANGE (tenant_id, created_at)), но обычно overkill.

`PG-P-022` — Распределение нагрузки

Партиции должны быть сравнимого размера. Если 99% данных — в одной партиции, остальные — почти пустые, это не партиционирование. Проверь распределение перед выбором:

SELECT date_trunc('month', occurred_at) AS m, count(*)
FROM event_log GROUP BY m ORDER BY m;

5. Размер партиции

`PG-P-030` — Целевой размер одной партиции — 1–50 GB

Больше — autovacuum снова тормозит. Меньше — overhead планировщика на множество партиций.

Для time-series обычно:

Помесячно — для умеренной нагрузки (миллионы строк в месяц, 1–20 GB).
Понедельно — для средней нагрузки (десятки миллионов в неделю).
Подневно — для высокой (сотни миллионов в день).

`PG-P-031` — Не делай сотни мелких партиций

1000 партиций — планировщик начинает тормозить. > 10000 — кластер еле дышит.

6. Управление партициями

`PG-P-040` — Создание новых партиций — заранее, не по факту

Если первая запись месяца попадёт без партиции — INSERT упадёт. Заводи партиции на месяц-два вперёд.

Вручную:

CREATE TABLE event_log_2026_07 PARTITION OF event_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Автоматически — расширение pg_partman:

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table => 'public.event_log',
    p_control      => 'occurred_at',
    p_type         => 'native',
    p_interval     => '1 month',
    p_premake      => 4    -- сколько партиций вперёд держать
);

-- регулярно (cron):
SELECT partman.run_maintenance('public.event_log');

pg_partman создаёт новые партиции по графику и при желании дропает старые.

`PG-P-041` — Удаление старых данных — `DROP TABLE` партиции, не `DELETE`

DROP TABLE event_log_2025_05;   -- мгновенно, без MVCC-следов

Это главный практический выигрыш партиционирования. DELETE FROM event_log WHERE occurred_at < ... — сначала seq scan миллионов строк, потом MVCC-маркеры, потом vacuum для освобождения места. На time-series это десятки минут с активным WAL и replication lag. DROP TABLE — миллисекунды и одна WAL-запись.

`PG-P-042` — `DETACH PARTITION` отделяет партицию, не удаляя данные

Полезно для переноса данных в холодное хранилище:

ALTER TABLE event_log DETACH PARTITION event_log_2025_05;
-- теперь event_log_2025_05 — обычная таблица, можно дропнуть, экспортировать, перенести

С DETACH ... CONCURRENTLY (PG14+) — без блокировки на родительской таблице.

7. Индексы на партиционированной таблице

`PG-P-050` — `CREATE INDEX` на родительской таблице автоматически создаёт индексы на каждой партиции

CREATE INDEX ON event_log (payload->>'event_type');
-- создаст этот индекс на event_log_2026_05, event_log_2026_06, ...

Удобно, но можно завести partial index per-партиция, если нагрузка отличается.

`PG-P-051` — Уникальные индексы должны включать ключ партиционирования

То же ограничение, что для PK. Уникальность гарантируется только в пределах партиции — если бизнес требует глобальной уникальности по UUID, единственный путь — отдельная таблица справочника или приложение.

8. Foreign Keys и партиции

`PG-P-060` — Partitioned table может ссылаться на обычную таблицу — FK работает

CREATE TABLE event_log (
    customer_id bigint NOT NULL REFERENCES customer(id),
    ...
) PARTITION BY RANGE (occurred_at);

`PG-P-061` — Обратная ссылка (обычная таблица ссылается на partitioned) — была ограничением до PG12. С PG12+ работает

CREATE TABLE order_event (
    order_id bigint NOT NULL REFERENCES order_doc(id, created_at),
    ...
);

Но FK на partitioned требует, чтобы партиция-источник имела PK с ключом партиционирования (см. PG-P-003).

9. Миграция существующей таблицы в партиции

`PG-P-070` — Прямого `ALTER TABLE ... PARTITION BY ...` нет. Миграция — через теневую таблицу

Шаги:

Создаёшь новую partitioned-таблицу с правильным ключом.
Копируешь данные (INSERT INTO new SELECT FROM old).
Меняешь имена через RENAME в одной транзакции.

-- 1. новая партиционированная
CREATE TABLE event_log_new (LIKE event_log INCLUDING ALL)
    PARTITION BY RANGE (occurred_at);

-- партиции на покрытие диапазона
CREATE TABLE event_log_new_2026_05 PARTITION OF event_log_new
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
-- ... остальные

-- 2. копирование (можно батчами для большой таблицы)
INSERT INTO event_log_new SELECT * FROM event_log;

-- 3. swap
BEGIN;
ALTER TABLE event_log RENAME TO event_log_old;
ALTER TABLE event_log_new RENAME TO event_log;
COMMIT;

-- проверить, потом DROP TABLE event_log_old;

Для живых таблиц — добавь логику dual-write (или используй pg_partman partition_data_proc).

`PG-P-071` — Это масштабная миграция

На таблице 500 GB — несколько часов копирования + место под обе версии. Планируй заранее.

10. Антипаттерны

`PG-P-080` — Партиционировать малые таблицы

< 10 GB — overhead больше выигрыша. Сначала выжми из обычных индексов и vacuum.

`PG-P-081` — Партиционировать по полю, которого нет в большинстве `WHERE`

Без partition pruning партиции — это просто куча таблиц с одинаковой схемой.

`PG-P-082` — Multi-tenant per-tenant партиции

Соблазнительно («каждому тенанту своя партиция»), но если тенантов сотни — это сотни партиций, autovacuum и pg_class распухают. Multi-tenant решается partial index'ами или row-level security, не партициями. Партиции по тенанту — только если есть несколько очень крупных тенантов и compliance требует физической изоляции.

`PG-P-083` — Множество мелких партиций (по часам, по минутам)

1000 партиций — планировщик SQL начинает заметно тормозить. Помесячно/понедельно — обычный максимум.

`PG-P-084` — Забыть создать партицию заранее

INSERT без подходящей партиции упадёт с no partition of relation found for row. Должен быть мониторинг или pg_partman.

`PG-P-085` — `UPDATE` поля, по которому идёт партиционирование

Если меняется occurred_at (с одной партиции на другую), PG должен переместить строку — это DELETE + INSERT в WAL. На больших объёмах больно. Проектируй так, чтобы partition key был immutable.

Multi-tenancy паттерны

Multi-tenant — продукт, обслуживающий несколько изолированных клиентов (тенантов) на одной инфраструктуре. Три классических паттерна: row-per-tenant, schema-per-tenant, db-per-tenant. Каждый — компромисс между изоляцией, миграциями, нагрузкой.

Эта статья — как выбрать. Правила пронумерованы кодами PG-MT-NNN.

1. Три паттерна — обзор

	Row-per-tenant	Schema-per-tenant	DB-per-tenant
Где живут данные	в общих таблицах с `tenant_id`	в отдельных схемах PG	в отдельных БД
Изоляция данных	логическая (RLS опционально)	физическая в рамках одного кластера	физическая, отдельные БД
Миграции	один раз для всех	по тенантам (или скриптом для всех)	по тенантам
Backup per-tenant	сложно (нужен фильтр)	средне (`pg_dump --schema=...`)	просто
Производительность	хорошая (один пул, один cache)	средняя (много схем)	низкая (overhead на БД)
Шардинг тенантов	трудно	можно (тенанты по разным кластерам)	легко
Custom-логика на тенанта	нет	через сторонние таблицы в схеме	свобода

`PG-MT-001` — Default выбор — row-per-tenant с `tenant_id` колонкой

Просто, эффективно, масштабируемо до тысяч тенантов.

`PG-MT-002` — Schema-per-tenant — для энтерпрайз-клиентов с регуляторными требованиями к изоляции

(HIPAA, banking) или с custom-логикой на каждого тенанта.

`PG-MT-003` — DB-per-tenant — только когда тенанты — это полноценные клиенты с собственным контрактом (≤ десятки)

, и нужна полная изоляция (отдельные креды, backup-стратегия, иногда отдельный хост).

2. Row-per-tenant — детали

`PG-MT-010` — Каждая бизнес-таблица имеет колонку `tenant_id`. PK — `(tenant_id, id)` или просто `id` с FK на `tenant`

CREATE TABLE tenant (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE order_doc (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    tenant_id   bigint NOT NULL REFERENCES tenant(id),
    customer_id bigint NOT NULL,
    -- ...
    PRIMARY KEY (tenant_id, id)
);

-- индекс на tenant_id первой колонкой везде, где есть запросы
CREATE INDEX ix_order_tenant_status ON order_doc (tenant_id, status);

`PG-MT-011` — Каждый запрос обязан включать `WHERE tenant_id = ?`

Иначе утечка данных между тенантами — критическая security-уязвимость.

`PG-MT-012` — Для гарантии — `Row-Level Security` (RLS, см. §3) или фильтр в коде

В Spring/jOOQ — interceptor или явный helper:

public class TenantAwareDsl {
    private final DSLContext dsl;
    private final TenantContext ctx;

    public Result<Record> selectOrders() {
        return dsl.selectFrom(ORDER_DOC)
            .where(ORDER_DOC.TENANT_ID.eq(ctx.currentTenantId()))
            // ... другие условия
            .fetch();
    }
}

`PG-MT-013` — Композитные PK с `tenant_id` первой колонкой

— все индексы автоматически partition'ятся по тенанту, partition pruning работает на бизнес-уровне.

3. Row-Level Security (RLS)

`PG-MT-020` — RLS — серверная защита: PG сам добавляет фильтр в каждый запрос

ALTER TABLE order_doc ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON order_doc
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

В коде перед каждым запросом:

jdbc.execute("SET LOCAL app.tenant_id = ?", tenantId);
// далее любые SELECT/UPDATE автоматически отфильтрованы PG

`PG-MT-021` — RLS — defense in depth

Даже если разработчик забыл WHERE tenant_id, PG не отдаст чужие данные.

`PG-MT-022` — RLS имеет небольшой overhead

(PG проверяет policy на каждой строке), но обычно незаметный. Включай для multi-tenant с любой security-чувствительностью.

`PG-MT-023` — `SET LOCAL` обязателен

— без LOCAL GUC живёт всю сессию, тенант «протечёт» при возврате connection в pool.

`PG-MT-024` — RLS не работает для superuser

Для backup/admin — отдельный role без RLS bypass.

4. Schema-per-tenant — детали

`PG-MT-030` — Каждый тенант — своя PG schema с одинаковой структурой

CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- одинаковая DDL в каждой схеме
CREATE TABLE tenant_acme.order_doc (...);
CREATE TABLE tenant_globex.order_doc (...);

-- запрос с явной схемой:
SELECT * FROM tenant_acme.order_doc;

-- или через search_path:
SET search_path = tenant_acme, public;
SELECT * FROM order_doc;

`PG-MT-031` — `search_path` обычно ставится из тенант-контекста:

jdbc.execute("SET LOCAL search_path = " + tenantSchema + ", public");

`PG-MT-032` — Миграция — на каждую схему отдельно

Liquibase / Flyway имеют поддержку schema iteration. На 100 схемах миграция будет N×длиннее.

`PG-MT-033` — Connection pool — общий, но каждое соединение SET search_path

перед использованием. Реально работает, но требует дисциплины.

`PG-MT-034` — Не масштабируется до тысяч тенантов

На 1000+ схем pg_class распухает, autovacuum не справляется, cache misses растут.

5. DB-per-tenant — детали

`PG-MT-040` — Каждый тенант — отдельная БД

Можно на одном кластере PG, можно на разных хостах.

// routing DataSource по тенанту
@Component
class TenantRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TenantContext.current();
    }
}

`PG-MT-041` — Подходит для:

≤ десятков энтерпрайз-клиентов, которые платят за отдельные SLA / compliance / backup.

`PG-MT-042` — Не подходит для:

SaaS со свободной регистрацией, тысячи мелких тенантов.

`PG-MT-043` — DB-per-tenant самый дорогой по операциям

— каждый rollout миграции, каждый monitoring setup, каждый backup-job на тенанта.

6. Гибрид — самое частое в реальности

`PG-MT-050` — Большинство SaaS используют гибрид:

Маленькие/free тенанты — row-per-tenant в общей БД.
Платящие enterprise — отдельная БД (или отдельный кластер).

Решение принимается на бизнес-плане, не на технике.

7. Партиционирование по тенанту

`PG-MT-060` — Партиционирование по `tenant_id` (LIST или HASH) — спорный паттерн

Плюсы:

Физическая локальность данных тенанта.
DROP PARTITION мгновенно удаляет всех данных тенанта.

Минусы:

1000 тенантов = 1000 партиций → планировщик тормозит, autovacuum утомлён.
При неравномерной нагрузке (один тенант = 80% данных) — bias.

`PG-MT-061` — Партиционируй по тенанту только если:

≤ десятки тенантов.
У каждого крупного объёма данных (GB+).
GDPR-требование «удалить всё за тенанта одной операцией».

Для тысяч мелких — обычная row-per-tenant без партиций.

См. Партиционирование.

8. Антипаттерны

`PG-MT-080` — Row-per-tenant без `WHERE tenant_id` хотя бы в одном запросе

— security-уязвимость, утечка данных.

`PG-MT-081` — Row-per-tenant без RLS

при том, что защита только в коде — легко забыть в новом методе.

`PG-MT-082` — Schema-per-tenant на тысячах тенантов

— pg_class распухает, кластер тормозит.

`PG-MT-083` — DB-per-tenant для всех тенантов

SaaS — операционный кошмар.

`PG-MT-084` — Партиционирование по tenant_id на сотнях/тысячах тенантов

— больше партиций, чем планировщик любит.

`PG-MT-085` — `SET search_path` без `LOCAL`

в transaction-pool mode — search_path остаётся в сессии, следующий тенант видит чужую схему.

PostGIS для геоданных

PostGIS — расширение PostgreSQL для работы с географическими данными. Стандарт индустрии (используется в OpenStreetMap, Uber, многих gov-системах). Если в продукте есть карты, расстояния, поиск «рядом» — PostGIS закрывает 95% задач без отдельной БД для гео.

Эта статья — практический минимум для разработчика. Правила пронумерованы кодами PG-GIS-NNN.

1. Когда нужен PostGIS

`PG-GIS-001` — PostGIS оправдан, когда:

Поиск в радиусе («магазины в 5 км»).
Расстояние между точками.
Нахождение в полигоне («в каком районе адрес?»).
Маршруты, kNN («10 ближайших ресторанов»).
Геокодирование/обратное (через расширения).

`PG-GIS-002` — Для двух колонок `lat`/`lon` без операций — PostGIS избыточен

Достаточно numeric(9,6) и numeric(10,6).

2. Установка и базовые типы

`PG-GIS-010` — Расширение:

CREATE EXTENSION postgis;

PostGIS добавляет три основных типа:

geometry — плоская геометрия. Координаты «как есть», расчёты в евклидовой плоскости.
geography — сферическая. Координаты в lat/lon, расчёты учитывают кривизну Земли.
raster — растровые данные (карты-картинки). Редко.

`PG-GIS-011` — `geography` vs `geometry`:

	`geography`	`geometry`
Точность	сферическая (точно для Земли)	плоская (точно только для small-area)
Скорость	медленнее (тригонометрия)	быстрее
Операции	ограниченный набор	полный набор
SRID	только 4326 (WGS84)	любой (UTM, локальные системы)

`PG-GIS-012` — Default выбор: `geography`

Точно для глобальных приложений, проще (не надо думать про SRID и проекции). geometry — когда производительность критична и зона работы локальная (один город, регион — можно перевести в UTM).

3. Хранение точек

`PG-GIS-020` — Базовая таблица с точками:

CREATE TABLE shop (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      text NOT NULL,
    location  geography(Point, 4326) NOT NULL
);

-- вставка точки (lon, lat) — порядок важен!
INSERT INTO shop (name, location) VALUES (
    'Магазин на Невском',
    ST_GeogFromText('SRID=4326;POINT(30.3358 59.9343)')
);

-- альтернативный синтаксис:
INSERT INTO shop (name, location) VALUES (
    'Магазин на Невском',
    ST_MakePoint(30.3358, 59.9343)::geography
);

`PG-GIS-021` — Порядок координат: `POINT(lon lat)` — долгота сначала, широта потом

Запутаться легко. Многие туториалы делают наоборот, путаница ведёт к багам.

4. Spatial индекс — обязательно

`PG-GIS-030` — GiST-индекс на geography/geometry-колонке:

CREATE INDEX ix_shop_location_gist ON shop USING gist (location);

Без spatial-индекса любой ST_DWithin/ST_Contains идёт seq-scan, на 1M точках — секунды. С индексом — миллисекунды.

`PG-GIS-031` — `SPGiST` для специфических случаев

(точки в равномерной сетке) — редко.

5. Базовые операции

`PG-GIS-040` — Поиск в радиусе — `ST_DWithin`:

-- магазины в 5 км от точки (Дворцовая площадь)
SELECT id, name,
       ST_Distance(location, ST_GeogFromText('SRID=4326;POINT(30.3158 59.9398)')) AS distance_m
FROM shop
WHERE ST_DWithin(
    location,
    ST_GeogFromText('SRID=4326;POINT(30.3158 59.9398)'),
    5000  -- метры
)
ORDER BY distance_m
LIMIT 50;

`PG-GIS-041` — `ST_DWithin` использует индекс. `ST_Distance` без `WHERE` — нет

Всегда оборачивай в ST_DWithin для фильтрации.

`PG-GIS-042` — kNN-поиск (ближайшие N) — оператор `<->`:

-- 10 ближайших магазинов
SELECT id, name
FROM shop
ORDER BY location <-> ST_GeogFromText('SRID=4326;POINT(30.3158 59.9398)')
LIMIT 10;

<-> использует GiST-индекс — быстро даже на миллионах точек.

`PG-GIS-043` — `ST_Distance(a, b)` для geography возвращает метры

Для geometry — единицы SRID (часто метры в UTM, градусы в WGS84-как-geometry, что почти не используется).

6. Полигоны и попадание в зону

`PG-GIS-050` — Хранение полигонов:

CREATE TABLE district (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    boundary  geography(Polygon, 4326) NOT NULL
);

CREATE INDEX ix_district_boundary_gist ON district USING gist (boundary);

`PG-GIS-051` — Полигон из координат — обычно из GeoJSON:

INSERT INTO district (name, boundary) VALUES (
    'Центральный',
    ST_GeogFromGeoJSON('{"type":"Polygon","coordinates":[[[30.30,59.93],[30.32,59.93],[30.32,59.95],[30.30,59.95],[30.30,59.93]]]}')
);

`PG-GIS-052` — «В каком районе адрес?» — `ST_Contains`/`ST_Intersects`:

-- найти district для точки
SELECT name FROM district
WHERE ST_Contains(boundary::geometry, ST_MakePoint(30.3158, 59.9398));
-- ::geometry потому что ST_Contains для geography ограничено

`PG-GIS-053` — Различие `ST_Contains` / `ST_Within` / `ST_Intersects`:

Contains(A, B) — A полностью содержит B.
Within(A, B) — A находится внутри B (зеркально).
Intersects(A, B) — A и B имеют общие точки (включая касание).

7. Java/Spring + PostGIS

`PG-GIS-060` — jOOQ умеет читать PostGIS-типы через расширение `jooq-postgres-extensions`

(PostgresExtensions.POINT, etc.).

`PG-GIS-061` — Без специальных типов — храни как WKT-строку и парси на стороне Java

(org.locationtech.jts):

public record GeoPoint(double lat, double lon) {
    public String toWkt() { return "SRID=4326;POINT(%f %f)".formatted(lon, lat); }
}

// jOOQ:
ctx.insertInto(SHOP)
   .set(SHOP.NAME, "...")
   .set(SHOP.LOCATION, DSL.field("ST_GeogFromText({0})", String.class, point.toWkt()))
   .execute();

`PG-GIS-062` — JTS (Java Topology Suite)

— стандартная библиотека Java для геометрии. PostGIS совместим, можно гонять Geometry-объекты туда-обратно.

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

`PG-GIS-070` — Spatial-операции тяжелее обычных

Профилируй с EXPLAIN ANALYZE обычным способом.

`PG-GIS-071` — На 100K+ точек кешируй частые запросы или используй materialized views

(см. Materialized views).

`PG-GIS-072` — Для геопоиска с фильтрами по другим полям — composite GiST с `btree_gist` extension:

CREATE EXTENSION btree_gist;
CREATE INDEX ix_shop_active_loc ON shop USING gist (is_active, location);

SELECT * FROM shop
WHERE is_active = true
  AND ST_DWithin(location, ?, 5000);

9. Обратная совместимость с lat/lon

`PG-GIS-080` — Если уже есть `lat numeric, lon numeric` колонки:

Можно жить без PostGIS, считая через формулу haversine, но индекс не поможет — seq-scan.
Лучше — добавить generated column geography:

ALTER TABLE shop ADD COLUMN location geography(Point, 4326)
GENERATED ALWAYS AS (ST_MakePoint(lon, lat)::geography) STORED;

CREATE INDEX ix_shop_location_gist ON shop USING gist (location);

Старый код продолжает читать lat/lon, новый — использовать location для spatial.

10. Антипаттерны

`PG-GIS-090` — `ST_Distance` без `ST_DWithin` в `WHERE`

— seq-scan, медленно.

`PG-GIS-091` — `POINT(lat lon)` (наоборот)

— distance отрицателен или просто врёт. Помни порядок: (lon lat).

`PG-GIS-092` — Хранение lat/lon как `varchar`

— не сравнить, не индексировать. numeric или geography(Point).

`PG-GIS-093` — Расчёт расстояния через haversine в коде Java

— медленно, неиндексируемо. PostGIS делает это нативно.

`PG-GIS-094` — `geometry` для глобальных координат

— ошибка на больших расстояниях (земля не плоская).

`PG-GIS-095` — Spatial-индекс отсутствует

— все запросы seq-scan.

Репликация для разработчика

PostgreSQL поддерживает streaming replication «из коробки»: master пишет WAL → реплика проигрывает → второе соединение готово к чтению. Для разработчика главный вопрос — как и когда читать с реплики, чтобы не получить устаревшие данные и при этом разгрузить мастер.

Эта статья — про code-side. DBA-аспекты (настройка pg_hba.conf, recovery.conf, slot management) — за границами. Правила пронумерованы кодами PG-RP-NNN.

1. Архитектура

`PG-RP-001` — Стандартная схема:

Master (primary) — single source of truth. Принимает все INSERT/UPDATE/DELETE, генерирует WAL.
Replica(s) (standby, hot standby) — проигрывают WAL от мастера. Read-only.

PG поддерживает асинхронную (default) и синхронную replication. Для бэкенда чаще асинхронная — реплика может отстать на миллисекунды-секунды.

`PG-RP-002` — Replication lag — задержка между мастером и репликой

В хорошей сети — 50–500ms. Под нагрузкой / при больших транзакциях — до секунд.

2. Зачем разработчику реплика

`PG-RP-010` — Главные сценарии:

Разгрузка мастера — тяжёлые SELECT (отчёты, аналитика, поиск) не конкурируют с OLTP-нагрузкой.
High availability — при падении мастера можно promote реплику.
Геораспределение — реплика рядом с пользователем, чтение быстрое.

`PG-RP-011` — Что НЕ нужно делать с репликой:

Read-after-write в одном пользовательском действии (создал → сразу читаю).
Любая операция, требующая мгновенной консистентности с мастером.

3. Routing в коде — Spring AbstractRoutingDataSource

`PG-RP-020` — Spring `AbstractRoutingDataSource` выбирает DataSource на основе `@Transactional(readOnly = true)`

public enum DataSourceType { MASTER, REPLICA }

@Component
public class TransactionRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
            ? DataSourceType.REPLICA
            : DataSourceType.MASTER;
    }
}

@Configuration
public class DataSourceConfig {

    @Bean @ConfigurationProperties("spring.datasource.master")
    public HikariConfig masterConfig() { return new HikariConfig(); }

    @Bean @ConfigurationProperties("spring.datasource.replica")
    public HikariConfig replicaConfig() { return new HikariConfig(); }

    @Bean
    public DataSource masterDataSource(HikariConfig masterConfig) { return new HikariDataSource(masterConfig); }

    @Bean
    public DataSource replicaDataSource(HikariConfig replicaConfig) { return new HikariDataSource(replicaConfig); }

    @Bean @Primary
    public DataSource routingDataSource(DataSource masterDataSource, DataSource replicaDataSource) {
        var routing = new TransactionRoutingDataSource();
        routing.setTargetDataSources(Map.of(
            DataSourceType.MASTER,  masterDataSource,
            DataSourceType.REPLICA, replicaDataSource
        ));
        routing.setDefaultTargetDataSource(masterDataSource);
        return new LazyConnectionDataSourceProxy(routing);  // важно
    }
}

`PG-RP-021` — `LazyConnectionDataSourceProxy` обязателен

Без него Spring выбирает соединение в момент открытия транзакции, ДО того как становится известно readOnly. С LazyConnection — выбор откладывается до первого реального запроса.

`PG-RP-022` — Использование:

@Transactional(readOnly = true)
public List<OrderView> findOrders(...) {
    // pojедет на replicaDataSource
}

@Transactional
public OrderId createOrder(...) {
    // поедет на masterDataSource
}

4. Read-after-write — антипаттерн

`PG-RP-030` — «Создал заказ — сразу прочитал список»

на реплике покажет старый список без нового заказа. Replication lag.

// плохо
@Transactional
public OrderId createOrder(...) {
    return orderRepo.save(...).id();
}

@Transactional(readOnly = true)
public List<Order> myOrders(long customerId) {
    return orderRepo.findByCustomer(customerId);  // на реплике, без свежего заказа
}

// в API:
public OrderResponse handle(CreateOrderRequest req) {
    var id = service.createOrder(req);
    var orders = service.myOrders(req.customerId());  // ↑ может не вернуть только что созданный
    return ...;
}

`PG-RP-031` — Решения:

А. Read-from-master для read-after-write:

@Transactional   // без readOnly — пойдёт на мастер
public List<Order> myOrdersFromMaster(long customerId) { ... }

Б. Возвращать данные сразу из write-операции (если возможно):

public OrderResponse createOrder(...) {
    var saved = orderRepo.save(...);
    return OrderResponse.from(saved);  // данные уже в руке, реплика не нужна
}

В. Wait for replica catch-up (специфические кейсы):

// на мастере получили LSN
String lsn = jdbc.queryForObject("SELECT pg_current_wal_lsn()", String.class);

// на реплике ждём, пока проиграет до этого LSN
do {
    String replayLsn = replicaJdbc.queryForObject("SELECT pg_last_wal_replay_lsn()", String.class);
    if (lsnGte(replayLsn, lsn)) break;
    Thread.sleep(50);
} while (true);

// теперь читать с реплики

Сложно, нужен только в специфических случаях. Чаще всего достаточно вариантов А или Б.

5. Synchronous replication

`PG-RP-040` — Синхронная replication: мастер ждёт подтверждения реплики перед возвратом OK на COMMIT

Гарантирует, что после COMMIT реплика тоже видит изменение.

# postgresql.conf на мастере
synchronous_commit = on
synchronous_standby_names = 'replica1, replica2'

`PG-RP-041` — Цена — латентность COMMIT увеличивается на network round-trip + replica fsync

На локальной сети — 1–5 ms, на geo — десятки ms.

`PG-RP-042` — Для разработчика — обычно не нужно

Async replication + правильный routing решает 99% сценариев. Sync — только для критических данных, где «после COMMIT обязательно на двух дисках».

6. Failover — что происходит при падении мастера

`PG-RP-050` — При падении мастера:

Failover-инструмент (Patroni / repmgr) обнаруживает падение.
Promote одной из реплик в master.
DNS / load balancer / VIP переключаются на нового мастера.
Приложение должно переподключиться.

`PG-RP-051` — Spring + HikariCP справится с переподключением,

если правильно настроены validationTimeout и connectionTestQuery. После failover все соединения в пуле инвалидируются, новые открываются к новому мастеру.

`PG-RP-052` — На время failover (10–60 сек) приложение видит ошибки writes

Реализуй retry в критичных операциях:

@Retryable(retryFor = SQLException.class, maxAttempts = 5, backoff = @Backoff(delay = 1000, multiplier = 2))

7. Logical replication

`PG-RP-060` — Logical replication (PG10+) — копирует не WAL, а изменения по таблицам

Можно реплицировать подмножество таблиц, в другую схему, с трансформацией.

`PG-RP-061` — Когда оправдан:

Гибридный stack — реплицировать одну БД в data warehouse / Kafka.
Online migration с одного PG на другой.
Мульти-master scenarios (с conflict resolution).

`PG-RP-062` — Для read-replica с целью разгрузки — обычная streaming replication. Logical имеет больший overhead

8. Мониторинг replication lag

`PG-RP-070` — На мастере:

SELECT
    application_name,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

replay_lag — основная метрика. > 1 сек на постоянке — реплика отстаёт.

`PG-RP-071` — На реплике:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

`PG-RP-072` — Алёрт на `replay_lag > 30 сек` или > 1 GB WAL

9. Антипаттерны

PG-RP-080 Read-after-write через реплику — пропускает только что созданные записи.

PG-RP-081 Все SELECT'ы автоматом на реплику без @Transactional(readOnly = true) — write-after-read становится write-after-stale-read.

PG-RP-082 AbstractRoutingDataSource без LazyConnectionDataSourceProxy — выбор DataSource до знания readOnly.

PG-RP-083 Synchronous replication «на всякий случай» — двукратный латенси commit без необходимости.

PG-RP-084 Игнорирование replication lag в мониторинге — не узнаешь, что реплика отстала, пока не пожалуются пользователи.

PG-RP-085 Использование sticky-session по userId для read-after-write — частично работает, но не покрывает cross-user сценарии (admin создал, user читает).

Расширения, которые часто нужны

PostgreSQL имеет богатую экосистему расширений (CREATE EXTENSION). Эта статья — про те, которые чаще всего реально нужны разработчику. Все они либо встроены (contrib), либо доступны в большинстве дистрибутивов.

Правила пронумерованы кодами PG-EXT-NNN.

1. Что включить почти всегда

`PG-EXT-001` — На любой проект:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- мониторинг запросов
CREATE EXTENSION IF NOT EXISTS pgcrypto;             -- gen_random_uuid(), crypt(), digest()
CREATE EXTENSION IF NOT EXISTS pg_trgm;              -- триграммы для LIKE и опечаток

Они почти ничего не стоят, и почти всегда пригодятся.

2. `pg_stat_statements` — мониторинг

`PG-EXT-010` — Топ запросов по времени, числу вызовов, IO

Базовый инструмент диагностики «у нас всё медленно». Подробно — в Мониторинг.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Требует перезагрузки кластера один раз. Дальше:

CREATE EXTENSION pg_stat_statements;
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

3. `pgcrypto` — хэши, шифрование, UUID

`PG-EXT-020` — Включает функции:

gen_random_uuid() — встроенный с PG13+, но для совместимости проще через pgcrypto.
crypt(password, gen_salt('bf')) — bcrypt-хэширование.
digest('text', 'sha256') — хэши.
encrypt/decrypt — симметричное шифрование (используй сервер-side очень редко).

-- bcrypt-хэш пароля
INSERT INTO account (email, password_hash)
VALUES ('user@example.com', crypt('plaintext-password', gen_salt('bf', 10)));

-- проверка
SELECT id FROM account
WHERE email = 'user@example.com'
  AND password_hash = crypt('plaintext-password', password_hash);

`PG-EXT-021` — Хэширование паролей в БД через pgcrypto

— рабочий вариант, но обычно хэши делаются в коде (Spring Security BCryptPasswordEncoder). Так пароль не уходит на сервер БД в plaintext (только хэш).

`PG-EXT-022` — Шифрование колонок (`pgp_sym_encrypt`) — анти-паттерн

Лучше — шифровать на стороне приложения или использовать TDE на уровне диска. БД не должна знать ключи.

4. `pg_trgm` — триграммы

`PG-EXT-030` — Для `LIKE '%substring%'` с индексом и для поиска с опечатками

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_customer_name_trgm
    ON customer USING gin (full_name gin_trgm_ops);

-- поиск по подстроке (ILIKE):
SELECT * FROM customer WHERE full_name ILIKE '%иван%';

-- поиск с опечатками (similarity):
SELECT * FROM customer
WHERE similarity(full_name, 'иванв') > 0.4
ORDER BY similarity(full_name, 'иванв') DESC LIMIT 10;

Подробно — в Типы индексов §9 и FTS §8.

5. `btree_gist` — composite GIST

`PG-EXT-040` — Расширяет GiST для скалярных типов (int, text, etc.) — позволяет composite индексы по `(scalar, range)` или `(scalar, geo)`

Главные применения:

EXCLUDE constraint для непересечения интервалов с группировкой по ключу:

CREATE EXTENSION btree_gist;

CREATE TABLE booking (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    period  tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

Composite GiST для PostGIS:

CREATE INDEX ix_shop_active_loc ON shop USING gist (is_active, location);

Подробно — в Массивы и range-типы §3 и PostGIS §8.

6. `citext` — case-insensitive text

`PG-EXT-050` — Тип `citext` ведёт себя как `text`, но сравнения нечувствительны к регистру

Без него — LOWER() в каждом запросе.

CREATE EXTENSION citext;

CREATE TABLE account (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email  citext NOT NULL UNIQUE
);

-- эти запросы возвращают одну и ту же запись:
SELECT * FROM account WHERE email = 'user@example.com';
SELECT * FROM account WHERE email = 'USER@EXAMPLE.COM';

Подробно — в Строки §4.

7. `pgstattuple` — точная статистика bloat

`PG-EXT-060` — Для оценки bloat таблицы и индекса. Точно, но медленно (читает всю таблицу)

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('orders');
-- table_len, tuple_count, dead_tuple_count, dead_tuple_percent, free_space

SELECT * FROM pgstatindex('ix_orders_customer');
-- avg_leaf_density (низкая = bloated)

SELECT * FROM pgstattuple_approx('orders');  -- быстрее, но приближённо

Подробно — в VACUUM §3.

8. `pg_partman` — автоматизация партиций

`PG-EXT-070` — Создание новых партиций по расписанию + дроп старых. Работает с native PG12+ partitioning

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table => 'public.event_log',
    p_control      => 'occurred_at',
    p_type         => 'native',
    p_interval     => '1 month',
    p_premake      => 4   -- держать 4 партиции вперёд
);

-- регулярно (cron):
SELECT partman.run_maintenance('public.event_log');

Без pg_partman — cron + ручной DDL. С ним — одна команда.

Подробно — в Партиционирование §6.

9. `unaccent` — снятие диакритики

`PG-EXT-080` — Убирает диакритические знаки

(для поиска по «ёлка» когда введено «елка»):

CREATE EXTENSION unaccent;

SELECT unaccent('Naïve café résumé');
-- Naive cafe resume

-- combo с FTS:
SELECT * FROM article
WHERE search_doc @@ plainto_tsquery('russian', unaccent($1));

Применяется при индексации и в запросе.

10. `hstore` — legacy key-value

`PG-EXT-090` — `hstore` — старый key-value тип PG

До появления JSONB был основным «гибким полем». Сейчас — legacy.

CREATE EXTENSION hstore;
CREATE TABLE product (id bigint, attrs hstore);
INSERT INTO product VALUES (1, 'color => red, size => XL'::hstore);
SELECT * FROM product WHERE attrs->'color' = 'red';

`PG-EXT-091` — Для нового кода — `jsonb`, не `hstore`

Hstore только если поддерживаешь legacy схему.

11. `uuid-ossp` — генераторы UUID

`PG-EXT-100` — `uuid-ossp` — генерация UUID v1, v3, v4, v5 на стороне PG

Был стандартом до PG13. С PG13+ для UUID v4 встроенная gen_random_uuid() (через pgcrypto), для v7 нужны клиентские генераторы (см. UUID §3).

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();

`PG-EXT-101` — Для нового кода — `gen_random_uuid()` из pgcrypto или клиентская генерация UUID v7

12. `tablefunc` — pivot и crosstab

`PG-EXT-110` — `crosstab` для pivot-таблиц

(превращение строк в колонки):

CREATE EXTENSION tablefunc;

SELECT * FROM crosstab(
    'SELECT region, year, sum(amount) FROM sales GROUP BY 1, 2 ORDER BY 1, 2',
    'SELECT DISTINCT year FROM sales ORDER BY 1'
) AS ct (region text, y2024 numeric, y2025 numeric, y2026 numeric);

Полезно для отчётов, чтобы не делать pivot в коде Java.

13. `pg_repack` / `pg_squeeze` — реорганизация без блокировки

`PG-EXT-120` — `pg_repack` — переписывает таблицу/индекс без `ACCESS EXCLUSIVE`

Альтернатива VACUUM FULL без даунтайма.

Запускается как отдельный процесс (не extension в обычном смысле), но требует одноимённой extension в БД.

pg_repack -d mydb -t order_doc       # таблица
pg_repack -d mydb -i ix_order_status  # индекс

Подробно — в VACUUM §8.

14. Антипаттерны

`PG-EXT-150` — Включать все extensions «на всякий случай»

— некоторые требуют preload в postgresql.conf, бесцельно увеличивают cluster overhead.

`PG-EXT-151` — Расширения с зависимостью от системных пакетов

(PostGIS, plpython3u) — проверь, что доступны в твоём dev/prod образе PG, до миграции.

`PG-EXT-152` — `hstore` в новом коде

— устаревшая альтернатива JSONB.

`PG-EXT-153` — `pgp_sym_encrypt` / шифрование колонок на стороне БД

— БД знает ключи, что часто противоречит модели угроз.

Триггеры и хранимые процедуры — когда НЕ надо

Бизнес-логика в БД — антипаттерн в современной разработке. Эта статья объясняет почему, и где триггеры ещё имеют место.

Правила пронумерованы кодами PG-TR-NNN.

1. Базовая позиция

`PG-TR-001` — Бизнес-логика живёт в коде приложения, не в БД

В коде она:

Тестируется обычными unit-тестами.
Видна в IDE с навигацией.
Версионируется в git вместе с фичей.
Откатывается релизом приложения, не миграцией.
Не зависит от движка БД (можно поменять PG на что-то).

`PG-TR-002` — Логика в триггерах:

Тестируется через интеграционные тесты с БД.
Не видна в IDE Java/Spring (отдельный SQL).
Версионируется через миграции (накат — отдельный релиз).
Откатывается миграцией (часто болезненно).
Привязана к PG (триггеры plpgsql переписать на другой движок — большая работа).

`PG-TR-003` — Это не значит «триггеры зло»

— есть редкие сценарии, где они оправданы (см. §3). Но default — без них.

2. Что обычно делают триггерами и почему НЕ надо

`created_at` / `updated_at`

`PG-TR-010` — Триггер для `updated_at = now()` — антипаттерн

Используй DEFAULT now() в DDL и обновляй из приложения.

-- плохо: триггер
CREATE TRIGGER tr_set_updated_at
BEFORE UPDATE ON order_doc FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- хорошо: DDL + явный UPDATE
CREATE TABLE order_doc (
    ...
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

-- в коде:
UPDATE order_doc SET status = ?, updated_at = now() WHERE id = ?;

`PG-TR-011` — Триггер скрывает поведение

Разработчик пишет UPDATE без updated_at, ожидает что он останется старым — а триггер тихо обновил. Дебажить приходится.

Денормализация (агрегаты, счётчики)

`PG-TR-020` — Триггер `AFTER INSERT/DELETE` для обновления `count`/`sum` — терпимо, но не идеально

-- терпимо: триггер обновляет post_count в forum
CREATE TRIGGER tr_update_post_count
AFTER INSERT OR DELETE ON post
FOR EACH ROW EXECUTE FUNCTION update_forum_post_count();

Альтернативы:

Materialized view с refresh — проще, видно явно.
Read Model в коде через event-handler — гибкость, тестируемость.
Расчёт on-the-fly при чтении (SELECT count(*) FROM post WHERE ...) — если данных мало.

Триггер удобен для мгновенной консистентности (счётчик всегда точный после COMMIT). Но цена — невидимая логика.

Audit-trail

`PG-TR-030` — Триггер для audit-таблицы — оправдан, если нужна гарантия «никто не пропустит»

CREATE TRIGGER tr_order_doc_audit
AFTER INSERT OR UPDATE OR DELETE ON order_doc
FOR EACH ROW EXECUTE FUNCTION log_to_audit();

Альтернативы:

Outbox + код — каждое изменение в коде явно создаёт audit-event. Гибче, но можно забыть.
Logical replication / CDC — внешний сервис ловит WAL, пишет в audit-store. Подходит для compliance.
Триггер — гарантия покрытия, не зависит от того, что разработчик не забыл.

Для compliance-чувствительных систем (банки, медицина) триггер — разумный выбор.

Валидация инвариантов

`PG-TR-040` — CHECK constraint > триггер

Простые инварианты — через CHECK. Сложные (зависящие от других строк) — в коде с SELECT FOR UPDATE.

-- хорошо: CHECK
ALTER TABLE order_doc ADD CONSTRAINT ck_order_total_positive CHECK (total_amount >= 0);

-- хорошо: EXCLUDE для непересечения интервалов
EXCLUDE USING gist (room_id WITH =, period WITH &&)

-- терпимо: триггер для сложного инварианта
CREATE TRIGGER tr_doctor_on_call_check
BEFORE UPDATE ON doctor FOR EACH ROW EXECUTE FUNCTION check_at_least_one_oncall();

`PG-TR-041` — Если инвариант между несколькими строками или таблицами — лучше код с FOR UPDATE и SELECT count

Триггер FOR EACH ROW плохо видит общую картину.

3. Когда триггер действительно оправдан

`PG-TR-050` — Триггер оправдан, когда:

Compliance-требование «не пропустить ни одно изменение» (медицина, банк, регулируемая отрасль). Audit-триггер гарантирует.
Существующая система с PG как «источником истины», через который ходят несколько приложений. Триггер — единственный способ обеспечить инвариант для всех.
Денормализация, где гарантия мгновенной консистентности важнее простоты. Точный count после COMMIT, а не «когда-то догонит».
Защита от ручного UPDATE через psql — devops иногда лезут руками, триггер защитит схему.

4. Хранимые процедуры (FUNCTION / PROCEDURE)

`PG-TR-060` — Хранимые процедуры в современном Java/Spring — почти никогда не нужны

Что обычно делают процедурами:

Сложная транзакция → есть @Transactional в Spring.
Bulk-логика → есть INSERT INTO ... SELECT ... без процедуры.
Производительность → редко выигрывает у хорошо написанного app-кода с jOOQ batch.

`PG-TR-061` — Где процедура оправдана:

Очень тяжёлые SQL-операции, где cost network round-trip между PG и app существенен (миллионы строк, сложные join'ы).
ETL, который читает гигабайты, агрегирует, пишет — а app-сервер это не его масштаб.

`PG-TR-062` — PG procedures (PG11+) умеют commit/rollback внутри

в отличие от functions. Полезно для batch-job'ов.

CREATE PROCEDURE archive_old_orders() LANGUAGE plpgsql AS $$
DECLARE rows_affected integer := 1;
BEGIN
    WHILE rows_affected > 0 LOOP
        WITH deleted AS (
            DELETE FROM order_doc
            WHERE created_at < now() - interval '1 year'
            RETURNING *
        )
        INSERT INTO order_archive SELECT * FROM deleted;
        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        COMMIT;
    END LOOP;
END;
$$;

`PG-TR-063` — Альтернатива — `@Scheduled` + батчированный код в Java

Та же логика, тестируется обычно, видно в репо.

5. Стоимость триггеров

`PG-TR-070` — Триггер увеличивает время записи

BEFORE — выполняется до записи, может изменить значения.
AFTER — выполняется после, может только читать.
FOR EACH ROW — на каждую строку (медленно для bulk).
FOR EACH STATEMENT — один раз на statement (быстрее).

`PG-TR-071` — На bulk операциях `FOR EACH ROW` особенно дорог

INSERT INTO target SELECT FROM source 1M строк → триггер выполнится 1M раз.

`PG-TR-072` — Триггеры могут стать причиной deadlock

Если триггер обновляет другую таблицу, порядок локов меняется недетерминированно.

6. Дебаг триггеров

`PG-TR-080` — Триггер — невидимая логика. Чтобы найти, кто что меняет:

-- найди все триггеры на таблице
SELECT trigger_name, event_manipulation, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'order_doc';

-- посмотри тело функции
SELECT prosrc FROM pg_proc WHERE proname = 'set_updated_at';

`PG-TR-081` — Лог в триггере для дебага:

CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'TRIGGER fired: % on table %', TG_OP, TG_TABLE_NAME;
    -- ...
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

`PG-TR-082` — Тесты триггеров — через интеграционные тесты в Spring

(Testcontainers PostgreSQL). Логика проверяется через INSERT/UPDATE и assertion на итоговое состояние.

7. Антипаттерны

PG-TR-090 Триггер для updated_at — лучше явно в коде.

PG-TR-091 Триггер для бизнес-валидации (например, if order.total < 1000 then disallow) — должно быть в Java-сервисе.

PG-TR-092 Триггер, отправляющий NOTIFY или вызывающий внешний HTTP — связь приложения с триггером скрыта.

PG-TR-093 Сложная цепочка триггеров (A -> trigger -> B -> trigger -> C) — отлаживать ад.

PG-TR-094 Stored procedure для логики, которую можно сделать в коде — лишние миграции, нет тестов.

PG-TR-095 Триггер BEFORE INSERT модифицирующий значение в NEW.* без явного документирования — разработчик удивляется, что INSERT записал не то, что отправил.

VACUUM, autovacuum и bloat

PostgreSQL использует MVCC: каждый UPDATE и DELETE не меняет строку на месте, а помечает старую версию как мёртвую и пишет новую. Мёртвые версии (dead tuples) копятся на страницах, занимают место, замедляют чтение. Их убирает VACUUM.

В типичном проекте VACUUM работает сам через autovacuum. Разработчику почти ничего настраивать не нужно. Но иногда autovacuum не справляется, и важно понимать почему — bloat, медленные запросы, долгий restart, всё это последствия одного и того же.

1. Зачем нужен VACUUM

`PG-V-001` — VACUUM делает три вещи:

Освобождает место от мёртвых tuple'ов — заносит свободные слоты в FSM (Free Space Map), чтобы новые INSERT могли их занять. Не возвращает место ОС — таблица не уменьшается на диске.
Обновляет visibility map — карту страниц, где все tuple'ы видны всем транзакциям. Это позволяет Index Only Scan работать без Heap Fetches.
Предотвращает transaction ID wraparound — счётчик XID 32-битный, через ~2 млрд транзакций «проходит круг»; VACUUM «замораживает» старые tuple'ы, чтобы не путались.

`PG-V-002` — VACUUM не блокирует чтение и запись

Берёт SHARE UPDATE EXCLUSIVE lock — параллельные SELECT/INSERT/UPDATE/DELETE работают. Только DDL (ALTER TABLE) ждёт.

2. Три варианта VACUUM

Команда	Что делает	Lock	Возвращает место ОС
`VACUUM`	Помечает dead tuples свободными в FSM	SHARE UPDATE EXCLUSIVE (нет блокировки чтения/записи)	Нет
`VACUUM FULL`	Перезаписывает таблицу с нуля без dead tuples	ACCESS EXCLUSIVE (блокирует ВСЁ)	Да
`VACUUM ANALYZE`	Обычный VACUUM + сбор статистики для планировщика	SHARE UPDATE EXCLUSIVE	Нет

`PG-V-010` — `VACUUM FULL` — почти никогда в проде

ACCESS EXCLUSIVE блокирует всех читателей и писателей на время перезаписи (минуты-часы на больших таблицах). Используй только в окне обслуживания. Альтернатива — pg_repack / pg_squeeze (re-write без блокировки).

`PG-V-011` — `VACUUM ANALYZE` — после массовой загрузки/обновления

autovacuum запустит ANALYZE сам, но с задержкой; если планировщик нужен правильный сразу — руками.

3. autovacuum — что включено по умолчанию

`PG-V-020` — autovacuum работает автоматически и тюнен на дефолты

В большинстве случаев его трогать не надо.

Дефолтные пороги (PG14+):

autovacuum_vacuum_threshold = 50 — минимум dead tuples для запуска.
autovacuum_vacuum_scale_factor = 0.2 — плюс 20% от размера таблицы.
Триггер: dead_tuples > threshold + scale_factor × n_live_tup.

То есть на таблице 1M строк autovacuum запустится, когда мёртвых будет ~200K. Для большинства бизнес-нагрузок норм.

`PG-V-021` — На больших горячих таблицах дефолт мал

200K dead tuples при UPDATE-нагрузке набегают за минуты, autovacuum гоняется постоянно и не успевает. Решение — снизить scale_factor до 0.05 (5%) для конкретной таблицы:

ALTER TABLE order_doc SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.05
);

Теперь VACUUM запустится при ~50K dead tuples — реже простаивает, реже копится bloat.

`PG-V-022` — Параметр `autovacuum_naptime = 1min`

— autovacuum-launcher проверяет таблицы раз в минуту. Это нижняя граница «как быстро сработает».

4. Bloat — как обнаружить

Bloat = разница между «полезным размером данных» и «реальным размером таблицы/индекса». Если 50% таблицы — пустые места от dead tuples, у тебя 2x bloat.

`PG-V-030` — Базовый мониторинг — `pg_stat_user_tables`:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC LIMIT 10;

dead_pct > 20% — кандидат на ручной VACUUM или подкрутку scale_factor. last_autovacuum старше суток на горячей таблице — autovacuum не успевает (см. §6).

`PG-V-031` — Фактический bloat — через расширения `pgstattuple` или `pg_visibility`:

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('order_doc');
-- table_len, tuple_count, tuple_percent, dead_tuple_count, dead_tuple_percent, free_space

dead_tuple_percent > 30% — таблица распухла, нужен VACUUM (или VACUUM FULL в окно обслуживания, или pg_repack).

`PG-V-032` — Bloat индексов

— отдельная история. Индексы тоже распухают при UPDATE на индексируемой колонке. Показывает pgstattuple. Лечится REINDEX CONCURRENTLY (PG12+) — не блокирует таблицу.

5. fillfactor — снизить bloat на write-heavy таблицах

`PG-V-040` — На таблицах с частыми `UPDATE` — `fillfactor = 80–90`

Подробно — в статье про WAL, правила PG-W-020/PG-W-021.

Кратко: PG умеет HOT (Heap-Only Tuple) — UPDATE без перемещения строки, если на странице есть свободное место. С fillfactor < 100 страницы заполняются не до конца, остаётся место под HOT, dead tuples реже плодятся.

ALTER TABLE order_doc SET (fillfactor = 85);
VACUUM FULL order_doc;   -- переразложит таблицу под новый fillfactor (один раз, в окно)

6. Когда autovacuum не справляется

`PG-V-050` — Открытая долгая транзакция блокирует освобождение dead tuples

PG не может пометить tuple свободным, пока его видит хоть какая-то открытая транзакция (для MVCC). Долгая транзакция = bloat растёт.

-- найди долгие транзакции:
SELECT pid, age(now(), xact_start) AS xact_age, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC LIMIT 10;

Алёрт: xact_age > 5 минут — обычно сломанная сессия в IDE, висящий тест, забытый BEGIN без COMMIT. Подробно — WAL §8.

`PG-V-051` — Висящий replication slot

Если читатель WAL отстал, PG не освобождает WAL → autovacuum не освобождает dead tuples. Тот же WAL §9.

`PG-V-052` — Long-running query / prepared transaction

— те же эффекты. Prepared transactions (PREPARE TRANSACTION) живут в pg_prepared_xacts до явного COMMIT PREPARED / ROLLBACK PREPARED. Проверка:

SELECT * FROM pg_prepared_xacts;

`PG-V-053` — Бан-лист `autovacuum = off`

Кто-то выключил autovacuum глобально (autovacuum = off в postgresql.conf) или для конкретной таблицы (ALTER TABLE ... SET (autovacuum_enabled = false)). Иногда оправдано (массовая загрузка staging), но забывают вернуть.

-- проверь, что autovacuum ВКЛЮЧЕН
SELECT name, setting FROM pg_settings WHERE name = 'autovacuum';

-- per-table:
SELECT relname, reloptions FROM pg_class
WHERE relkind = 'r' AND reloptions::text LIKE '%autovacuum%';

7. VACUUM как часть миграции

`PG-V-060` — После big `UPDATE` или `DELETE` миграции — `VACUUM ANALYZE` руками

Не жди autovacuum.

-- миграция: пометить старые заказы архивными
UPDATE order_doc SET status = 'ARCHIVED' WHERE created_at < now() - interval '1 year';

-- сразу после:
VACUUM ANALYZE order_doc;

Иначе планировщик ещё долго будет считать, что 99% — NEW/PAID, и выбирать неоптимальные планы для запросов по status = 'ARCHIVED'.

`PG-V-061` — `CREATE INDEX CONCURRENTLY` обновляет visibility map

— после построения индекса для Index Only Scan нужен VACUUM, чтобы Heap Fetches упали до нуля.

8. Когда VACUUM FULL оправдан

`PG-V-070` — `VACUUM FULL` — только в окно обслуживания и при сильном bloat (>50%)

Сценарии:

После массового DELETE старых данных, которые занимали >30% таблицы.
После изменения fillfactor — переразложить под новый параметр.
Bloat обнаружен через pgstattuple, обычный VACUUM не помогает (потому что UPDATE нагрузка постоянно создаёт новые dead tuples).

Альтернатива без блокировки — pg_repack:

pg_repack -d mydb -t order_doc

Запускается как обычный процесс, не блокирует, переписывает таблицу в фон. На больших объёмах безопаснее.

9. Транзакционный wraparound

`PG-V-080` — PG XID-счётчик 32-битный — 2.1 млрд значений. Через wraparound кластер встаёт

VACUUM «замораживает» старые tuple'ы (vacuum_freeze_min_age, vacuum_freeze_table_age) — снимает с них видимость по XID.

В нормальной работе этим занимается autovacuum, разработчик не видит. Но на очень нагруженных кластерах (миллионы транзакций в час) wraparound подкрадывается за месяцы — мониторь:

SELECT datname,
       age(datfrozenxid) AS xid_age,
       2147483647 - age(datfrozenxid) AS xids_left
FROM pg_database;

Если xid_age > 1.5 млрд — алёрт, autovacuum не справляется (см. §6).

10. Чек-лист «таблица распухла»

n_dead_tup в pg_stat_user_tables — большой (>20% от n_live_tup)? Да → autovacuum не отрабатывает.
last_autovacuum — давно (часы/дни)? Проверь долгие транзакции, replication slots, autovacuum_enabled.
pgstattuple показывает dead_tuple_percent > 30%? Запусти VACUUM <table>; руками. Если не помогает — копит быстрее, чем чистит → подкрути autovacuum_vacuum_scale_factor.
fillfactor оставлен дефолтным (100) на write-heavy таблице? Снизь до 80–90.
Если ничего не помогает и нужно вернуть место ОС — окно обслуживания + pg_repack или VACUUM FULL.
Bloat индексов — REINDEX CONCURRENTLY.

Мониторинг и slow queries

«У нас всё медленно» — самый частый репорт. Без мониторинга это разговор «по ощущениям». PostgreSQL даёт три ключевых инструмента: pg_stat_statements (топ запросов), auto_explain (автоматический EXPLAIN при медленных), log_min_duration_statement (лог медленных). Плюс набор pg_stat_*-views для оперативной диагностики.

Эта статья — что включить в проде, что мониторить дашбордом, на что ставить алёрты. Правила пронумерованы кодами PG-MON-NNN.

1. `pg_stat_statements` — топ запросов

`PG-MON-001` — Включи `pg_stat_statements` в проде

Это extension, требует перезагрузки кластера один раз:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

CREATE EXTENSION pg_stat_statements;

`PG-MON-002` — Топ-N запросов по разным метрикам:

-- по общему времени (cumulative)
SELECT
    substring(query, 1, 80) AS query,
    calls,
    round(total_exec_time::numeric, 0) AS total_ms,
    round(mean_exec_time::numeric, 1) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct
FROM pg_stat_statements
WHERE userid = (SELECT oid FROM pg_roles WHERE rolname = 'app')
ORDER BY total_exec_time DESC
LIMIT 20;

-- по средней латентности
ORDER BY mean_exec_time DESC

-- по числу вызовов
ORDER BY calls DESC

-- по IO
ORDER BY (shared_blks_read + shared_blks_dirtied) DESC

`PG-MON-003` — Запросы нормализованы:

SELECT * FROM o WHERE id = $1 — параметры заменены на placeholder, одинаковые запросы агрегируются.

`PG-MON-004` — Reset перед измерением:

SELECT pg_stat_statements_reset(); — чтобы видеть данные за конкретный период.

2. `auto_explain` — автоматический EXPLAIN на медленных

`PG-MON-010` — `auto_explain` логирует план запроса автоматически, если он выполнялся дольше N ms

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'    # log everything > 500ms
auto_explain.log_analyze = on               # с реальными rows и time
auto_explain.log_buffers = on
auto_explain.log_format = json              # для парсинга

`PG-MON-011` — `log_analyze = on` имеет overhead

(5–20%) на запросы. Включай в проде осторожно — на «debug» окнах или фиксированный период.

`PG-MON-012` — `auto_explain` логирует в server log

Дальше парсить через ELK/Loki/Splunk и отдельно через explain.depesz.com/explain.dalibo.com.

3. `log_min_duration_statement` — лог медленных

`PG-MON-020` — Логировать каждый запрос медленнее N ms:

# postgresql.conf
log_min_duration_statement = '500ms'
log_line_prefix = '%m [%p] %q%u@%d '

Прод-уровень: 500ms или 1s. Снижение до 100ms даст море логов на нагруженном кластере.

`PG-MON-021` — Не путай с `auto_explain`:

log_min_duration_statement логирует только текст запроса и время. auto_explain — план. Часто включают оба.

4. Activity и блокировки

`PG-MON-030` — `pg_stat_activity` — что выполняется прямо сейчас:

SELECT
    pid,
    now() - xact_start AS xact_age,
    now() - query_start AS query_age,
    state,
    wait_event_type, wait_event,
    substring(query, 1, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_age DESC
LIMIT 20;

`PG-MON-031` — Алёрты:

xact_age > 5 минут — долгая транзакция (см. WAL §8, VACUUM §6).
wait_event_type = 'Lock' стабильно — лок-очередь.
state = 'idle in transaction' AND xact_age > 1 минуту — забытая транзакция.

`PG-MON-032` — `pg_locks` — кто кого ждёт:

SELECT
    blocked.pid AS blocked_pid,
    blocking.pid AS blocking_pid,
    blocked.query AS blocked_query,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl_lock ON bl_lock.pid = blocked.pid AND NOT bl_lock.granted
JOIN pg_locks gr_lock ON gr_lock.locktype = bl_lock.locktype
    AND gr_lock.database IS NOT DISTINCT FROM bl_lock.database
    AND gr_lock.relation IS NOT DISTINCT FROM bl_lock.relation
    AND gr_lock.granted
JOIN pg_stat_activity blocking ON blocking.pid = gr_lock.pid;

5. Таблицы, индексы, WAL

`PG-MON-040` — `pg_stat_user_tables` — состояние таблиц:

n_live_tup/n_dead_tup — bloat.
last_autovacuum/last_autoanalyze — autovacuum работает?
n_tup_upd/n_tup_hot_upd — HOT-ratio.
seq_scan/idx_scan — seq-scan vs index-scan ratio.

`PG-MON-041` — `pg_stat_user_indexes` — какие индексы используются:

idx_scan = 0 после месяца на проде — индекс не используется, кандидат на дроп.

`PG-MON-042` — `pg_stat_wal` (PG14+) — генерация WAL:

wal_records/wal_bytes — объём генерируемого WAL.
wal_buffers_full — растёт → увеличить wal_buffers.

`PG-MON-043` — `pg_replication_slots` — replication slots (см. WAL §9):

restart_lsn отстаёт от pg_current_wal_lsn() → лаг.
active = false дольше часа — алёрт.

6. Connection pool метрики

`PG-MON-050` — `pg_stat_activity.state` — соединения:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- active / idle / idle in transaction / idle in transaction (aborted)

Алёрты:

state = 'active' близко к max_connections — пул переполнен.
state = 'idle in transaction' стабильно >5 — забытые транзакции.

7. Что мониторить дашбордом

`PG-MON-060` — Минимальный набор метрик в Grafana / своём дашборде:

Метрика	Источник	Алёрт
TPS (transactions/sec)	`pg_stat_database.xact_commit + xact_rollback`	падение от baseline
Connections used	`pg_stat_activity.state`	> 80% от `max_connections`
Long transactions	`pg_stat_activity.xact_age` max	> 5 минут
Replication lag	`pg_stat_replication.replay_lag`	> 30 сек
WAL throughput	`pg_stat_wal.wal_bytes` rate	резкий рост
Cache hit ratio	`pg_stat_database.blks_hit / (blks_hit + blks_read)`	< 95%
Buffer cache write	`pg_stat_bgwriter.checkpoints_req`	растёт = checkpoints под нагрузкой
Disk size	`pg_database_size()`, `pg_total_relation_size()`	растёт нестандартно
autovacuum lag	`pg_stat_user_tables.last_autovacuum`	> 1 дня на горячих
Bloat	`pgstattuple` или approximate	> 30% на горячих

`PG-MON-061` — Для Spring Boot — `micrometer-registry-prometheus` + `pg-stat-statements-exporter`

дают всё это автоматически.

8. Цикл диагностики «медленно»

`PG-MON-070` — Алгоритм при репорте «всё медленно»:

pg_stat_activity — сейчас идут долгие запросы? Если да — какие, где блокировки?
pg_stat_statements — топ-10 по total_exec_time за последний час. Что новое появилось?
pg_stat_database.blks_read rate — disk IO растёт? Cache miss?
pg_stat_user_tables.n_dead_tup — bloat растёт? autovacuum отстаёт?
pg_stat_replication — replication lag? Если на реплике медленно — мастер забивает WAL быстрее, чем реплика проигрывает.
OS-level — iostat/top/vmstat — диск, CPU, RAM на пределе?
Сетевая часть — ss -t state established | wc -l — connections от приложения растут?

9. Что в проде должно быть включено

`PG-MON-080` — Всегда:

pg_stat_statements.
log_min_duration_statement = '1s' или ниже.
track_activity_query_size = 4096 (default 1024 — режет длинные запросы).
track_io_timing = on (немного overhead, но позволяет видеть IO в pg_stat_statements).

`PG-MON-081` — Опционально:

auto_explain с log_min_duration = '500ms' (overhead на slow запросы — но это уже плохо).
log_lock_waits = on — лог запросов, ждущих лок дольше deadlock_timeout (default 1s).
log_temp_files = 0 — лог запросов, использующих temp-files (часто sort на диск).

10. Антипаттерны

`PG-MON-090` — Не включать `pg_stat_statements` в проде

— летаешь вслепую. Включи сейчас.

`PG-MON-091` — `log_min_duration_statement = 0`

в проде — логирует каждый запрос, диск заполняется за час.

`PG-MON-092` — Мониторинг только TPS и Connections

без long transactions / bloat / replication lag — пропустишь все интересные проблемы.

`PG-MON-093` — `auto_explain.log_analyze = on` на high-throughput-кластере без причины

— overhead 10–20% на каждый запрос.

`PG-MON-094` — Долгий `pg_stat_statements_reset()` без согласования

— потеряли историю наблюдения.

Backup и restore — что должен знать разработчик

DBA настраивает backup-стратегию. Разработчику обычно достаточно понимать: какие типы backup'ов бывают, как восстановить локальный дамп для дебага, что говорить, когда нужен restore прода. Эта статья — этот минимум.

Правила пронумерованы кодами PG-BK-NNN.

1. Два класса backup

`PG-BK-001` — Логический backup

— SQL-дамп через pg_dump. Данные как INSERT/COPY-команды, читаемо.

`PG-BK-002` — Физический backup

— копия файлов кластера через pg_basebackup. Бинарный snapshot.

	Логический (`pg_dump`)	Физический (`pg_basebackup`)
Гранулярность	таблицы, схемы, БД	весь кластер
Размер	компактно (без bloat)	как на диске (с bloat)
Скорость backup	медленно	быстро (просто copy)
Скорость restore	медленно (parsing SQL)	быстро (просто copy)
Кросс-версия PG	да (есть совместимость)	нет (только same major)
PITR	нет	да + WAL archive
Подходит для	дебаг, миграция между версиями, cherry-pick данных	прод-backup, replica setup

`PG-BK-003` — Прод обычно использует физический + WAL archive (для PITR)

Логические дампы — для разработчика и одноразовых задач.

2. `pg_dump` — логический backup

`PG-BK-010` — Базовый дамп БД:

pg_dump -U user -h host -p 5432 mydb > mydb.sql

Создаст файл с DDL + INSERT'ами. Восстановление:

psql -U user -h host -p 5432 mydb_new < mydb.sql

`PG-BK-011` — Custom format (рекомендуется) — компактнее, парallel restore:

pg_dump -Fc -U user -h host mydb > mydb.dump

# restore
pg_restore -j 4 -d mydb_new mydb.dump   # 4 параллельных worker'а

`PG-BK-012` — Полезные флаги:

pg_dump --schema=public            # только одна схема
pg_dump --table=order_doc          # только одна таблица (с внешними FK — осторожно)
pg_dump --schema-only              # только структура без данных
pg_dump --data-only                # только данные без структуры
pg_dump --exclude-table=audit_log  # исключить таблицу
pg_dump --no-owner --no-acl        # без владельца и прав (для restore в другой кластер)

`PG-BK-013` — `pg_dumpall` — для глобальных объектов

(роли, tablespaces, настройки). Дополняет pg_dump:

pg_dumpall --globals-only > globals.sql
pg_dump mydb > mydb.sql

3. Восстановить локально для дебага

`PG-BK-020` — Типичный сценарий: «у клиента баг, нужен дамп для отладки»

DBA отдаёт customer-prod-2026-05-01.dump. Разработчик:

# создать пустую локальную БД
createdb -U postgres customer_debug

# восстановить
pg_restore -d customer_debug -j 4 customer-prod-2026-05-01.dump

# подключиться
psql customer_debug

`PG-BK-021` — Анонимизация перед передачей дампа разработчику

— обязательна для PII. См. отдельную статью Анонимизация дампов.

4. `pg_basebackup` — физический backup

`PG-BK-030` — Базовая команда:

pg_basebackup -U replicator -h master-host -D /backup/2026-05-07 -Ft -z -P
# -Ft — tar формат
# -z — gzip сжатие
# -P — progress

Создаёт snapshot всего кластера. Используется для:

Бэкапа прода (вместе с WAL archive — PITR).
Создания новой реплики.
Полного restore кластера.

`PG-BK-031` — Restore — распаковать архив в `data_directory` и стартовать PG

Версия PG должна совпадать.

5. PITR — Point-In-Time Recovery

`PG-BK-040` — PITR = базовый physical backup + непрерывный WAL archive

Можно восстановить кластер на любой момент времени между backup и текущим.

Конфигурация на мастере:

# postgresql.conf
archive_mode = on
archive_command = 'rsync %p backup-host:/wal-archive/%f'

`PG-BK-041` — Restore на конкретный момент

(restore.signal + recovery_target_time):

# recovery.signal в data_directory + postgresql.conf:
restore_command = 'rsync backup-host:/wal-archive/%f %p'
recovery_target_time = '2026-05-07 14:23:00 MSK'

PG восстановит base backup, потом проиграет WAL до указанного момента.

`PG-BK-042` — PITR — то, что нужно после катастрофы

: «откатились в 14:23, до удаления заказов». DBA-операция, разработчик обычно лишь ставит запрос.

6. Стратегия retention на проде

`PG-BK-050` — Типичная стратегия (DBA-зона):

Daily full backup, retention 7 дней.
Weekly full backup, retention 4 недели.
Monthly full backup, retention 12 месяцев.
Continuous WAL archive — всегда последние 7 дней.

`PG-BK-051` — Все backup'ы хранятся в отдельном (от прода) хранилище

— S3, NAS, Glacier. Локальный backup на той же машине — не backup.

`PG-BK-052` — Backup тестируется restore'ом раз в N дней

«Backup, который не проверяли — ничей не backup.» DBA организует test-restore процедуру, разработчик может быть втянут для валидации содержимого.

7. Backup для multi-tenant

`PG-BK-060` — Row-per-tenant — backup всей БД, restore тоже всей

Чтобы вытащить данные одного тенанта — фильтр в pg_dump --table или COPY ... TO ... WHERE tenant_id = ?.

`PG-BK-061` — Schema-per-tenant — `pg_dump --schema=tenant_X`

, гранулярность лучше.

`PG-BK-062` — DB-per-tenant — каждая БД свой backup-расписание

Удобно, но операционно дороже.

8. Что делать когда «всё сломалось»

`PG-BK-070` — Алгоритм:

STOP — не делай больше изменений. Каждый INSERT после катастрофы усложняет восстановление.
Зови DBA. Если ты разработчик и нет DBA — позови владельца проекта.
Backup current state, прежде чем восстанавливать. Catastrophe recovery должна быть обратимой.
PITR на момент ДО проблемы, если есть архив WAL.
Если PITR нет — последний нормальный backup + ручное восстановление потерянных данных.

`PG-BK-071` — «Удалили таблицу» != «потеряли данные»

Сначала останови все приложения, проверь, не висит ли DDL в транзакции (pg_stat_activity). Если транзакция ещё открыта — ROLLBACK восстановит. Только после этого переходи к restore.

9. Антипаттерны

PG-BK-080 pg_dump плана А, без WAL archive — нет PITR, потеряешь всё между ночными backup'ами.

PG-BK-081 Backup на той же машине — не backup. Диск помрёт — backup умрёт с ним.

PG-BK-082 Backup без проверки restore — обнаружишь, что backup битый, в момент когда нужен.

PG-BK-083 Дамп прода передан разработчику без анонимизации PII — нарушение GDPR / 152-ФЗ.

PG-BK-084 pg_dump --inserts для big DB — INSERT работает в 10x медленнее COPY (default).

WAL и как разработчик влияет на него

WAL (Write-Ahead Log) — это журнал всех изменений в БД. До того как PostgreSQL запишет изменение в табличный файл, он пишет его в WAL. Это даёт три ключевых свойства: durability (после COMMIT транзакция переживёт падение), репликация (реплики проигрывают WAL), PITR (Point-In-Time Recovery).

WAL невидим разработчику в обычной работе, но при росте нагрузки он становится узким местом: WAL пишется до того, как ответ на COMMIT уйдёт клиенту — значит, скорость дисковой записи WAL = верхняя граница пропускной способности на запись.

Эта статья — про то, как разработчик уменьшает объём WAL и латентность коммитов, не трогая инфраструктурные настройки.

1. Жизненный цикл одной записи

Когда клиент делает INSERT INTO orders ...; COMMIT;:

PG модифицирует страницу таблицы orders в shared buffers (память).
Записывает запись об изменении в WAL buffer (память).
На COMMIT — fsync-ит WAL buffer на диск (pg_wal/000000010000000000000001).
Только после успешного fsync — отвечает клиенту OK.
Позже (при checkpoint) — изменённая страница orders сбрасывается на диск (table file).

`PG-W-001` — `fsync` WAL — синхронная операция в каждом коммите

Это и есть «цена durability». Влиять можно либо реже коммитить (батчинг), либо разрешить async-коммит (synchronous_commit = off, опасно), либо группировать коммиты.

2. Что генерирует WAL

Операция	Размер WAL
`INSERT` одной строки	~50–200 байт + длина значений
`UPDATE` строки (полное переписывание)	~50 байт + новая строка целиком
`UPDATE` с HOT	~50 байт + дельта (только изменённые поля, без индексов)
`DELETE`	~50 байт + ID строки
`CREATE INDEX`	весь индекс целиком
`VACUUM`	список освобождённых tuple'ов
`TOAST`-апдейт	если TOAST-значение не изменилось — оно НЕ пишется в WAL повторно

`PG-W-002` — `UPDATE` пишет в WAL новую строку целиком, не дельту

— кроме случая HOT (см. §4). Если в строке 30 колонок и обновили 1 из них, в WAL уйдёт вся строка.

`PG-W-003` — `CREATE INDEX` без `CONCURRENTLY` — мощный генератор WAL

На больших таблицах это может удвоить размер WAL за минуты, и реплика лагнёт.

3. Bulk-операции: COPY vs INSERT, batch

`PG-W-010` — Для массовой вставки — `COPY`, не цикл `INSERT`

-- плохо: 100K транзакций, 100K WAL-fsync, 100K round-trip
for (var row : rows) {
    jdbcTemplate.update("INSERT INTO orders ... VALUES (?, ?, ...)", ...);
}

-- лучше: batch — одна транзакция, меньше WAL-fsync
jdbcTemplate.batchUpdate("INSERT INTO orders ... VALUES (?, ?, ...)", batch);

-- ещё лучше: COPY — минимальный overhead на каждую строку
copyManager.copyIn("COPY orders (col1, col2) FROM STDIN", reader);

Разница на 1M строк может быть 10–100x по времени и в 5–10x по объёму WAL.

`PG-W-011` — Один большой `COMMIT` с 10K вставок дешевле 10K маленьких `COMMIT`

Меньше fsync-ов. Но: длинная транзакция блокирует autovacuum (см. §8). Оптимум — батчи по 1–10K строк.

`PG-W-012` — Перед массовой загрузкой в пустую таблицу — дроп индексов, загрузка, восстановление

DROP INDEX ix_orders_customer;
DROP INDEX ix_orders_status_created;
COPY orders FROM '/tmp/data.csv';
CREATE INDEX CONCURRENTLY ix_orders_customer ON orders (customer_id);
CREATE INDEX CONCURRENTLY ix_orders_status_created ON orders (status, created_at);

Каждая вставка с активным индексом пишет в WAL обновление и таблицы, и каждого индекса. Без индексов — только таблицы.

4. HOT и `fillfactor` — самый недооценённый рычаг

HOT (Heap-Only Tuple) — оптимизация PG для UPDATE: если новая версия строки помещается на ту же страницу И не изменены индексируемые колонки, PG не обновляет индексы и не пишет полные строки в WAL дважды. Просто кладёт новую версию рядом со старой и связывает их chain-ом.

`PG-W-020` — HOT работает только если на странице есть свободное место для новой версии строки

Если таблица заполнена под завязку (fillfactor=100, дефолт), UPDATE почти никогда не получит HOT — пойдёт на новую страницу, обновит все индексы, в WAL уйдёт новая строка + изменения индексов.

-- таблицы с частыми UPDATE'ами:
CREATE TABLE orders (...) WITH (fillfactor = 90);  -- 10% страницы оставлено под HOT-апдейты

ALTER TABLE orders SET (fillfactor = 80);  -- задним числом, для существующей таблицы
-- + VACUUM FULL orders;  чтобы переразложить таблицу под новый fillfactor

`PG-W-021` — Для интенсивно обновляемых таблиц — `fillfactor = 80–90`

Для статичных (read-mostly, например, справочники) — оставь 100. Цена — 10–20% больше места на диске, выигрыш — кратное уменьшение WAL и износа индексов.

`PG-W-022` — HOT отключается, если `UPDATE` меняет индексируемую колонку

Даже если у строки была HOT-возможность, UPDATE поля, по которому есть индекс, всегда обновляет индекс. Если поле меняется часто (например, last_seen_at), а индекс по нему слабоселективный — индекс может вообще не нужен, удали его.

Проверка HOT-ratio:

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY n_tup_upd DESC LIMIT 10;

Если hot_pct < 50% на горячей таблице — кандидат на снижение fillfactor.

5. TOAST и большие значения

`PG-W-030` — Большое значение в колонке (text/jsonb/bytea) попадает в TOAST. Если при `UPDATE` оно не изменилось — TOAST не переписывается, в WAL не идёт

UPDATE article SET view_count = view_count + 1 WHERE id = ?;
-- view_count маленькое поле, body (text 50KB) в TOAST — НЕ пишется в WAL.

Это ещё один аргумент против «всё в один большой data jsonb»: при обновлении одного маленького ключа PG не знает, что внутри JSONB не изменилось, и пишет всё содержимое в WAL.

`PG-W-031` — Если в строке есть и часто-обновляемые маленькие поля, и редко-меняющиеся большие — большие должны быть либо в TOAST (длинный `text`/`bytea`), либо в отдельной таблице

TOAST это делает автоматически для значений > ~2KB.

6. UNLOGGED таблицы — нулевой WAL

`PG-W-040` — Для данных, которые можно потерять при crash, — `UNLOGGED` таблица

Нет WAL, нет реплики, при падении сервера данные обнуляются.

CREATE UNLOGGED TABLE session_cache (
    session_id uuid PRIMARY KEY,
    payload    jsonb NOT NULL,
    expires_at timestamptz NOT NULL
);

Подходит для:

Кеши, которые легко прогреть заново.
Промежуточные данные ETL (загрузили, обработали, переложили).
Очереди задач с TTL, которые не страшно потерять.
Тесты, где БД пересоздаётся каждый прогон.

Не подходит для бизнес-данных (даже «не очень важных» — упасть может в самый неудобный момент).

`PG-W-041` — `UNLOGGED → LOGGED` через `ALTER TABLE` переписывает всю таблицу с генерацией полного WAL

Если планируется смена статуса — это разовая операция, не делай в hot path.

7. `synchronous_commit` — ускорение коммитов

`PG-W-050` — `synchronous_commit = off` отключает ожидание `fsync` WAL — коммит возвращает OK без подтверждения дискa

Это даёт большой прирост throughput на коммит-heavy нагрузке, но ценой: при крахе сервера в окне 200мс перед падением закоммиченные транзакции могут потеряться. Согласованность БД сохраняется (транзакции либо все пройдут, либо ни одна), но клиент думал что закоммитил, а на самом деле нет.

Применимо для:

Аналитики, метрик, логов — где потеря секунды событий не критична.
Тестовых окружений.

Не применять для:

Финансы, заказы, всё что должно быть «гарантированно записано».

`PG-W-051` — Можно регулировать на уровне сессии/транзакции:

SET LOCAL synchronous_commit = off;
INSERT INTO metrics_event ...;
COMMIT;

Полезно, если в одном сервисе есть и критичные данные (по умолчанию on), и метрики (явно off для batch'ей).

8. Длинные транзакции — тихий убийца

`PG-W-060` — Открытая долгая транзакция блокирует освобождение WAL и работу autovacuum

PostgreSQL не может удалить WAL-файлы, в которых есть данные, нужные хоть какой-то открытой транзакции (для MVCC). Открытая транзакция от часа держит часы WAL — диск заполняется до отказа, кластер встаёт.

То же с VACUUM: он не может почистить мёртвые tuple'ы, видимые открытой транзакцией → таблица распухает (bloat) → запросы медленнее.

`PG-W-061` — Не делай долгие транзакции в коде

Типичные ошибки:

Spring @Transactional на методе, который ходит во внешний HTTP / в Kafka / в S3 (десятки секунд = открытая транзакция).
Долгий debug в IDE с открытой PgAdmin-транзакцией.
Batch jobs без коммитов внутри.

Правило: транзакция — секунды, не минуты. Если нужно долго — режь на куски, коммить чаще.

`PG-W-062` — Мониторинг долгих транзакций:

SELECT pid, age(now(), xact_start) AS xact_age, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC LIMIT 10;

Поставь алёрт на xact_age > 5 min — это всегда баг или брошенная сессия.

9. Replication slot — ещё один тихий убийца WAL

`PG-W-070` — Висящий replication slot не даёт PG удалить WAL

Если реплика отвалилась или logical-consumer не читает — WAL копится бесконечно, диск заполняется.

SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots
ORDER BY lag_bytes DESC;

Алёрт на lag_bytes > 10GB или active = false дольше часа — почти всегда сломанный consumer / реплика.

`PG-W-071` — `max_slot_wal_keep_size` (PG13+) ограничивает запас WAL для слота

Если слот отстал больше — PG удаляет WAL, слот ломается, но кластер не падает.

10. Мониторинг WAL

`PG-W-080` — Базовые метрики:

-- размер pg_wal/
SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir();

-- скорость генерации WAL
SELECT now(), pg_current_wal_lsn();
-- замерь дважды через минуту, посчитай разницу

-- статистика записи
SELECT * FROM pg_stat_wal;
-- wal_records, wal_bytes, wal_buffers_full, wal_write, wal_sync, wal_write_time, wal_sync_time

`PG-W-081` — Что мониторить в проде:

pg_wal/ size — алёрт на > N GB.
WAL throughput (bytes/sec) — рост указывает на новую нагрузку.
wal_buffers_full — растёт → увеличить wal_buffers.
HOT-ratio в pg_stat_user_tables < 50% на горячих таблицах.
Долгие транзакции > 5 мин.
Replication slot lag > N GB.

11. Чек-лист «тормозит запись»

Это write-нагрузка? Если read — WAL ни при чём, см. Чтение EXPLAIN.
synchronous_commit = on (дефолт)? — да, это правильно для бизнеса. Если нет — починить, если транзакции критичные.
Bulk-вставки идут через COPY или batch? Не цикл INSERT по одной.
Индексы на горячей таблице — все нужны? Каждый индекс = +WAL на каждую вставку.
HOT-ratio > 80% на интенсивно-обновляемых таблицах? Если нет — снизить fillfactor до 80–90.
Долгие транзакции > 5 мин? Если есть — найти и починить, autovacuum/WAL заблокированы.
Replication slot лагает? Если да — починить consumer или временно дропнуть слот.
Большие JSONB или text часто обновляются целиком? Разделить на маленькие колонки или вынести в отдельную таблицу.

Дамп прода для разработчика — анонимизация

Разработчику нужен дамп прода для дебага. Прод содержит PII (email, телефоны, ФИО, документы). Передавать как есть — нарушение GDPR / 152-ФЗ. Эта статья — что и как маскировать.

Правила пронумерованы кодами PG-AN-NNN.

1. Базовая позиция

`PG-AN-001` — Никогда не передавай дамп прода разработчику без анонимизации

Даже если «он надёжный».

Юридически — нарушение закона о персональных данных.
Безопасно — каждая копия данных = новая поверхность атаки.
Этически — пользователи не подписывались на это.

`PG-AN-002` — Default для разработчика — синтетический dataset

Сгенерированные данные через scripts / faker. Покрывает большинство кейсов.

`PG-AN-003` — Если нужен реальный дамп для воспроизведения проблемы:

Анонимизация PII.
Минимальный объём (одна сущность, не вся БД).
Подписан DPA (Data Processing Agreement) или внутренний регламент.

2. Что считается PII

`PG-AN-010` — Прямые идентификаторы:

Имя, фамилия, отчество.
Email.
Телефон.
Адрес (физический).
Паспорт, СНИЛС, ИНН, водительское удостоверение.
Дата рождения.
IP-адрес.
Номер банковской карты, счёта.

`PG-AN-011` — Косвенные идентификаторы:

Геолокация (точность > населённого пункта).
Уникальные комбинации (профессия + город + возраст могут идентифицировать).
User-Agent / fingerprint.
Связи в графе пользователей.

`PG-AN-012` — Бизнес-секреты, не PII, но всё равно чувствительные:

Цены на B2B-контракты.
Внутренние комментарии модераторов.
Логи действий внутренних сотрудников.

3. Стратегии анонимизации

`PG-AN-020` — Несколько подходов, выбираются под задачу:

Стратегия	Что делает	Когда оправдана
Удаление	`UPDATE t SET email = NULL`	Поле не нужно для дебага
Замена константой	`email = 'masked@example.com'`	Тестируем структуру, не содержимое
Псевдонимизация	детерминированно `email = 'user' \|\| md5(email) \|\| '@test'`	Уникальность сохранилась, обратимости нет
Faker-замена	сгенерить случайное имя/email/телефон	Реалистичный dataset для тестов
Shuffle	переставить значения между строками	Сохранение распределения, потеря связи
Generalization	возраст → возрастная группа	Снижение точности для аналитики
Хэширование	SHA-256 от email	Только для использования as opaque ID

4. Маскирование одного поля

`PG-AN-030` — Email — псевдонимизация с хэшем:

UPDATE customer
SET email = 'user' || substring(md5(email), 1, 8) || '@example.test'
WHERE email IS NOT NULL;

Уникальность сохранилась (одинаковый email → одинаковый псевдоним). Восстановить нельзя без оригинала.

`PG-AN-031` — Телефон — заменить на формат с шумом:

UPDATE customer
SET phone = '+7000' || lpad((random() * 10000000)::int::text, 7, '0')
WHERE phone IS NOT NULL;

`PG-AN-032` — Имя — fake-генерация:

Через extension postgresql_anonymizer:

CREATE EXTENSION anon CASCADE;
SELECT anon.init();

UPDATE customer SET
    first_name = anon.fake_first_name(),
    last_name  = anon.fake_last_name();

Без extension — бить по фейк-словарю на стороне Java/Python (faker.js, Faker).

`PG-AN-033` — Дата рождения — generalize до года:

UPDATE customer SET
    born_on = date_trunc('year', born_on)::date;

Возраст плюс-минус сохранён, конкретный день — нет.

`PG-AN-034` — Адрес — оставить только город или область:

UPDATE customer SET
    address = NULL,         -- или 'г. ' || city
    apartment = NULL;

5. Полный сценарий

`PG-AN-040` — Стандартный flow:

Скопировать прод-БД в staging-anonymizer:
```
pg_dump prod | psql anonymizer
```

Запустить анонимизирующий SQL-скрипт:

BEGIN;

-- email
UPDATE customer SET email = 'user' || substring(md5(email), 1, 8) || '@example.test'
WHERE email IS NOT NULL;

-- phone
UPDATE customer SET phone = '+7000' || lpad((random()*10000000)::int::text, 7, '0')
WHERE phone IS NOT NULL;

-- имена
UPDATE customer SET
  first_name = 'Имя' || id,
  last_name  = 'Фамилия' || id;

-- адреса
UPDATE customer_address SET
  street = NULL,
  building = NULL,
  apartment = NULL;

-- паспорта
UPDATE customer_document SET
  number = '0000' || lpad(id::text, 6, '0'),
  issued_by = 'TEST';

-- payment cards (не должно быть в БД вообще, но на всякий)
UPDATE payment SET
  card_last4 = '0000',
  card_holder_name = 'TEST';

-- audit log с PII
DELETE FROM audit_log WHERE created_at < now() - interval '7 days';

COMMIT;

Дамп анонимизированной БД:

pg_dump anonymizer -Fc > prod-anon-$(date +%F).dump

Передача дампа разработчику.
Удалить anonymizer-БД (не оставляй полу-обработанные данные).

`PG-AN-041` — Скрипт хранится в репозитории

, версионируется, ревьюется. Не однострочник «по памяти».

6. PostgreSQL Anonymizer — extension

`PG-AN-050` — `postgresql_anonymizer` — специализированное расширение

Возможности:

Декларативные правила: «колонка email маскируется как fake email».
Динамическое маскирование (вид с маскированием для определённых ролей).
Готовые функции: anon.fake_first_name(), anon.fake_email(), anon.partial(text, 2, '*****', 2) etc.

CREATE EXTENSION anon CASCADE;
SELECT anon.init();

SECURITY LABEL FOR anon ON COLUMN customer.email
    IS 'MASKED WITH FUNCTION anon.fake_email()';

SECURITY LABEL FOR anon ON COLUMN customer.last_name
    IS 'MASKED WITH FUNCTION anon.fake_last_name()';

-- маскированный дамп:
SELECT anon.dump('customer');   -- или специальный command

`PG-AN-051` — Альтернатива — Greenmask, ARX, ad-hoc-скрипты

Выбор зависит от стека.

7. Что нельзя замаскировать

`PG-AN-060` — Идентификаторы для бизнес-связности оставлять как есть:

customer.id, order.id — связи между таблицами должны работать.
Технические FK.

`PG-AN-061` — Бизнес-данные, не PII:

Названия продуктов, цены — обычно сохраняем.
Статусы, типы — сохраняем.
Текст описаний/комментариев — спорно (может содержать PII), решать на каждый кейс.

`PG-AN-062` — Free-text поля (комментарии, описания) — сложный случай

Пользователь мог вписать туда email, телефон, ФИО. Варианты:

Удалить целиком (SET comment = NULL).
Заменить на placeholder (SET comment = '[REDACTED]').
ML-fly через pg_anonymizer.partial_text() или внешний NER.

8. Re-identification — реальная угроза

`PG-AN-070` — Анонимизация имени и email недостаточна, если можно идентифицировать через комбинацию полей

Пример: если в дампе остался "профессия + дата рождения + город" — для маленького города это уникально идентифицирует.

`PG-AN-071` — k-anonymity

— каждый объект неразличим от ≥k других по идентифицирующим полям. Сложная техника, для большинства проектов overkill, но знать стоит.

`PG-AN-072` — Lite-вариант — обобщение полей-идентификаторов

(дата рождения → год, город → область).

9. Антипаттерны

PG-AN-080 Дамп прода передан разработчику как есть «он надёжный».

PG-AN-081 Анонимизация только email, оставляя ФИО и телефон.

PG-AN-082 Скрипт анонимизации руками, не в репозитории.

PG-AN-083 Использование random() для генерации, потом сравнение результатов разных дампов — каждый раз разные значения.

PG-AN-084 Удаление PII через DELETE вместо UPDATE — теряется бизнес-связность.

PG-AN-085 Хэширование email через MD5 без соли — для популярных доменов rainbow tables может «развернуть» обратно.

Антипаттерны типов

Сводный список того, что чаще всего встречается в продовых схемах и больше всего мешает потом. Каждый пункт — со ссылкой на правило и развёрнутую статью.

1. `varchar(255)` по привычке

`PG-T-080` — Антипаттерн

varchar(255) пришёл из MySQL/Oracle и в PostgreSQL не имеет смысла. text и varchar(n) хранятся одинаково и работают одинаково быстро — длина это просто CHECK.

Что делать:

Если длина — доменное правило (формат phone E.164, ИНН, ISO-страна) — varchar(n) с явным n по делу.
Если просто «ну надо ограничить» — text без длины.

Подробно: Строки, правила PG-T-020, PG-T-021.

2. `timestamp without time zone` для бизнес-времени

`PG-T-081` — Антипаттерн

timestamp без зоны хранит «локальное время непонятно где». Через год никто не помнит, в какой зоне тогда работал сервер. Заказ от 23:30 пятницы превращается в воскресенье после переезда на UTC-host.

Что делать:

timestamptz для всех «когда что-то произошло».
timestamp без зоны — только для локального времени с явной отдельной зоной (расписание открытия магазина в локали города).

Подробно: Время и таймзоны, правило PG-T-030.

3. `varchar(36)` / `char(36)` / `text` для UUID

`PG-T-082` — Антипаттерн

UUID — это 16 байт, а varchar(36) — 36+ байт, без валидации формата, медленнее на сравнениях, чувствителен к регистру.

Что делать:

Тип uuid.
Для PK / FK — UUID v7, не v4.

Подробно: UUID и идентификаторы, правила PG-T-040, PG-T-041.

4. `float` / `real` / `double precision` для денег

`PG-T-083` — Антипаттерн

Двоичная плавающая точка не представляет десятичные дроби точно. 0.1 + 0.2 уже ≠ 0.3. На длинной цепочке расчётов копятся погрешности, которые проявляются на сверке с банком и при налоговой отчётности.

Что делать:

numeric(p, s) для всех денежных полей.
Для интенсивных расчётов — bigint в копейках с дисциплиной во всём приложении.

Подробно: Числа и точность, правило PG-T-013.

5. `serial` / `bigserial` в новой схеме

`PG-T-084` — Устаревший паттерн

С PG10+ есть стандартный GENERATED ALWAYS AS IDENTITY. Он лучше связан с колонкой, корректно копируется при pg_dump, защищён от случайных явных вставок.

Что делать:

bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY — стандарт.
BY DEFAULT — мягкая версия, если бывают старые импорты с явными id.

Подробно: Числа и точность, правило PG-T-012.

6. `smallint 0/1` или `char(1) Y/N` вместо `boolean`

`PG-T-085` — Антипаттерн

В PostgreSQL есть boolean — 1 байт, типобезопасный, читается через WHERE is_active. У smallint/char(1) — лишний CHECK, путаница с типами, неудобство в SQL.

Что делать:

boolean NOT NULL DEFAULT false.

Подробно: Enum и boolean, правило PG-T-050.

7. PG `ENUM` для часто-меняющегося списка

`PG-T-086` — Антипаттерн

Удалить значение из enum нативно невозможно. Переименовать — можно с PG10+, но координация миграции с релизами тяжёлая. Дополнительные атрибуты (порядок, описание) к enum не привесишь.

Что делать:

Если значения растут / переименовываются / имеют атрибуты — reference table.
Если это техническое короткое перечисление до 5–7 значений — ENUM или CHECK IN.

Подробно: Enum и boolean, правило PG-T-051.

8. JSONB как «гибкая схема» для основных полей

`PG-T-087` — Антипаттерн

Если по полю регулярно фильтруют, сортируют или джойнят — это колонка, не JSON-ключ. JSONB не даёт типобезопасности, сложнее индексируется и через год становится «мы фактически document store, но без преимуществ».

Что делать:

В JSONB — только полиморфные / опциональные / редко-используемые атрибуты (содержимое события аудита, настройки интеграций, специфичные атрибуты товара).
Все «горячие» поля выносить в обычные колонки с типами и индексами.

Подробно: JSONB, правило PG-T-061.

9. Массив там, где должна быть отдельная таблица

`PG-T-088` — Антипаттерн

jsonb[] или record[] для строк заказа, элементов корзины, атрибутов с весом — это таблица, перевернутая на бок. Теряются FK, валидация на элемент, INSERT/UPDATE отдельной строки, эффективная индексация.

Что делать:

Массив — только простые скалярные значения без идентичности и атрибутов (теги, локали, разрешения).
Всё остальное — отдельная таблица с FK.

Подробно: Массивы и range-типы, правило PG-T-071.

10. Две колонки `valid_from` / `valid_to` вместо range-типа

`PG-T-089` — Антипаттерн

Когда сущность семантически — это интервал, две колонки требуют дублировать логику пересечений / включений / открытых концов в каждом запросе. Race-condition при «не пересекаются» решается только триггерами или application-level lock-ами.

Что делать:

tstzrange / daterange / int4range.
Для непересечения — EXCLUDE USING gist (key WITH =, period WITH &&).

Подробно: Массивы и range-типы, правила PG-T-072, PG-T-073.

11. Тип `money`

`PG-T-090` — Антипаттерн

Привязан к глобальной локали сервера. Не хранит код валюты. Для мультивалютной системы бесполезен, для одновалютной — numeric всё равно лучше.

Что делать:

numeric(p, s) + отдельная колонка currency char(3) (ISO 4217).

Подробно: Числа и точность, правило PG-T-014.

12. `LocalDateTime` в Java для `timestamptz`-колонки

`PG-T-091` — Антипаттерн

LocalDateTime теряет таймзону. На сервере с TZ=UTC и на ноутбуке разработчика с TZ=Europe/Moscow один и тот же запрос вернёт разные значения.

Что делать:

Instant или OffsetDateTime для timestamptz.
LocalDateTime — только для timestamp without time zone (что само по себе нежелательно для бизнес-времени).

Подробно: Время и таймзоны, правило PG-T-031.

13. `Instant.now()` / `LocalDateTime.now()` напрямую в коде

`PG-T-092` — Антипаттерн

Делает поведение зависимым от системного времени сервера, ломает детерминированность тестов.

Что делать:

DateTimeService (или аналог) с моком в интеграционных тестах.

Подробно: Время и таймзоны, правило PG-T-034.

14. UUID v4 для PK

`PG-T-093` — Не строго антипаттерн, но почти всегда субоптимально

Случайные UUID разбрасывают вставки по всему btree-индексу — больше random IO, плохая упаковка страниц, медленнее последовательные чтения.

Что делать:

UUID v7 (timestamp-ordered) для PK / FK.
v4 — только если важна максимальная непредсказуемость id (security-чувствительные случаи) и проседание производительности приемлемо.

Подробно: UUID и идентификаторы, правило PG-T-041.

Сводный чек-лист на ревью схемы

Перед merge'ом миграции пройтись:

[ ] Все id — bigint GENERATED ALWAYS AS IDENTITY или uuid (v7).
[ ] Все «когда что-то произошло» — timestamptz, не timestamp.
[ ] Все деньги — numeric(p, s), не float.
[ ] Все строки без бизнес-длины — text, не varchar(255).
[ ] UUID — тип uuid, не varchar(36).
[ ] Boolean — boolean, не smallint/char(1).
[ ] Перечисления, которые могут расти — reference table, не PG ENUM.
[ ] JSONB — только полиморфные / опциональные атрибуты, не основные поля.
[ ] Массивы — только скаляры без идентичности; объекты — отдельная таблица.
[ ] Интервалы — range-типы с EXCLUDE для непересечения.
[ ] На Java — Instant/OffsetDateTime для timestamptz, enum для перечислений.
[ ] Время в коде — через DateTimeService, не Instant.now().

Куда идти за конкретным вопросом

Контекст применения

Структура

Числа и точность

1. Целые числа

PG-T-010 — Для id таблицы — bigint. Без вариантов

PG-T-011 — smallint оправдан только когда тип — нумератор фиксированной короткой шкалы

2. Генерация id: IDENTITY вместо serial

PG-T-012 — С PostgreSQL 10+ используем GENERATED ALWAYS AS IDENTITY, а не serial / bigserial

3. Деньги: numeric, не float

PG-T-013 — Деньги, проценты, налоги, тарифы — numeric(p, s). Никогда не real / double precision

PG-T-014 — Тип money PostgreSQL не используем

4. Метрики, проценты, физические величины

PG-T-015 — real (float4) и double precision (float8) применяем только когда уместна неточность

5. Boolean

PG-T-016 — Boolean — это boolean

Строковые типы

1. По умолчанию — text

PG-T-020 — Если у поля нет бизнес-причины ограничивать длину — берём text

2. Когда длина оправдана

PG-T-021 — Длину ставим, когда она — доменное правило, а не «техническое ограничение»

3. char(n) — почти никогда

PG-T-022 — char(n) оправдан только для строго фиксированной длины из стандарта

4. Case-insensitive: citext или функциональный индекс

PG-T-023 — Для case-insensitive — citext либо lower() + functional index. Не LOWER() без индекса в каждом запросе

5. Энкодинг

PG-T-024 — Кластер должен быть UTF8. Локально-зависимые сборки баз — наследие

6. Длинные строки (TOAST)

Время и таймзоны

1. Главное правило

PG-T-030 — Для бизнес-времени — всегда timestamptz. Никогда timestamp (он же timestamp without time zone)

2. Что хранится на самом деле

3. Откуда берутся проблемы

4. JDBC и Java-сторона

PG-T-031 — На Java-стороне используем Instant или OffsetDateTime для timestamptz. LocalDateTime — никогда

5. Когда timestamp without time zone всё-таки нужен

PG-T-032 — timestamp (без TZ) допустим только для «локального времени без привязки к моменту»

6. now(), clock_timestamp(), statement_timestamp()

PG-T-033 — Знайте разницу:

7. Тестируемое время

PG-T-034 — В приложении не вызывайте Instant.now() / LocalDateTime.now() напрямую. Заведите DateTimeService и моките его в тестах

8. INTERVAL и арифметика

PG-T-035 — Используйте INTERVAL для смещений в SQL, не вычисляйте секунды вручную

9. Важная мелочь: +infinity и -infinity

UUID и идентификаторы

1. Тип uuid, не varchar(36)

PG-T-040 — UUID хранится в типе uuid. Никогда не varchar(36) / char(36) / text

2. v4 vs v7: тот же бит-формат, разная производительность

PG-T-041 — Для PK / FK берём UUID v7, не v4

Что происходит с UUID v4

Что меняет UUID v7

Цена перехода

3. Где взять генератор UUID v7

PG-T-042 — Генерируем UUID v7 на стороне приложения

4. UUID vs bigint IDENTITY: что выбирать

PG-T-043 — bigint IDENTITY дешевле и быстрее. UUID — когда нужен по делу

5. UUID и FK

PG-T-044 — При UUID-PK — обязательно индекс по FK с такой же uuid-колонкой

6. Что делать с уже существующими varchar(36)

Enum, boolean и перечисления

1. Boolean — это boolean

PG-T-050 — Для двух значений «да/нет» — тип boolean. Не smallint 0/1, не varchar('Y'/'N'), не char(1) с CHECK

2. Три способа сделать перечисление

Вариант 1 — PG ENUM

Вариант 2 — reference table

Вариант 3 — text + CHECK IN (...)

3. Когда что брать

PG-T-051 — Правила выбора:

4. Миграционные ловушки ENUM

PG-M-020 — Добавление значения в enum (PG12+) — ALTER TYPE ... ADD VALUE 'X'. Делается мгновенно, но в отдельной транзакции

PG-M-021 — Переименование значения (PG10+):

PG-M-022 — Удаление значения нативно невозможно. Замена enum реализуется так:

5. Java-сторона: как маппить

PG-T-052 — Для перечислений в Java — enum, не String

6. Не путайте: status vs lifecycle

JSONB — когда оправдан, когда нет

1. jsonb vs json

PG-T-060 — Всегда jsonb. json — почти никогда

2. Главное правило: что класть в JSONB, а что в колонку

PG-T-061 — Если по полю регулярно фильтруют, сортируют или джойнят — это колонка, не JSON-ключ

`PG-T-010` — Для id таблицы — `bigint`. Без вариантов

`PG-T-011` — `smallint` оправдан только когда тип — нумератор фиксированной короткой шкалы

2. Генерация id: `IDENTITY` вместо `serial`

`PG-T-012` — С PostgreSQL 10+ используем `GENERATED ALWAYS AS IDENTITY`, а не `serial` / `bigserial`

3. Деньги: `numeric`, не `float`

`PG-T-013` — Деньги, проценты, налоги, тарифы — `numeric(p, s)`. Никогда не `real` / `double precision`

`PG-T-014` — Тип `money` PostgreSQL не используем

`PG-T-015` — `real` (`float4`) и `double precision` (`float8`) применяем только когда уместна неточность

`PG-T-016` — Boolean — это `boolean`

1. По умолчанию — `text`

`PG-T-020` — Если у поля нет бизнес-причины ограничивать длину — берём `text`

`PG-T-021` — Длину ставим, когда она — доменное правило, а не «техническое ограничение»

3. `char(n)` — почти никогда

`PG-T-022` — `char(n)` оправдан только для строго фиксированной длины из стандарта

4. Case-insensitive: `citext` или функциональный индекс

`PG-T-023` — Для case-insensitive — `citext` либо `lower()` + functional index. Не `LOWER()` без индекса в каждом запросе

`PG-T-024` — Кластер должен быть `UTF8`. Локально-зависимые сборки баз — наследие

`PG-T-030` — Для бизнес-времени — всегда `timestamptz`. Никогда `timestamp` (он же `timestamp without time zone`)

`PG-T-031` — На Java-стороне используем `Instant` или `OffsetDateTime` для `timestamptz`. `LocalDateTime` — никогда

5. Когда `timestamp without time zone` всё-таки нужен

`PG-T-032` — `timestamp` (без TZ) допустим только для «локального времени без привязки к моменту»

6. `now()`, `clock_timestamp()`, `statement_timestamp()`

`PG-T-033` — Знайте разницу:

`PG-T-034` — В приложении не вызывайте `Instant.now()` / `LocalDateTime.now()` напрямую. Заведите `DateTimeService` и моките его в тестах

8. `INTERVAL` и арифметика

`PG-T-035` — Используйте `INTERVAL` для смещений в SQL, не вычисляйте секунды вручную

9. Важная мелочь: `+infinity` и `-infinity`

1. Тип `uuid`, не `varchar(36)`

`PG-T-040` — UUID хранится в типе `uuid`. Никогда не `varchar(36)` / `char(36)` / `text`

`PG-T-041` — Для PK / FK берём UUID v7, не v4

`PG-T-042` — Генерируем UUID v7 на стороне приложения

4. UUID vs `bigint IDENTITY`: что выбирать

`PG-T-043` — `bigint IDENTITY` дешевле и быстрее. UUID — когда нужен по делу

`PG-T-044` — При UUID-PK — обязательно индекс по FK с такой же `uuid`-колонкой

6. Что делать с уже существующими `varchar(36)`

1. Boolean — это `boolean`

`PG-T-050` — Для двух значений «да/нет» — тип `boolean`. Не `smallint 0/1`, не `varchar('Y'/'N')`, не `char(1)` с `CHECK`

Вариант 1 — PG `ENUM`

Вариант 3 — `text` + `CHECK IN (...)`

`PG-T-051` — Правила выбора:

4. Миграционные ловушки `ENUM`

`PG-M-020` — Добавление значения в enum (PG12+) — `ALTER TYPE ... ADD VALUE 'X'`. Делается мгновенно, но в отдельной транзакции

`PG-M-021` — Переименование значения (PG10+):

`PG-M-022` — Удаление значения нативно невозможно. Замена enum реализуется так:

`PG-T-052` — Для перечислений в Java — `enum`, не `String`

1. `jsonb` vs `json`

`PG-T-060` — Всегда `jsonb`. `json` — почти никогда

`PG-T-061` — Если по полю регулярно фильтруют, сортируют или джойнят — это колонка, не JSON-ключ

`PG-T-062` — Хорошие случаи:

`PG-T-063` — Плохие случаи:

`PG-T-064` — Запомнить четыре основных:

`PG-T-065` — GIN-индекс с оператором `jsonb_path_ops` для поиска `@>`:

`PG-T-066` — Если запрос всегда по конкретному ключу — functional index, а не GIN

`PG-T-067` — Не пишите в `jsonb` бинарь / большие тексты / base64

`PG-T-070` — Массив уместен, когда:

`PG-T-071` — Массив неуместен, когда:

`PG-T-072` — Range-тип оправдан, когда сущность сама по себе — это интервал

`EXCLUDE` для непересечений

`PG-T-073` — Если бизнес-задача — «эти интервалы для одного ключа не пересекаются», используем `EXCLUDE` constraint, а не приложение

`PG-T-074` — По умолчанию используем `[)` — это даёт корректное `&&` без перекрытия концов

`PG-N-001` — `snake_case` для всего: таблиц, колонок, индексов, constraint'ов, sequence'ов, view'ов

`PG-N-002` — Никаких кавычек в идентификаторах

`PG-N-010` — Существительные в единственном числе:

`PG-N-011` — Junction-таблицы (many-to-many) — обе сущности в порядке:

`PG-N-012` — Префикс домена для крупных схем

`PG-N-020` — id-колонка таблицы — `id`. Не `customer_id` в таблице `customer`

`PG-N-021` — Foreign key column — `<parent>_id`

`PG-N-022` — Boolean-колонки — с префиксом `is_`/`has_`/`can_`

`PG-N-023` — Время — глагол прошедшего времени + `_at` для timestamp, без — для дат:

`PG-N-024` — Денежные колонки — `_amount` суффикс для сумм, `_price` для цен, `_rate` для курсов/процентов

`PG-N-025` — Длительность — `_seconds`/`_ms`/`_days`/`_hours` суффикс с явной единицей

`PG-N-026` — Перечисления — без префикса/суффикса:

`PG-N-027` — Опционально размер коллекции — `_count` суффикс:

`PG-N-030` — Стандартный набор для каждой бизнес-таблицы:

`PG-N-031` — Soft-delete — `deleted_at timestamptz` (NULL = не удалено), не `is_deleted boolean`

`PG-N-040` — Префикс по типу — `ix_` / `uk_` / `ck_` / `fk_` + таблица + колонки:

`PG-N-041` — Индекс на одну колонку:

`PG-N-042` — Composite-индекс на несколько:

`PG-N-043` — Functional index — суффикс с операцией:

`PG-N-044` — Partial index — суффикс c фильтром: