Эффективное хранение документов в Postgres: партиции, индексы, архив

Предположим, что корпоративная СЭД обрабатывает порядка ~1 000 000 документов в сутки (≈365 млн строк в год). Хранение всех записей в одной таблице приводит к деградации производительности:

• Индексы неконтролируемо растут;
• Типовые запросы вынужденно сканируют гигабайты данных;
• autovacuum не успевает поддерживать таблицу в рабочем состоянии.

⚠️ autovacuum — это встроенный процесс PostgreSQL, который чистит строки после UPDATE/DELETE и поддерживает статистику. При массовых DELETE нагрузка на него резко возрастает. При работе с партициями этой проблемы не возникает, т.к. старые данные удаляются целиком.

Целевые требования

• Стабильная скорость вставки новых документов. Поток данных идет непрерывно, и вставки должны оставаться одинаково быстрыми при росте объема. Если скорость деградирует, переполняются очереди интеграции, начинают отваливаться сервисы, и система становится нестабильной.
• Низкая латентность оперативных выборок по свежим данным. Пользователь ждет быстрых откликов при поиске и просмотре недавних документов. Для него критично, чтобы запросы по последним 30–90 дням выполнялись в миллисекундах, а не в секундах.
• Изоляция и экономичное хранение исторических данных. Документы старше года редко используются, но при этом они составляют основной объем. Хранить их в том же виде, что и новые данные, — дорого и неэффективно. Нужно изолировать их в отдельные партиции или даже в архивное хранилище, чтобы они не мешали рабочей нагрузке.

Базовый подход

Для выполнения этих требований используется комбинация трех проверенных техник, которые в сумме дают прогнозируемую производительность даже при больших потоках:

• Временное партиционирование. Основная таблица разбивается на диапазоны по дате создания (created_at). Каждая партиция покрывает небольшой интервал (обычно сутки при высоком трафике). Это позволяет планировщику PostgreSQL быстро отсекать лишние партиции при запросах и не сканировать десятки миллионов строк. Кроме того, операции VACUUM и ANALYZE становятся локальными для каждой партиции, что резко снижает нагрузку на систему.

• Архивация партиций по политике ретеншна. Устаревшие партиции (например, старше 12 месяцев) не удаляются построчно, а целиком отсоединяются (DETACH) или удаляются (DROP). Такой подход позволяет мгновенно освободить сотни гигабайт данных без долгих транзакций и блокировок. При необходимости они могут быть перенесены в отдельный архивный кластер или сохранены в файловом формате (например, Apache Parquet).

• Разделение метаданных и полезной нагрузки. В PostgreSQL целесообразно хранить только “шапку документа”: идентификаторы, даты, статусы и ключевые поля для поиска. Тяжелый payload (большие XML/JSON, бинарные вложения, файлы) лучше вынести в объектное хранилище (S3, MinIO). В БД остается лишь ссылка и контрольная сумма. Это уменьшает размер партиций и ускоряет индексацию.

⚠️ Ретеншн (retention policy) — это просто “политика хранения данных”, то есть правила, сколько времени и в каком виде данные хранятся в основной БД. Например, хранить документы 90 дней в “горячих” партициях, до 12 месяцев в “теплых” партициях, все старше года переносить в архив или удалять.

Модель hot/warm/cold

В крупных системах документооборота все данные делятся на три слоя хранения. Такой подход позволяет задать разные режимы обслуживания и разные требования к ресурсам:

• Hot (горячие данные, последние 30–90 дней). Основная зона активной работы пользователей. Данные находятся в партициях на быстрых дисках (SSD/NVMe). Используются полноценные B-Tree индексы (часто частичные — WHERE status = 'ACTIVE'), чтобы обеспечивать миллисекундные отклики. На этом уровне работают все критичные запросы: выборки, фильтрация по статусу, сортировка по дате.
• Warm (теплые данные, 3–12 месяцев). Данные обращаются реже: отчеты, аудит, выборки по истории. Могут храниться в базе, но с минимальным набором индексов. Для больших объемов лучше использовать компактные BRIN-индексы, которые не растут пропорционально числу строк, а работают за счет диапазонов. Хранение можно вынести на более дешевое хранилище (например, HDD).
• Cold (холодные данные, старше года). Практически не используются в ежедневной работе. Архивируются через DETACH партиций и переносятся в отдельный PostgreSQL-кластер или в объектное хранилище (S3/MinIO) в формате Parquet/CSV. В основной базе остаются только ключевые метаданные и ссылки на архив, чем достигается экономия места и снижение нагрузки на продакшн-кластер.

Партиционирование по времени

В PostgreSQL есть встроенный механизм RANGE partitioning, который позволяет разрезать таблицу на части по диапазону значений. В случае документооборота естественным ключом для партиционирования является поле created_at.

Такой подход гарантирует, что каждая партиция содержит данные за определенный интервал времени (чаще всего за сутки). Это делает таблицу управляемой по размеру и облегчает обслуживание.

Пример создания базовой структуры:

Пример:

create table document (
  id bigserial primary key,
  created_at timestamptz not null,
  status text not null,
  account_id bigint not null,
  payload_ref text,       -- ссылка на объект в S3
  payload_hash bytea
) partition by range (created_at);

-- Партиция на день
create table document_2025_08_21
  partition of document
  for values from ('2025-08-21') to ('2025-08-22');

Когда данные устаревают, работать с ними построчно нецелесообразно. Массовые DELETE приводят к росту таблицы и индексов, а также увеличивают нагрузку на обслуживание. Вместо этого операции выполняются на уровне целых партиций:

-- Отсоединение партиции (операция выполняется быстро, без долгих блокировок)
alter table document detach partition document_2024_08_21;

-- Полное удаление партиции
drop table document_2024_08_21;

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

Архивация

При достижении срока хранения старые данные нужно либо перенести в архив, либо удалить. Делать это следует на уровне целых партиций, а не отдельных строк. Такой подход позволяет управлять объемами данных без лишних накладных расходов.

Перенос в отдельную схему

Если требуется сохранить доступ к данным (например, для отчетности или аудита), устаревшую партицию можно «отцепить» от основной таблицы и прикрепить к таблице в схеме archive:

-- Отсоединяем партицию от основной таблицы
alter table document detach partition document_2024_08_21;

-- Прикрепляем ее к архивной структуре
alter table archive.document attach partition document_2024_08_21
  for values from ('2024-08-21') to ('2024-08-22');

Такой перенос выполняется быстро, так как PostgreSQL работает с метаданными, а не физически копирует данные.

Удаление после истечения срока хранения

Если данные больше не нужны, партицию можно удалить полностью:

drop table document_2024_08_21;

В результате освобождается место на диске, а производственная база продолжает работать только с актуальными данными.

Разделение метаданных и payload

Документ в системе обычно состоит из двух частей:

• Метаданные — идентификатор, дата создания, статус, бизнес-ключи, ссылки на связанный объект;
• Payload — собственно содержимое документа: XML, JSON, вложенные файлы.

Для СУБД эти части ведут себя по-разному. Метаданные постоянно участвуют в фильтрации и выборках (WHERE status = 'ACTIVE', WHERE created_at > ...), поэтому они должны храниться в PostgreSQL в “плоском” виде, доступном для индексов. Payload же почти никогда не фильтруется в запросах, и его размер может быть в десятки или сотни килобайт.

Если payload хранить в PostgreSQL напрямую (например, в bytea или jsonb), включается механизм TOAST (внутреннее хранение больших значений вне основной страницы). Это резко увеличивает I/O, усложняет вакуумирование и раздувает бэкапы.

Поэтому практическая схема выглядит так:

• В PostgreSQL хранится только шапка документа (метаданные) и ссылка на содержимое (например, payload_ref), плюс контрольная сумма (payload_hash) для проверки целостности;
• В объектном хранилище (S3, MinIO) хранится payload — большие XML/JSON и бинарные файлы.

Преимущества:

• Нагрузка на Postgres снижается, так как в запросах участвуют только легкие поля;
• Индексы остаются компактными;
• Резервное копирование базы ускоряется, так как бэкапятся только метаданные, а не сотни гигабайт документов;
• payload можно масштабировать независимо — вынести в дешевое облачное хранилище, подключить CDN или отдельный кластер для файлов.

Индексация

Индексы — ключевой инструмент для ускорения выборок в PostgreSQL. Однако при работе с большими объемами данных важно правильно выбирать их тип и область применения.

B-Tree индексы

B-Tree — это стандартный тип индекса в PostgreSQL. Он оптимален для точных сравнений (=) и диапазонных запросов (>, <, BETWEEN). На практике такие индексы применяются для “горячих” партиций, где выполняется большинство оперативных выборок.

Пример:

create index on document_2025_08_21 (account_id, created_at desc)
where status = 'ACTIVE';

В данном случае используется partial index — он строится только для подмножества строк, удовлетворяющих условию (status = 'ACTIVE'). Такой прием позволяет уменьшить размер индекса и ускорить выборки по часто используемому сценарию: “все активные документы за последние дни”.

BRIN индексы

BRIN (Block Range Index) — компактный индекс, который хранит статистику по диапазонам данных в блоках таблицы. Его размер не зависит линейно от числа строк и остается очень маленьким даже для сотен миллионов записей.

Пример:

create index document_2025_01_created_brin
  on document_2025_01 using brin (created_at);

BRIN не подходит для поиска конкретного id, но эффективно ускоряет запросы, работающие с диапазонами времени, например: “все документы за январь”. Для исторических данных, где нагрузка в основном состоит из отчетных выборок по дате, это оптимальное решение.

Практическая схема

• На горячих (hot) партициях: полнофункциональные B-Tree (часто частичные) для полей status, account_id, created_at.
• На теплых (warm): минимум индексов, возможен переход на BRIN.
• На холодных (cold): либо только BRIN, либо индексы отсутствуют вовсе (если данные перенесены в архивное хранилище).

Запросы

Чтобы партиционирование и индексация реально работали, запросы к таблице должны быть правильно сформулированы.

Основной принцип

Планировщик PostgreSQL может “отсечь” ненужные партиции только в том случае, если в WHERE присутствует фильтр по полю, которое используется для партиционирования (например, created_at).

Пример корректного запроса:

select id, created_at
from document
where created_at >= now() - interval '30 days'
  and status = 'ACTIVE'
  and account_id = :acc
order by created_at desc
limit 100;

В этом запросе сразу заданы:

• Диапазон по дате (created_at >= ...) — отсекаются партиции за ненужные месяцы;
• Статус (status = 'ACTIVE') — задействуется partial index;
• Бизнес-ключ (account_id = :acc) — используется составной индекс.

Ошибки, которых нужно избегать

• 1) Функции на поле партиционирования.

  where date(created_at) = current_date
  

  Такой фильтр приведет к полному сканированию всех партиций, потому что оптимизатор не сможет сопоставить выражение с границами секций.

• 2) Фильтры, не совпадающие с индексами. Если построен индекс (account_id, created_at desc) where status = 'ACTIVE', а запрос использует status in ('ACTIVE', 'ARCHIVED'), индекс не будет использован полностью.

• 3) Отсутствие ограничения по дате. Запрос без фильтра по created_at заставит планировщик обходить все партиции, что эквивалентно сканированию одной большой таблицы.

Рекомендации

• Всегда проверять планы выполнения (explain analyze);
• Поддерживать соответствие фильтров в запросах и условий индексов;
• Для API-запросов внедрять обязательные параметры фильтрации по времени.

Готовые сниппеты

Создание дневных партиций на месяц вперед

Создает daily-партиции для родительской таблицы с RANGE-секционированием по created_at:

create or replace function create_daily_partitions(
  parent_table text,        -- имя родителя, например 'document'
  start_date   date,        -- с какой даты создавать
  days         int          -- сколько дней вперед
) returns void
language plpgsql
as $$
declare
  d date;
  sql text;
  part_name text;
begin
  for i in 0..days-1 loop
    d := start_date + i;
    part_name := format('%I_%s', parent_table, to_char(d, 'YYYY_MM_DD'));
    sql := format(
      'create table if not exists %s partition of %I
         for values from (%L) to (%L);',
      part_name, parent_table, d::timestamptz, (d + 1)::timestamptz
    );
    execute sql;
  end loop;
end
$$;

-- пример использования:
select create_daily_partitions('document', date '2025-09-01', 31);

Функция предполагает, что document уже создан как partition by range (created_at).

Удаление партиций старше N дней (ретеншн)

Безопасно парсит дату из имени партиции формата document_YYYY_MM_DD и дропает старше порога. Без CASCADE по умолчанию:

create or replace function drop_old_partitions(
  table_prefix text,   -- префикс родителя, например 'document'
  keep_days    int,    -- сколько дней держать
  use_cascade  boolean default false -- использовать ли CASCADE (по умолчанию нет)
) returns void
language plpgsql
as $$
declare
  r record;
  cutoff date := (now() - (keep_days || ' days')::interval)::date;
  part_date date;
  drop_sql text;
begin
  for r in
    select schemaname, tablename
    from pg_tables
    where tablename like table_prefix || '\_%' escape '\'
  loop
    begin
      part_date := to_date(
        substring(r.tablename from '.+_(\d{4}_\d{2}_\d{2})$'),
        'YYYY_MM_DD'
      );

      if part_date is not null and part_date < cutoff then
        drop_sql := format('drop table if exists %I.%I %s;',
          r.schemaname, r.tablename,
          case when use_cascade then 'cascade' else '' end
        );
        execute drop_sql;
      end if;

    exception when others then
      raise notice 'skip %.% due to: %', r.schemaname, r.tablename, sqlerrm;
    end;
  end loop;
end
$$;

-- пример: держим 120 дней, без CASCADE
select drop_old_partitions('document', 120, false);

Рукомендуется держать имена партиций в едином формате document_YYYY_MM_DD. Если есть внешние ключи на партиции — внимательно отнестись к CASCADE.

Перенос партиции в архивную схему

Отсоединяет партицию от «боевого» родителя и прикрепляет к “родителю-архиву” в схеме archive:

-- предпосылка: в схеме archive есть родитель с тем же DDL:
--   create table archive.document (...) partition by range (created_at);

do $$
declare
  part text := 'document_2024_08_21';
begin
  execute format('alter table %I detach partition %I;', 'document', part);
  execute format(
    'alter table %I.%I attach partition %I for values from (%L) to (%L);',
    'archive', 'document', part, timestamp '2024-08-21', timestamp '2024-08-22'
  );
end $$;

Если архив хранится в другом кластере — вместо attach используйте логическую репликацию/дамп или оставьте партицию как отдельную таблицу archive.document_YYYY_MM_DD.

Индексы для hot-партиций (partial B-Tree)

Создает частичный индекс “под запросы”: активные документы конкретной партиции:

create or replace function ensure_hot_indexes(part_table text)
returns void language plpgsql as $$
begin
  execute format(
    'create index if not exists %I_acc_created_active_idx
       on %I (account_id, created_at desc)
     where status = ''ACTIVE'';',
    part_table || '_', part_table
  );

  execute format(
    'create index if not exists %I_created_desc_idx
       on %I (created_at desc);',
    part_table || '_', part_table
  );
end $$;

-- пример:
select ensure_hot_indexes('document_2025_08_21');

BRIN для исторических партиций

Компактный индекс по времени для больших “теплых”/”холодных” таблиц:

create or replace function ensure_brin_created(part_table text)
returns void language plpgsql as $$
begin
  execute format(
    'create index if not exists %I_created_brin
       on %I using brin (created_at) with (pages_per_range=128);',
    part_table || '_', part_table
  );
end $$;

-- пример:
select ensure_brin_created('document_2025_01');

pages_per_range подберите под свой профиль данных и размер блоков. Чем больше параметр — тем меньше индекс и тем грубее диапазоны.

Пример корректного запроса с отсечением партиций

Фильтр по полю партиционирования обязателен; поля в WHERE должны совпадать с индексами:

explain analyze
select id, created_at
from document
where created_at >= now() - interval '30 days'
  and status = 'ACTIVE'
  and account_id = :acc
order by created_at desc
limit 100;

Следет избегать date(created_at) = ... — такие функции на колонке мешают отсечению партиций и использованию индексов.

Заготовка CRON-job (pg_cron) для обслуживания

Плановое создание будущих партиций и удаление старых:

-- пример для расширения pg_cron
-- создать партиции на 30 дней вперед каждую ночь
select cron.schedule('0 2 * * *',
  $$select create_daily_partitions('document', (current_date + 1), 30);$$
);

-- удалять партиции старше 120 дней каждую ночь
select cron.schedule('30 2 * * *',
  $$select drop_old_partitions('document', 120, false);$$
);

Вместо pg_cron можно использовать внешние планировщики (systemd timer, Jenkins, Airflow). Главное — запускать операции вне пиков.

Мини-FAQ

• Почему нельзя просто делать DELETE старого? Потому что PostgreSQL при DELETE только помечает строки как удаленные, а сами страницы таблицы остаются занятыми до очистки VACUUM. В итоге растет объем таблицы и индексов, а производительность падает. Удаление целой партиции (DROP PARTITION) выполняется мгновенно и не оставляет «хвостов».
• Зачем отделять payload? Большие XML/JSON перегружают дисковый ввод-вывод и механизм TOAST. Храните payload во внешнем объектном хранилище (MinIO, S3), а в базе — только ссылку и хеш. Так метаданные остаются компактными, а запросы быстрыми.
• А если нужен поиск по payload? Извлекайте необходимые атрибуты из payload при загрузке и сохраняйте их как отдельные колонки или индексы в метаданных. Сам payload пусть остаеся за пределами БД.
• Week или Day партиции? При потоке порядка 1M документов в день лучше использовать дневные партиции. Если поток ниже (например, 50–100k/день), можно ограничиться недельными.

Итог

Для нагрузки в миллион документов в день рабочая схема выглядит следующим образом:

• Партиционирование по времени (обычно дневные партиции);
• Хранение payload во внешнем объектном хранилище, в БД — только метаданные и ссылки;
• B-Tree и partial индексы для горячих данных, BRIN — для исторических;
• Удаление и архивирование через DETACH / DROP PARTITION;
• Контроль вставки через батчи и настройку autovacuum на уровне партиций.

Такой подход позволяет системе стабильно работать при больших объемах вставки и не терять производительность при росте данных.