Каковы стратегии партиционирования таблиц в PostgreSQL (range, list, hash)?

RANGE — партиционирование по диапазону

Используется для временны́х данных (логи, события, заказы по дате). Планировщик автоматически исключает секции вне запрошенного диапазона.

CREATE TABLE events (
  id          BIGSERIAL,
  created_at  TIMESTAMPTZ NOT NULL,
  user_id     BIGINT,
  payload     JSONB
) PARTITION BY RANGE (created_at);

-- Создание секций вручную
CREATE TABLE events_2024_01
  PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02
  PARTITION OF events
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Секция для «всего остального» (DEFAULT)
CREATE TABLE events_default
  PARTITION OF events DEFAULT;

-- Partition pruning в действии
EXPLAIN SELECT * FROM events WHERE created_at >= '2024-01-15';
-- Partitions: Seq Scan on events_2024_01, events_2024_02... (остальные исключены)

Зачем нужно партиционирование

Партиционирование делит одну логическую таблицу на физически отдельные «секции» (partitions). PostgreSQL поддерживает декларативное партиционирование с версии 10. Основные выгоды: partition pruning (планировщик сканирует только релевантные секции), параллельный scan, быстрое удаление старых данных через DROP PARTITION вместо DELETE.

LIST — партиционирование по списку значений

Используется для дискретных категорий: регион, статус, тип объекта. Не подходит для полей с высокой кардинальностью.

CREATE TABLE orders (
  id      BIGSERIAL,
  region  TEXT NOT NULL,
  amount  NUMERIC
) PARTITION BY LIST (region);

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

CREATE TABLE orders_us
  PARTITION OF orders
  FOR VALUES IN ('US', 'CA');

CREATE TABLE orders_apac
  PARTITION OF orders
  FOR VALUES IN ('JP', 'SG', 'AU');

CREATE TABLE orders_other
  PARTITION OF orders DEFAULT;

HASH — партиционирование по хешу

Равномерно распределяет строки по N секциям. Используется когда нет естественного диапазона или списка, но хочется уменьшить размер каждой секции (например, для параллельного vacuum или разноса по разным tablespace).

CREATE TABLE user_activity (
  id      BIGSERIAL,
  user_id BIGINT NOT NULL,
  data    JSONB
) PARTITION BY HASH (user_id);

-- 4 равных секции
CREATE TABLE user_activity_p0
  PARTITION OF user_activity
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_activity_p1
  PARTITION OF user_activity
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_activity_p2
  PARTITION OF user_activity
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_activity_p3
  PARTITION OF user_activity
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

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

-- Глобальный индекс создаётся автоматически на всех секциях
CREATE INDEX idx_events_user_id ON events (user_id);

-- PRIMARY KEY и UNIQUE должны включать ключ партиционирования
ALTER TABLE events ADD PRIMARY KEY (id, created_at);

Автоматическое создание секций

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

-- После установки pg_partman
SELECT partman.create_parent(
  p_parent_table := 'public.events',
  p_control := 'created_at',
  p_type := 'native',
  p_interval := '1 month',
  p_premake := 3  -- создать 3 секции вперёд
);

Подводные камни

• Partition key в WHERE обязателен для pruning. Если запрос не фильтрует по ключу партиционирования, PostgreSQL сканирует все секции. EXPLAIN покажет «Append» со всеми дочерними узлами.
• PRIMARY KEY / UNIQUE должны включать ключ партиционирования. Это ограничение архитектуры декларативного партиционирования в PostgreSQL — глобальных уникальных индексов нет.
• Внешние ключи на партиционированную таблицу не поддерживаются (FK из другой таблицы, ссылающийся на партиционированную). Решение — триггеры или денормализация.
• Слишком много секций замедляет planning time. При 1000+ секций планировщик тратит значительное время на pruning. Оптимум — десятки, максимум сотня секций.
• DROP TABLE partition vs DETACH + DROP. DETACH PARTITION позволяет «открепить» секцию и работать с ней как с обычной таблицей. С PG 14 есть DETACH CONCURRENTLY без долгой блокировки.
• Bloat в DEFAULT-секции. Если данные часто попадают в DEFAULT, а потом новые секции создаются вручную, DEFAULT разрастается. Используйте pg_partman для автоматизации.
• Миграция существующей таблицы. Нельзя конвертировать обычную таблицу в партиционированную на месте. Нужно создать новую, перелить данные, переименовать.

• Отвечать про range/list/hash partitioning как про абстрактный SQL без учета MVCC, WAL, planner или конкретной версии PostgreSQL.
• Переносить поведение MySQL или ClickHouse на PostgreSQL без проверки документации.
• Не связывать механизм с последствиями для индексов, транзакций, блокировок или эксплуатации.
• Показывать синтаксис, который не поддерживается PostgreSQL.

• Кандидат объясняет range/list/hash partitioning через реальный механизм PostgreSQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.