[PostgreSQL] Партицирование таблиц в PostgreSQL

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

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

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

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

CREATE TABLE ads_hits ( “ad_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 ads_hits_insert_trigger() RETURNS trigger AS $BODY$ DECLARE table_master varchar(255) := ‘ads_hits’; 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
        (ad_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 ads_hits_insert_triger BEFORE INSERT ON ads_hits FOR EACH ROW EXECUTE PROCEDURE ads_hits_insert_trigger();

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

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

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

CREATE SEQUENCE ads_hits_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 ads_hits ( id bigserial NOT NULL, … CONSTRAINT ads_hits_id_pk PRIMARY KEY (id) ) WITH ( OIDS=FALSE )

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