Партиционирование таблиц в PostgreSQL

Партиционирование таблиц в PostgreSQL

Главная идея

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

1. Таблица events растёт на 5M строк в месяц. Через год — 60M строк. VACUUM начинает занимать 40 минут, индекс user_id весит 8GB. Аналитические запросы по месяцам медленные даже с индексом.
2. Принимаешь решение о партиционировании по created_at с квартальными партициями. Создаёшь родительскую таблицу PARTITION BY RANGE, создаёшь 8 квартальных партиций + DEFAULT.
3. Миграция: создаёшь новую партиционированную таблицу events_partitioned, переносишь данные батчами (INSERT INTO events_partitioned SELECT ... LIMIT 100000), переименовываешь.
4. Partition pruning в действии: EXPLAIN SELECT COUNT(*) FROM events WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01' показывает Seq Scan только на events_2024_q1 — три строки вместо чтения 60M.
5. Удаление данных старше 2 лет: раньше DELETE ... WHERE created_at < '2022-01-01' занимало 3 часа и генерировало огромный WAL. Теперь: DROP TABLE events_2021_q1, events_2021_q2, events_2021_q3, events_2021_q4 — 4 секунды.
6. VACUUM теперь работает на каждой партиции отдельно — autovacuum завершает обслуживание каждой за 2-3 минуты вместо 40 минут для всей таблицы.
7. Новый коллега добавляет запрос WHERE user_id = 123 без условия по дате. EXPLAIN показывает Append с 8 партициями — full scan всех. Объясняешь: для запросов без ключа партиционирования нужен отдельный индекс и это медленнее чем единая таблица.
8. Пытаешься добавить UNIQUE constraint на email в партиционированной таблице. Ошибка: unique constraint must include all partitioning columns. PostgreSQL не поддерживает глобальную уникальность без ключа партиционирования. Решение: уникальность проверяется в коде или через отдельную lookup таблицу.
9. Устанавливаешь pg_partman и настраиваешь автоматическое создание месячных партиций (premake = 2) и удаление старше 18 месяцев (retention = '18 months'). Добавляешь в cron: SELECT partman.run_maintenance();
10. Аналитическая задача: агрегировать данные за Q1 2024. Запрос с WHERE created_at BETWEEN '2024-01-01' AND '2024-04-01' — партиция pruning исключает все остальные 7 партиций. Запрос в 8 раз быстрее чем без партиционирования.
11. Появляется требование хранить данные в разных странах в разных локациях (data residency). LIST partitioning по country + tablespace для каждой партиции. Каждая партиция физически на нужном сервере.
12. Горячая таблица sessions: 10k INSERT/секунду, частые конкурентные записи вызывают contention. HASH partitioning на 8 частей по user_id распределяет нагрузку — каждая партиция получает ~1/8 трафика, lock contention снижается.

Примеры кода

Создание партиционированной таблицы RANGE по дате

-- Создание партиционированной таблицы событий
CREATE TABLE events (
  id          BIGSERIAL,
  user_id     BIGINT NOT NULL,
  type        TEXT NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Создание партиций вручную
CREATE TABLE events_2024_q1 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE events_2024_q2 PARTITION OF events
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE events_2024_q3 PARTITION OF events
  FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE events_2024_q4 PARTITION OF events
  FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

-- DEFAULT partition ловит строки вне диапазонов
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Индексы создаются на каждой партиции (LOCAL)
CREATE INDEX ON events_2024_q1 (user_id);
CREATE INDEX ON events_2024_q2 (user_id);
-- Или сразу на родителе — автоматически создаёт на всех партициях
CREATE INDEX ON events (user_id, created_at);

-- INSERT работает прозрачно — PostgreSQL сам маршрутизирует
INSERT INTO events (user_id, type, payload, created_at)
VALUES (123, 'page_view', '{"page": "/home"}', '2024-03-15');
-- Строка попадёт в events_2024_q1

-- Проверка: какие партиции существуют и сколько строк
SELECT
  child.relname AS partition_name,
  pg_size_pretty(pg_total_relation_size(child.oid)) AS total_size,
  pspt.n_live_tup AS approx_rows
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
JOIN pg_stat_user_tables pspt ON pspt.relname = child.relname
WHERE parent.relname = 'events'
ORDER BY child.relname;

Partition pruning, удаление данных и LIST/HASH

-- Partition pruning в действии
-- Запрос к конкретному кварталу — PostgreSQL обращается только к одной партиции
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), SUM(amount)
FROM events
WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';
-- Вывод: Seq Scan on events_2024_q1 (партиция выбрана, остальные исключены)

-- Запрос без условия по ключу — читает ВСЕ партиции (антипаттерн)
EXPLAIN SELECT * FROM events WHERE user_id = 123;
-- Вывод: Append (читает events_2024_q1, events_2024_q2, ... — все партиции!)

-- Мгновенное удаление устаревших данных
-- DELETE 100M строк = часы. DROP TABLE = миллисекунды!
DROP TABLE events_2022_q1;  -- удаляет партицию мгновенно

-- Или: detach перед drop (партиция становится обычной таблицей)
ALTER TABLE events DETACH PARTITION events_2022_q1 CONCURRENTLY;
-- events_2022_q1 теперь обычная таблица, можно анализировать, архивировать
DROP TABLE events_2022_q1;

-- LIST partitioning: по стране (дискретные значения)
CREATE TABLE orders (
  id         BIGSERIAL,
  user_id    BIGINT,
  country    TEXT NOT NULL,
  amount     NUMERIC(10,2),
  created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY LIST (country);

CREATE TABLE orders_ru PARTITION OF orders FOR VALUES IN ('RU', 'BY', 'KZ');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('DE', 'FR', 'NL', 'IT');
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US', 'CA');
CREATE TABLE orders_other PARTITION OF orders DEFAULT;

-- HASH partitioning: равномерное распределение для горячих таблиц
CREATE TABLE user_sessions (
  id      BIGSERIAL,
  user_id BIGINT NOT NULL,
  data    JSONB
) PARTITION BY HASH (user_id);

CREATE TABLE user_sessions_0 PARTITION OF user_sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

pg_partman: автоматическое управление партициями

-- Установка pg_partman
CREATE EXTENSION pg_partman;

-- Настройка автоматического управления: создание и удаление партиций
SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control      => 'created_at',
  p_type         => 'range',
  p_interval     => 'monthly',     -- или 'daily', 'weekly', 'quarterly'
  p_premake      => 3              -- создать 3 партиции вперёд
);

-- Настройка retention policy (удаление партиций старше 12 месяцев)
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false,  -- удалять, не только detach
    infinite_time_partitions = true
WHERE parent_table = 'public.events';

-- Запуск обслуживания вручную (обычно по cron или pg_cron)
SELECT partman.run_maintenance('public.events');

-- pg_partman создаёт/удаляет партиции автоматически:
-- Создаёт partitions на p_premake периодов вперёд
-- Удаляет/детачит партиции старше retention

-- Мониторинг партиций
SELECT * FROM partman.part_config WHERE parent_table = 'public.events';

-- Проверка что INSERT попадёт в правильную партицию
SELECT tableoid::regclass AS partition
FROM events
WHERE ctid = (
  INSERT INTO events (user_id, type, created_at)
  VALUES (1, 'test', '2024-06-15')
  RETURNING ctid
);

Что происходит под капотом

• Declarative partitioning (PostgreSQL 10+): родительская таблица — только логический объект, физических данных не хранит. Все данные в дочерних таблицах (партициях). Partitioned table inheritance хранится в pg_inherits.
• Partition pruning этапы: compile-time pruning при статических условиях (WHERE date = '2024-01-01' — PostgreSQL знает в плане какую партицию читать). Runtime pruning (PostgreSQL 11+) для параметров ($1) — исключение выполняется при получении значения параметра.
• Маршрутизация INSERT: PostgreSQL проверяет значение partition key и находит подходящую партицию через pg_partitioned_table и ограничения чека. Если нет подходящей партиции и нет DEFAULT — ошибка 'no partition of relation found'.
• LOCAL индексы: каждая партиция имеет свои индексы. CREATE INDEX ON parent создаёт индексы на всех существующих партициях и будет автоматически создавать на новых. GLOBAL индексов в PostgreSQL нет — это отличие от Oracle.
• UNIQUE constraints: должны включать ключ партиционирования. Это значит что uniqueness проверяется только внутри партиции, не глобально. Нельзя гарантировать уникальность email если email не является ключом партиционирования.
• Attach/Detach: ATTACH PARTITION позволяет преобразовать обычную таблицу в партицию (с проверкой данных). DETACH PARTITION — обратная операция. PostgreSQL 14+ добавил CONCURRENTLY — без блокировки родительской таблицы.
• Subpartitioning: партиции могут быть партиционированы дальше. Например, RANGE по году → LIST по стране. Это добавляет сложность планирования и поддержки — используй только при реальной необходимости.
• Параллельное сканирование: PostgreSQL может сканировать несколько партиций параллельно (Parallel Append). Полезно для аналитических запросов без условия по ключу — каждый worker читает свою партицию.

Термины урока

Мини-разбор реальной ситуации

Таблица audit_log: 3 года данных, 2 миллиарда строк, 800GB. VACUUM занимает 8 часов и не успевает — dead tuples растут. Индекс на created_at весит 40GB. Удаление данных старше года: DELETE ... занял бы 12 часов. Решение: CREATE TABLE audit_log_partitioned PARTITION BY RANGE (created_at), создание 36 месячных партиций, миграция данных батчами за выходные. Результат: VACUUM каждой партиции — 15 минут. Удаление 12 старых партиций: DROP TABLE × 12 — 30 секунд. Размер индекса каждой партиции: 1-2GB. pg_partman настроен на автосоздание месячных партиций и удаление партиций старше 24 месяцев.