Партиционирование таблицы в Postgresql

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

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

В этой статье рассматривается пример партиционирования таблицы по полю с типом DATE, содержащей большое количество данных, собранных за 3 месяца, включая текущий.

Процесс партиционирования сводится ко следующим этапам:

  1. Создание таблиц-партиций
  2. Добавление функции, обеспечивающей обработку данных в соответствующей партиции, а не в основной таблице
  3. Добавление триггера, который будет запускать функцию из пункта 2
  4. Перенос данных из основной таблицы в партиции и удаление данных в основной таблице
  5. Автоматизация создания новых партиций

Создание таблиц-партиций

Для определенности, пусть структура основной таблицы будет такой:

 CREATE TABLE tbl (
    id integer,
    created date,
    field1 bigint,
    field2 integer,
    field3 integer
);

По условию, в таблице находятся данные за 3 месяца, включая текущий. Допустим, текущей месяц — март 2014 года, таким образом в таблице содержатся данные за 01, 02, 03 месяцы 2014 года. Партиционирование производится по полю created помесячно (один месяц = одна партиция).

Лучше всего создать партиции не только для всех указанных месяцев, но также и для следующего месяца за текущим. Таким образом, должно получиться 4 таблицы-партиции. Размещать партиции можно как в той же схеме, где находится основная таблица, так и в отдельной схеме. В данном примере партиции будут располагаться в отдельной схеме с именем parts, основная таблица находится в схеме public.

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

Шаблон таблицы-партиции:

SET search_path = parts, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE tbl__YYYYMM (
    LIKE public.tbl INCLUDING ALL,
    CONSTRAINT tbl__YYYYMM_created_check CHECK ((created >= 'YYYY-MM-01'::date) AND (created < 'NYYYY-NMM-01'::date))
)
INHERITS (public.tbl);

REVOKE ALL ON TABLE tbl__YYYYMM FROM PUBLIС;
REVOKE ALL ON TABLE tbl__YYYYMM FROM user1;
GRANT ALL ON TABLE tbl__YYYYMM FROM user2;

Где:

  • YYYY и MM — год (YYYY) и месяц (MM), которыми датируются данные, содержащиеся в партиция;
  • NYYYY и NMM — год и месяц, полученные после добавления месяца к YYYYMM.

Выражение:

CONSTRAINT tbl__YYYYMM_created_check CHECK ((created >= 'YYYY-MM-01'::date) AND (created < 'NYYYY-NMM-01'::date))

работает во время обращения к данным через основную таблицу, позволяя СУБД выбрать нужную партицию в соответствии с содержимым поля created.

Учетные записи user1 и user2 приведены для примера: user1 не имеет доступа к партиции, user2 имеет полный доступ. Конечно, можно указать иной набор привилегий.

Итак, для первого месяца (201401) таблица-партиция имеет вид:

SET search_path = parts, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE tbl__201401 (
    LIKE public.tbl INCLUDING ALL,
    CONSTRAINT tbl__201401_created_check CHECK ((created >= '2014-01-01'::date) AND (created < '2014-02-01'::date))
)
INHERITS (public.tbl);

REVOKE ALL ON TABLE tbl__201401 FROM PUBLIС;
REVOKE ALL ON TABLE tbl__201401 FROM user1;
GRANT ALL ON TABLE tbl__201401 FROM user2;

Получается, что все данные, попадающие в условие

(created >= '2014-01-01'::date) AND (created < '2014-02-01'::date)

будут извлекаться из этой партиции.

Остальные таблицы-партиции выглядят аналогично, изменяется только дата в имени и в условии для проверки.

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

Добавление функции

Функция выглядит следующим образом:

CREATE OR REPLACE FUNCTION tbl__partitioning() RETURNS "trigger"
    LANGUAGE "plpgsql"
    AS $$
        BEGIN
            BEGIN
                EXECUTE 'INSERT INTO parts.tbl__'||to_char(NEW.created, 'YYYYMM')||' SELECT $1.*' USING NEW;
            EXCEPTION
            WHEN undefined_table THEN
                RETURN NEW;
            END;
            RETURN NULL;
        END;
        $$;

Как видно, при выполнении команды INSERT, данные будут добавлены не в основную таблицу, а в соответствующую полю created партицию.

Чтобы функция выполнялась, необходимо создать триггер.

Добавление триггера

Текст триггера:

CREATE TRIGGER partitioning_trigger BEFORE INSERT ON public.tbl FOR EACH ROW EXECUTE PROCEDURE tbl__partitioning();

После добавления этого триггера все новые данные, добавляемые в основную таблицу, окажутся в соответствующую партиции, вместо основной таблицы. Что, собственно, и требуется.

Осталось перенести все данные из основной таблицы в партиции.

Перенос данных

Данные перенести можно несложным запросом (для партиции 201401, остальные по аналогии):

INSERT INTO parts.tbl__201401 (SELECT * FROM public.tbl WHERE (created >= '2014-01-01'::date) AND (created < '2014-02-01'::date));

Затем следует удалить перенесенные данные из основной таблицы. Важно не упустить в запросе удаления данных инструкцию ONLY:

DELETE FROM ONLY public.tbl WHERE (created >= '2014-01-01'::date) AND (created < '2014-02-01'::date);

После завершения переноса данных во все партиции, основная таблица должна стать пустой. Проверить это можно, например, таким запросом:

SELECT * FROM ONLY public.tbl LIMIT 10;

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

Автоматизация создания новых партиций

Для решения вопроса понадобится файл, содержащий шаблон партиции (или нескольких партиций, если партиционированию подвергнуты несколько таблиц) и скрипт, который будет по шаблону генерировать готовый к импорту sql-файл.

Шаблон партиции для рассматриваемой таблицы уже есть (приведен в разделе Создание таблиц-партиций). Если партиционированных таблиц две (например tbl и tbl2), то шаблон принимает следующий вид (подразумевается, что все партиции всех таблиц находятся в выделенной схеме parts):

SET search_path = parts, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE tbl__YYYYMM (
    LIKE public.tbl INCLUDING ALL,
    CONSTRAINT tbl__YYYYMM_created_check CHECK ((created >= 'YYYY-MM-01'::date) AND (created < 'NYYYY-NMM-01'::date))
)
INHERITS (public.tbl);

REVOKE ALL ON TABLE tbl__YYYYMM FROM PUBLIС;
REVOKE ALL ON TABLE tbl__YYYYMM FROM user1;
GRANT ALL ON TABLE tbl__YYYYMM FROM user2;

CREATE TABLE tbl2__YYYYMM (
    LIKE public.tbl2 INCLUDING ALL,
    CONSTRAINT tbl2__YYYYMM_insdate_check CHECK ((insdate >= 'YYYY-MM-01'::date) AND (insdate < 'NYYYY-NMM-01'::date))
)
INHERITS (public.tbl2);

REVOKE ALL ON TABLE tbl2__YYYYMM FROM PUBLIС;
REVOKE ALL ON TABLE tbl2__YYYYMM FROM user2;
GRANT ALL ON TABLE tbl2__YYYYMM FROM user1;

Партиционирование второй таблицы выполнено по полю insdate.

Сгенерировать sql-файл по шаблону можно, например, следующим perl-скриптом:

#!/usr/bin/perl

use strict;
use warnings;

my $table=$ARGV[0] || die "need template sql file as first argument";

create_tables ($table);

sub create_tables {
        my $filename = shift;

        my ($month,$year) = (localtime)[4..5];
        ++$month;
        $year += 1900;

        if ($month==12)  {
                $month=1;
                $year++;
        } else {
                $month++;
        }

        my ($next_year,$next_month);
        if ($month==12)  {
                $next_month=1;
                $next_year=$year+1;
        } else {
                $next_month=$month+1;
                $next_year=$year;
        }

        $month =~ s/^(\d)$/0$1/;
        $next_month =~ s/^(\d)$/0$1/;

        open IN, '<', $filename or die $!;
        while ( <IN> ) {
                s/NYYYY/$next_year/g;
                s/NMM/$next_month/g;
                s/YYYY/$year/g;
                s/MM/$month/g;
                print;
        }
        close IN;
}

Выполнить автоматическое создание партиции на следующий месяц можно таким заданием в планировщике (выполнятся задание должно от учетной записи postgres):

0 0 15 * * /path/to/create_nextmonth_partition.pl /path/to/nextmonth_template.sql > /path/to/tmpdir//bin/date "+%Y%m".sql ; psql -q -X -d dbname -f /path/to/tmpdir//bin/date "+%Y%m".sql

Где:

  • /path/to/create_nextmonth_partition.pl — скрипт, генерирующий sql-файл партиций по шаблону;
  • /path/to/nextmonth_template.sql — шаблон партиций;
  • dbname — имя базы данных, в которой находятся таблица и партиции.

В соответствие с указанным заданием, каждое 15-е число каждого месяца в 0 часов 0 минут будет генерироваться очередная таблица-партиция для следующего месяца.

  • турандот

    спасибо, как раз есть большая таблица с которой надо что то сделать

css.php