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

← назад к разделу

Когда таблица растёт до нескольких десятков гигабайт, начинаются проблемы: запросы замедляются, VACUUM идёт часами, удаление старых данных превращается в многочасовую операцию. В этой статье разберём два инструмента — партиционирование и шардирование, — что это такое, чем они отличаются и когда что применять.

Как PostgreSQL хранит данные и почему большая таблица — это боль

Когда к таблице обращается запрос, PostgreSQL работает со страницами (8 KB каждая). Индекс помогает найти нужные страницы быстро. Но если таблица занимает 50–100 GB, даже индекс становится большим — он не помещается целиком в оперативную память (shared_buffers), и чтение превращается в постоянные обращения к диску.

Ещё одна боль — очистка устаревших версий строк. PostgreSQL не удаляет старые версии сразу; это делает фоновый процесс VACUUM. На большой таблице он может работать сутки напролёт, не успевая за потоком изменений.

Есть две стратегии решения этих проблем:

• Партиционирование — разрезать одну большую таблицу на несколько физических кусков внутри одной базы данных на одном сервере.
• Шардирование — разнести данные по нескольким физическим серверам.

Это принципиально разные вещи. Партиционирование — оптимизация внутри одной машины. Шардирование — масштабирование на несколько машин.

Как узнать, сколько места занимает таблица

Прежде чем что-то оптимизировать, нужно измерить. У PostgreSQL есть удобные функции:

-- Размер всей базы данных
SELECT pg_size_pretty(pg_database_size('shop'));
-- → 42 GB

-- Размер таблицы вместе с индексами и TOAST
SELECT pg_size_pretty(pg_total_relation_size('product'));
-- → 12 GB

-- Только сами строки (без индексов)
SELECT pg_size_pretty(pg_relation_size('product'));
-- → 7 GB

-- Только индексы
SELECT pg_size_pretty(pg_indexes_size('product'));
-- → 5 GB

TOAST — отдельное хранилище для длинных значений (текстов, JSON). Если pg_total_relation_size сильно больше суммы pg_relation_size и pg_indexes_size, значит таблица хранит много длинных полей.

Чтобы увидеть все таблицы, отсортированные по размеру:

SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname))       AS table_only,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || relname))        AS indexes,
    n_live_tup   AS live_rows,
    n_dead_tup   AS dead_rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname || '.' || relname) DESC
LIMIT 20;

Если dead_rows сравнимо с live_rows — VACUUM не успевает за удалениями. Если indexes больше table_only — возможно, индексов слишком много.

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

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

Это называется partition pruning — планировщик «отсекает» ненужные партиции и читает только те, где могут быть нужные строки.

Партиционирование решает проблемы большой таблицы на одном сервере:

• индексы на каждой партиции меньше и лучше помещаются в память;
• VACUUM работает на партициях по отдельности — быстрее и легче;
• удалить партицию целиком (DROP TABLE product_2023) — мгновенная операция вместо многочасового DELETE;
• запрос за конкретный месяц читает только нужные партиции, а не всю таблицу.

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

Самый популярный вариант — разрезать по датам. Логи, заказы, транзакции — всё, что накапливается со временем, хорошо ложится в RANGE-партиции.

CREATE TABLE product (
    id          BIGSERIAL,
    category_id BIGINT,
    price       NUMERIC(10, 2) NOT NULL,
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)   -- partition key обязан входить в PRIMARY KEY
) PARTITION BY RANGE (created_at);

CREATE TABLE product_2026_q1 PARTITION OF product
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

CREATE TABLE product_2026_q2 PARTITION OF product
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

Запрос SELECT * FROM product WHERE created_at >= '2026-04-01' прочитает только product_2026_q2. Остальные партиции даже не открываются.

LIST — по конкретным значениям

Когда данные делятся по конечному набору значений — например, по стране, региону или статусу.

CREATE TABLE product (
    id          BIGSERIAL,
    category_id BIGINT NOT NULL,
    price       NUMERIC(10, 2) NOT NULL,
    name        TEXT NOT NULL,
    PRIMARY KEY (id, category_id)
) PARTITION BY LIST (category_id);

CREATE TABLE product_sweets PARTITION OF product FOR VALUES IN (1);
CREATE TABLE product_meat   PARTITION OF product FOR VALUES IN (2);
CREATE TABLE product_dairy  PARTITION OF product FOR VALUES IN (3);
CREATE TABLE product_other  PARTITION OF product DEFAULT;

Запрос WHERE category_id = 1 читает только product_sweets. Остальное планировщик пропускает.

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

Когда нет естественной шкалы и нужно просто равномерно распределить нагрузку между партициями. PostgreSQL берёт хеш от значения ключа и распределяет строки по формуле hash(id) % N.

CREATE TABLE product (
    id          BIGSERIAL,
    category_id BIGINT,
    price       NUMERIC(10, 2) NOT NULL,
    name        TEXT NOT NULL,
    PRIMARY KEY (id)
) PARTITION BY HASH (id);

CREATE TABLE product_p0 PARTITION OF product FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE product_p1 PARTITION OF product FOR VALUES WITH (MODULUS 8, REMAINDER 1);
-- ... product_p7

Запрос WHERE id = 12345 читает только одну партицию. Но запрос без фильтра по id всё равно читает все восемь.

DEFAULT-партиция

Типичная авария с RANGE-партиционированием: наступает новый квартал, партиция ещё не создана, и INSERT падает с ошибкой no partition of relation found for row. Данные теряются, приложение сыплет ошибками.

Защита — DEFAULT-партиция: она ловит все строки, которые не попали ни в одну объявленную партицию.

CREATE TABLE product_default PARTITION OF product DEFAULT;

Теперь INSERT не упадёт. Но DEFAULT — это страховка, а не место для хранения данных. Если туда накопилось много строк, то при попытке ATTACH PARTITION нового диапазона PostgreSQL сначала просканирует DEFAULT под блокировкой, и это может быть долго.

Правильная работа с DEFAULT:

1. Создавать новые партиции заранее — за несколько периодов вперёд (кроном или через pg_partman).
2. Настроить алерт, если в DEFAULT что-то появляется — значит, автосоздание сломалось.
3. Перед каждым ATTACH убедиться, что DEFAULT пустая.

Как выбирать ключ партиционирования

Три обязательных условия:

1. Ключ присутствует в большинстве запросов — иначе планировщик не сможет отсечь лишние партиции и будет читать все.
2. Данные распределяются равномерно — если 95% строк попадают в одну партицию, смысла нет.
3. Ключ почти не меняется — изменение ключа физически перемещает строку из одной партиции в другую, и на горячих обновлениях это дорого.

Ещё одно ограничение: ключ партиционирования обязан входить в PRIMARY KEY и каждый UNIQUE-индекс. Именно поэтому в примерах выше PRIMARY KEY (id, created_at), а не просто PRIMARY KEY (id).

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

Шардирование — это когда данные разносятся по нескольким физическим серверам. Каждый сервер (шард) хранит только часть данных. Приложение или прокси-слой знает, на какой шард идти с каждым конкретным запросом.

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

Стратегии шардирования

По хешу — самая простая. shard = hash(id) % N. Данные распределяются равномерно. Проблема: при добавлении нового сервера нужно перенести значительную часть данных. Решается через consistent hashing, который минимизирует количество перемещаемых строк.

По диапазону — данные с id от 0 до 10M на одном шарде, от 10M до 20M — на другом. Легко добавлять новые серверы, но последний диапазон всегда «горячий» — туда идут все новые записи.

По арендатору — у каждого клиента (tenant) свой шард. Удобно для SaaS: большой клиент получает выделенный сервер, изоляция данных простая. Требует таблицы маршрутизации.

По географии — данные пользователей Европы, Азии, США хранятся на серверах в соответствующих регионах. Снижает задержку и помогает с требованиями хранения данных в определённых странах.

Как выбирать ключ шардирования

Всё то же, что для ключа партиционирования, плюс два дополнительных требования:

• Связанные данные должны лежать на одном шарде — если product шардируется по category_id и category тоже по category_id, то JOIN между ними остаётся локальным внутри шарда. Иначе каждый JOIN превращается в распределённый запрос.
• Транзакции должны укладываться в один шард — распределённые транзакции между несколькими серверами работают медленно и плохо переживают сетевые сбои.

Цена шардирования

Шардирование добавляет сложность, которой нет при партиционировании:

• Распределённые транзакции — медленные и ненадёжные. На практике их стараются избегать через дизайн: одна операция = один шард.
• JOIN между шардами — дорого, почти всегда говорит об ошибке в выборе ключа. Приходится денормализовать данные или синхронизировать их асинхронно.
• Глобальная уникальность — BIGSERIAL не работает: счётчики на разных шардах расходятся. Нужны UUID или специальные генераторы идентификаторов.
• Добавление нового сервера — требует переноса данных. Это долго и нужна специальная инфраструктура (Citus, Vitess).
• Маршрутизация — приложение или прокси должны знать, на какой шард отправить запрос.

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

Практическое правило: сначала партиционируй, шардируй только когда упёрся в потолок одного сервера. Партиционирование решает 80% проблем больших таблиц и несравнимо проще в управлении.

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

Коротко

• Партиционирование — одна таблица, несколько физических кусков на одном сервере. Шардирование — данные на нескольких серверах.
• RANGE — по диапазону (даты, числа). LIST — по конкретным значениям. HASH — равномерное распределение.
• Ключ партиционирования обязан входить в PRIMARY KEY и все UNIQUE-индексы.
• Ключ партиционирования должен присутствовать в большинстве запросов, иначе планировщик читает все партиции.
• DEFAULT-партиция — страховка от потери данных, в норме пустая. Новые партиции создают заранее (кроном или pg_partman).
• Шардирование делает JOIN между шардами и транзакции через несколько шардов дорогими — это избегают через дизайн.
• Глобальный BIGSERIAL при шардировании не работает — нужны UUID или специальные генераторы.

Что почитать дальше

• ACID и уровни изоляции в PostgreSQL — как транзакционная модель меняется при шардировании.
• Covering Index в PostgreSQL — как индексы устроены и почему размер индекса важен.