Что такое партиционирование (partitioning) таблиц в базах данных?

Question

Партиционирование — это разделение одной большой таблицы на меньшие физические части (партиции) на основе определенных правил. Каждая партиция хранится отдельно, но логически представляет единую таблицу. Основные типы: по диапазону (range), по списку (list) и по хэшу (hash). Партиционирование улучшает производительность запросов, упрощает управление данными (архивация, удаление) и ускоряет операции за счет работы только с relevant партициями.

YeaHub · Accepted Answer

Партиционирование решает проблемы производительности и управляемости больших таблиц.1. Типы партиционирования в PostgreSQLПо диапазону (RANGE):CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    region VARCHAR(50)
) PARTITION BY RANGE (sale_date);

-- Создание партиций
CREATE TABLE sales_2023_q1 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_2023_q2 PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');По списку (LIST):CREATE TABLE users (
    id SERIAL,
    name VARCHAR(100),
    country_code VARCHAR(2)
) PARTITION BY LIST (country_code);

CREATE TABLE users_eu PARTITION OF users
    FOR VALUES IN ('DE', 'FR', 'IT', 'ES');

CREATE TABLE users_na PARTITION OF users
    FOR VALUES IN ('US', 'CA', 'MX');По хэшу (HASH):CREATE TABLE events (
    id BIGSERIAL,
    event_data JSONB,
    created_at TIMESTAMP
) PARTITION BY HASH (id);

CREATE TABLE events_p0 PARTITION OF events
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);2. Преимущества партиционированияПроизводительность запросов: PostgreSQL обращается только к relevant партициямУправление данными: Легко архивировать или удалять старые данныеПараллельные операции: Разные партиции могут обрабатываться параллельноРазделение хранилища: Партиции можно размещать на разных дисках3. Пример использования с временными данными-- Автоматическое создание партиций
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
    next_month TEXT;
    partition_name TEXT;
BEGIN
    next_month := to_char(CURRENT_DATE + INTERVAL '1 month', 'YYYY_MM');
    partition_name := 'sales_' || next_month;
    
    EXECUTE format(
        'CREATE TABLE %I PARTITION OF sales FOR VALUES FROM (%L) TO (%L)',
        partition_name,
        date_trunc('month', CURRENT_DATE + INTERVAL '1 month')::text,
        date_trunc('month', CURRENT_DATE + INTERVAL '2 month')::text
    );
END;
$$ LANGUAGE plpgsql;4. Ограничения и рекомендацииОграничения:Уникальные ключи должны включать колонку партиционированияFOREIGN KEY constraints работают с ограничениямиНекоторые типы индексов могут не поддерживатьсяРекомендации:Выбирайте колонку партиционирования с высокой кардинальностьюИзбегайте слишком большого количества партиций (сотни+)Используйте CONSTRAINT EXCLUSION для оптимизации запросовРегулярно анализируйте использование партиций-- Анализ эффективности партиционирования
SELECT schemaname, tablename, 
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables 
WHERE tablename LIKE 'sales_%';

-- Проверка использования партиций в запросах
EXPLAIN ANALYZE 
SELECT * FROM sales 
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';Вывод: Партиционирование — мощный инструмент для управления большими таблицами. Используйте его для временных данных, географического распределения или когда нужна быстрая архивация старых данных. Тщательно планируйте стратегию партиционирования, так как ее изменение может потребовать перестройки таблицы.