Партицирование (partitioning) больших таблиц PostgreSQL

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

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

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

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

Сама таблица пускай будет выглядеть так:

CREATE TABLE ads_hits
(
  "ad_id" integer NOT NULL,
  "date" timestamp without time zone NOT NULL DEFAULT now(),
  "user_ip" cidr NOT NULL
)
WITH ( OIDS=FALSE );

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

Теперь нам необходимо создать триггер, что будет срабатывать при каждой вставке записи в таблицу и создавать новую партицию, если это будет нужно.
Знаю что это не очень быстро, но иначе пока никак. Возможно в следующих версиях PostgreSQL всё будет удобнее, но зато вы получаете полное управление процессом разбиения на партиции.

Итак, сам триггер выглядит так:

CREATE OR REPLACE 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_trigger
  BEFORE INSERT
  ON ads_hits
  FOR EACH ROW
  EXECUTE PROCEDURE ads_hits_insert_trigger();

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

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

Пускай sequence описывается так:

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

Тогда в триггере будет написано следующее:

...
-- Cоздаём партицию, наследуя мастер-таблицу --------------------------
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 );