Часто возникает проблема: одна из таблиц в базе данных сильно выросла и время выполнения запросов к этой таблице увеличилось. Одним из вариантов решения такой проблемы в PostgreSQL является партицирование. В статье затронем не только техническую реализацию, но и опишем этапы подготовки к партицированию. 

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

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

Таблицу, которую партицируют, называют мастер-таблицей. Партиция имеет связь с мастер-таблицей и представляет собой обычную таблицу, то есть к ней можно обращаться точно так же, как к самой обычной таблице: SELECT, INSERT (если не нарушает ограничений, накладываемых на партицию), UPDATE, DELETE. Допустимы операции обслуживания (VACUUM, ANALYZE), а также операции по изменению схемы таблицы (ALTER), правда, с некоторыми ограничения (подробнее см. официальную документацию).

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

Какие проблемы может решить партицирование?

• ускорение выборки данных;

• ускорение вставки данных;

• упрощение удаления старых данных;

• упрощение обслуживания таблицы.

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

Стандартный процесс при удалении старых данных: выполняем DELETE FROM по условию, а затем запускаем полную вакуумизацию (VACUUM FULL) с простоем (про полный вакуум без простоя см. pg_repack). Обе операции затратны по времени и нагрузке на сервер БД и слабо контролируемы: нельзя понять прогресс выполняемой операции. В случае партицирования (при условии, что партицирование выполнено по полю created_at либо аналогичному) удаление старых данных занимает 2-3 секунды и выполняется через удаление соответствующей партиции с помощью DROP TABLE.

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

В каких случаях партицирование не поможет либо никак не повлияет?

• время создания бэкапа;

• время восстановления из бэкапа;

• место на диске.

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

Некоторые ограничения и возможные проблемы по итогам партицирования

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

• на партицируемую таблицу нельзя ссылаться через FOREIGN KEYS (можно, начиная с PostgreSQL 12); при этом обратное (партицируемая таблица ссылается на другие) допустимо;

• в некоторых случаях партицирование может ухудшить производительность на операциях чтения и записи; как указывалось выше, итоги партицирования сильно зависят от многих условий;

• в идеале запрос будет выполняться против одной партиции, но в худшем случае — затронет все партиции и, в зависимости от настроек PostgreSQL, увеличит время выполнения запроса, как в предыдущем пункте.

Виды партицирования

Декларативное партицирование появилось в PostgreSQL 10. Является встроенным (built-in) и наиболее производительным решением по партицированию, но имеет существенные ограничения:

• требует изначально создать таблицу, готовую к партицированию;

• нельзя партицировать уже существующую таблицу через ALTER TABLE;

• при добавлении и удалении партиций будет простой в работе таблицы из-за ACCESS EXCLUSIVE LOCK (начиная с PostgreSQL 12, режим блокировки более щадящий: можно использовать SELECT, но только без FOR UPDATE/SHARE);

• имеет ряд других ограничений по сравнению с другим типом партицирования.

Партицирование через наследование является более гибким решением:

• можно партицировать уже существующую таблицу;

• нет даунтайма при добавлении и удалении партиций;

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

• возможно множественное наследование (наследование схем более чем одной таблицы);

• в конце концов партицирование можно безболезненно отменить.

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

Процедура партицирования через наследование

1. Создаём таблицы-партиции с использованием ключевого слова INHERITS:

CREATE TABLE bigtable_y2021m03 (   
    CHECK (created_at >= '2021-03-01'::DATE AND created_at < '2021-04-01'::DATE) 
  ) INHERITS (bigtable);
CREATE TABLE bigtable_y2021m04 (    
    CHECK (created_at >= '2021-04-01'::DATE AND created_at < '2021-05-01'::DATE)  
  ) INHERITS (bigtable);

Указываем уникальное имя таблицы-партиции. Желательно в имени кратно указать критерий (условие) партицирования. Задаём критерий партицирования для каждой партиции через CHECK. Обратите внимание, условия для партиций должны быть уникальны, вставляемая строка данных должна удовлетворять условию только одной партиции. Поэтому в данном примере нижняя граница условия имеет знак нестрогого неравенства, а верхняя граница — строгого.

Если планируется разнести имеющиеся в мастер-таблице данные по партициям, то в данном шаге создаём столько партиций, сколько необходимо, чтобы покрыть ими текущие данные плюс некоторое количество партиций для будущих данных на 2-3 месяца вперёд. Иначе создаём партиции только для будущих данных.

2. Добавляем индексы, такие же, как в мастер-таблице:

ALTER TABLE ONLY bigtable_y2021m03    
    ADD CONSTRAINT bigtable_y2021m03__pkey PRIMARY KEY (id);
CREATE INDEX bigtable_y2021m03__created_at ON bigtable_y2021m03 (created_at);
ALTER TABLE ONLY bigtable_y2021m04  
    ADD CONSTRAINT bigtable_y2021m04__pkey PRIMARY KEY (id);
CREATE INDEX bigtable_y2021m04__created_at ON bigtable_y2021m04 (created_at);

3. Создаём функцию, обеспечивающую партицирование:

CREATE OR REPLACE FUNCTION     
    bigtable_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.created_at >= '2021-03-01'::DATE AND 
    NEW.created_at < '2021-04-01'::DATE ) THEN    
        INSERT INTO bigtable_y2021m03 VALUES (NEW.*);
ELSIF ( NEW.created_at >= '2021-04-01'::DATE AND   
    NEW.created_at < '2021-05-01'::DATE ) THEN  
        INSERT INTO bigtable_y2021m04 VALUES (NEW.);
ELSE
    RAISE EXCEPTION 'Date out of range.   
        Fix the bigtable_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

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

4. Подключаем функцию к мастер-таблице:

CREATE TRIGGER insert_bigtable    
    BEFORE INSERT ON bigtable
    FOR EACH ROW EXECUTE FUNCTION bigtable_insert_trigger();

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

5. Разносим данные из мастер-таблицы по партициям:

WITH x AS (  
    DELETE FROM ONLY bigtable      
        WHERE created_at BETWEEN .. AND .. RETURNING *)
INSERT INTO bigtable_y20XXmYY   
    SELECT * FROM x;

Итерационно по условию WHERE удаляем данные из мастер-таблицы и удалённые данные возвращаем (RETURNING) в качестве ответа на запрос DELETE FROM. Эти данные используем для вставки в нужную партицию. Обратите внимание на ключевое слово ONLY в операции удаления: без него запрос отработает не только по мастер-таблице, но и по всем партициям. Данное ключевое слово может быть полезно и при выборке данных ТОЛЬКО из мастер-таблицы, и при прочих операциях с мастер-таблицей, если не хотим, чтобы были затронуты партиции.

Если планируется полностью удалить данные из мастер-таблицы без переноса, то пропускаем данный шаг.

6. Очищаем мастер-таблицу

TRUNCATE ONLY bigtable;

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

Отлично, партицирование выполнено, всё работает.

Проходит три месяца, новые данные перестают писаться в таблицу. Всё потому, что мы забыли добавить в крон добавление новых партиций. Быстро лечим кроном и переписываем функцию (добавляем условия проверки). Через год у нас уже 100 партиций, и функция разрослась до неимоверных размеров. Обслуживать такую таблицу со временем стало сложнее. Давайте не будем так делать и вместо этого заставим PostgreSQL самостоятельно решать подобные проблемы.

Объединим первые три шага процедуры в один. В итоге получим следующую процедуру:

1. Создаём расширенную функцию, обеспечивающую партицирование с автоматическим созданием партиций:

CREATE OR REPLACE FUNCTION bigtable_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
  current_date_part DATE;
  current_date_part_id TEXT;
  partition_table_name TEXT;
  first_day_of_month DATE;
  last_day_of_month DATE;
BEGIN
  current_date_part := CAST(DATE_TRUNC('month', NEW.created_at) AS DATE);
  current_date_part_text := REGEXP_REPLACE(current_date_part::TEXT, '-','_','g');
  partition_table_name := FORMAT('bigtable_%s', current_date_part_text::TEXT);
  IF (TO_REGCLASS(partition_table_name::TEXT) ISNULL) THEN
    first_day_of_month := current_date_part;
    last_day_of_month := current_date_part + '1 month'::INTERVAL;
    EXECUTE FORMAT(
      'CREATE TABLE %I ('
      '  CHECK (created_at >= DATE %L AND created_at < DATE %L)'
      ') INHERITS (bigtable);'
      , partition_table_name, first_day_of_month, last_day_of_month);
    EXECUTE FORMAT(
      'ALTER TABLE ONLY %1$I ADD CONSTRAINT %1$s__pkey PRIMARY KEY (id);'
      , partition_table_name);
    EXECUTE FORMAT(
      'CREATE INDEX %1$s__created_at ON %1$I (created_at);'
      , partition_table_name);
  END IF;
  EXECUTE FORMAT('INSERT INTO %I VALUES ($1.*)', partition_table_name) USING NEW;

  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

В данном шаге на основе входных данных определяем имя партиции, к которой должны относится новые данные, и условия принадлежности к этой партиции (первые три строки блока BEGIN). Проверяем существование такой партиции (TO_REGCLASS), и если её нет — создаём соответствующую партицию и индексы к ней (EXECUTE FORMAT … CREATE TABLE, ALTER TABLE, CREATE INDEX). В конце вставляем новые данные в правильную партицию. Соответственно, если партиция существует, то просто вставляем в неё новые данные.

2. Подключаем функцию к мастер-таблице.

3. Разносим данные из мастер-таблицы по партициям.

4. Очищаем мастер-таблицу.

Таким образом, 

• вместо 6 шагов процедура сократилась до 4;

• нет необходимости в периодическом ручном, либо полуавтоматическом (через cron) создании партиций;

• функция существенно упростилась (да, всё так: 100 if’ов для каждой партиции против одного if’а на проверку существования партиции — это значительное упрощение);

• изменение функции требуется только при изменении схемы мастер-таблицы.

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

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

Давайте разберёмся с этими вопросами!

Собираем чек-лист партицирования

Итак, вы решили выполнить партицирование таблицы и, вероятно, таким образом хотите решить какую-то проблему. Какой итоговый результат вы ожидаете от партицирования? Почему вы выбрали для решения этой проблемы партицирование? Рассматривали ли вы другие варианты решения? Может быть стоит выполнить рефакторинг кода? Или на сервере БД поставить более мощное железо? Или просто в таблице не хватает индекса? Иными словами, есть ли у вас цель? Без цели выполнение партицирования чревато простоями и ухудшением производительности.

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

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

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

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

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

Во-первых, это значительно усложняет код функции, обеспечивающей партицирование.

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

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

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

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

• количество строк в партиции;

• периоды времени хранения информации (день, неделя, месяц, год);

• диапазон идентификаторов записей в таблице (например, 1 млн id на партицию);

• иные варианты, которые считаете приемлемыми для оценки размера партиции.

Иногда вместо размера партиции говорят о шаге партицирования: партицировали таблицу с шагом 1 месяц, 10 млн id, 100 млн строк и т.п.

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

В более сложных случаях, ответ не так очевиден. Например, в случае если критерий партицирования — поле id, то какой размер будет оптимальным: 1 млн id, 10 млн, 100 млн, 1 млрд? Если есть сомнения, выберите несколько возможных вариантов. Желательно ограничить количество таких вариантов числом 5.

Оптимальный размер партиции 5-20ГБ, но в вашем случае может быть и другой размер.

Когда вы оценили размер партиции и остановились на одном из вариантов, добавьте к нему ещё два: с бОльшим и мЕньшим шагом. Например, при партицировании с шагом в 1 месяц добавляем варианты с шагом 1 неделя и с шагом 2 месяца.

Подобьём промежуточный итог

• имеется некая проблема, которую пытаемся решить;

• найден самый часто выполняющийся запрос на чтение (либо топ запросов);

• определен один или несколько критериев партиционирования;

• подобраны несколько вариантов шага партиционирования для каждого из критериев.

Итого имеем N гипотез (К критериев x Ш шагов) партиционирования таблицы.

Теперь для каждой гипотезы нужно подготовить:

• SQL-запрос на создание функции, обеспечивающей партицирование;

• SQL-запрос (одинаковый для всех гипотез) на добавление триггера к таблице;

• скрипт по переносу данных из мастер-таблицы по партициям;

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

Переходим к тестированию

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

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

Как тестировать? Разворачиваем из бэкапа либо всю базу, либо только партицируемую таблицу со связанными таблицами. Добавляем функцию и триггер. С помощью подготовленного скрипта переносим данные из мастер-таблицы по партициям. Прогоняем пачку топ запросов, замеряем время выполнения (в этом поможет поможет команда \timing), смотрим план выполнения, сравниваем результаты с продом.

Если тестирование показало отрицательные результаты, стоит пересмотреть критерий партицирования и шаг партицирования. Может быть, вы поставили слишком много целей: ускорить всё и вся и чтобы проще было удалять старые данные. Достигнуть таких целей возможно, но подобная комбинация схемы таблицы и логики работы приложения (а значит и структура запросов) встречается крайне редко.

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

Итого, получаем следующий чек-лист по партицированию:

• определяем цель;

• находим самый часто встречающийся запрос;

• определяем критерий партицирования;

• подбираем размер партиции;

• готовим скрипты для рассматриваемых гипотез:

  • функцию, обеспечивающую партицирование;

  • подключение триггера;

  • скрипт переноса данных;

  • скрипты отката изменений;

• выполняем тестирование гипотез;

• выбираем подходящую гипотезу по итогам тестирования;

• делаем бэкап продуктивных данных;

• партицируем таблицу на проде.

Пара слов о параметрах СУБД, которые влияют на партицирование

Наиболее критичные из них:

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

• параметр max_parallel_workers_per_gather, отвечающий за максимальное количество воркеров, используемых для сборки результатов поиска от разных воркеров (при выполнении операции Gather или Gather Merge); в некоторых случаях может существенно ускорить работу запросов, в других — привести к деградации производительности всей СУБД.

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

Полезные ссылки

1. Статья по партицированию из официальной документации PostgreSQL

2. Как работает распараллеливание процессов в PostgreSQL

3. Параметры, влияющие на производительность PostgeSQL