Секционирование в PostgreSQL. Архитектура корзинного хранения данных. (Basket partitioning)28.03.2025 10:31

Привет, ХАБР! Я Хаймин Владимир, эксперт по системам управления базами данных PostgreSQL в ВТБ. Когда размеры таблиц становятся большими — обслуживание и доступ к данным становятся непростой задачей. Я хочу поделиться методикой организации секционирования в PostgreSQL, которая существенно упростила нам жизнь с таблицами большого размера, хранящими, например, исторические данные по датам. Назовем ее условно «Корзинным секционированием» (Basket partitioning). Данная технология реализована архитектурно, без необходимости расширения функционала сторонними инструментами или расширениями, только штатными средствами ванильных версий PostgreSQL. Такая система хранения реализована, например, в проекте мониторинга pg_awr для упрощения удаления старых данных.

Секционирование в PostgreSQL

Секционирование — это техника разбиения одной большой таблицы базы данных (БД) на несколько меньших, логически связанных частей, называемых секциями. При этом, такая таблица с точки зрения ППО выглядит как одна большая таблица.

Современные версии PostgreSQL имеют достаточно развитые средства для работы с секционированными таблицами. При этом, несмотря на то что страдает автоматизация, например, создания и удаления секций, при правильной организации хранения данных работать с такой БД достаточно удобно. И эти удобства минимизируют вероятные ошибки.

PostgreSQL начиная с 10 версии, помимо наследования, поддерживается декларативное секционирование: диапазонное, списочное, хэш-секционирование.

Какие бывают базы данных по методу их наполнения и использования?

В общем случае БД разделяют на операционные (OLTP) и аналитические (OLAP). В этой статье мы рассмотрим только операционные базы, но, вероятно, то, о чем мы будем говорить, будет применимо также для аналитических, и БД смешанного типа. Речь пойдет о БД, имеющих вообще какие то хронологические данные по множеству таблиц.

OLTP профиль нагрузки в PostgreSQL без секционирования

Условно, мы можем показать это через яблоки и корзины.

Особенности.

При OLTP профиле нагрузке она постоянно наполняется данными. Это наполнение может быть достаточно интенсивным, и доходить до нескольких десятков тысяч вставок (INSERT) в секунду. В эту корзину мы собираем яблоки несколько дней. Это приводит к:

Росту объема таблицы и индексов (объема корзины);
Усложнению операций обслуживания таблицы (VACUUM, ANALYZE);
Деградации скорости доступа к данным. Теперь не вся таблица умещается в памяти и какие-то блоки приходится читать с дисков, то это разница по сравнению с чтением из памяти — может отличаться в десятки и сотни раз.

Предположим, у нас в БД всего одна таблица APPLE.

CREATE TABLE public.apple (

id bigserial NOT NULL, — Номер яблока

weight int8 NULL, — Вес яблока

color char(10), — Цвет

date_inc date NULL — Дата сбора яблока

);

Заполним корзину яблоками, собранными за три дня, так, чтобы вес их отличался случайным образом:

-- *** Позавчера ***

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Yello', (now() — interval '2 days'):: date);

-- … (100 ставок)

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Yello', (now() — interval '2 days'):: date);

-- *** Вчера ***

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Green', (now() — interval '1 days'):: date);

-- … (100 ставок)

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Green', (now() — interval '1 days'):: date);

-- *** Сегодня ***

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Red', now():: date);

-- … (100 ставок)

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Red', now():: date);

Посмотрим, сколько корзина занимает места:

# \dt+ apple

List of relations

--------±------±------±---------±------------±--------------±------±------------

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

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

delete from apple where date_inc <= (now() — interval '2 days'):: date;

БД сообщила, что действительно удалилось 100 записей

Посмотрим размер таблицы:

postgres=# \dt+ apple

List of relations

--------±------±------±---------±------------±--------------±------±------------

Таблица не уменьшилась в размере (?)

Теперь, положим еще 100 яблок за текущий день. И снова посмотрим ее размер:

-- *** Сегодня ***

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Red', now():: date);

-- … (100 ставок)

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Red', now():: date);

postgres=# \dt+ apple

List of relations

--------±------±------±---------±------------±--------------±------±------------

Размер таблицы даже увеличился. Хотя суммарное количество строк все то же 300.

Причина такого поведения — механизм MVCC, который хорошо описан в документации. Коротко можно сказать, для того чтобы на самом деле уменьшить размер таблицы после удаления, нужно фактически пересоздать таблицу, выполнив операцию VACUUM FULL либо в неблокирующем режиме воспользоваться сторонними продуктами вроде pg_repack или compact table. VACUUM FULL происходит в эксклюзивном режиме, то есть блокирует новые вставки и может выполняться значительное время.

Что мы выявили при таком хранении?

Наполнение данных линейно с количеством вставленных строк для чистой таблицы.
Очистка данных через DELETE не приводит к фактическому уменьшению размера таблицы. Если удаляемых строк миллионы, то удаление может занять значительное время.
После удаления старых строк для того чтобы уменьшить ее размер нужно провести в блокирующем режиме VACUUM FULL, что также может выполняться значительное время. Либо для того чтобы таблица не росла, и удаленные строки переиспользовались новыми данными необходим обычный VACUUM в неблокирующем режиме. Но к уменьшению размера таблицы это не приводит, хотя какое-то время таблица не растет.

OLTP в PostgreSQL с секционированием. Раскладываем данные по корзинам

Как сделать так, чтобы и удаление старых данных выполнялось максимально быстро, и чтобы после удаления место очищалось и не требовалось больше никаких действий?

Структура и наполнение.

Будем собирать каждый день яблоки только в ту корзину, которая соответствует определенному дню. Получим такую картинку:

Чтобы удалить данные 2 дня назад — просто убираем корзину целиком:

Выглядит просто!

Техническая реализация

Структура данных

Идея реализации корзин такая, что корзина — это схема, название которой содержит дату (или другой критерий секционирования) для простоты идентифицирования.

# \dn

List of schemas

Name | Owner

---------------------------------±------------------

public | pg_user1

basket_20250203 | pg_user1

basket_20250204 | pg_user1

basket_20250205 | pg_user1

Схемы basket_XXXXXXXX — содержат данные только за этот день. Но основная таблица находится в public.

Создадим схемы корзин данных:

create schema basket_20250203;

create schema basket_20250204;

create schema basket_20250205;

Создаем секционированную таблицу яблок в основной схеме public:

CREATE TABLE public.apple (

id bigserial NOT NULL, — Номер яблока

weight int8 NULL, — Вес яблока

color char(10), — Цвет

date_inc date NULL — Дата сбора яблока

) partition by range (date_inc);

Но сами данные будут находится в корзинах (!)

create table basket_20250203.apple partition of apple for values

from ('2025–02–03 00:00:00+03') to ('2025–02–04 00:00:00+03');

create table basket_20250204.apple partition of apple for values

from ('2025–02–04 00:00:00+03') to ('2025–02–05 00:00:00+03');

create table basket_20250205.apple partition of apple for values

from ('2025–02–05 00:00:00+03') to ('2025–02–06 00:00:00+03');

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

CREATE TABLE public.plum (

id bigserial NOT NULL, — Номер сливы

weight int8 NULL, — Вес сливы

color char(10), — Цвет

date_inc date NULL — Дата сбора сливы

) partition by range (date_inc);

CREATE TABLE public.pear (

id bigserial NOT NULL, — Номер груши

weight int8 NULL, — Вес груши

color char(10), — Цвет

date_inc date NULL — Дата сбора груши

) partition by range (date_inc);

Также создадим секции данных для них в этих же самых таблицах, но с данными в корзинах за дату:

create table basket_20250203.plum partition of plum for values

from ('2025–02–03 00:00:00+03') to ('2025–02–04 00:00:00+03');

create table basket_20250204.plum partition of plum for values

from ('2025–02–04 00:00:00+03') to ('2025–02–05 00:00:00+03');

create table basket_20250205.plum partition of plum for values

from ('2025–02–05 00:00:00+03') to ('2025–02–06 00:00:00+03');

create table basket_20250203.pear partition of pear for values

from ('2025–02–03 00:00:00+03') to ('2025–02–04 00:00:00+03');

create table basket_20250204.pear partition of pear for values

from ('2025–02–04 00:00:00+03') to ('2025–02–05 00:00:00+03');

create table basket_20250205.pear partition of pear for values

from ('2025–02–05 00:00:00+03') to ('2025–02–06 00:00:00+03');

Заполним теперь таблицы public.apple, public.plum, public.pear данными. Да именно так. К самим таблицам мы будем обращаться по-старому, через схему public. Например:

select * from public.apple limit 10;

Заполнение точно такое же, как для несекционированной таблицы:

-- *** Позавчера ***

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Yello', (now() — interval '2 days'):: date);

-- … (100000 ставок)

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Yello', (now() — interval '2 days'):: date);

-- *** Вчера ***

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Green', (now() — interval '1 days'):: date);

-- … (100000 ставок)

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Green', (now() — interval '1 days'):: date);

-- *** Сегодня ***

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Red', now():: date);

-- … (100000 ставок)

insert into apple (weight, color, date_inc) values (100 + random()*50, 'Red', now():: date);

Также и для других фруктов.

После чего посмотрим, как они заполнены, например, таким запросом:

SELECT schema_name,

pg_size_pretty (sum(table_size):: bigint)

FROM (

SELECT pg_catalog.pg_namespace.nspname as schema_name,

pg_relation_size (pg_catalog.pg_class.oid) as table_size

FROM pg_catalog.pg_class

JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid

) t

GROUP BY schema_name

ORDER BY schema_name DESC;

Размер схем:

schema_name | pg_size_pretty

--------------------±---------------

basket_20250205×687 MB

basket_20250204×949 MB

basket_20250203×993 MB

public | 24 kB

Как видим, в основной схеме public данных нет, все находится в наших корзинах.

Очистка старых данных

Теперь самое интересное. Удалим все данные за позавчера:

DROP SCHEMA basket_20250203 CASCADE;

В консоли:

postgres=# DROP SCHEMA basket_20250203 CASCADE;

NOTICE: drop cascades to 3 other objects

DETAIL: drop cascades to table basket_20250203.apple

drop cascades to table basket_20250203.plum

drop cascades to table basket_20250203.pear

DROP SCHEMA

Всё готово! Менее чем за секунду мы удалили почти 1 Гб данных.

Что с данными?

schema_name | pg_size_pretty

--------------------±---------------

basket_20250205×687 MB

basket_20250204×949 MB

public | 24 kB

Данных за позавчера теперь нет. (а в этой корзине, напомню, были данные по трем таблицам. Их может быть десятки и сотни, как в некоторых наших проектах).

Так как это DDL операция — то она происходит очень быстро. И не требует последующих операций обслуживания.

Каскадное удаление схемы выполняет deattach секций таблиц и ее физическое удаление файлов данных конкретной секции, тем самым и высвобождая место.

Выводы

В статье я описал, как организовать Корзинную архитектуру для секционирования (Basket partitioning). Из главных преимуществ такого подхода:

Почти мгновенное удаление старых данных по множеству таблиц с устаревшими данными. Время очистки почти не зависит от их объема.
Нет необходимости в обслуживании таблиц после удаления.

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

Спасибо за внимание!