Декларативное секционирование таблиц в PostgreSQL: RANGE, LIST и HASH

• Размер таблицы превышает сотни гигабайт, приводя к деградации построения B-Tree индексов и медленному VACUUM.
• Удаление старых архивных данных через DELETE WHERE created_at < ... генерирует терабайты WAL и колоссальный Bloat.
• Планировщик сканирует всю таблицу целиком вместо выборки конкретного временного интервала.

1. Создание секционированной таблицы по диапазону дат (RANGE)

CREATE TABLE measurements (
    id uuid DEFAULT gen_random_uuid(),
    device_id int NOT NULL,
    measured_at timestamptz NOT NULL,
    value numeric(10,2)
) PARTITION BY RANGE (measured_at);

2. Создание секций и дефолтного раздела

-- Секция за Октябрь 2023
CREATE TABLE measurements_y2023m10 PARTITION OF measurements
    FOR VALUES FROM ('2023-10-01 00:00:00+00') TO ('2023-11-01 00:00:00+00');

-- Секция за Ноябрь 2023
CREATE TABLE measurements_y2023m11 PARTITION OF measurements
    FOR VALUES FROM ('2023-11-01 00:00:00+00') TO ('2023-12-01 00:00:00+00');

-- Секция по умолчанию для предотвращения ошибок вставки не попавших значений
CREATE TABLE measurements_default PARTITION OF measurements DEFAULT;

3. Проверка механизма исключения разделов (Partition Pruning)

Убедитесь, что в postgresql.conf включен параметр enable_partition_pruning = on.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM measurements 
WHERE measured_at >= '2023-10-15' AND measured_at < '2023-10-20';

В плане выполнения должно присутствовать сканирование только партиции measurements_y2023m10 (0 partitions scanned for other tables).

4. Безопасное отсоединение старых данных без блокировки

ALTER TABLE measurements DETACH PARTITION measurements_y2023m10 CONCURRENTLY;
DROP TABLE measurements_y2023m10;