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



Что такое партиционирование, какие виды бывают и доп.материалы
Секционирование таблиц
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 по теме: «Резервное копирование и восстановление». Зарегистрироваться на вебинар и узнать о нем подробнее можно по данной ссылке.

Комментарии (14)


  1. shalamberidze
    28.03.2022 19:27
    +1

    Пример плохой :) Это сколько же документов должно быть в базе чтобы партиционорование имело смысл :) Изначально партиционирование было придумано дла ОЧЕНЬ больших таблиц

    Не могу судить насчет постгре но в оракле с партиционированием надо быть достаточно осторожным.


    1. ertegix
      28.03.2022 21:02
      +1

      В постгре тоже надо быть очень осторожным.


      1. nvv Автор
        29.03.2022 19:09
        +1

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


  1. vasyakolobok77
    28.03.2022 23:58
    +1

    Если doc_id до рефакторинга являлся первичным ключом, то его одного достаточно для связи вспомогательных таблиц с основной таблицей, и doc_id+doc_date не нужны. Разве нет?


    1. nvv Автор
      29.03.2022 01:22
      +1

      После партиционирования возможна ситуация, когда в разных партициях записи могут содержать одинаковые значения doc_id, поэтому используется пара doc_id+doc_date (это также отражение требования «контроль возложить на СУБД»).


      1. akhkmed
        30.03.2022 15:48

        Вот всё-таки вопрос про модель данных. Есть исходная таблица.

        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 table doc (
            id integer not null,
            date date not null,
            number varchar(15) not null,
            text text not null,
            constraint doc_pk primary key (id)
        ) partition by range (id);
        
        create index idx_doc__number on doc using btree (number);
        
        create index idx_doc__date on doc using btree (date);

        Тогда PK сохранится как есть и будет поддерживать глобальную уникальность.

        Второй уникальный индекс по номеру документа, конечно, поддерживать уникальность глобально уже не сможет. Чтобы смог, нужна другая "проекция" этой таблицы, партицированная уже по doc_number.

        create table doc_number (
            number varchar(15) not null
            constraint doc_number_pk primary key (number)
        ) partition by hash (number);

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

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

        В статье есть фактические ошибки. Но если честно, самое большое разочарование, что не пишете про то, зачем именно партицировать, хотя заголовок статьи это подразумевает. Это исключительно простота обслуживания, масштабирование и снижение стоимости хранения.


        1. nvv Автор
          30.03.2022 16:58

          Каким образом партиционирование по id поможет улучшить выполнение запросов, которые зависят от даты документа (фильтруют по ней)?

          Синхронизация на триггерах имеет ряд недостатков. Из-за отсутствия FK, вероятно, не получится возложить контроль синхронности данных на СУБД.


          1. akhkmed
            30.03.2022 19:28

            Если id serial, то будет высокая прямая корреляция между значениями id и датой. Поэтому на каждой из партиций надо добавить через check её фактический диапазон дат, тогда предикат по дате будет отключать ненужные партиции при constraint_exclusion=on.

            Допустим id не serial, а, например uuid. Зачем он вообще такой нужен? Он будет ничуть не лучше pk(number).


      1. vasyakolobok77
        30.03.2022 22:19

        После партиционирования возможна ситуация, когда в разных партициях записи могут содержать одинаковые значения doc_id, поэтому используется пара doc_id+doc_date

        Получается, поле doc_date мутабельно? По номеру doc_number мы получаем связку id+date, по ней идем уже в нужную партицию, и при этом где-то там могут лежать "мусорные" записи с тем же id, но другой датой?

        А партицирование по doc_number не выглядит эффективней? Если предположить, что doc_number (или какой-то иной счетчик) идут в нарастающей последовательности, и запрашиваются / редактируются обычно самые свежие документы, то это также бы решило проблему "локализации" доступа.


  1. Ivan22
    30.03.2022 13:31

    плюсы партицирования по сравнению с индексом

    1. гораздо лучше ускоряет низкоселективную выборку

    2. не замедляет инсерт

    3. не занимает место

    4. обмен и добавление партиций - супер эффективная вещь для масс-инсерта

    Минусы:

    1. апдейт поля партицирования невозможен

    2. индексов много - партицировангие только одно

    3. для высокоселективных выборок - размер метаданных увеличивается критически


    1. nvv Автор
      30.03.2022 13:43

      апдейт поля партицирования невозможен

      Обновление полей по которым выполняется партиционирование работает, может быть не совсем ожидаемое поведение из-за реализации, если существует FK на PK партиционированной таблицы.


  1. vdeltsov
    30.03.2022 16:35

    Хорошая статья о том, как делать не надо. Единственное, что не жалко было времени на чтение, чтобы пришлось подумать, почему так плохо сделали реорганизацию. Фраза "в разных партициях записи могут содержать одинаковые значения doc_id" - просто шедевральна. Обновлять дату тоже придется в двух таблицах одновременно, возможно перемещая записи из партиции в партицию? В ORACLE на эту тему есть глобальный индекс. Поэтому при поиске по ID ищется запись в одно индексе, а не в 50 маленьких кусочках. Надеюсь тут тоже такое потом появится (в MS SQL тоже, как в PG, нельзя сделать глобальный уникальный индекс поверх всех партиций). Также понравилась идея "Recheck Cond" в плане запроса. Кажется, что это гениально. PS: только изучаю Posgresql, что-то нравится, что-то вымораживает.

    PS: в MS SQL на эту тему сделал кластеризованный ключ по PrDate+PrId (в итоге данные гарантированно посортированы по дате). А по PrId отдельный некластеризованный уникальный индекс. Благодаря этому большинство запросов (все они с фильтром за период) смотрят только таблицу, только нужные записи. Ходить через индекс по дате было бы менее гуманно. Большая часть запросов "за сегодня" - вообще смотрят только небольшой кусок данных. Плюс эта же дата попадает по все другие индексы, что тоже часто удобно, и обычно не мешает. Никаких партиций не потребовалось. По сути это получилось партиционирование по дню.


    1. nvv Автор
      30.03.2022 17:03

      В PG, к сожалению, в настоящее время нет кластеризованного индекса, который постоянно поддерживал бы порядок записей.


    1. nvv Автор
      30.03.2022 17:07

      Фраза "в разных партициях записи могут содержать одинаковые значения doc_id" - просто шедевральна.

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