Когда помогает партиционирование, пример в PostgreSQL

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

Что такое партиционирование, какие виды бывают и доп.материалы

Секционирование таблиц
CREATE TABLE
Частичные индексы
CLUSTER

Искренняя благодарность Postgres Professional за великолепный перевод документации.

Исходная задача

Задача достаточно типовая. В системе документооборота есть таблица документов, таблица постоянно растёт, работа замедляется. При этом необходимо минимизировать объем переделок в базе и приложениях.

Партиционирование может повысить эффективность работы с данными, если ключ (набор полей) для партиционирования часто используется в запросах для фильтрации. Партиционирование позволит работать с отдельными более компактными партициями/секциями и относящимися к ним индексами, а не со всем объемом.

Небольшое погружение в предметную область и мы узнаем следующие особенности:

• записи используются неравномерно
• востребованность записей зависит от даты документа (отлично, значит дата документа это кандидат на ключ секционирования)
• нумерация документов сквозная и уникальная за всё время (а это уже не здорово, ниже рассмотрим почему)

Записи используются неравномерно

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

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

Востребованность записей зависит от…

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

Необходимо учитывать, что партиционирование изменяет структуру физического хранения данных — создаются секции/партиции, в которых хранятся данные в соответствии с одной выбранной группировкой.

В некоторых случаях возможно обойтись без партиционирования, применив частичные индексы. Мощный инструмент, к сожалению поддерживается не всеми СУБД. Синергия партиционирования и частичных индексов может творить чудеса. Например, выбрав партиционирование по дате документа, частичные индексы по часто используемому статусу документа «в работе» хорошо ускоряют соответствующие выборки. Размер индекса зависит не от общего количества записей за всё время, а только от записей в определенном статусе, количество таких документов достаточно стабильно и не велико.

Есть ещё возможность изменить физический порядок строк в таблице, кластеризованный индекс. Этот функционал не поддерживается в PostgreSQL, команда CLUSTER выполняет переупорядочивание строк таблицы, но не поддерживает его после своего выполнения.

Нумерация документов сквозная и уникальная за всё время

Перед окончательным выбором новой структуры хранения данных проверяем учтены ли все бизнес-требования, ограничения СУБД, алгоритмы обработки (запросы к данным, что необходимо ускорить, что не ухудшить).

Если бы в задаче не было жесткого требования «нумерация документов сквозная и уникальная за всё время» + «контроль возложить на СУБД», типовое партиционирование по дате документа могло быть оптимальным решением. Декларативное секционирование, которые мы применим для решения задачи, имеет ряд ограничений: ограничение уникальности (уникальный индекс), должно включать все колонки, которые участвуют в секционировании. Для нас это означает, что индекс будет содержать и номер и дату документа, и это не соответствует требованию глобальной уникальности номера документа.

Вариант решения

• «разрезать» таблицу вертикально, отделив номер документа и некоторые часто используемые служебные поля от «тяжелых» и реже используемых полей
• определим связку между этими группами полей (у нас это будут суррогатный id документа и дата документа), эти поля будут присутствовать в обеих группах
• группу полей с номером документа вынесем в одну таблицу
• у первой таблицы id первичный ключ, именно на неё будут ссылаться остальные таблицы базы, также как ссылались на id исходной таблицы до изменения структуры
• «тяжелые» поля вынесем в другую таблицу и партиционируем её по дате документа
• эта таблица будет ссылаться на первую

SQL скрипты и наборы полей в таблицах сокращены.

версия сервера PostgreSQL 13.6

select version();
-- PostgreSQL 13.6 (Ubuntu 13.6-1.pgdg18.04+1)

Исходная таблица

Исходная таблица с документами.

create table docs_initial (
    doc_id integer not null,
    doc_date date not null,
    doc_number varchar(15) not null,
    doc_text text not null,
    constraint docs_initial_pk primary key (doc_id)
);

create unique index idx_doc_number_initial on docs_initial using btree (doc_number);

create index idx_doc_date_initial on docs_initial using btree (doc_date);

Таблицы после партиционирования

Отметим, что интервалы секционирования не пересекаются, дополнительное требование уникальности id в каждой партиции/секции обеспечивает уникальность пар id + дата документа в партиции/секции.

-- таблица с уникальными и часто используемыми полями
create table docs_partition_unique (
    doc_id integer not null,
    doc_date date not null,
    doc_number varchar(15) not null,
    constraint docs_partition_unique_pk primary key (doc_id) -- для FK из других таблиц базы
);

-- глобальный уникальный индекс по номеру документа
create unique index idx_doc_number_partition_unique on docs_partition_unique using btree (doc_number);

-- индекс по дате документа
create index idx_doc_date_partition_unique on docs_partition_unique using btree (doc_date);

-- уникальный индекс по id и дате документа для FK из партиционированной таблицы
create unique index idx_doc_id_and_date_partition_unique on docs_partition_unique using btree (doc_id, doc_date);

-- партиционированная по дате документа таблица
create table docs_partition (
    doc_partition_id integer not null,
    doc_partition_date date not null,
    doc_partition_text text not null,
    constraint docs_partition_fk foreign key (doc_partition_id, doc_partition_date) references docs_partition_unique(doc_id, doc_date)
) partition by range (doc_partition_date);

create index idx_doc_date_partition on docs_partition using btree (doc_partition_date);

-- партиции/секции
create table docs_partition_2000_and_earlier partition of docs_partition for values from ('-infinity'::date) to ('2001-01-01'::date);
create table docs_partition_2001 partition of docs_partition for values from ('2001-01-01'::date) to ('2002-01-01'::date);
-- ...
create table docs_partition_2021 partition of docs_partition for values from ('2021-01-01'::date) to ('2022-01-01'::date);
create table docs_partition_2022_and_later partition of docs_partition for values from ('2022-01-01'::date) to ('infinity'::date);

-- уникальный индекс по id для каждой секции
create unique index idx_doc_partition_id_2000_and_earlier on docs_partition_2000_and_earlier using btree (doc_partition_id);
create unique index idx_doc_partition_id_2001 on docs_partition_2001 using btree (doc_partition_id);
-- ...
create unique index idx_doc_partition_id_2021 on docs_partition_2021 using btree (doc_partition_id);
create unique index idx_doc_partition_id_2022_and_later on docs_partition_2022_and_later using btree (doc_partition_id);

Заполнение данными

Варианты реализации функции random_string(length integer) returns text легко найти, используется для заполнения данными.

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

-- 50 млн записей в исходную талицу
insert into docs_initial(doc_id, doc_date, doc_number, doc_text)
select 
  	gs.id,
  	make_date(2000 + (random()*22)::integer, 1 + (random()*11)::integer, 1 + (random()*27)::integer),
  	random_string(2) || '-' || gs.id::text, 
   	random_string((1 + random()*300)::integer)	
from generate_series(1, 50000000) as gs(id);

-- эти же 50 млн записей распределяем по новым таблицам
insert into docs_partition_unique (doc_id, doc_date, doc_number)
select doc_id, doc_date, doc_number from docs_initial;

insert into docs_partition (doc_partition_id, doc_partition_date, doc_partition_text)
select doc_id, doc_date, doc_text from docs_initial;

Оценим размер получившихся объектов

select 
    relname  as object_name,
    pg_size_pretty(pg_total_relation_size(pg_class.oid)) as total_relation_size, 
    pg_size_pretty(pg_relation_size(pg_class.oid)) as relation_size 
from pg_class
    left join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where 
    nspname not in ('pg_catalog', 'information_schema')
    and pg_total_relation_size(pg_class.oid) >= 50*1024
order by 
    pg_total_relation_size(pg_class.oid) desc 

docs_initial	14 GB	9871 MB
docs_partition_unique	8929 MB	2488 MB
idx_doc_number_initial	2562 MB	2562 MB
idx_doc_number_partition_unique	2561 MB	2561 MB
idx_doc_id_and_date_partition_unique	1775 MB	1775 MB
docs_partition_unique_pk	1775 MB	1775 MB
docs_initial_pk	1071 MB	1071 MB
docs_partition_2007	487 MB	423 MB
docs_partition_2002	487 MB	423 MB
docs_partition_2005	486 MB	423 MB
...
docs_partition_2003	485 MB	422 MB
idx_doc_date_partition_unique	329 MB	329 MB
idx_doc_date_initial	329 MB	329 MB
docs_partition_2022_and_later	244 MB	211 MB
docs_partition_2000_and_earlier	243 MB	211 MB
idx_doc_partition_id_2007	49 MB	49 MB
idx_doc_partition_id_2002	49 MB	49 MB
...
idx_doc_partition_id_2003	49 MB	49 MB
idx_doc_partition_id_2022_and_later	24 MB	24 MB
idx_doc_partition_id_2000_and_earlier	24 MB	24 MB
docs_partition_2007_doc_partition_date_idx	15 MB	15 MB
docs_partition_2013_doc_partition_date_idx	15 MB	15 MB

Обязательный этап — проверка запросов

После изменения структуры таблиц необходимо проверить, достигнуты ли целевые показатели, не стало ли хуже. Для этого проверяем частые запросы и их скорректированные версии, учитывающие новую структуру. Результаты запросов должны совпадать. Собрать запросы можно с помощью pg_stat_statements или другими средствами.

Запрос для проверки (медленный)

Запрос за длительный период (год) с фильтрацией по префиксу номера документа и тексту.

explain select * from docs_initial 
where 
    doc_date between '2015-09-01'::date and '2016-08-31'::date
    and substring(doc_number, 1, 1) in ('W')
    and length(doc_text) = 123;

Gather  (cost=25454.45..1239844.63 rows=56 width=172)
  Workers Planned: 2
  ->  Parallel Bitmap Heap Scan on docs_initial  (cost=24454.45..1238839.03 rows=23 width=172)
        Recheck Cond: ((doc_date >= '2015-09-01'::date) AND (doc_date <= '2016-08-31'::date))
        Filter: (("substring"((doc_number)::text, 1, 1) = 'W'::text) AND (length(doc_text) = 123))
        ->  Bitmap Index Scan on idx_doc_date_initial  (cost=0.00..24454.44 rows=2246425 width=0)
              Index Cond: ((doc_date >= '2015-09-01'::date) AND (doc_date <= '2016-08-31'::date))

explain select docs_partition.*, docs_partition_unique.doc_number   
from docs_partition_unique inner join docs_partition on doc_partition_id = doc_id and doc_partition_date =  doc_date
where 
    doc_partition_date between '2015-09-01'::date and '2016-08-31'::date
    and substring(doc_number, 1, 1) in ('W')
    and length(doc_partition_text) = 123;

Gather  (cost=1000.57..154504.99 rows=1 width=172)
  Workers Planned: 2
  ->  Nested Loop  (cost=0.56..153504.89 rows=1 width=172)
        ->  Parallel Append  (cost=0.00..140993.93 rows=4728 width=161)
              ->  Parallel Bitmap Heap Scan on docs_partition_2015 docs_partition_1  (cost=7870.43..67982.27 rows=1506 width=160)
                    Recheck Cond: ((doc_partition_date >= '2015-09-01'::date) AND (doc_partition_date <= '2016-08-31'::date))
                    Filter: (length(doc_partition_text) = 123)
                    ->  Bitmap Index Scan on docs_partition_2015_doc_partition_date_idx  (cost=0.00..7869.53 rows=722860 width=0)
                          Index Cond: ((doc_partition_date >= '2015-09-01'::date) AND (doc_partition_date <= '2016-08-31'::date))
              ->  Parallel Seq Scan on docs_partition_2016 docs_partition_2  (cost=0.00..72988.02 rows=3222 width=161)
                    Filter: ((doc_partition_date >= '2015-09-01'::date) AND (doc_partition_date <= '2016-08-31'::date) AND (length(doc_partition_text) = 123))
        ->  Index Scan using idx_doc_id_and_date_partition_unique on docs_partition_unique  (cost=0.56..2.65 rows=1 width=19)
              Index Cond: ((doc_id = docs_partition.doc_partition_id) AND (doc_date = docs_partition.doc_partition_date))
              Filter: ("substring"((doc_number)::text, 1, 1) = 'W'::text)

Точное значение времени выполнения зависит от железа и настроек конкретного сервера. Время выполнения на стенде первого запроса около 110 секунд, второго — около 1 секунды (20 вызовов, отбрасываем минимальное и максимальное значения, усредняем).

Если к docs_initial применить команду CLUSTER, то время выполнения первого и второго запросов будет очень близким, около 1 секунды.

Запрос для проверки (частый, простой)

Запрос документов за последние N дней.

explain select * from docs_initial 
where 
	doc_date between now() - '50 days'::interval and now();

Index Scan using idx_doc_date_initial on docs_initial  (cost=0.57..196810.83 rows=197739 width=172)
  Index Cond: ((doc_date >= (now() - '50 days'::interval)) AND (doc_date <= now()))

select docs_partition.*, docs_partition_unique.doc_number  
from docs_partition_unique inner join docs_partition on doc_partition_id = doc_id and doc_partition_date =  doc_date
where 
	doc_partition_date between now() - '50 days'::interval and now()

Gather  (cost=1001.00..199767.29 rows=24 width=173)
  Workers Planned: 2
  ->  Nested Loop  (cost=1.00..198764.89 rows=10 width=173)
        ->  Parallel Append  (cost=0.44..31425.76 rows=75342 width=162)
              Subplans Removed: 22
              ->  Parallel Bitmap Heap Scan on docs_partition_2022_and_later docs_partition_1  (cost=2018.17..30992.88 rows=75320 width=162)
                    Recheck Cond: ((doc_partition_date >= (now() - '50 days'::interval)) AND (doc_partition_date <= now()))
                    ->  Bitmap Index Scan on docs_partition_2022_and_later_doc_partition_date_idx  (cost=0.00..1972.98 rows=180769 width=0)
                          Index Cond: ((doc_partition_date >= (now() - '50 days'::interval)) AND (doc_partition_date <= now()))
        ->  Index Scan using idx_doc_id_and_date_partition_unique on docs_partition_unique  (cost=0.56..2.22 rows=1 width=19)
              Index Cond: ((doc_id = docs_partition.doc_partition_id) AND (doc_date = docs_partition.doc_partition_date))

К docs_initial предварительно применена команда CLUSTER. Время выполнения на стенде первого запроса около 12 миллисекунд, второго — около 11 миллисекунд (20 вызовов, отбрасываем минимальное и максимальное значения, усредняем).

Вместо заключения

Оценив результаты можно сделать вывод, что в данной ситуации внедрение партиционирования не принесет явной выгоды, при этом потребуется скорректировать программное обеспечение. Периодическое выполнение команды CLUSTER, во время технологического окна, позволяет добиться необходимых показателей без изменений структуры базы данных.

Жесткие требования «нумерация документов сквозная и уникальная за всё время» + «контроль возложить на СУБД» сильно повлияли на результат.

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

Также хочу пригласить всех желающих на бесплатный вебинар от моих коллег из OTUS по теме: «Резервное копирование и восстановление». Зарегистрироваться на вебинар и узнать о нем подробнее можно по данной ссылке.