Разбираемся с 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’ов.

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 не только для партиций, но и еще и для нормализации данных.
  • +31
  • 8.8k
  • 7
Share post
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 7

    +1
    А не пробовали смотреть на pg_pathman? Не подошло?
      +1

      Я пробовал пару лет назад. Хорошая штука — никаких триггеров — создал вызовами его функций партиции и в ORM ничего менять не надо: вставляй в неё, читай из неё. Данные из корневой таблицы в партиции можно переливать в фоне по ночам.


      Помнится, требовались пляски для обновления расширения (на первых порах было очень много детских болячек, но они очень оперативно правились): скомпилировать и установить новую версию расширения в постгрес, перезапустить СУБД, вызвать UPDATE EXTENSION, молиться. Может, с тех пор лучше стало — больше не работал.


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

        0
        Смотрели, но в итоге решили не использовать, по причинам, которые уже так же описал Envek
        В нашем случае основной проблемой было так же накатывать custom extension.
        В 2016ом у нас это делал Chef скрипт, который был общий для всех микросервисов. Плюс, поскольку мы работаем с Amazon RDS, там кажется были какие-то свои еще тонкости с установкой.
        +1
        Не плохо бы было добавить:
        1. про настройку constraint_exclusion (enum)
        2. что делаете с устаревшими партициями
        3. наложить запрет на вставку в родительскую таблицу, это поможет выявлять ситуации когда не совсем корректно настроена вставка.
          0
          1. Спасибо, добавил еще пункт о constraint_exclusion
          2. Это отдельная довольно большая тема, которая сильно зависит от бизнес логики. Оптимально конечно когда спустя некоторое время такая партиция становится не нужна, и ее можно легко дропнуть. Но бывает так не всегда.
          3. Сильно зависит от имплементации. Если делать "под ORM", тогда вставка нужна. Если же в таблицу пишут ручками, можно конечно и перестраховаться.
          +1
          Спасибо за статью, всегда раз, когда коллеги делятся своими решениями. Тоже сталкивался с проблемой автопартицирования примерно полгода назад. Вы изучали другие практики? Подобных решений много в том числе и на русскоязычном сегменте, вот, например, из 2015 года: www.youtube.com/watch?v=ECkQtFnOxuA

          Мне нравится, что создание партиций в вашем подходе идемпотентно, и race condition тут:
          -- 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; 
          

          ничего страшного не делает.

          В моем случае идут bulk insert-ы и триггер создавал неприемлемый overhead по performance. Плюс требовалось двух уровневое партицирование, сначала разрезать по диапазону одной колонке, потом по второй. Поэтому сравнивайте, на сколько вы «просядите» с триггером или без.

          Хочу отметить, что для декларативного партицирования, которая появилась в 10-ке, в 11-ой версии обещают Runtime partition pruning (https://commitfest.postgresql.org/17/1330/). Так же для декларативное партицирования обещают unique index (https://commitfest.postgresql.org/17/1452/). Будет ли поддерживаться эти фичи, для legacy-партицирования через наследования нужно еще проверить, поэтому для новой разработки, я бы не рекомендовал масштабироваться подобным образом.
            0
            Одно из первых правил, котором учат DBA — при bulk'ах отключайте trigger'ы. К тому же наше решение со вставкой и удалением строк явно не оптимально по производительности. Зато очень легко в поддержке.

            Для новой разработки мы уже используем декларативное партиционирование. Но об этом в другой раз.

          Only users with full accounts can post comments. Log in, please.