Архивация в PostgreSQL: как измерить эффект до и после

После переноса старых записей запрос к таблице в PostgreSQL может ускориться, не измениться или даже стать медленнее — зависит от того, как устроены запросы и что именно вы называете архивацией. Ниже — разбор для PostgreSQL 16: как выбрать между удалением строк, секционированием и отдельным архивным хранилищем, а главное — как измерить результат без выдуманных процентов и случайного эффекта от прогретого кеша.

Архивация — не одна операция

В PostgreSQL «архивировать» можно по-разному. Удалить старые строки из рабочей таблицы — не то же самое, что отсоединить секцию или перенести данные в другую базу. Меняются объём рабочей таблицы, доступность истории, нагрузка на запись и стоимость обслуживания.

Ключевое различие: удаление строк не означает, что файл таблицы сразу уменьшится. PostgreSQL помечает записи как удалённые, а место освобождает для повторного использования после очистки. Чтобы вернуть место операционной системе, обычно нужна отдельная операция переписывания таблицы; она может требовать дополнительного дискового пространства и влиять на доступность. Поэтому сравнивайте не только размер файла, но и время запросов и цену обслуживания.

Если исторические данные всё ещё лежат в той же таблице и запросы продолжают их просматривать, переименование процесса в «архивацию» не уменьшит объём работы базы.

Три подхода и их цена

Секционирование в PostgreSQL 16 полезно, когда таблица разделена по диапазонам, например по месяцу, и запросы ограничивают выборку тем же столбцом даты. Планировщик может исключить секции, которые заведомо не подходят. Если запрос выбирает записи без ограничения по дате, секционирование само по себе не избавит его от чтения старой истории.

Отсоединённая секция при этом не исчезает с диска: она остаётся таблицей в той же базе. Это может упростить обслуживание или последующий перенос, но не равнозначно освобождению места. Проверьте, какую задачу решаете: ускорить запрос, сократить размер рабочей таблицы или освободить диск.

Что считать до и после

Универсальной цифры вроде «архивация ускоряет PostgreSQL вдвое» нет. Результат зависит от схемы, индексов, объёма данных, запросов, оборудования и состояния кеша. Зафиксируйте условия теста, иначе сравнение покажет не эффект архивации, а разницу между двумя запусками.

• Время запроса. Сравнивайте одинаковые запросы и параметры. Для пользовательских сценариев смотрите распределение времени, включая p95, а не только среднее.
• План выполнения и чтение блоков. Для выбранного запроса изучите план через EXPLAIN с ANALYZE и BUFFERS: фактическое время, число строк, попадания в кеш и чтения с диска. ANALYZE выполняет запрос, поэтому осторожно применяйте его к операциям записи.
• Размер данных. Отдельно фиксируйте общий размер таблицы с индексами и размер её данных. После удаления строк файл может не уменьшиться, даже если свободные для повторного использования страницы появились.
• Цена переноса. Измеряйте длительность архивации, объём созданного WAL и задержку реплик, если они есть. Большая пакетная операция может ухудшить работу системы, даже если итоговый запрос стал быстрее.

Снимите показатели до изменений на типичном наборе запросов и сохраните планы. После переноса повторите те же запросы с теми же параметрами, объёмом данных и сопоставимым состоянием кеша. Если тестируете на копии, укажите её характеристики: объём и распределение данных, версию PostgreSQL, тип диска и число параллельных запросов.

Как читать результат

Если время упало, а в плане заметно сократилось число прочитанных блоков, это сильный признак, что запрос перестал обрабатывать лишнюю историю. Если улучшился только один запрос, а остальные остались прежними, эффект локальный — не приписывайте его всей базе.

Если секционирование включили, но план всё ещё обращается ко многим секциям, проверьте, есть ли в запросе условие по столбцу секционирования и совпадает ли оно с типом данных этого столбца. Запросы, которые намеренно читают всю историю, могут не ускориться. Сравните планы до и после, а не только размер таблицы.

Где решение ломается

Удаление миллионов строк одним большим действием способно надолго занять ресурсы, создать значительный объём WAL и увеличить отставание реплик. Пакетная обработка обычно снижает размер одной операции, но требует продумать границы пакетов, порядок повторного запуска и обработку ошибок. Конкретный темп безопаснее подбирать на копии и затем контролировать в проде.

Секционирование тоже не бесплатное. Нужно заранее определить ключ и диапазоны, поддерживать создание новых секций и учитывать ограничения схемы. Запросы к родительской таблице должны использовать фильтры, которые позволяют исключать ненужные секции. Если приложение обращается напрямую к отдельным секциям или полагается на неявные особенности старой схемы, миграция может изменить поведение.

Наконец, выгрузка истории за пределы основной базы добавляет путь возврата: кто имеет доступ, сколько занимает восстановление, как проверяется целостность и что произойдёт при потере архивного носителя. Редко читаемые данные всё равно могут быть нужны для расследования инцидента или пересчёта отчёта. Архив без проверенного восстановления — не план хранения.

Когда что выбирать

• Выбирайте удаление, если данных немного, история больше не нужна в рабочем контуре, а всплеск нагрузки от очистки допустим.
• Рассматривайте секционирование по времени, если таблица растёт постоянно, большинство запросов ограничено датой и старые диапазоны можно обслуживать целиком.
• Выносите историю в отдельную базу или хранилище, если важно отделить рабочие ресурсы и вы готовы принять дополнительную сложность доступа и восстановления.

Не выбирайте секционирование только ради красивого размера активной таблицы. Сначала проверьте, что конкретный медленный запрос действительно читает лишние исторические данные, а затем оцените цену миграции и хранения.

Что делать дальше

Для PostgreSQL 16 начните с инвентаризации: какие таблицы растут, сколько занимают индексы, какие запросы чаще читают историю и какие из них ограничены датой. Снимите планы и время на репрезентативной нагрузке. Затем испытайте один подход на копии и сравните не один показатель, а весь набор: задержки запросов, чтение блоков, размер, длительность переноса и влияние на реплики.

Если выбираете секционирование, отдельно изучите официальную документацию PostgreSQL 16 по секционированным таблицам и исключению секций. Если проблема связана с удалением строк, проверьте документацию по EXPLAIN и очистке таблиц: они помогут отличить реальное ускорение запросов от временного эффекта и понять, куда ушло место.

Термин: исключение секций

Исключение секций — это пропуск секций, которые не могут содержать строки, подходящие под условие запроса. Например, при секционировании по месяцу запрос за один месяц может не читать таблицы с остальными месяцами. Это работает не для любого запроса: важно, чтобы условие позволяло планировщику определить нужный диапазон.