Партиционирование

Партиционирование PostgreSQL¶

Партиционирование — разбиение большой таблицы на несколько физических частей (partitions). Логически это одна таблица, но PostgreSQL хранит каждую партицию отдельно и может читать только нужные.

Когда нужно:

Таблица > 50-100 GB или > 100 млн строк
Запросы всегда фильтруют по одному столбцу (дата, регион, статус)
Нужно быстро удалять старые данные (DROP partition вместо DELETE)
VACUUM на огромной таблице слишком долгий

Декларативное партиционирование (PG 10+)¶

RANGE — по диапазону¶

Самый частый вариант: партиции по месяцам, кварталам, годам.

SQL

-- Родительская таблица
CREATE TABLE events_part (
    event_id    BIGSERIAL,
    customer_id INT,
    event_type  TEXT,
    event_date  TIMESTAMP NOT NULL,
    page_url    TEXT,
    session_id  TEXT
) PARTITION BY RANGE (event_date);

-- Партиции по месяцам
CREATE TABLE events_2024_06 PARTITION OF events_part
    FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');

CREATE TABLE events_2024_07 PARTITION OF events_part
    FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');

CREATE TABLE events_2024_08 PARTITION OF events_part
    FOR VALUES FROM ('2024-08-01') TO ('2024-09-01');

-- DEFAULT — ловит всё, что не попадает в другие партиции
CREATE TABLE events_default PARTITION OF events_part DEFAULT;

Границы: FROM включительно, TO исключительно

FROM ('2024-06-01') TO ('2024-07-01') содержит все данные за июнь. Строка с event_date = '2024-07-01 00:00:00' попадёт в следующую партицию.

LIST — по списку значений¶

SQL

CREATE TABLE orders_by_status (
    order_id    SERIAL,
    customer_id INT,
    order_date  DATE NOT NULL,
    status      TEXT NOT NULL,
    total_amount NUMERIC(12,2)
) PARTITION BY LIST (status);

CREATE TABLE orders_active PARTITION OF orders_by_status
    FOR VALUES IN ('new', 'processing', 'shipped');

CREATE TABLE orders_completed PARTITION OF orders_by_status
    FOR VALUES IN ('delivered');

CREATE TABLE orders_cancelled PARTITION OF orders_by_status
    FOR VALUES IN ('cancelled');

HASH — равномерное распределение¶

SQL

CREATE TABLE events_hash (
    event_id    BIGSERIAL,
    customer_id INT NOT NULL,
    event_date  TIMESTAMP
) PARTITION BY HASH (customer_id);

-- 4 партиции (modulus = 4)
CREATE TABLE events_hash_0 PARTITION OF events_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_hash_1 PARTITION OF events_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_hash_2 PARTITION OF events_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_hash_3 PARTITION OF events_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partition Pruning¶

Ключевая оптимизация: PostgreSQL читает только нужные партиции.

SQL

-- Запрос по июню → читается ТОЛЬКО events_2024_06
EXPLAIN SELECT * FROM events_part
WHERE event_date >= '2024-06-15' AND event_date < '2024-06-30';

-- В плане увидишь:
-- Append
--   →  Seq Scan on events_2024_06  ← только эта партиция!
--        Filter: (event_date >= ... AND event_date < ...)

SQL

-- Убедись, что pruning включён (по умолчанию on)
SHOW enable_partition_pruning;

Pruning не работает если

Фильтр не по ключу партиционирования
Фильтр использует функцию: WHERE EXTRACT(MONTH FROM event_date) = 6 — pruning не сработает! Используй WHERE event_date >= '2024-06-01' AND event_date < '2024-07-01'
Фильтр через подзапрос (в PG < 14 dynamic pruning ограничен)

Многоуровневое партиционирование¶

Партиции можно партиционировать дальше:

SQL

-- Сначала по году, потом по месяцу
CREATE TABLE events_2024 PARTITION OF events_part
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    PARTITION BY RANGE (event_date);

CREATE TABLE events_2024_q1 PARTITION OF events_2024
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

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

Управление партициями¶

Добавление новых партиций¶

SQL

-- Создать партицию на следующий месяц
CREATE TABLE events_2024_09 PARTITION OF events_part
    FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');

ATTACH / DETACH¶

SQL

-- Отключить партицию (данные остаются, но не видны через родителя)
ALTER TABLE events_part DETACH PARTITION events_2024_06;

-- Теперь events_2024_06 — обычная таблица, можно архивировать
-- pg_dump -t events_2024_06 ...

-- После архивирования — удалить
DROP TABLE events_2024_06;

-- Подключить существующую таблицу как партицию
-- Таблица должна соответствовать схеме и constraint
ALTER TABLE events_part ATTACH PARTITION events_2024_06_restored
    FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');

DETACH CONCURRENTLY (PG 14+)

ALTER TABLE events_part DETACH PARTITION events_2024_06 CONCURRENTLY — не блокирует SELECT на родительской таблице.

Удаление старых данных¶

SQL

-- Вместо DELETE FROM events WHERE event_date < '2024-01-01' (медленно, bloat)
-- Мгновенно:
DROP TABLE events_2023_12;
-- Или: DETACH → архивирование → DROP

Индексы на партиционированных таблицах¶

SQL

-- Индекс на родительской таблице автоматически создаёт индексы на партициях (PG 11+)
CREATE INDEX idx_events_part_date ON events_part (event_date);
CREATE INDEX idx_events_part_customer ON events_part (customer_id);

-- Проверить
SELECT tablename, indexname
FROM pg_indexes
WHERE tablename LIKE 'events_2024%'
ORDER BY tablename, indexname;

Уникальные ключи¶

SQL

-- PRIMARY KEY и UNIQUE должны включать ключ партиционирования!
-- Это НЕ сработает:
-- ALTER TABLE events_part ADD PRIMARY KEY (event_id);
-- ERROR: unique constraint must include all partitioning columns

-- Правильно:
ALTER TABLE events_part ADD PRIMARY KEY (event_id, event_date);

pg_partman — автоматизация¶

pg_partman — расширение для автоматического создания и удаления партиций.

SQL

CREATE EXTENSION pg_partman;

-- Настроить автоматическое партиционирование по месяцам
SELECT partman.create_parent(
    p_parent_table => 'public.events_part',
    p_control => 'event_date',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 3                -- создать 3 партиции вперёд
);

-- Автоматическое обслуживание (запускать по cron или pg_cron)
SELECT partman.run_maintenance();

pg_cron для автоматизации¶

SQL

CREATE EXTENSION pg_cron;

-- Создавать новые партиции и удалять старые каждый день в 3:00
SELECT cron.schedule('partman-maintenance', '0 3 * * *',
    'SELECT partman.run_maintenance()');

Когда НЕ нужно партиционирование¶

Ситуация	Почему не нужно
Таблица < 10 GB	Overhead партиционирования > выгоды
Запросы не фильтруют по ключу	Partition pruning не работает → все партиции читаются
Много мелких партиций (> 1000)	Увеличивает время планирования запросов
Нужен UNIQUE без ключа партиционирования	Невозможно в PostgreSQL

Мониторинг¶

SQL

-- Размер каждой партиции
SELECT
    inhrelid::regclass AS partition_name,
    pg_size_pretty(pg_relation_size(inhrelid)) AS size,
    pg_size_pretty(pg_total_relation_size(inhrelid)) AS total_size
FROM pg_inherits
WHERE inhparent = 'events_part'::regclass
ORDER BY partition_name;

-- Количество строк (оценка из pg_class)
SELECT
    relname,
    reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relname LIKE 'events_2024%'
ORDER BY relname;

Проверь себя¶

Источники¶

PostgreSQL: Table Partitioning — официальная документация
PostgreSQL: Partition Pruning — оптимизация запросов
pg_partman Documentation — автоматизация партиционирования