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

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

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

Сначала измерить

Прежде чем что-то резать, смотрят, что именно выросло: сама таблица, её индексы или длинные значения в TOAST, отдельном хранилище для текстов и JSON.

живой пример

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS table_only,
       pg_size_pretty(pg_indexes_size(relid))        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(relid) DESC
LIMIT 20;

Похожие запросы из интернета склеивают имя строкой, pg_total_relation_size(schemaname || '.' || relname), и падают на первой же таблице с прописной буквой в имени: склеенная строка не экранирует идентификатор. Через relid этой беды нет. Если dead_rows сравнимо с live_rows, таблица распухла от мёртвых строк, и резать её рано, сначала уборка: часть гигабайт это не данные, а мусор. Если индексы больше таблицы, лишние из них дороже любого партиционирования.

Партиционирование: одна таблица, много кусков

Партиционирование это когда логическая таблица физически разрезана на несколько частей, а приложение по-прежнему пишет и читает product: PostgreSQL сам кладёт строку в нужную партицию и при чтении открывает только те, где могут быть нужные строки. Это отсечение партиций, partition pruning, и весь выигрыш держится на нём: горячий кусок с его индексом помещается в память, VACUUM убирает партиции по отдельности, а старый квартал удаляется командой DROP TABLE product_2026_q1 за миллисекунды вместо четырёхчасового DELETE. Суммарный объём индексов от разрезания не уменьшается, а даже чуть растёт: у каждого маленького дерева свой корень.

Режут по тому, по чему фильтруют. Данные, которые накапливаются со временем, режут по диапазону дат:

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 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');

Первичный ключ здесь (id, created_at), а не просто id, и это не стиль: ключ партиционирования обязан входить в первичный ключ и в каждый уникальный индекс, потому что уникальность проверяется внутри одной партиции, и без ключа в индексе база не смогла бы гарантировать её по всей таблице. Об это спотыкаются первым делом при попытке разрезать готовую таблицу.

DEFAULT-партиция

Типичная авария с диапазонами: наступает новый квартал, партиция ещё не создана, и INSERT падает с ошибкой no partition of relation "product" found for row. Строка не сохраняется вообще, и приложение сыплет ошибками. Страховка это партиция по умолчанию, CREATE TABLE product_default PARTITION OF product DEFAULT: она ловит все строки, не попавшие ни в один объявленный диапазон.

DEFAULT это страховка, а не место для хранения. Если туда накопились строки, то при ATTACH PARTITION нового диапазона PostgreSQL сначала просканирует DEFAULT под блокировкой, и на большой таблице это долго. И отсекается она не всегда: её диапазон «всё остальное», поэтому выбросить её из плана можно только когда условие целиком укладывается в объявленные диапазоны. Условие created_at >= '2026-04-01' открыто вправо: в DEFAULT может найтись строка за 2027 год, и её придётся прочитать. Отсюда правила: новые партиции создают заранее, кроном или pg_partman; на появление строк в DEFAULT вешают оповещение, это значит, что автосоздание сломалось; перед каждым ATTACH убеждаются, что DEFAULT пуста.

Ключ выбирают по трём условиям: он стоит в большинстве запросов, иначе отсечение не работает и читаются все партиции; данные по нему распределены ровно, иначе 95 % строк лягут в одну партицию; он почти не меняется, потому что изменение ключа физически переносит строку из партиции в партицию. Как разрезать уже живую таблицу без остановки, через ATTACH и DETACH, разобрано в статье про партиционирование.

Развилка: когда партиционирование уже не поможет

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

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

Ключ шардирования

Шард это отдельный сервер PostgreSQL со своей частью данных, и каждый запрос должен знать, на какой из них идти. Знает он это по ключу шардирования, и выбор ключа это единственное решение, которое потом не переиграть.

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

У ключа по арендатору есть обратная сторона, которую в статьях обычно пропускают: перекос. Гигант-продавец с миллионом карточек занимает узел один, а тысяча мелких делят соседний; узел гиганта горячий, остальные простаивают. Перекос не лечится хешем, он лечится руками: гигантов переселяют на выделенные узлы, и за ними следят как за отдельными сущностями. Вторая обратная сторона, запрос без ключа. Поиск товара по названию по всему каталогу не знает seller_id, и такой запрос веером уходит на все шестнадцать узлов, а его задержка равна самому медленному из них; любой узел в аварии роняет запрос целиком. Таких запросов должно быть мало, и им дают отдельную дорогу: поисковый индекс рядом или предрассчитанные витрины.

Глобальные идентификаторы тоже перестают быть бесплатными. BIGSERIAL на каждом узле начинает с единицы, и два шарда в свой срок выдадут одинаковый id = 1000 двум разным товарам; свести такие данные потом невозможно. Дешёвое лекарство: своя последовательность на шард, START WITH 2 INCREMENT BY 4 на втором из четырёх, но число шардов зашито в шаг. Надёжнее UUID или генераторы с номером узла в битах.

Глубже: LIST, HASH и как строка находит партициюрасширенное

Когда значений конечное число, регион или категория, режут списком: PARTITION BY LIST (category_id) и FOR VALUES IN (1) на партицию. Когда естественной шкалы нет и нужно только равномерно разложить нагрузку, режут хешем: PARTITION BY HASH (id) и партиции WITH (MODULUS 8, REMAINDER 0); запрос по id читает одну из восьми, запрос без id все восемь, и у такого разрезания не бывает партиции по умолчанию, лишних значений в нём не существует.

Как строка по значению ключа находит партицию и почему DEFAULT отсекается не всегда, видно на схеме:

Вся механика это функция от значения ключа, и её видно без базы, в двадцати строках на любом языке:

import java.time.LocalDate;

public class PartitionRouting {
    record Partition(String name, LocalDate from, LocalDate to) {
        boolean holds(LocalDate key) {
            return !key.isBefore(from) && key.isBefore(to);
        }
    }

    static final Partition[] PARTS = {
            new Partition("product_2026_q1", LocalDate.parse("2026-01-01"), LocalDate.parse("2026-04-01")),
            new Partition("product_2026_q2", LocalDate.parse("2026-04-01"), LocalDate.parse("2026-07-01"))};

    public static void main(String[] args) {
        for (String date : new String[]{"2026-02-11", "2026-05-14", "2026-11-02"}) {
            LocalDate key = LocalDate.parse(date);
            String target = "product_default";
            for (Partition p : PARTS) {
                if (p.holds(key)) {
                    target = p.name();
                }
            }
            System.out.println("INSERT created_at=" + date + " -> " + target);
        }

        LocalDate from = LocalDate.parse("2026-04-01");
        System.out.println("SELECT ... WHERE created_at >= " + from);
        for (Partition p : PARTS) {
            System.out.println("  " + p.name() + (p.to().isAfter(from) ? " читается" : " отсечена"));
        }
        System.out.println("  product_default читается: справа диапазон не ограничен");
    }
}

package main

import (
	"fmt"
	"time"
)

type partition struct {
	name     string
	from, to time.Time
}

func (p partition) holds(key time.Time) bool {
	return !key.Before(p.from) && key.Before(p.to)
}

func day(s string) time.Time {
	t, err := time.Parse(time.DateOnly, s)
	if err != nil {
		panic(err)
	}
	return t
}

func main() {
	parts := []partition{
		{"product_2026_q1", day("2026-01-01"), day("2026-04-01")},
		{"product_2026_q2", day("2026-04-01"), day("2026-07-01")},
	}
	for _, date := range []string{"2026-02-11", "2026-05-14", "2026-11-02"} {
		target := "product_default"
		for _, p := range parts {
			if p.holds(day(date)) {
				target = p.name
			}
		}
		fmt.Printf("INSERT created_at=%s -> %s\n", date, target)
	}
	from := day("2026-04-01")
	fmt.Println("SELECT ... WHERE created_at >=", from.Format(time.DateOnly))
	for _, p := range parts {
		state := "отсечена"
		if p.to.After(from) {
			state = "читается"
		}
		fmt.Printf("  %s %s\n", p.name, state)
	}
	fmt.Println("  product_default читается: справа диапазон не ограничен")
}

const parts = [
  { name: 'product_2026_q1', from: '2026-01-01', to: '2026-04-01' },
  { name: 'product_2026_q2', from: '2026-04-01', to: '2026-07-01' },
];
const holds = (p, key) => key >= p.from && key < p.to;

for (const date of ['2026-02-11', '2026-05-14', '2026-11-02']) {
  let target = 'product_default';
  for (const p of parts) if (holds(p, date)) target = p.name;
  console.log(`INSERT created_at=${date} -> ${target}`);
}

const from = '2026-04-01';
console.log(`SELECT ... WHERE created_at >= ${from}`);
for (const p of parts) console.log(`  ${p.name} ${p.to > from ? 'читается' : 'отсечена'}`);
console.log('  product_default читается: справа диапазон не ограничен');

from dataclasses import dataclass
from datetime import date


@dataclass(frozen=True)
class Partition:
    name: str
    start: date
    end: date

    def holds(self, key: date) -> bool:
        return self.start <= key < self.end


parts = [
    Partition("product_2026_q1", date(2026, 1, 1), date(2026, 4, 1)),
    Partition("product_2026_q2", date(2026, 4, 1), date(2026, 7, 1)),
]
for text in ["2026-02-11", "2026-05-14", "2026-11-02"]:
    key = date.fromisoformat(text)
    target = "product_default"
    for p in parts:
        if p.holds(key):
            target = p.name
    print(f"INSERT created_at={text} -> {target}")

start = date(2026, 4, 1)
print(f"SELECT ... WHERE created_at >= {start}")
for p in parts:
    print(f"  {p.name} {'читается' if p.end > start else 'отсечена'}")
print("  product_default читается: справа диапазон не ограничен")

Ноябрьская строка не нашла диапазона и легла в product_default; условие created_at >= '2026-04-01' отсекло первый квартал, но не DEFAULT.

Глубже: Чем шардируют: Citus и альтернативырасширенное

Готовое шардирование для PostgreSQL это расширение Citus. Один узел становится координатором: он держит схему, карту распределения и принимает запросы приложения; рабочие узлы хранят данные. Обычная таблица становится распределённой одной командой:

SELECT create_distributed_table('orders', 'seller_id');
SELECT create_distributed_table('order_items', 'seller_id', colocate_with => 'orders');
SELECT create_reference_table('currencies');

Второй аргумент это колонка распределения, ключ шардирования. Совместное размещение (colocate_with) кладёт строки заказов и позиций с одним seller_id на один узел, и соединение между ними остаётся локальным. Справочные таблицы (create_reference_table) копируются на каждый узел целиком: валюты, статусы, регионы малы и нужны в каждом JOIN. Запрос с seller_id в условии координатор отправляет на один узел, запрос без него на все и собирает результат сам; DDL, выполненный на координаторе, разъезжается по узлам автоматически, и это снимает половину эксплуатационной боли.

Альтернативы дешевле и уже. Маршрутизация в приложении: таблица tenant → база, и приложение само открывает нужное соединение; годится, когда арендаторов сотни и они не пересекаются, и не годится, когда нужны запросы поперёк арендаторов. Отдельная база на арендатора это её крайний случай: изоляция и копии на каждого, и полная невозможность общего отчёта. postgres_fdw поверх партиций, когда партиции объявлены внешними таблицами на других серверах: работает для чтения и для редких записей, но распределённых транзакций нет, часть соединений и агрегатов считается на стороне запроса, и держат это как временную меру. И четвёртый ответ, который часто оказывается правильным: данные, ради которых шардируют, это не реляционные данные вовсе, логи событий и метрики уходят в хранилище, для этого сделанное.

Глубже: Корзины, добавление узла и пересборкарасширенное

Самая дорогая операция в жизни кластера это добавить узел. Если данные разложены формулой hash(key) % 16, то с семнадцатым узлом формула меняется на % 17, и почти каждый ключ меняет адрес: переезжает 94 % данных, это дни копирования и либо простой, либо двойная запись всё это время.

Поэтому шардируют не по числу узлов, а по фиксированному числу логических корзин: 1024 корзины по hash(key) % 1024, а таблица распределения говорит, какая корзина на каком узле. Семнадцатый узел забирает у каждого из шестнадцати по три-четыре корзины, переезжает шесть процентов данных, остальные не трогают. Citus так и устроен: citus.shard_count задаёт число корзин при создании таблицы, а rebalance_table_shards() переносит их между узлами онлайн, через логическую репликацию, без остановки записи. Гиганта, из-за которого перекосился узел, выселяют тем же механизмом: isolate_tenant_to_new_shard('orders', 42) выделяет ему собственную корзину, и её можно перенести на отдельную машину. Число корзин выбирают с запасом на годы: увеличить его потом это пересборка всего.

Глубже: Эксплуатация: то, чему посвящена фазарасширенное

С шардированием каждая операция из этой фазы умножается на число узлов. Копии снимают с каждого узла и с координатора отдельно, и согласованной точки между узлами у обычного PostgreSQL нет: восстановление на момент времени даёт шестнадцать моментов, отличающихся на секунды, и это допустимо только потому, что транзакции по дизайну не пересекают шарды. Миграция схемы либо разъезжается через координатор, как в Citus, либо накатывается на каждый узел с версией схемы в таблице, и приложение обязано работать с обеими версиями, пока накат не дошёл до последнего узла. Отставание реплик, раздувание таблиц и очереди блокировок теперь смотрят на шестнадцати панелях, а не на одной, поэтому метрики сводят по узлам с меткой шарда, и оповещение срабатывает на худший узел, а не на среднее. Всё это и есть цена, которую платят за шардирование, и она не разовая.

Коротко

• Сначала измерить: pg_total_relation_size(relid) по pg_stat_user_tables показывает, что выросло, таблица, индексы или TOAST. Если dead_rows сравнимо с live_rows, сначала VACUUM; если индексы больше таблицы, лишние из них дороже любого разрезания.
• Партиционирование режет таблицу на куски внутри одной машины, приложение видит одну логическую таблицу. Весь выигрыш держится на отсечении партиций; старый квартал удаляется DROP TABLE product_2026_q1 за миллисекунды вместо многочасового DELETE.
• Режут по тому, по чему фильтруют: по времени PARTITION BY RANGE (created_at), по конечному набору значений LIST, ради ровной нагрузки HASH. Ключ партиционирования обязан входить в первичный ключ и в каждый уникальный индекс: PRIMARY KEY (id, created_at).
• DEFAULT-партиция страхует от no partition of relation "product" found for row, но хранить в ней нельзя: партиции создают заранее кроном или pg_partman, на строки в DEFAULT вешают оповещение, перед ATTACH PARTITION проверяют, что она пуста. Из плана DEFAULT не отсекается, пока условие открыто вправо.
• Шардирование нужно, только когда упёрлись в железо одной машины: запись в диск мастера, память под горячие данные, регламент арендатора. Без этих признаков шардирование это цена без выигрыша; ориентир для партиционирования таблица от 50–100 ГБ.
• Ключ шардирования потом не переиграть: он стоит в большинстве запросов, держит связанные данные на одном узле, укладывает транзакцию в один шард. Обратная сторона: перекос по гиганту (лечится выделенным узлом, не хешем), запрос без ключа веером на все узлы, одинаковые BIGSERIAL на разных шардах, поэтому UUID.
• Citus: координатор держит схему и карту, create_distributed_table('orders', 'seller_id'), colocate_with для связанных таблиц, create_reference_table для справочников; DDL с координатора разъезжается сам. Альтернативы, маршрутизация в приложении и postgres_fdw, уже и дешевле.
• Шардируют по фиксированному числу логических корзин, hash(key) % 1024 плюс таблица, какая корзина на каком узле (в Citus citus.shard_count), а не по числу узлов: новый узел переносит около 6 % данных, а hash % 17 вместо % 16 переносит 94 %.
• Копии, миграции схемы и слежение умножаются на число узлов, согласованной точки восстановления между шардами нет. Это постоянная цена, не разовая.

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

• Партиционирование в PostgreSQL — ATTACH, DETACH и как разрезать живую таблицу без остановки.
• VACUUM, autovacuum и bloat — сколько из ваших гигабайт мусор.
• UUID в PostgreSQL — идентификатор, который не ломается при шардировании.
• Multi-tenancy в PostgreSQL — арендаторы как ключ и что из этого следует.