Партиционирование в PostgreSQL: отсечение секций, время планирования и DETACH вместо DELETE

Таблица событий выросла, запросы за последний месяц читают весь год, а чистка старых данных DELETE-ом пишет в журнал каждую удалённую строку. Партиционирование решает обе беды, если планировщик может отбросить лишние секции, а секций не слишком много. Секция в PostgreSQL — отдельная таблица со своим файлом, индексами и статистикой, которую собирает ANALYZE; отсечение — это решение, какие из этих таблиц вообще попадут в план. С константами его принимает планировщик, с параметрами prepared statement — исполнитель, поэтому оно тесно связано с generic-планом. Цена — планирование растёт с числом секций, а блокировки на родителе при отсоединении секции зависят от формы команды.

DBA / 01koddo

Запрос по дате: что остаётся от 36 секций и сколько стоит план

Опыт 1: что отсекается и когда

Таблица событий по месяцам: 36 секций с января 2024 по декабрь 2026, 720 тыс. строк.

docker run -d --name dba-partitioning-postgres -e POSTGRES_PASSWORD=pg postgres:18
docker exec -it dba-partitioning-postgres psql -U postgres

CREATE TABLE events (id bigint NOT NULL, created_at timestamptz NOT NULL, payload text)
  PARTITION BY RANGE (created_at);
SELECT format('CREATE TABLE events_%s PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
  to_char(m,'YYYY_MM'), m, m + interval '1 month')
FROM generate_series('2024-01-01'::timestamptz, '2026-12-01', interval '1 month') m \gexec
INSERT INTO events SELECT g, timestamptz '2024-01-01' + (g % 1096) * interval '1 day'
  + (g % 86400) * interval '1 second', 'x' FROM generate_series(1, 720000) g;
CREATE INDEX ON events (created_at);
ANALYZE events;
SET max_parallel_workers_per_gather = 0;
SET enable_bitmapscan = off; SET enable_indexscan = off;  -- план короче

EXPLAIN (COSTS OFF, SUMMARY OFF) SELECT count(*) FROM events
WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01';

 Aggregate
   ->  Seq Scan on events_2026_05 events
         Filter: ((created_at >= '2026-05-01 00:00:00+00'::timestamp with time zone) AND (created_at < ...

Остальные 35 секций в плане отсутствуют: константы известны планировщику. С параметрами prepared statement результат зависит от вида плана. Пять первых исполнений планируются под значение (custom-план, как в статье про регрессии планов) и выглядят как выше. Generic-план значений не знает, и отсекать приходится исполнителю:

PREPARE q(timestamptz, timestamptz) AS
  SELECT count(*) FROM events WHERE created_at >= $1 AND created_at < $2;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF) EXECUTE q('2026-05-01', '2026-06-01');

 Aggregate (actual rows=1.00 loops=1)
   ->  Append (actual rows=20367.00 loops=1)
         Subplans Removed: 35
         ->  Seq Scan on events_2026_05 events_1 (actual rows=20367.00 loops=1)
               Filter: ((created_at >= $1) AND (created_at < $2))

Subplans Removed: 35 значит: в кэшированном плане Append содержит все 36 дочерних планов, а при старте исполнитель оставил один. Тот же приём работает и без prepared statement для stable-выражений. Условие created_at >= now() - interval '30 days' при настройках по умолчанию дало четыре оставшиеся секции (сентябрь–декабрь 2026), потому что now() планировщик подставить не может:

   ->  Append
         Subplans Removed: 32
         ->  Bitmap Heap Scan on events_2026_09 events_1
         ... events_2026_10, events_2026_11, events_2026_12

Разделение зашито в коде: три значения перечисления PartClauseTarget (partprune.c) задают, когда отсечение допустимо.

	PARTTARGET_PLANNER,			/* want to prune during planning */
	PARTTARGET_INITIAL,			/* want to prune during executor startup */
	PARTTARGET_EXEC,			/* want to prune during each plan node scan */

Заголовок раздела «Run-Time Partition Pruning Support» в execPartition.c, откуда вызывается ExecDoInitialPruning, формулирует правило: константа — дело планировщика, выражение stable — исполнителя при старте. Следующий абзац того же комментария отделяет параметры PARAM_EXEC: по ним отсекают на каждом проходе узла (третье значение).

 * When pruning involves comparison of a partition key to a constant, it's
 * done by the planner.  However, if we have a comparison to a non-constant
 * but not volatile expression, that presents an opportunity for run-time
 * pruning by the executor, allowing irrelevant partitions to be skipped
 * dynamically.

Запрос без ключа в WHERE (payload = 'x') отсечь нечем: EXPLAIN вывел 36 узлов Seq Scan из 36 секций.

Опыт 2: время планирования при 12, 120 и 1 200 секциях

Таблицы почти пустые (по строке на секцию): меряется план, не данные. Для каждого размера создана секционированная таблица по месяцам (показана ev1200) с индексом по created_at:

CREATE TABLE ev1200 (id bigint NOT NULL, created_at timestamptz NOT NULL, payload text)
  PARTITION BY RANGE (created_at);
SELECT format('CREATE TABLE ev1200_p%s PARTITION OF ev1200 FOR VALUES FROM (%L) TO (%L)', i,
  timestamptz '2000-01-01' + i*interval '1 month', timestamptz '2000-01-01' + (i+1)*interval '1 month')
FROM generate_series(0, 1199) i \gexec
CREATE INDEX ON ev1200 (created_at);

Аналогично ev12 и ev120, в каждую по строке на секцию и ANALYZE. Запрос с ключом отбирает один месяц, без ключа — payload = 'x'. Каждый вариант идёт в новой сессии: шесть EXPLAIN (SUMMARY, MEMORY, COSTS OFF) подряд, первый холодный (кэш описаний отношений сессии пуст), остальные тёплые:

SET max_parallel_workers_per_gather = 0;
EXPLAIN (SUMMARY, MEMORY, COSTS OFF) SELECT count(*) FROM ev1200
WHERE created_at >= '2005-03-01' AND created_at < '2005-04-01';

Вывод трёх подряд запусков в одной сессии (оставлены узел, память и время):

   ->  Seq Scan on ev1200_p62 ev1200
   Memory: used=2022kB  allocated=2120kB
 Planning Time: 8.473 ms
   Memory: used=40kB  allocated=50kB
 Planning Time: 0.043 ms
   Memory: used=40kB  allocated=50kB
 Planning Time: 0.044 ms

Так же прогнаны остальные пять размеров и форм запроса, по 5 сессий на каждый. Сводка: Planning Time в мс (холодный первый запрос — минимум–максимум по 5 сессиям, тёплый — медиана по 25 запускам) и память планировщика тёплого запроса:

запрос         секций   холодный первый   тёплый (медиана)   память, тёплый
с ключом           12     0,205–0,364       0,007              20 кБ
с ключом          120     0,783–0,849       0,023              31 кБ
с ключом        1 200     4,791–5,796       0,021              40 кБ
без ключа          12     0,582–1,034       0,049              83 кБ
без ключа         120     3,891–5,56        0,33              648 кБ
без ключа       1 200    37,028–40,173      9,216            6705 кБ

С ключом тёплое планирование не растёт с 120 до 1 200 секций (0,023 и 0,021 мс). Без ключа время идёт вверх вместе с числом секций: 0,049, 0,33 и 9,2 мс. Первый запрос сессии на 1 200 секциях стоил 4,8–5,8 мс даже с ключом и 2022 кБ памяти против 40 кБ: он делает работу, которую следующие запросы берут готовой из кэша сессии (описания секций из системных каталогов). Для пула соединений это значит, что каждое новое соединение платит такую цену один раз. Первый запрос в выводе выше (8,5 мс) вышел за диапазон сводки, а один дополнительный прогон без ключа на 1 200 секциях дал 215 мс против 37–40 мс; причину не выясняли, так что цифры холодного старта грубее тёплых.

Для prepared statement счёт другой. Тот же запрос с параметрами на ev1200, три EXECUTE под force_custom_plan, затем в новом PREPARE три под force_generic_plan:

custom:  Planning Time: 5.048 ms   (первый в сессии)
         Planning Time: 0.043 ms
         Planning Time: 0.078 ms
generic: Subplans Removed: 1199
         Planning Time: 39.413 ms
         Planning Time: 0.160 ms
         Planning Time: 0.122 ms

Custom-план каждый раз отсекает секции при планировании и укладывается в сотые доли миллисекунды. Generic-план строится один раз и включает все 1 200 секций: 39,4 мс на первое исполнение, после чего Subplans Removed: 1199 снимает почти всё при старте, а повторные EXECUTE берут готовый план.

Опыт 3: DETACH вместо DELETE и ограничение уникальности

Три секции по 1 млн строк (по 88 МБ вместе с индексом по created_at):

CREATE TABLE logs (id bigint NOT NULL, created_at timestamptz NOT NULL, payload text)
  PARTITION BY RANGE (created_at);
CREATE TABLE logs_a PARTITION OF logs FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE logs_b PARTITION OF logs FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE logs_c PARTITION OF logs FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
INSERT INTO logs SELECT g, timestamptz '2026-01-01' + (g % 3) * interval '31 days'
  + (g % 86400) * interval '1 second', md5(g::text) FROM generate_series(1, 3000000) g;
CREATE INDEX ON logs (created_at);
VACUUM ANALYZE logs;

Старый месяц убирается двумя способами. logs_a — DELETE всех строк, logs_b — DETACH PARTITION ... CONCURRENTLY и DROP TABLE. Журнал измерен разностью pg_current_wal_lsn() до и после, время — \timing:

\timing on
SELECT pg_current_wal_lsn() AS l0 \gset
DELETE FROM logs_a;
SELECT pg_size_pretty(pg_current_wal_lsn() - :'l0') AS wal_delete;
SELECT pg_stat_force_next_flush();
SELECT n_live_tup, n_dead_tup, pg_size_pretty(pg_total_relation_size('logs_a'))
FROM pg_stat_user_tables WHERE relname = 'logs_a';
SELECT pg_current_wal_lsn() AS l1 \gset
ALTER TABLE logs DETACH PARTITION logs_b CONCURRENTLY;
DROP TABLE logs_b;
SELECT pg_size_pretty(pg_current_wal_lsn() - :'l1') AS wal_detach_drop;

DELETE:               Time: 302.669 ms   wal_delete: 54 MB
после DELETE:         size 88 MB, n_live_tup 0, n_dead_tup 1000000
DETACH CONCURRENTLY:  Time: 2.802 ms
DROP TABLE:           Time: 12.531 ms    wal_detach_drop: 9552 bytes

DELETE оставил файл в 88 МБ: миллион мёртвых версий строк ждёт VACUUM, а размер файла остаётся прежним, как и в статье про bloat и перепаковку. Отсоединение и удаление файла заняли 15,3 мс и 9552 байт журнала против 302,7 мс и 54 МБ (один прогон каждого, времена зависят от машины, а размер журнала — нет). По документации, CONCURRENTLY выполняется в двух транзакциях и не берёт на родителе ACCESS EXCLUSIVE; обычный DETACH PARTITION берёт его, и очередь за ним устроена так, как описано в статье про блокировки. Подробности и ограничения команды — в документации.

Второе ограничение видно при создании первичного ключа:

ALTER TABLE logs ADD PRIMARY KEY (id);
ALTER TABLE logs ADD PRIMARY KEY (id, created_at);

ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "logs" lacks column "created_at" which is part of the partition key.
ALTER TABLE

Индекс живёт внутри секций, поэтому уникальность между секциями гарантировать нечем: ключ должен содержать столбец секционирования. Ключ (id, created_at) защищает пару, а не id: одинаковый id с другой датой пройдёт. Уникальность одного id можно получить только индексом внутри каждой секции, а между месяцами её держит источник значений (identity или sequence) или приложение.

Стенд убирается командой docker rm -f -v dba-partitioning-postgres: без -v том с данными остаётся на диске.

Что из этого следует

• Секционируй по столбцу, который почти каждый запрос ограничивает константой или параметром. Если ключа в WHERE нет, как в запросе без ключа из опыта 2, секции только добавляют работы планировщику: 9,2 мс на 1 200 секциях против 0,049 мс на 12.
• Число секций держи в десятках или сотнях. Тёплое планирование с ключом не растёт (0,021 мс на 1 200), но без ключа, при первом запросе сессии и в generic-плане цена растёт с числом секций: 38,6 мс, 5,0 мс и 39,4 мс на 1 200.
• Для приложения через prepared statement проверь Subplans Removed в EXPLAIN (ANALYZE) EXECUTE под generic-планом: отсечение работает, но план хранит все секции, и это ещё одна причина смотреть на переключение custom и generic.
• Старые данные убирай отсоединением секции, не DELETE: опыт 3 — 15,3 мс и 9552 байта журнала против 302,7 мс и 54 МБ. Автоматическое создание и удаление секций по расписанию делает расширение pg_partman; в этих опытах оно не запускалось.
• Первичный ключ и уникальный индекс должны включать ключ секционирования. Уникальность одного id между секциями держит identity или sequence, а не индекс.

Чек-лист

1. Выясни, какой столбец стоит в WHERE почти всех запросов к таблице, и сделай его ключом секционирования; для каждого типичного запроса проверь в EXPLAIN, что остаётся одна-две секции.
2. Для prepared statement сравни Planning Time custom-плана и generic-плана и найди Subplans Removed в EXPLAIN (ANALYZE).
3. Посчитай SELECT count(*) FROM pg_inherits WHERE inhparent = 'таблица'::regclass и замерь EXPLAIN (SUMMARY, MEMORY) без ключа в WHERE: если Planning Time без ключа заметен на фоне самого запроса, секций слишком много.
4. Автоматизируй удаление старых данных через DETACH PARTITION ... CONCURRENTLY и DROP TABLE, а не DELETE.
5. Убери стенд командой docker rm -f -v dba-partitioning-postgres.