Все что нужно знать о секционировании (Часть 2)

Часть 1

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

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

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

Автоматизация задач секционирования

Для того, чтобы в какой-то мере автоматизировать нашу задачу, необходимо создать несколько вспомогательных объектов.

Таблица секций

В этой таблице мы будем хранить наши секции. master_table — название основной таблицы (вместе со схемой), partition_table — название секции (вместе со схемой), range_check — ограничение для данной секции.

CREATE TABLE public.table_partitions
(
  master_table text NOT NULL,
  partition_table text NOT NULL,
  range_check text NOT NULL,
  time_added TIMESTAMP DEFAULT now() NOT NULL,
  CONSTRAINT table_partitions_primary_key PRIMARY KEY (master_table, partition_table)
);

* This source code was highlighted with Source Code Highlighter.

Функция для добавления новой секции

С помощью этой функции мы сможем добавить новую секцию. Автоматически будут изменены условия вставки в триггере.

CREATE OR REPLACE FUNCTION public.pg_add_range_partition(IN p_master_table text,
  IN p_partition_table text, IN p_range_check text, IN p_trigger_function text,
  OUT status_code text)
RETURNS text AS
$$
DECLARE
  v_table_ddl text := 'CREATE TABLE [PARTITION_TABLE] ( CHECK ( [RANGE_CHECK] ) ) INHERITS ([MASTER_TABLE]);';
  v_trigger_ddl text := 'CREATE OR REPLACE FUNCTION [TRIGGER_FUNCTION]() RETURNS TRIGGER AS $body$ ' ||
             'BEGIN [RANGE_CHECKS] ELSE RAISE EXCEPTION ' ||
             '''Inserted data is out of range. Fix [TRIGGER_FUNCTION].''; ' ||
             'END IF; RETURN NULL; END; $body$ LANGUAGE plpgsql;';
  v_range_checks text := '';
  rec record;
BEGIN
  IF EXISTS (SELECT 1 FROM public.table_partitions
        WHERE master_table = p_master_table
         AND partition_table = p_partition_table) THEN
    status_code := 'Partition ' || p_partition_table || ' already exists';
    RETURN;
  END IF;

  v_table_ddl := replace(v_table_ddl, '[PARTITION_TABLE]', p_partition_table);
  v_table_ddl := replace(v_table_ddl, '[RANGE_CHECK]', p_range_check);
  v_table_ddl := replace(v_table_ddl, '[MASTER_TABLE]', p_master_table);
  
  FOR rec IN (SELECT 'ELSIF (' || tp.range_check || ') THEN INSERT INTO ' ||
            tp.partition_table || ' VALUES (NEW.*); ' AS range_check
         FROM public.table_partitions tp
         WHERE tp.master_table = p_master_table
        ORDER BY tp.time_added DESC) LOOP
    v_range_checks := _pg_check_to_trigger(p_master_table, rec.range_check) || v_range_checks;
  END LOOP;
  
  v_range_checks := 'IF (' || _pg_check_to_trigger(p_master_table, p_range_check) ||
           ') THEN INSERT INTO ' || p_partition_table ||
           ' VALUES (NEW.*); ' || v_range_checks;
          
  v_trigger_ddl := replace(v_trigger_ddl, '[TRIGGER_FUNCTION]', p_trigger_function);
  v_trigger_ddl := replace(v_trigger_ddl, '[RANGE_CHECKS]', v_range_checks);
  
  RAISE NOTICE 'Partition script: %', v_table_ddl;
  RAISE NOTICE 'Trigger script: %', v_trigger_ddl;
  
  EXECUTE v_table_ddl;
  EXECUTE v_trigger_ddl;
  
  INSERT INTO public.table_partitions (master_table, partition_table, range_check)
     VALUES (p_master_table, p_partition_table, p_range_check);
  
  status_code := 'OK';
  RETURN;
EXCEPTION
WHEN OTHERS THEN
  status_code := 'Unexpected error: ' || SQLERRM;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION public._pg_check_to_trigger(IN master_table text, IN range_check text)
RETURNS text AS
$$
DECLARE
  v_schema text := COALESCE(SUBSTRING(master_table FROM E'(.*)\\.'),'public');
  v_tablename text := replace(master_table, v_schema || '.', '');
  v_range_check text := range_check;
  rec record;
BEGIN
  RAISE NOTICE '%', v_schema;
  RAISE NOTICE '%', v_tablename;

  FOR rec IN (SELECT column_name
         FROM information_schema.columns
         WHERE table_schema = v_schema
          AND table_name = v_tablename) LOOP
    v_range_check := replace(v_range_check, rec.column_name, 'NEW.' || rec.column_name);
  END LOOP;
  
  RETURN v_range_check;
END;
$$ LANGUAGE 'plpgsql';

* This source code was highlighted with Source Code Highlighter.

Пример

1. Первый шаг — создаем master-таблицу и триггер

CREATE TABLE analytics.events
(
 event_id BIGINT DEFAULT nextval('analytics.seq_events') PRIMARY KEY,
 user_id UUID NOT NULL,
 event_type_id SMALLINT NOT NULL,
 event_time TIMESTAMP DEFAULT now() NOT NULL,
 url VARCHAR(1024) NOT NULL,
 referrer VARCHAR(1024),
 ip INET NOT NULL
);

CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS
$body$
BEGIN
RETURN NULL;
END; $body$ LANGUAGE plpgsql;

CREATE TRIGGER events_before_insert
 BEFORE INSERT ON analytics.events
 FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();

* This source code was highlighted with Source Code Highlighter.

2. Добавляем секцию

SELECT *
FROM pg_add_range_partition('analytics.events',
               'analytics.events_01012010',
               'event_time >= TIMESTAMP ''2010-01-01 00:00:00'' AND event_time < TIMESTAMP ''2010-01-02 00:00:00''',
               'analytics.events_insert_trigger');

* This source code was highlighted with Source Code Highlighter.

3. Получили секционированную таблицу, можем начинать с ней работать.

INSERT INTO analytics.events (user_id, event_type_id, event_time, url, referrer, ip)
   VALUES ('550e8400-e29b-41d4-a716-446655440000'::UUID, 1, '2010-01-01 15:01:01'::TIMESTAMP,
       'http://aymeshkov.habrahabr.ru', 'http://habrahabr.ru', '127.0.0.1'::INET);

* This source code was highlighted with Source Code Highlighter.

Послесловие

1. Конечно, решение, которое я предлагаю подойдет не всем, но, думаю, доработать его под вашу конкретную задачу будет не так сложно.
2. Одно маленькое замечание, которое многим облегчит жизнь — используйте generate_series(), чтобы понять, какие секции вам нужно еще создать.

SELECT '2010-01-01'::DATE + num * '1 day'::INTERVAL AS day
 FROM generate_series(0, 29) num

* This source code was highlighted with Source Code Highlighter.

Конец второй части

Топик вышел объемный, в основном из-за большого количества кода. Опять же, поместилось не все, о чем я хотел рассказать. На третью часть остаются следующие темы:
1. Управление секциями через RULE — как это делается, и почему это плохо.
2. Не хотите каждый раз мучаться с триггером? И не надо — применяем LIST PARTITIONING.