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

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

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

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

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

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

1
2
3
4
5
6
7
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-адрес запроса. На этом я думаю хватит для примера, но в реальности количество полей может быть огромным.

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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;

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

1
2
3
4
5
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 будет описан таким образом:

1
2
3
4
5
6
CREATE SEQUENCE item_pulse_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

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

1
2
3
4
5
6
7
8
9
10
11
...
-- создаём партицию, наследуя мастер-таблицу --------------------------
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 стоит описать вот так:

1
2
3
4
5
6
7
CREATE TABLE item_pulse
(
id bigserial NOT NULL,
...
CONSTRAINT item_pulse_id_pk PRIMARY KEY (id)
)
WITH ( OIDS=FALSE )

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

Опубликовано 16.07.2018 в 16:27 · Автор JTProg_ru · Ссылка
Рубрики: PostgreSQL · Теги: , , ,