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’ов.

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

Первый подводный камень такого подхода казалось бы совсем очевиден: любой запрос обязан содержать tenant_id. И тем не менее, если не напоминать себе об этом постоянно, рано или поздно сам же напишешь custom SQL, в котором этот 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 заполняет для нас довольно много переменных, к которым мы можем обращаться. Полный список можно посмотреть тут.

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

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

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

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

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

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 для того чтобы определить, какие данные туда должны попадать.

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

У нас появилась новая проблема. 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.

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

Но дело в том, что большинство ORM используют при INSERT’е RETURNING clause. Если мы вернем NULL из нашего trigger’а, то ORM запаникует, посчитав, что строка не добавлена. Она то добавлена, только не туда, куда 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. А те, для кого эта тема уже не нова, все же узнали пару-тройку интересных трюков.

UPD:
Как верно подметил bigtrot, вся эта уличная магия не будет работать, если выключена настройка CONSTRAINT_EXCLUSION.

Проверить это можно при помощи команды

show CONSTRAINT_EXCLUSION

У настройки есть три значения: on, off и partition

Настройка partition является более оптимальной, если вы вдруг любите использовать CHECK CONSTRAINTS не только для партиций, но и еще и для нормализации данных.