PostgreSQL
Партиционирование таблиц
Таблица логов выросла до 500 ГБ. `DELETE FROM logs WHERE created_at < '2023-01-01'` - и сервер лежит 40 минут, генерируя WAL. С партиционированием `DROP TABLE logs_2022` выполняется за 0.5 секунды. Это не просто удобство - это другой порядок операций.
- **Cloudflare:** таблицы DNS-запросов с триллионами строк - партиционирование по времени позволяет удалять старые данные за секунды вместо часов блокирующего DELETE
- **Booking.com:** таблицы бронирований партиционированы по году и региону; запросы по конкретному году читают 1/10 данных, старые данные архивируются без downtime через DETACH + перенос
- **Instagram:** партиционирование по user_id HASH для таблиц медиа; равномерное распределение нагрузки между партициями, каждая из которых живёт на своём tablespace
Declarative Partitioning: зачем и как
Партиционирование разбивает одну логическую таблицу на физические подтаблицы (партиции) по заданному ключу. С точки зрения SQL работа идёт с одной таблицей, но PostgreSQL физически читает только нужные партиции. Declarative partitioning (PG 10+) делает это прозрачно - не нужны триггеры и наследование.
Timescale (makers of TimescaleDB) демонстрировали: таблица метрик за 2 года без партиционирования - 800 ГБ, запрос за последний месяц читает всё. С партиционированием по месяцам - тот же запрос читает 1/24 данных, ускорение в 10-20 раз при одинаковых индексах.
Ключ партиционирования должен быть в PRIMARY KEY. Если `id` - суррогатный ключ, добавь поле партиционирования: `PRIMARY KEY (id, created_at)`. Иначе PostgreSQL не сможет гарантировать уникальность id без сканирования всех партиций.
Таблица `events` создана с `PARTITION BY RANGE (created_at)`. Что произойдёт при INSERT, если созданных партиций нет?
Три стратегии: RANGE, LIST, HASH
PostgreSQL поддерживает три стратегии партиционирования, каждая для своего сценария. Выбор стратегии критичен: неправильный выбор может дать не ускорение, а замедление из-за overhead на маршрутизацию.
- RANGE - по диапазонам — Для данных с временным или числовым диапазоном: логи, события, метрики, транзакции. Пример: партиции по месяцам или кварталам. Старые партиции легко архивировать или удалять (DROP PARTITION = мгновенно).
- LIST - по списку значений — Для данных с небольшим набором дискретных значений: регион (EU, US, APAC), статус (active, archived), тип контента. Хорошо когда запросы часто фильтруют по этому полю.
- HASH - равномерное распределение — Для равномерного распределения нагрузки когда нет естественного ключа для RANGE/LIST. Пример: партиции по user_id % N. Минус: нельзя эффективно удалять 'старые' данные.
Таблица `payments` с полем `country_code`. Запросы всегда фильтруют по стране. Какая стратегия оптимальна?
Partition Pruning: умное исключение партиций
Partition pruning - механизм, при котором PostgreSQL на этапе планирования запроса определяет, какие партиции нужно читать, и исключает остальные. Это главная причина, по которой партиционирование ускоряет запросы. Без pruning партиционирование только добавляет overhead.
Pruning не сработает если: ключ партиции обёрнут в функцию (`DATE(created_at) = '2024-01-01'`); условие использует `OR` с несмежными диапазонами без явных границ; тип ключа партиции не совпадает с типом в WHERE (implicit cast).
Таблица партиционирована по `RANGE (created_at)`. Какой WHERE-clause гарантирует partition pruning?
ATTACH и DETACH: управление партициями
ATTACH PARTITION и DETACH PARTITION позволяют добавлять и убирать партиции без создания новой таблицы. Это ключевой инструмент для архивирования: детачить старую партицию, переместить в архивное хранилище, удалить из основной таблицы.
PG 14 добавил `DETACH PARTITION ... CONCURRENTLY` - детач без блокировки таблицы. До этого DETACH брал ShareUpdateExclusiveLock. Для production с высокой нагрузкой всегда используй CONCURRENTLY-вариант.
Зачем добавлять CHECK-ограничение на staging-таблицу перед ATTACH PARTITION?
Routing и DEFAULT-партиция
PostgreSQL автоматически маршрутизирует INSERT в нужную партицию по ключу партиционирования. Но что делать со строками, не попадающими ни в одну партицию? DEFAULT-партиция принимает всё остальное.
UPDATE, меняющий ключ партиционирования, выполняется как DELETE + INSERT. При высокой частоте таких UPDATE это создаёт overhead - мертвые строки в старой партиции и новые в новой. Если ключ меняется часто, стоит пересмотреть выбор ключа партиционирования.
Партиционирование всегда ускоряет запросы; достаточно разбить большую таблицу на партиции и всё станет быстрее.
Партиционирование ускоряет запросы только при эффективном partition pruning. Если WHERE не содержит условий по ключу партиционирования, PostgreSQL сканирует все партиции - это медленнее, чем один B-tree индекс.
Планировщик должен обработать план для каждой партиции отдельно. При 100 партициях и запросе без pruning накладные расходы на планирование и сканирование всех партиций могут быть больше, чем Seq Scan одной большой таблицы с хорошим индексом.
Почему UPDATE, меняющий значение ключа партиционирования, медленнее обычного UPDATE?
Итоги
- **Partition pruning:** главный бенефит - при WHERE по ключу партиционирования PostgreSQL читает только нужные партиции; без pruning партиционирование только замедляет
- **Стратегии:** RANGE для временных данных (логи, события), LIST для дискретных значений (регион, статус), HASH для равномерного распределения без естественного ключа
- **Архивирование:** `DETACH PARTITION CONCURRENTLY` + `DROP TABLE` = мгновенное удаление огромных объёмов данных без блокировок и WAL-перегрузки
Связанные темы
Партиционирование работает в связке с другими механизмами PostgreSQL:
- Индексы PostgreSQL — Индексы, созданные на родительской таблице, автоматически применяются ко всем партициям; понимание B-tree нужно для правильного выбора ключа партиционирования
- Расширения: pg_cron и TimescaleDB — pg_cron автоматизирует создание новых партиций; TimescaleDB превращает партиционирование временных рядов в fully managed процесс
- VACUUM и bloat — UPDATE ключа партиционирования создаёт bloat в двух партициях одновременно; понимание VACUUM помогает управлять этим эффектом
Вопросы для размышления
- Таблица `orders` партиционирована по `RANGE (created_at)` помесячно. Запросы часто фильтруют и по `user_id`. Как ещё ускорить такие запросы помимо партиционирования?
- Почему `DROP TABLE events_2022_q1` после DETACH быстрее, чем `DELETE FROM events WHERE created_at < '2023-01-01'`?
- Компания решила добавить партиционирование к существующей таблице на 1 ТБ без downtime. Как это сделать?