PostgreSQL 17 — партиционирование по диапазону дат, реально ли ускорить запросы в 10 раз?

[RabbitNerd]

Есть таблица событий, сейчас около 800 миллионов строк, растёт примерно на 5 миллионов в сутки. Индексы есть, запросы по диапазону дат работают, но медленно — типичный аналитический запрос за месяц занимает 8-12 секунд. Читал что партиционирование по диапазону дат может дать кратный прирост за счёт partition pruning. Кто делал такую миграцию на живой базе — насколько реально ускорение и как не угробить продакшен?

[archmaster]

Делали точно такую же миграцию год назад, таблица была 600 млн строк. Прирост реальный — запросы за месяц упали с 10 секунд до 0.8-1.2 секунды после партиционирования по месяцам (PARTITION BY RANGE на столбец created_at типа timestamptz). Ключ успеха — правильно настроить partition pruning: убедитесь что в WHERE всегда передаётся константа или параметр запроса, а не выражение типа DATE_TRUNC('month', created_at) — такие выражения pruning ломают в ряде случаев. Используйте EXPLAIN (ANALYZE, BUFFERS) чтобы увидеть Subplans Removed.

[kube_fan]

Осторожно с granularity партиций. Если делаете по дням при 5 млн строк в сутки — получите много маленьких партиций, управлять неудобно, планировщик тратит время на их перебор. По месяцам при вашем объёме — хороший баланс. Ещё важно: autovacuum надо настроить отдельно для партиций, он не наследует настройки родительской таблицы автоматически. И индексы на партиционированных таблицах создаются на каждой партиции отдельно — если у вас много индексов, это больно при создании новой партиции каждый месяц, лучше автоматизировать через pg_partman.

[Macrano]

800 млн строк это ещё не тот масштаб где Postgres начинает скрипеть, но уже пора думать. Если задача чисто аналитическая (исторические данные за прошлые периоды не меняются) — посмотрите в сторону ClickHouse для аналитического слоя. Типичная схема: PostgreSQL как OLTP-источник, репликация через Kafka или напрямую в ClickHouse, аналитика крутится там. Запрос за месяц на 800 млн строк в ClickHouse — это 50-200 миллисекунд без всяких партиций вручную.

[postgres2]

Добавлю про Pg 17 конкретно: там улучшили работу partition-wise join и partition-wise aggregate. Если у вас JOIN двух партиционированных таблиц по одному ключу партиционирования — это теперь работает значительно эффективнее. enable_partitionwise_join и enable_partitionwise_aggregate — проверьте что они ON (в 17 по умолчанию off из соображений совместимости, но для аналитики лучше включить на уровне сессии или базы).

[asynclover]

Миграцию на живой базе делали через pg_partman + логическую репликацию в новую партиционированную таблицу. Схема: создаёте новую таблицу с партициями, настраиваете репликацию изменений через триггеры или logical replication slot, ждёте синхронизации, делаете быстрый swap через RENAME в транзакции. Downtime был около 30 секунд. Главный подводный камень — foreign keys на партиционированную таблицу в Postgres 17 работают лучше чем в 16, но всё равно нужно проверить все зависимые таблицы заранее.