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

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

Партиціонування — це розбиття однієї логічної таблиці на фізично окремі частини (партиції) за значенням ключа. Запит 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_ua PARTITION OF orders FOR VALUES IN ('UA');
CREATE TABLE orders_ru PARTITION OF orders FOR VALUES IN ('RU');
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"
-- (не "Seq Scan on events" що означає сканування всієї таблиці)