Партиционирование и шардирование в PostgreSQL

Оба подхода помогают масштабировать данные, но работают на разных уровнях: партиции живут в одной СУБД, шарды — на разных узлах. Ниже — сравнение, примеры PARTITION BY, автоматизация через pg_partman, идея шардирования через Citus или FDW, плюс чек-лист выбора стратегии.

1. Термины: что есть что

Коротко: партиции — внутри одного сервера; шарды — между серверами. Их часто путают; даже «FDW-партиции» — это уже распределённая система с другими компромиссами.

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

Типы

• RANGE — даты, идентификаторы по диапазону (самый частый кейс для логов и событий).
• LIST — дискретные значения (регион, статус, валюта).
• HASH — равномерный spread без естественного ключа; «отрезать старый месяц» так обычно неудобно.

Пример RANGE по времени

CREATE TABLE events (
    id SERIAL,
    event_time TIMESTAMP NOT NULL,
    user_id INTEGER,
    event_type VARCHAR(50),
    payload JSONB
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2025_q1 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

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

CREATE INDEX idx_events_time_q1 ON events_2025_q1 (event_time);
CREATE INDEX idx_events_time_q2 ON events_2025_q2 (event_time);

Запросы с предикатом по event_time получают partition pruning — читаются только нужные партиции. Если строка не попадает ни в одну партицию, PostgreSQL вернёт ошибку — заранее планируйте «хвост» и дефолтную партицию при необходимости.

Автоматизация: cron или pg_partman

Вариант A — SQL в задаче по расписанию (идея: создать партицию на следующий квартал):

DO $$
DECLARE
    next_start DATE := date_trunc('quarter', now() + interval '3 months');
    next_end   DATE := date_trunc('quarter', now() + interval '6 months');
BEGIN
    EXECUTE format('
        CREATE TABLE IF NOT EXISTS events_%s PARTITION OF events
        FOR VALUES FROM (%L) TO (%L)',
        to_char(next_start, 'YYYY"q"Q'), next_start, next_end
    );
END;
$$;

Вариант B — pg_partman — расширение для pre-create, retention, управления индексами и ограничениями по политике.

Ограничения

• Для pruning в WHERE должен участвовать ключ партиционирования (или эквивалентное выражение, которое оптимизатор умеет сопоставить).
• PRIMARY KEY / UNIQUE должны включать колонку партиционирования — иначе глобальную уникальность между партициями без полного скана не гарантировать.
• Обычные индексы могут не включать ключ партиционирования — но pruning от этого не зависит.

Статистика по партициям

Родительская partitioned table не хранит строки — смотрите размеры и статистику на дочерних партициях. Пример обхода pg_inherits:

SELECT
    i.inhparent::regclass AS parent,
    i.inhrelid::regclass AS partition,
    psat.last_analyze,
    psat.last_autoanalyze
FROM pg_inherits i
JOIN pg_stat_all_tables psat ON i.inhrelid = psat.relid
WHERE i.inhparent = 'events'::regclass;

3. Шардирование

Ванильный PostgreSQL не шардит прозрачно между кластерами «из коробки». Типовые варианты:

1. Citus — координатор + воркеры, распределённые запросы, ребалансировка (rebalance_table_shards и др.).
2. FDW (postgres_fdw) — партиции как foreign tables на удалённые узлы: прозрачность выше, но latency и операционная сложность растут.
3. Логика в приложении — максимальный контроль и максимальная цена поддержки.

Пример идеи FDW + партиции (упрощённо)

На узлах создаются серверы FDW и маппинги пользователей, затем на «координирующей» стороне — партиционированная таблица, где партиции указывают на шарды. Кросс-шардовые JOIN и транзакции часто упираются в 2PC, задержки и риск «зависших» prepared-транзакций — это нужно закладывать в эксплуатацию.

CREATE EXTENSION postgres_fdw;

CREATE SERVER shard1_server FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'shard1_host', port '5432', dbname 'mydb');

CREATE USER MAPPING FOR current_user
    SERVER shard1_server
    OPTIONS (user 'postgres', password 'your_password');

Дальнейшая схема: CREATE FOREIGN TABLE … на каждом узле для «чужих» данных и PARTITION BY HASH на координаторе. Для продакшена такой путь — редкость; чаще начинают с Citus.

Ограничения шардирования

• 2PC при записи в несколько шардов — задержки и обслуживание prepared.
• JOIN между шардами дорогие; оптимально co-location данных, которые всегда читаются вместе.
• Ребалансировка — долгий процесс; у Citus есть инструменты, у «самодельного» FDW — почти всегда боль.
• Глобальные уникальные ограничения — часто только на уровне приложения или с компромиссами.

4. Партиции vs шарды — практика

Три сценария, где партиций обычно достаточно:

Когда всерьёз смотреть на шардирование:

1. Не влезаете в диск самой большой доступной машины.
2. Упираетесь в CPU/RAM одной ноды.
3. RTO/RPO не достигаются из-за времени бэкапа/restore.
4. Нужна геораспределённость данных.

Порядок величины: пока таблица укладывается в комфортный объём одной машины (условно до ~1 ТБ — не закон, а ориентир), начинайте с партиционирования и мониторинга.

5. Чек-лист масштабирования

1. Соберите топ запросов через pg_stat_statements и динамику роста таблиц за несколько месяцев.
2. Выберите ключ партиционирования: время, tenant_id, регион.
3. Внедряйте на копии: CREATE TABLE … PARTITION BY на пустой таблице безопасно; для большой существующей — миграция через новую таблицу и переливку или инструменты вроде pg_partman с forward-режимом.
4. Автоматизируйте жизненный цикл партиций (cron или pg_partman).
5. Проверьте EXPLAIN, что pruning реально работает.
6. Шардирование — осознанный шаг: сначала готовые решения (Citus), не «велосипед» на FDW без необходимости.

Итог

Партиционирование закрывает большинство задач «очень большой таблицы» внутри одного инстанса. Шардирование — это уже распределённая система: планируйте сеть, эксплуатацию, модель транзакций и стоимость поддержки заранее.