Реализация партиционирования таблиц базы данных

Реализация партиционирования таблиц базы данных

Партиционирование — это разбиение одной логической таблицы на физически отдельные куски (партиции) по значению ключа. Запрос SELECT * FROM events WHERE created_at > '2025-01-01' на нераздроблённой таблице из 500 миллионов строк сканирует всё. На партиционированной по месяцам — только одну партицию с ~40 миллионами строк. Плюс каждую партицию можно независимо архивировать, переносить на дешёвое хранилище или удалять.

PostgreSQL: декларативное партиционирование

С версии PostgreSQL 10 партиционирование встроено и не требует триггеров.

Range партиционирование по дате

-- Создаём родительскую таблицу
CREATE TABLE events (
    id          BIGSERIAL,
    user_id     INTEGER NOT NULL,
    event_type  VARCHAR(64) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Создаём партиции — по одному месяцу
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

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

-- Каждая партиция имеет свои индексы
CREATE INDEX ON events_2025_01 (user_id, created_at DESC);
CREATE INDEX ON events_2025_02 (user_id, created_at DESC);

-- Индекс на родительской таблице автоматически создаётся на всех партициях (PG 11+)
CREATE INDEX ON events (user_id, created_at DESC);

Hash партиционирование (равномерное распределение)

CREATE TABLE user_sessions (
    id          UUID DEFAULT gen_random_uuid(),
    user_id     INTEGER NOT NULL,
    data        JSONB,
    created_at  TIMESTAMPTZ DEFAULT now()
) PARTITION BY HASH (user_id);

-- 8 партиций для равномерного распределения
DO $$
BEGIN
    FOR i IN 0..7 LOOP
        EXECUTE format(
            'CREATE TABLE user_sessions_%s PARTITION OF user_sessions
             FOR VALUES WITH (MODULUS 8, REMAINDER %s)',
            i, i
        );
    END LOOP;
END $$;

List партиционирование по значению

CREATE TABLE orders (
    id          BIGSERIAL,
    country     CHAR(2) NOT NULL,
    status      VARCHAR(32),
    total       NUMERIC(12,2),
    created_at  TIMESTAMPTZ DEFAULT now()
) PARTITION BY LIST (country);

CREATE TABLE orders_ru PARTITION OF orders FOR VALUES IN ('RU');
CREATE TABLE orders_kz PARTITION OF orders FOR VALUES IN ('KZ');
CREATE TABLE orders_by PARTITION OF orders FOR VALUES IN ('BY');
CREATE TABLE orders_other PARTITION OF orders DEFAULT; -- для всех остальных

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

Создавать партиции вручную каждый месяц — ненадёжно. Автоматизация через pg_partman:

# Установка расширения
apt-get install postgresql-14-partman

# В PostgreSQL
CREATE EXTENSION pg_partman SCHEMA partman;

-- Настройка автоматического управления
SELECT partman.create_parent(
    p_parent_table   => 'public.events',
    p_control        => 'created_at',
    p_type           => 'native',
    p_interval       => 'monthly',
    p_premake        => 3  -- создавать 3 партиции вперёд
);

-- Обновляем конфигурацию
UPDATE partman.part_config
SET retention            = '12 months',  -- удалять партиции старше 12 месяцев
    retention_keep_table = false,         -- удалять физически, не только отсоединять
    infinite_time_partitions = true
WHERE parent_table = 'public.events';

Запуск обслуживания (добавление новых + удаление старых партиций):

# cron: каждый день в 2:00
0 2 * * * psql -d mydb -c "SELECT partman.run_maintenance_proc();"

MySQL: партиционирование

MySQL поддерживает партиционирование, но с ограничениями: все уникальные ключи должны включать ключ партиционирования.

CREATE TABLE orders (
    id         BIGINT NOT NULL AUTO_INCREMENT,
    user_id    INT NOT NULL,
    status     VARCHAR(32),
    total      DECIMAL(12,2),
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at),  -- created_at обязателен в PK для partition by range
    KEY idx_user_id (user_id)
)
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503),
    PARTITION p202503 VALUES LESS THAN (202504),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Добавление партиции перед истечением предыдущей:

-- Перед добавлением новой партиции — убрать MAXVALUE
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p202504 VALUES LESS THAN (202505),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Partition Pruning: убеждаемся, что он работает

-- PostgreSQL: проверяем, что используется partition pruning
EXPLAIN SELECT * FROM events
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';

-- Должно быть: "Seq Scan on events_2025_01"
-- Если видим сканирование всех партиций — pruning не работает
-- Причина: приведение типов в WHERE или use of функций

-- Плохо: функция отключает pruning
SELECT * FROM events WHERE DATE(created_at) = '2025-01-15';

-- Хорошо
SELECT * FROM events
WHERE created_at >= '2025-01-15'::date
  AND created_at <  '2025-01-16'::date;

Миграция существующей таблицы

Добавить партиционирование к таблице с данными — нетривиальная задача в PostgreSQL (нельзя сделать ALTER TABLE ... PARTITION BY). Подход без простоя:

-- 1. Создаём новую партиционированную таблицу
CREATE TABLE events_partitioned (
    LIKE events INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- 2. Создаём партиции
-- (скрипт создания партиций для исторических данных)

-- 3. Копируем данные батчами
INSERT INTO events_partitioned
SELECT * FROM events
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';
-- ... повторить для всех месяцев

-- 4. Переключение через транзакцию (быстро, секунды)
BEGIN;
  ALTER TABLE events RENAME TO events_old;
  ALTER TABLE events_partitioned RENAME TO events;
COMMIT;

-- 5. Старая таблица остаётся как страховка, удаляем через неделю
-- DROP TABLE events_old;

Для переноса без простоя — pglogical или logical replication на новую структуру.

Subpartitioning (подпартиции)

-- Партиционирование по году, внутри — по стране
CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')
    PARTITION BY LIST (country);

CREATE TABLE orders_2025_ru PARTITION OF orders_2025
    FOR VALUES IN ('RU');

CREATE TABLE orders_2025_kz PARTITION OF orders_2025
    FOR VALUES IN ('KZ');

Subpartitioning имеет смысл для таблиц с десятками миллиардов строк или при необходимости независимого управления разными осями данных.

Управление retention

-- Отсоединить старую партицию (данные остаются в таблице)
ALTER TABLE events DETACH PARTITION events_2023_01;

-- Архивировать в другую схему или базу
ALTER TABLE events_2023_01 SET TABLESPACE archive_tablespace;

-- Или экспортировать и удалить
COPY events_2023_01 TO '/backups/events_2023_01.csv' CSV HEADER;
DROP TABLE events_2023_01;

Отсоединение партиции операция мгновенная — не блокирует основную таблицу.

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

• Таблица < 10–20 миллионов строк — правильные индексы решат задачу
• Нет чёткого ключа партиционирования (данные без временной или категориальной метки)
• Запросы не фильтруют по ключу партиционирования — pruning не сработает

Партиционирование добавляет сложность обслуживания. Внедрять осознанно, с профилированием до и после.