Разбираемся с partitions в PostgreSQL 9

Автор: admin от 9-09-2018, 21:40, посмотрело: 94

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



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



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



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



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

Источник: Хабр / Интересные публикации

Теги: postgresql

Категория: Веб-разработка

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

Добавление комментария

Имя:*
E-Mail:
Комментарий:
Полужирный Наклонный текст Подчеркнутый текст Зачеркнутый текст | Выравнивание по левому краю По центру Выравнивание по правому краю | Вставка смайликов Выбор цвета | Скрытый текст Вставка цитаты Преобразовать выбранный текст из транслитерации в кириллицу Вставка спойлера
Введите два слова, показанных на изображении: *