Секционирование в PostgreSQL. Часть 2
Павел Лузанов, Postgres Professional
В первой части статьи были рассмотрены основы и принципы организации секционирования в PostgreSQL. Теперь рассмотрим некоторые типовые сценарии работы с секционированными таблицами.
Начальная настройка секционирования для большой таблицы
Предположим, что таблица с данными выросла настолько, что секционирование ей не помешает. Рассмотрим шаги, которые нужно выполнить, чтобы разбить таблицу на секции и при этом не остановить работу приложений.
В качестве примера возьмём следующую таблицу с тремя тестовыми записями:
postgres=# create table data_tab ( postgres(# id serial primary key postgres(# ,accounting_date date postgres(# ); CREATE TABLE postgres=# insert into data_tab (accounting_date) values postgres-# (date '2014-09-01'), (date '2015-01-31'), (date '2015-04-22'); INSERT 0 3 |
Шаг 1. Создание секций
В качестве ключа секционирования будем использовать столбец accounting_date.
Для каждой секции выполняем три действия:
1. Создание таблицы по образу и подобию таблицы data_tab
2. Включаем наследование для секции
3. Добавляем ограничение CHECK
postgres=# create table data_tab_2014 (like data_tab including all); CREATE TABLE postgres=# alter table data_tab_2014 inherit data_tab; ALTER TABLE postgres=# alter table data_tab_2014 add constraint check_date postgres-# check (accounting_date between date '2014-01-01' and date '2014-12-31'); ALTER TABLE postgres=# postgres=# create table data_tab_2015q1 (like data_tab including all); CREATE TABLE postgres=# alter table data_tab_2015q1 inherit data_tab; ALTER TABLE postgres=# alter table data_tab_2015q1 add constraint check_date postgres-# check (accounting_date between date '2015-01-01' and date '2015-03-31'); ALTER TABLE postgres=# postgres=# create table data_tab_2015m4 (like data_tab including all); CREATE TABLE postgres=# alter table data_tab_2015m4 inherit data_tab; ALTER TABLE postgres=# alter table data_tab_2015m4 add constraint check_date postgres-# check (accounting_date between date '2015-04-01' and date '2015-04-30'); ALTER TABLE |
Секции не обязаны быть одинакового диапазона. В нашем примере три секции, одна на весь 2014 год, другая на первый квартал 2015 года и последняя на апрель 2015 года. Важно лишь, чтобы ограничения CHECK не пересекались по диапазонам. Пересекаться они конечно могут, например, можно добавить секцию для второго квартала 2015 года. Но где тогда должны храниться записи для апреля 2015?
Проверим, что запросы уже используют секционирование:
postgres=# explain select * from data_tab where accounting_date = '2014-05-30'::date; QUERY PLAN --------------------------------------------------------------------- Append (cost=0.00..40.42 rows=12 width=8) -> Seq Scan on data_tab (cost=0.00..3.68 rows=1 width=8) Filter: (accounting_date = '2014-05-30'::date) -> Seq Scan on data_tab_2014 (cost=0.00..36.75 rows=11 width=8) Filter: (accounting_date = '2014-05-30'::date) (5 rows) |
Видим, что запрос, как и положено, помимо самой таблицы data_tab еще сканирует data_tab_2014. Остальные секции исключены по ограничению CHECK. Правда записи за 2014 год пока находятся в родительской таблице data_tab, а не data_tab_2014, но не все сразу.
Шаг 2. Триггер на INSERT
Теперь добавим строчный before триггер на таблицу data_tab
postgres=# create function data_tab_ins () returns trigger as $$ postgres$# begin postgres$# if new.accounting_date between date '2014-01-01' and date '2014-12-31' postgres$# then postgres$# insert into data_tab_2014 select new.*; postgres$# elsif new.accounting_date between date '2015-01-01' and date '2015-03-31' postgres$# then postgres$# insert into data_tab_2015q1 select new.*; postgres$# elsif new.accounting_date between date '2015-04-01' and date '2015-04-30' postgres$# then postgres$# insert into data_tab_2015m4 select new.*; postgres$# end if; postgres$# return null; postgres$# end; $$ language plpgsql; CREATE FUNCTION postgres=# create trigger data_tab_partition_ins postgres-# before insert on data_tab postgres-# for each row postgres-# execute procedure data_tab_ins(); CREATE TRIGGER |
Проверяем работу триггера. Вставим еще три записи, которые должны лечь каждая в свою секцию.
postgres=# insert into data_tab (accounting_date) values postgres-# (date '2014-07-05'), (date '2015-02-23'), (date '2015-04-12'); INSERT 0 0 Смотрим, что получилось: postgres=# select * from data_tab order by accounting_date; id | accounting_date ----+----------------- 4 | 2014-07-05 1 | 2014-09-01 2 | 2015-01-31 5 | 2015-02-23 6 | 2015-04-12 3 | 2015-04-22 (6 rows) postgres=# select * from only data_tab order by accounting_date; id | accounting_date ----+----------------- 1 | 2014-09-01 2 | 2015-01-31 3 | 2015-04-22 (3 rows) |
Отлично. Теперь все новые записи будут попадать в нужные секции. Осталось перенести записи из родительской таблицы по нужным секциям.
Шаг 3. Перенос данных
Делать это нужно обычными средствами: удалять записи из родительской таблицы и вставлять в дочерние. Никаких волшебных средств, автоматически переносящих данные в нужные секции, не существует. И здесь есть две проблемы. Во-первых, это может занять много времени. Во-вторых, чтобы обеспечить целостность данных, удаление и вставка должны выполняться в рамках одной транзакции.
Вот как это можно сделать. У нас три секции, соответственно, нужно в каждую из них перенести свои строки:
postgres=# with del as (delete from only data_tab postgres(# where accounting_date between date '2014-01-01' postgres(# and date '2014-12-31' postgres(# returning *) postgres-# insert into data_tab_2014 select * from del; INSERT 0 1 postgres=# with del as (delete from only data_tab postgres(# where accounting_date between date '2015-01-01' postgres(# and date '2015-03-31' postgres(# returning *) postgres-# insert into data_tab_2015q1 select * from del; INSERT 0 1 postgres=# with del as (delete from only data_tab postgres(# where accounting_date between date '2015-04-01' postgres(# and date '2015-04-30' postgres(# returning *) postgres-# insert into data_tab_2015m4 select * from del; INSERT 0 1 |
Проверяем:
postgres=# select * from only data_tab order by accounting_date; id | accounting_date ----+----------------- (0 rows) postgres=# select * from data_tab order by accounting_date; id | accounting_date ----+----------------- 4 | 2014-07-05 1 | 2014-09-01 2 | 2015-01-31 5 | 2015-02-23 6 | 2015-04-12 3 | 2015-04-22 (6 rows) |
Родительская таблица теперь пустая, а все данные разложены по секциям.
В нашем случае, записей в родительской таблице очень мало, поэтому достаточно использовать одну команду для каждой секции. В реальной жизни так не получится, нужно планировать запуск with … delete … insert порциями в отдельных транзакциях.
Но, что принципиально важно, перенос хоть и может выполняться долго, но не требует остановки работы приложения.
Информация о количестве секций
Получить информацию о количестве секций, можно запросив описание родительской таблицы в psql:
postgres=# \d+ data_tab Table "public.data_tab" ... Indexes: "data_tab_pkey" PRIMARY KEY, btree (id) Triggers: data_tab_partition_ins BEFORE INSERT ON data_tab FOR EACH ROW EXECUTE PROCEDURE data_tab_ins() Child tables: data_tab_2014, data_tab_2015m4, data_tab_2015q1 |
Здесь описание столбцов таблицы вырезано и заменено на …
Добавление новой секции
Для добавления новой секции нужно выполнить следующие действия:
- Создать дочернюю таблицу
- Создать для этой таблицы ограничение CHECK
- Обновить триггер на INSERT в родительской таблице
При секционировании по диапазонам значений, эти действия можно выполнять заранее. В нашем примере, секции по accounting_date можно создавать в конце каждого года и на весь следующий год.
Исключение секции и последующее восстановление
Для отключения секции, нужно просто отключить наследование. Например, нам больше не нужны данные за 2014 год:
postgres=# alter table data_tab_2014 no inherit data_tab; ALTER TABLE |
Теперь таблицу data_tab_2014 можно заархивировать и вообще удалить из системы. Нужно ли при этом менять триггер, чтобы удалить код, связанной со вставкой в data_tab_2014, вопрос скорее эстетический.
А если таблица вновь понадобится, то достаточно её восстановить (включая ограничение CHECK) и включить наследование:
postgres=# alter table data_tab_2014 inherit data_tab; ALTER TABLE |
Перенос секции на другой диск
Поскольку каждая секция это самостоятельная таблица, то командой ALTER TABLE … SET TABLESPACE … её можно перенести в другое табличное пространство, расположенное на другом диске.
Разделение секции на несколько частей (split partition)
Иногда может потребоваться разделить секцию на более мелкие, например было разбито по годам, а захотели по месяцам. С этой секцией нужно поступить также, как и при начальной настройке секционирования.
Объединение нескольких секций в одну (merge partitions)
Если секционирование слишком «мелко» нарезано, и требуется оставить из нескольких секций только одну:
1. Выбрать секцию, которая должна остаться
2. Переписать ограничение CHECK для это секции, так чтобы ключ секционирования включал в себя диапазон значений всех секций
3. Обновить строчный before триггер на вставку так, чтобы данные вставлялись только в одну выбранную секцию.
4. Перенести записи из всех ненужных секций в ту, которая остаётся.
5. Убедиться, что лишние секции очищены и их можно удалить.