Хабрахабр

[Из песочницы] Разбираемся с partitions в PostgreSQL 9

PostgreSQL 10 был выпущен еще в начале октября 2017го, почти год назад.

Но что, если вы не спешите апгрейдится до 10ки? Одна из наиболее интересных новых “фич” — это безусловно декларативное партиционирование. Вот Amazon, к примеру, не торопится, и ввел поддержку PostgreSQL 10 только в последних числах февраля 2018-го.

Я — software architect финансового отдела в компании занимающейся такси, так что все примеры будут так или иначе связаны с поездками (проблемы связанные с деньгами оставим на другой раз). Тогда на помощь приходит старое-доброе партиционирование через наследование.

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

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

Начнем с того, как примерно выглядит наша таблица:

create table rides ( id bigserial not null primary key, tenant_id varchar(20) not null, ride_id varchar(36) not null, created_at timestamp with time zone not null, metadata jsonb -- Probably more columns and indexes coming here
);

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

Для тех, кто до этого не углублялся в то, как работают партиции в PostgreSQL (счастливчики из Oracle, привет!) вкратце опишу процесс.

PostgreSQL полагается для этого на две свои “фичи”: возможность наследовать таблицы, table inheritance, и checked conditions.

Используя ключевое слово INHERITS мы указываем, что таблица, которую мы создаем наследует все поля наследуемой таблицы. Начнем с наследования. Это также создает взаимосвязь между двумя таблицами: сделав запрос из parent’а, мы также получаем все данные из child’ов.

Таким образом оптимизатор PostgreSQL может отсечь часть child таблиц, полагаясь на данные из запроса. Checked conditions дополняют картину тем, что гарантируют непересечение данных.

И тем не менее, если не напоминать себе об этом постоянно, рано или поздно сам же напишешь custom SQL, в котором этот tenant_id забудешь указать. Первый подводный камень такого подхода казалось бы совсем очевиден: любой запрос обязан содержать tenant_id. Как итог — сканирование всех партиций и нефункционирующая база данных.

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

Для этого мы пользуемся следующей хранимой процедурой:

CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER AS
$BODY$
DECLARE partition_env TEXT; partition_date TIMESTAMP; partition_name TEXT; sql TEXT;
BEGIN -- construct partition name partition_env := lower(NEW.tenant_id); partition_date := date_trunc('month', NEW.created_at AT TIME ZONE 'UTC'); partition_name := format('%s_%s_%s', TG_TABLE_NAME, partition_env, to_char(partition_date, 'YYYY_MM')); -- create partition, if necessary IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = partition_name) THEN PERFORM create_new_partition(TG_TABLE_NAME, NEW.tenant_id, partition_date, partition_name); END IF; select format('INSERT INTO %s values ($1.*)', partition_name) into sql; -- Propagate insert EXECUTE sql USING NEW; RETURN NEW; -- RETURN NULL; if no ORM
END;
$BODY$ LANGUAGE plpgsql;

Первое, на что стоит обратить внимание, это использование TG_TABLE_NAME. Поскольку речь идет о trigger’е, PostgreSQL заполняет для нас довольно много переменных, к которым мы можем обращаться. Полный список можно посмотреть тут.

В нашем случае это будет rides. В нашем случае мы хотим получить название parent таблицы, на которой сработал trigger. Мы используем схожий подход в нескольких микросервисах, и эту часть можно переносить практически без изменений.

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

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

Фактически, это можно перевести в NEW.*. $1.* развернет все значения новой строки. ID, NEW. Что переводится в NEW. TENANT_ID, …

Это случится раз за период для каждого tenant’а. Следующая процедура, которую мы вызываем при помощи PERFORM, создаст новую партицию, если она еще не существует.

CREATE OR REPLACE FUNCTION create_new_partition(parent_table_name text, env text, partition_date timestamp, partition_name text) RETURNS VOID AS
$BODY$
DECLARE sql text;
BEGIN -- Notifying RAISE NOTICE 'A new % partition will be created: %', parent_table_name, partition_name; select format('CREATE TABLE IF NOT EXISTS %s (CHECK ( tenant_id = ''%s'' AND created_at AT TIME ZONE ''UTC'' > ''%s'' AND created_at AT TIME ZONE ''UTC'' <= ''%s'')) INHERITS (%I)', partition_name, env, partition_date, partition_date + interval '1 month', parent_table_name) into sql; -- New table, inherited from a master one EXECUTE sql; PERFORM index_partition(partition_name);
END;
$BODY$
LANGUAGE plpgsql;

Как уже описывалось ранее, мы используем INHERITS для создания таблицы подобной parent’у, и CHECK для того чтобы определить, какие данные туда должны попадать.

Если мы сейчас запустим INSERT из psql, то сможем увидеть, была ли создана партиция. RAISE NOTICE просто отпечатывает строку в консоль.

INHERITS не наследует индексы. У нас появилась новая проблема. Для того у нас есть два решения:

Создавать индексы используя наследование:
Использовать CREATE TABLE LIKE, а затем ALTER TABLE INHERITS

Или же создавать индексы процедурально:

CREATE OR REPLACE FUNCTION index_partition(partition_name text) RETURNS VOID AS
$BODY$
BEGIN -- Ensure we have all the necessary indices in this partition; EXECUTE 'CREATE INDEX IF NOT EXISTS ' || partition_name || '_tenant_timezone_idx ON ' || partition_name || ' (tenant_id, timezone(''UTC''::text, created_at))'; -- More indexes here...
END;
$BODY$
LANGUAGE plpgsql;

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

Наконец мы создаем trigger который будет вызываться при создании новой строки:

CREATE TRIGGER before_insert_row_trigger
BEFORE INSERT ON rides
FOR EACH ROW EXECUTE PROCEDURE insert_row();

Тут есть еще одна тонкость, на которой редко заостряют внимание. Партиционировать лучше всего по колонкам, данные в которых никогда не меняются. В нашем случае это работает: у поездки никогда не меняется tenant_id и created_at. Проблема, которая возникает, если это не так — PostreSQL не вернет нам часть данных. Мы то обещали ему CHECK’ом, что все данные валидные.

Решений тут несколько (кроме очевидного — не мутировать данные по которым партиционируем):

Вместо UPDATE’а на уровне приложения всегда делаем DELETE+INSERT
Добавляем еще один trigger на UPDATE, который будет переносить данные в правильную партицию

Если мы используем в запросах AT TIME ZONE нужно не забывать, что на самом деле это вызов функции. Другой нюанс, который стоит рассмотреть, это то, как правильно индексировать колонки содержащие даты. Я забыл. А значит и наш индекс должен быть function based. Как итог — опять сдохшая от нагрузки база.

Последний аспект, который стоит рассмотреть, это то, как партиции взаимодействуют с различными ORM фреймворками, будь то ActiveRecord в Ruby или GORM в Go.

Если вы не используете ORM, то можете смело вернуться к первой хранимой процедуре, и поменять RETURN NEW; на RETURN NULL;. Партиции в PostgreSQL полагаются на то, что parent таблица всегда будет пустой. Тогда строка в parent таблицу просто не добавится, чего мы собственно и хотим.

Если мы вернем NULL из нашего trigger’а, то ORM запаникует, посчитав, что строка не добавлена. Но дело в том, что большинство ORM используют при INSERT’е RETURNING clause. Она то добавлена, только не туда, куда ORM смотрит.

Способов обойти это несколько:

  • Не использовать ORM для INSERT’ов
  • Патчить ORM (что иногда советуют в случае ActiveRecord)
  • Добавить еще один trigger, который будет удалять строку из parent’а.

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

CREATE OR REPLACE FUNCTION delete_parent_row() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN delete from only rides where id = NEW.ID; RETURN null;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER after_insert_row_trigger
AFTER INSERT ON rides
FOR EACH ROW EXECUTE PROCEDURE delete_parent_row();

Последнее, что нам осталось сделать — протестировать наше решение. Для этого мы генерируем некое количество строк:

DO
$script$
DECLARE year_start_epoch bigint := extract(epoch from '20170101'::timestamptz at time zone 'UTC'); delta bigint := extract(epoch from '20171231 23:59:59'::timestamptz at time zone 'UTC') - year_start_epoch; tenant varchar; tenants varchar[] := array['tenant_a', 'tenant_b', 'tenant_c', 'tenant_d'];
BEGIN FOREACH tenant IN ARRAY tenants LOOP FOR i IN 1..100000 LOOP insert into rides (tenant_id, created_at, ride_id) values (tenant, to_timestamp(random() * delta + year_start_epoch) at time zone 'UTC', i); END LOOP; END LOOP;
END
$script$;

И посмотрим, как поведет себя база данных:

explain select *
from rides
where tenant_id = 'tenant_a'
and created_at AT TIME ZONE 'UTC' > '20171102'
and created_at AT TIME ZONE 'UTC' <= '20171103';

Если все прошло как надо, мы должны увидеть следующий результат:

Append (cost=0.00..4803.76 rows=4 width=196) -> Seq Scan on rides (cost=0.00..4795.46 rows=3 width=196) Filter: (((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone) AND ((tenant_id)::text = 'tenant_a'::text)) -> Index Scan using rides_tenant_a_2017_11_tenant_timezone_idx on rides_tenant_a_2017_11 (cost=0.28..8.30 rows=1 width=196) Index Cond: (((tenant_id)::text = 'tenant_a'::text) AND ((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone))
(5 rows)

Несмотря на то, что у каждого tenant’а сотня тысяч строк, мы делаем выборку только из нужного среза данных. Успех!

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

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть