Модуль: Базы данных · Уровень: Senior

TL;DR#

• Декларативное партиционирование (PARTITION BY, появилось в PG 10, доработано в 11–14) разбивает одну логическую таблицу на физические партиции по ключу. Три стратегии: RANGE (диапазоны, чаще всего для time-series), LIST (явные списки значений), HASH (равномерное распределение по остатку хеша).
• Главная выгода — управление жизненным циклом данных: удаление старых данных через DROP TABLE/DETACH PARTITION (метаданные, мгновенно) вместо DELETE (миллионы строк, dead tuples, нагрузка на vacuum). Плюс partition pruning — планировщик отсекает ненужные партиции и сканирует только релевантные.
• Partition pruning работает только если ключ партиционирования присутствует в WHERE (или вычислим из условий). Есть pruning на этапе планирования (constraint exclusion / новый механизм) и runtime pruning для параметров prepared statements и nested loop.
• Ключевое ограничение: любой PRIMARY KEY / UNIQUE обязан включать колонки ключа партиционирования. Глобальных уникальных индексов нет. FK на партиционированную таблицу можно (с PG 12), FK из партиционированной — тоже.
• Партиционирование — не про “ускорить любой запрос”, а про обслуживаемость больших таблиц, локальность горячих данных и дешёвое удаление. Запросы без ключа партиционирования могут стать медленнее (скан всех партиций + накладные расходы планировщика).
• Старое наследование (table inheritance) + триггеры/CHECK — legacy. Декларативное партиционирование почти всегда предпочтительнее; для автоматизации создания партиций используют pg_partman.

Теория#

Что такое партиционирование и зачем оно#

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

Когда партиционирование оправдано:

Когда не стоит:

• Таблица маленькая/средняя — накладные расходы планировщика и сложность не окупаются.
• Нет естественного ключа партиционирования, который попадает в WHERE большинства запросов.
• Требуется глобальная уникальность по колонке, не входящей в ключ партиционирования (невозможно без ключа в индексе).

RANGE партиционирование#

Самый частый случай — time-series. Границы задаются как FROM (inclusive) TO (exclusive).

-- Родительская (партиционированная) таблица
CREATE TABLE events (
    id          bigint       GENERATED ALWAYS AS IDENTITY,
    tenant_id   bigint       NOT NULL,
    created_at  timestamptz  NOT NULL,
    payload     jsonb        NOT NULL,
    -- ВАЖНО: ключ партиционирования (created_at) обязан входить в PK
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Месячные партиции; TO эксклюзивен → нет пересечения границ
CREATE TABLE events_2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- DEFAULT-партиция ловит всё, что не попало в диапазоны (страховка от ошибки INSERT)
CREATE TABLE events_default PARTITION OF events DEFAULT;

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

• Границы соседних партиций не должны пересекаться, иначе ошибка при создании.
• MINVALUE / MAXVALUE обозначают «без нижней/верхней границы»: FROM (MINVALUE) TO ('2026-01-01').
• При многоколоночном ключе RANGE сравнение лексикографическое: первая колонка важнее.

LIST партиционирование#

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

CREATE TABLE customers (
    id          bigint       GENERATED ALWAYS AS IDENTITY,
    region      text         NOT NULL,
    name        text         NOT NULL,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE customers_eu PARTITION OF customers
    FOR VALUES IN ('DE', 'FR', 'NL', 'PL');

CREATE TABLE customers_us PARTITION OF customers
    FOR VALUES IN ('US', 'CA');

CREATE TABLE customers_other PARTITION OF customers DEFAULT;

HASH партиционирование#

Равномерное распределение по hash(key) % modulus. Используется, когда нужна локальность/параллелизм, но нет естественных диапазонов/списков (например, шардирование по tenant_id).

CREATE TABLE measurements (
    device_id  bigint       NOT NULL,
    ts         timestamptz  NOT NULL,
    value      double precision NOT NULL,
    PRIMARY KEY (device_id, ts)
) PARTITION BY HASH (device_id);

-- 4 партиции: modulus одинаковый, remainder 0..3
CREATE TABLE measurements_p0 PARTITION OF measurements FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE measurements_p1 PARTITION OF measurements FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE measurements_p2 PARTITION OF measurements FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE measurements_p3 PARTITION OF measurements FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Минусы HASH: нельзя дёшево «удалить старое» (нет диапазонов), DEFAULT-партиция запрещена, изменение числа партиций требует пересоздания/перераспределения данных.

Partition pruning (отсечение партиций)#

Это механизм, при котором планировщик/исполнитель исключает партиции, которые заведомо не содержат искомых строк. Это главный источник ускорения запросов.

Два этапа:

1. Plan-time pruning — на этапе планирования, когда значения в WHERE известны как константы.
2. Run-time pruning (PG 11+) — на этапе исполнения, когда значения становятся известны только в рантайме: параметры prepared statement ($1), generic plan, значения от внешней стороны nested loop join, подзапросы.

-- Pruning сработает: created_at в WHERE → планировщик трогает только events_2026_06
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-10';

В плане для эффективного случая видно только нужную партицию (или Append/Subplans Removed: N при runtime pruning):

Append
  ->  Seq Scan on events_2026_06 events_1
        Filter: (created_at >= ... AND created_at < ...)
(остальные партиции отсечены)

-- ПЛОХО: нет ключа партиционирования → скан ВСЕХ партиций
SELECT * FROM events WHERE payload->>'type' = 'login';

Связанные параметры:

• enable_partition_pruning (по умолчанию on) — общий выключатель нового механизма.
• constraint_exclusion — старый механизм (для наследования и UNION ALL); для декларативного партиционирования основной путь — именно enable_partition_pruning.
• enable_partitionwise_join / enable_partitionwise_aggregate (по умолчанию off) — позволяют джойнить/агрегировать пары совпадающих партиций по отдельности; включают вручную, т.к. растёт расход памяти на планирование.

Важно: функции от ключа ломают pruning. WHERE date_trunc('day', created_at) = '...' не отсечёт партиции — нужно сравнивать сам created_at с границами диапазона. То же про неявные приведения типов и created_at::date = '...'.

Проверка runtime pruning с prepared statement:

PREPARE q(timestamptz, timestamptz) AS
  SELECT count(*) FROM events WHERE created_at >= $1 AND created_at < $2;

EXPLAIN (ANALYZE) EXECUTE q('2026-06-01', '2026-06-02');
-- В плане появится "Subplans Removed: N" — это runtime pruning

Индексы на партициях#

С PG 11 индекс, созданный на партиционированной таблице, является «шаблоном»: автоматически создаётся на каждой существующей и будущей партиции (партиционированный индекс).

-- Создаст локальные индексы на всех партициях events
CREATE INDEX ON events (tenant_id, created_at);

Нюансы:

• Глобальных индексов в PostgreSQL нет — каждый индекс локален для партиции.
• CREATE INDEX ... CONCURRENTLY напрямую на партиционированной таблице не поддерживается. Паттерн: создать индекс CONCURRENTLY на каждой партиции по отдельности, затем CREATE INDEX ON parent (...) ONLY (создаст «инвалидный» родительский индекс) и ALTER INDEX parent_idx ATTACH PARTITION child_idx для каждого — после привязки всех партиций родительский индекс становится валидным.

Уникальность, PK и ON CONFLICT#

-- ОШИБКА: unique constraints on partitioned tables must include all partitioning columns
CREATE TABLE t (id bigint PRIMARY KEY, d date) PARTITION BY RANGE (d);
-- ERROR: insufficient columns in PRIMARY KEY constraint definition

Правило: PRIMARY KEY и UNIQUE обязаны включать все колонки ключа партиционирования. Причина — отсутствие глобального индекса: уникальность гарантируется только в пределах партиции, поэтому ключ партиционирования должен быть частью ограничения, чтобы дубликаты не могли «разъехаться» по разным партициям.

Следствия для ON CONFLICT:

• ON CONFLICT (col) DO ... требует, чтобы был соответствующий уникальный индекс/констрейнт — а значит, col должен включать ключ партиционирования.
• ON CONFLICT DO NOTHING без указания конфликтного таргета работает; с таргетом — только если такой констрейнт существует на родителе.

Внешние ключи (FK)#

• FK из обычной таблицы на партиционированную — поддерживается с PG 12 (раньше было нельзя). Внутренне создаются триггеры на каждую партицию.
• FK из партиционированной таблицы на другую — поддерживается.
• FK всё так же требует, чтобы целевые колонки имели уникальный индекс — а тот должен включать ключ партиционирования.

Обслуживание: ATTACH / DETACH, заблаговременное создание#

-- Создать таблицу отдельно, наполнить, затем подключить как партицию
CREATE TABLE events_2026_07 (LIKE events INCLUDING ALL);
-- ... массовая загрузка в events_2026_07 без нагрузки на родителя ...

-- ATTACH: PG проверяет, что данные подходят под границы.
-- Чтобы избежать полного сканирования на проверку, заранее создаём CHECK,
-- совпадающий с будущими границами:
ALTER TABLE events_2026_07
  ADD CONSTRAINT ck CHECK (created_at >= '2026-07-01' AND created_at < '2026-08-01') NOT VALID;
ALTER TABLE events_2026_07 VALIDATE CONSTRAINT ck;

ALTER TABLE events ATTACH PARTITION events_2026_07
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- DETACH: отвязать партицию (станет обычной таблицей). PG 14+: CONCURRENTLY без долгой блокировки
ALTER TABLE events DETACH PARTITION events_2026_05 CONCURRENTLY;

-- Retention: удалить старое — мгновенно, без DELETE
DROP TABLE events_2026_05;

Блокировки:

• ATTACH PARTITION берёт SHARE UPDATE EXCLUSIVE на родителе (с PG 12 не блокирует чтения/записи), но ACCESS EXCLUSIVE на присоединяемой таблице.
• DETACH PARTITION обычный — ACCESS EXCLUSIVE на родителе (блокирует всех). DETACH ... CONCURRENTLY (PG 14+) снимает эту проблему.
• Создание партиции через CREATE TABLE ... PARTITION OF берёт ACCESS EXCLUSIVE на родителе на короткое время.

Создавать партиции заранее обязательно: если строка не попадает ни в одну партицию и нет DEFAULT, INSERT упадёт с ошибкой no partition of relation ... found for row. DEFAULT-партиция спасает, но: вставка в неё при наличии большого объёма усложняет последующий ATTACH (PG сканирует DEFAULT, проверяя, что её данные не конфликтуют с новой партицией) и берёт сильную блокировку.

pg_partman#

Расширение для автоматизации: создаёт будущие партиции по расписанию (фоновый воркер или run_maintenance()), удаляет/архивирует старые по retention-политике, поддерживает RANGE (time и serial).

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control      := 'created_at',
    p_interval     := '1 month',
    p_premake      := 4              -- держать 4 партиции «в запасе» вперёд
);

-- Retention: автоматически дропать партиции старше 6 месяцев
UPDATE partman.part_config
   SET retention = '6 months', retention_keep_table = false
 WHERE parent_table = 'public.events';

-- Запускать периодически (cron/pg_cron):
SELECT partman.run_maintenance('public.events');

Многоуровневое (sub-) партиционирование#

Партиция сама может быть партиционированной таблицей. Типичный паттерн: RANGE по времени, внутри — HASH/LIST по тенанту.

CREATE TABLE events2 (
    tenant_id  bigint NOT NULL,
    created_at timestamptz NOT NULL,
    PRIMARY KEY (tenant_id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events2_2026_06 PARTITION OF events2
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01')
    PARTITION BY HASH (tenant_id);

CREATE TABLE events2_2026_06_h0 PARTITION OF events2_2026_06
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ... h1..h3

Осторожно: число листовых партиций растёт мультипликативно (12 месяцев × 4 хеша = 48). Слишком много партиций (тысячи) перегружает планировщик — растёт время планирования и потребление памяти.

Отличие от старого наследования (table inheritance)#

Наследование сегодня нужно лишь в редких случаях (нерегулярные/перекрывающиеся границы, добавление колонок только в дочерние). Для новых систем — только декларативное партиционирование.

Влияние на запросы и планы (Go-примеры)#

С точки зрения приложения партиционированная таблица — обычная таблица: INSERT/SELECT идут к родителю, маршрутизация прозрачна.

// database/sql — вставка идёт в родителя, PG сам маршрутизирует в партицию.
// Ключ партиционирования (created_at) должен быть валидным значением,
// иначе при отсутствии нужной партиции и DEFAULT — ошибка.
_, err := db.ExecContext(ctx, `
    INSERT INTO events (tenant_id, created_at, payload)
    VALUES ($1, $2, $3)
`, tenantID, time.Now().UTC(), payload)
if err != nil {
    // pq: no partition of relation "events" found for row
    return fmt.Errorf("insert event: %w", err)
}

// pgx — ОБЯЗАТЕЛЬНО передавать ключ партиционирования в WHERE,
// чтобы сработал partition pruning и не сканировались все партиции.
rows, err := pool.Query(ctx, `
    SELECT id, payload
    FROM events
    WHERE tenant_id = $1
      AND created_at >= $2          -- ключ партиционирования
      AND created_at <  $3          -- даёт RANGE pruning
    ORDER BY created_at DESC
    LIMIT 100
`, tenantID, from, to)

// Анти-паттерн: вычисляемое выражение от ключа ломает pruning.
//   WHERE date_trunc('month', created_at) = '2026-06-01'  -- НЕ отсекает партиции
// Правильно — сравнивать сам ключ с границами диапазона:
//   WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01'

Замечания по планам:

• При большом числе партиций даже с pruning время планирования заметно. Помогает plan_cache_mode + prepared statements (runtime pruning).
• EXPLAIN (ANALYZE) показывает реально просканированные партиции; Subplans Removed: N — отсечённые в рантайме.
• Глобальной сортировки по индексу нет «бесплатно»: ORDER BY через несколько партиций может потребовать Merge Append (если у каждой партиции подходящий индекс) или внешней сортировки.

Подводные камни / gotchas#

• Запросы без ключа партиционирования сканируют все партиции и могут стать медленнее, чем были на одной таблице. Партиционирование выбирают под паттерн доступа.
• PK/UNIQUE без ключа партиционирования невозможны → теряете глобальную уникальность по «бизнес-ключу» (например, email), если он не входит в ключ. Решения: вынести проверку в приложение, использовать HASH по самому уникальному столбцу, либо отдельную справочную таблицу.
• ON CONFLICT по произвольной колонке не работает — нужен констрейнт, включающий ключ партиционирования.
• Функции/касты над ключом в WHERE убивают pruning (created_at::date, date_trunc(...), неявные приведения timestamp vs timestamptz). Сравнивайте сырой ключ с границами.
• Забыли создать партицию заранее → INSERT падает (no partition found for row). Нужны pg_partman/cron или DEFAULT-партиция как страховка.
• DEFAULT-партиция — ловушка для производительности: большой объём в ней замедляет/блокирует ATTACH новых партиций (PG сканирует DEFAULT на конфликты под ACCESS EXCLUSIVE).
• ATTACH без заранее подготовленного CHECK делает полное сканирование подключаемой таблицы под блокировкой. Готовьте NOT VALID + VALIDATE заранее.
• DETACH PARTITION (без CONCURRENTLY) берёт ACCESS EXCLUSIVE на родителе — блокирует весь трафик к таблице. На проде используйте DETACH ... CONCURRENTLY (PG 14+).
• CREATE INDEX CONCURRENTLY нельзя на родителе — только на партициях по отдельности с последующим ATTACH PARTITION индекса.
• Слишком много партиций (тысячи) раздувают время планирования и память; обновление статистики и autovacuum по множеству объектов тоже стоит ресурсов. Балансируйте гранулярность.
• Перемещение строки между партициями (UPDATE, меняющий ключ) реализовано как DELETE+INSERT — дороже и может ловить триггеры BEFORE/AFTER неожиданно; до PG 11 было запрещено вовсе.
• TRUNCATE родителя очищает все партиции; будьте внимательны в скриптах обслуживания.
• enable_partitionwise_join/aggregate выключены по умолчанию — для аналитики по совпадающим партициям их нужно включать осознанно (рост памяти планировщика).

Вопросы на собеседовании#

В: Чем декларативное партиционирование отличается от наследования таблиц? О: Декларативное (PG 10+) даёт автоматическую маршрутизацию INSERT по ключу, декларативные непересекающиеся границы, партиционированные индексы и PK (с включением ключа), FK, ATTACH/DETACH, современный pruning с runtime-режимом. Наследование требует ручных триггеров/правил для маршрутизации, CHECK-констрейнтов вручную, не имеет общего PK и опирается лишь на constraint_exclusion без runtime pruning. Для новых систем — только декларативное.

В: Почему PRIMARY KEY обязан включать ключ партиционирования? О: В PostgreSQL нет глобальных индексов — каждый индекс локален для партиции, и уникальность гарантируется только в пределах партиции. Если бы PK не содержал ключ партиционирования, два одинаковых значения могли бы оказаться в разных партициях и не нарушили бы локальную уникальность. Включение ключа в PK гарантирует, что строки с конфликтующим ключом попадают в одну партицию.

В: Что такое partition pruning и когда он не срабатывает? О: Это отсечение партиций, заведомо не содержащих искомых строк. Работает на этапе планирования (константы в WHERE) и в рантайме (параметры $1, generic plan, внешняя сторона nested loop). Не срабатывает, когда ключа партиционирования нет в условии, либо когда над ним применены функции/касты (created_at::date, date_trunc) или есть несовпадение типов, мешающее сравнению с границами.

В: Как удалять старые данные в time-series таблице эффективно? О: Через DROP TABLE partition или DETACH PARTITION (а затем архивирование/дроп) вместо массового DELETE. DROP — это операция над метаданными: мгновенно, без генерации dead tuples, без нагрузки на vacuum и без раздувания индексов. DELETE миллионов строк создаёт bloat и долгую работу autovacuum.

В: Как создать индекс на партиционированной таблице без долгой блокировки записи? О: CREATE INDEX CONCURRENTLY на родителе не поддерживается. Нужно: создать индекс CONCURRENTLY на каждой партиции отдельно, затем создать «шаблонный» индекс на родителе через CREATE INDEX ON parent (...) ONLY (он будет невалидным), и ALTER INDEX ... ATTACH PARTITION привязать к нему все партиционные индексы. После привязки всех партиций родительский индекс становится валидным.

В: Какие риски у DEFAULT-партиции? О: Она полезна как страховка от потери строк, но: при ATTACH новой партиции PostgreSQL сканирует DEFAULT под ACCESS EXCLUSIVE, проверяя, что её содержимое не пересекается с границами новой партиции — при больших объёмах это долго и блокирует. Поэтому DEFAULT не должна накапливать много данных; партиции лучше создавать заранее (pg_partman/cron).

В: Когда HASH-партиционирование, а когда RANGE? О: RANGE — когда есть естественный диапазонный ключ (время, числовые серии) и нужны retention/pruning по диапазонам. HASH — когда нужна равномерная нагрузка/локальность без диапазонов (например, по tenant_id или device_id), но при этом теряется дешёвый retention (нельзя «дропнуть старое»), и изменение числа партиций требует перераспределения данных. DEFAULT для HASH не разрешён.

В: Может ли партиционирование замедлить запросы? О: Да. Запросы без ключа партиционирования сканируют все партиции и добавляют накладные расходы. При большом числе партиций растёт время планирования и потребление памяти. ORDER BY/агрегации поверх многих партиций могут требовать Merge Append или внешней сортировки. Партиционирование оправдано только под конкретный паттерн доступа и объём.

В: Поддерживаются ли внешние ключи на партиционированную таблицу? О: FK из партиционированной таблицы — давно. FK на партиционированную таблицу — с PG 12 (внутренне через триггеры на каждую партицию). Целевые колонки должны иметь уникальный индекс, а тот обязан включать ключ партиционирования.

В: Что такое run-time pruning и зачем он нужен? О: Это отсечение партиций на этапе исполнения, когда значения становятся известны не при планировании, а в рантайме: параметры prepared statements, generic plans, значения от внешней стороны nested loop join, подзапросы. В EXPLAIN ANALYZE отображается как Subplans Removed: N. Нужен, потому что иначе prepared statement с generic plan был бы вынужден сканировать все партиции.

На что копают на senior+#

• Стоимость планирования при тысячах партиций: как plan_cache_mode, generic vs custom plan и runtime pruning взаимодействуют; почему массовое число партиций — анти-паттерн, и где проходит практический предел (обычно сотни, не тысячи).
• Locking-модель обслуживания: какие блокировки берут CREATE/ATTACH/DETACH PARTITION в разных версиях PG, как DETACH CONCURRENTLY (PG 14) и ATTACH без full-scan (через подготовленный CHECK NOT VALID) делают онлайн-обслуживание безопасным.
• Эволюция фич по версиям: PG 10 — базовое декларативное; PG 11 — default-партиция, hash, обновление ключа с перемещением строки, партиционированные индексы/PK, runtime pruning; PG 12 — FK на партиционированную, ускорение pruning и COPY; PG 13 — partition-wise оптимизации, BEFORE row-триггеры; PG 14 — DETACH CONCURRENTLY, ускорение обновления статистики.
• partition-wise join/aggregate: когда включать, требования к совпадению схем партиционирования обеих таблиц, влияние на память планировщика.
• Стратегия глобальной уникальности при отсутствии глобальных индексов: вынос в приложение, HASH по уникальному ключу, отдельная dedup-таблица, или сознательный отказ.
• Взаимодействие с autovacuum/статистикой: per-partition статистика, ANALYZE родителя vs партиций, как раздутая DEFAULT-партиция и неравномерное распределение по HASH влияют на оценки кардинальности и планы.
• Архитектурный выбор vs шардинг: партиционирование — внутри одного инстанса; для горизонтального масштабирования за пределами одной машины нужны Citus/логический шардинг — где граница и как они комбинируются.