Привет, %username%! Мне понадобилось на работе заняться партицированием одной из таблиц в БД у заказчика. Не буду рассказывать о том, почему именно я выбрал партиционирование т.к. по этому вопросу и так много информации в сети Интернет. Далее в статье будет немного о том, как делается партицирование (секционирование).

Для начала определимся с понятиями:

Партицирование (partitioning) - это разбиение больших таблиц на логические части по выбранным критериям. Партиционированные или секционированные таблицы призваны улучшить производительнос и управляемость базами данных.

Вроде бы всё понятно. Осталось только понять как разбить таблицу на секции или партиции? В PostgreSQL эта процедура потребует небольших усилий, но оно того стоит!

Представим, что у нас есть таблица (db.item_pulse), которая за день может вырасти на несколько сотен тысяч строк (за месяц несколько миллионов). Логичным действием по оптимизации является разбиение на секции. Для упрощения будем дробить по дням (управлять в моем случае так будет немного проще).

Структура у таблицы пусть будет такая:

CREATE TABLE item_pulse ( "item_p_id" integer NOT NULL, "date" timestamp without tome zone NOT NULL DEFAULT now(), "user_ip" cidr NOT NULL ) WITH ( OIDS=FALSE );

Для пониманиия: ad_id - это уникальный ID, date - дата и время сбора метки, user_ip - IP-адрес запроса. На этом я думаю хватит для примера, но в реальности количество полей может быть огромным.

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

Пусть наш триггер будет выглядеть вот таким образом:

CREATE OR REPLAE FUNCTION item_pulse_insert_trigger() RETURNS trigger AS $BODY$ DECLARE table_master varchar(255) := 'item_pulse'; 
table_part varchar(255) := ''; 
BEGIN 
    -- Даём имя партиции -------------------------------------------------- 
    table_part := table_master || '_y' || date_part( 'year', NEW.date )::text || '_m' || date_part( 'month', NEW.date )::text || '_d' || date_part( 'day', NEW.date )::text; 
    -- Проверяем партицию на существование -------------------------------- 
    PERFORM 1 FROM pg_class WHERE relname = table_part LIMIT 1; 
    -- Если её ещё нет, то создаём -------------------------------------------- 
    IF NOT FOUND THEN 
        -- Cоздаём партицию, наследуя мастер-таблицу -------------------------- 
        EXECUTE ' CREATE TABLE ' || table_part || ' ( ) INHERITS ( ' || table_master || ' ) WITH ( OIDS=FALSE )'; 
        -- Создаём индексы для текущей партиции ------------------------------- 
        EXECUTE ' CREATE INDEX ' || table_part || '_adid_date_index ON ' || table_part || ' USING btree (item_p_id, date)'; 
    END IF;
-- Вставляем данные в партицию -------------------------------------------- 
    EXECUTE ' INSERT INTO ' || table_part || ' SELECT ( (' || quote_literal(NEW) || ')::' || TG_RELNAME || ' ).*'; 
    RETURN NULL; 
END; 
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

Привязываем созданный нами триггер к таблице:

CREATE TRIGGER item_pulse_insert_triger BEFORE INSERT ON item_pulse FOR EACH ROW EXECUTE PROCEDURE item_pulse_insert_trigger();

Теперь таблицы будут создаваться вот такого вида item_pulse_y<год>_m<месяц>_d<день> (пример: item_pulse_y2018_m07_d16).

В случае если ваши таблици, которые вы собираетесь секциониорвать содержат уникальный ID, то его стоит указывать в каждой партиции с указанием nextval() от sequence мастер-таблицы.

Пусть sequence будет описан таким образом:

CREATE SEQUENCE item_pulse_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;

В таком случае в триггере следует написать вот так:

... 
-- создаём партицию, наследуя мастер-таблицу -------------------------- 
EXECUTE ' CREATE TABLE ' || table_part || ' ( id bigint NOT NULL DEFAULT nextval(''' || table_master || '_id_seq''::regclass), CONSTRAINT ' || table_part || '_id_pk PRIMARY KEY (id) ) INHERITS ( ' || table_master || ' ) WITH ( OIDS=FALSE )';
...

А в мастер-таблице в таком случае id стоит описать вот так:

CREATE TABLE item_pulse ( id bigserial NOT NULL, ... CONSTRAINT item_pulse_id_pk PRIMARY KEY (id) ) WITH ( OIDS=FALSE )

Вот собственно и всё! На этом имеет смысл завершить.

Если у тебя есть вопросы, комментарии и/или замечания – заходи в чат, а так же подписывайся на канал.

О способах отблагодарить автора можно почитать на странице “Донаты”.