Секционирование в PostgreSQL. Часть 1
Павел Лузанов, Postgres Professional
Под секционированием (partitioning) обычно понимают разделение одной логической таблицы на отдельные физические части (секции). При этом, для приложений таблица выглядит единой. Разделение проводится по строкам таблицы, на основании значений некоторого столбца, который называют ключ секционирования.
Например, можно разделить таблицу с бухгалтерскими документами по столбцу accounting_date так, чтобы документы за разные годы(кварталы, месяцы, ...) хранились в отдельных секциях. Это позволит значительно ускорить доступ к таблице, т. к. большинство запросов будет обращаться только к секции текущего (последнего) периода. Дополнительный плюс секционирования в том, что данные за прошлые периоды можно убрать на медленные носители. Или вообще отключить от таблицы, предварительно сделав копию, а при необходимости обратно подключить.
Рассмотренный вариант разделения на секции по accounting_date называется секционированием по диапазонам значений. Другой вариант — секционирование по списку значений. Следующий пример использует именно секционирование по списку значений.
Начинаем
Нам потребуется таблица со следующей структурой и одной записью:
postgres=# create table city_msk ( postgres(# id serial postgres(# ,city text postgres(# ,constraint check_city CHECK (city = 'MSK') postgres(# ); CREATE TABLE postgres=# insert into city_msk (city) values ('MSK'); INSERT 0 1 |
Отметим ограничение целостности CHECK. Немного странно выглядит столбец city, с учетом того, что в него можно вставить только одно значение. Но эта заготовка под будущее секционирование по списку городов.
Теперь смотрим на план следующего запроса:
postgres=# explain select * from city_msk where city = 'SPB'; QUERY PLAN ---------------------------------------------------------- Seq Scan on city_msk (cost=0.00..25.38 rows=6 width=36) Filter: (city = 'SPB'::text) (2 rows) |
Планировщик выбрал последовательное сканирование таблицы. Также можно заметить, что условие в запросе (where city = 'SPB') никогда не даст истину, ведь ограничение проверки гарантирует нам наличие только значений 'MSK'. Т.е. планировщик мог бы в таблицу не смотреть вообще, а сразу выдать, что записи, удовлетворяющие условию запроса отсутствуют.
И такая возможность есть. Можно проинструктировать планировщик учитывать имеющиеся ограничения проверки при построении плана выполнения команды. Это делается установкой конфигурационного параметра constraint_exclusion в значение on:
postgres=# set constraint_exclusion to on; SET postgres=# show constraint_exclusion; constraint_exclusion ---------------------- on (1 row) |
Еще раз тот же запрос:
postgres=# explain select * from city_msk where city = 'SPB'; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) One-Time Filter: false (2 rows) |
Мы видим, что теперь планировщик не смотрит в таблицу, а сразу возвращает ложь.
В дальнейшем, параметр constraint_exclusion будет рассмотрен подробнее.
Секционирование на основе представления
Создадим вторую таблицу, теперь для Санкт-Петербурга:
postgres=# create table city_spb ( postgres(# id serial postgres(# ,city text postgres(# ,constraint check_city CHECK (city = 'SPB') postgres(# ); CREATE TABLE postgres=# insert into city_spb (city) values ('SPB'); INSERT 0 1 |
Для удобства использования, можно создать представление, которое объединяет записи обеих таблиц:
postgres=# create view cities_view (id, city) postgres-# as postgres-# select id, city from city_msk postgres-# union all postgres-# select id, city from city_spb; CREATE VIEW |
Смотрим на работу планировщика при доступе к данным разных городов:
postgres=# explain select * from cities_view where city = 'SPB'; QUERY PLAN ---------------------------------------------------------------- Append (cost=0.00..25.38 rows=6 width=36) -> Seq Scan on city_spb (cost=0.00..25.38 rows=6 width=36) Filter: (city = 'SPB'::text) (3 rows) postgres=# explain select * from cities_view where city = 'MSK'; QUERY PLAN ---------------------------------------------------------------- Append (cost=0.00..25.38 rows=6 width=36) -> Seq Scan on city_msk (cost=0.00..25.38 rows=6 width=36) Filter: (city = 'MSK'::text) (3 rows) postgres=# explain select * from cities_view where city = 'NSK'; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) One-Time Filter: false (2 rows) |
Сканируется только та таблица, ограничение CHECK для которой, не противоречит условию запроса.
Таким образом, можно реализовать вариант секционирования для команд SELECT. Для вставки, изменения и удаления нужно создавать на представление триггер INSTEAD OF, что уже не является тривиальной задачей. Но есть и другой путь.
Наследование
PostgreSQL поддерживает наследование для таблиц. При наследовании, дочерние таблицы содержат все столбцы родительской таблицы. Также важно, что все запросы к родительской таблице выбирают данные не только из родительской, но и из всех дочерних таблиц. Причем под запросами понимаются не только команды SELECT, но и DELETE, UPDATE, а также TRUNCATE. Ведь при удалении и изменении, сначала нужно отобрать записи удовлетворяющие условию запроса.
Дополним наш пример таблицей cities. А таблицы city_msk и city_spb сделаем дочерними по отношению к ней. Также создадим новую (тоже дочернюю) таблицу city_nsk для Новосибирска и убедимся, что у нее точно такой же набор столбцов, что и у родительской таблицы, хотя при создании они не указаны:
postgres=# create table cities ( postgres(# id serial postgres(# ,city text postgres(# ); CREATE TABLE postgres=# alter table city_msk inherit cities; ALTER TABLE postgres=# alter table city_spb inherit cities; ALTER TABLE postgres=# create table city_nsk() inherits (cities); CREATE TABLE postgres=# insert into city_nsk (city) values ('NSK'); INSERT 0 1 postgres=# \d city_nsk Table "public.city_nsk" Column | Type | Modifiers --------+---------+----------------------------------------------------- id | integer | not null default nextval('cities_id_seq'::regclass) city | text | Inherits: cities |
Проверим, что запрос к таблице cities возвращает данные из всех дочерних таблиц:
postgres=# select * from cities; id | city ----+------ 1 | MSK 1 | SPB 1 | NSK (3 rows) |
Хотя сама таблица cities пустая. Чтобы обратиться только к ней нужно использовать ключевое слово only:
postgres=# select * from only cities; id | city ----+------ (0 rows) |
Секционирование через наследование
Смотрим на планы запросов при обращении к родительской таблице по ключу секционирования:
postgres=# explain select * from cities where city = 'SPB'; QUERY PLAN ---------------------------------------------------------------- Append (cost=0.00..50.75 rows=13 width=36) -> Seq Scan on cities (cost=0.00..0.00 rows=1 width=36) Filter: (city = 'SPB'::text) -> Seq Scan on city_spb (cost=0.00..25.38 rows=6 width=36) Filter: (city = 'SPB'::text) -> Seq Scan on city_nsk (cost=0.00..25.38 rows=6 width=36) Filter: (city = 'SPB'::text) (7 rows) |
Видим сканирование родительской таблицы cities, сканирование таблицы city_spb (что логично), таблица city_msk пропущена, но зачем-то еще сканируется таблица city_nsk, которую можно было бы и пропустить. Но ведь при создании таблицы city_nsk не было создано ограничение проверки. Добавим его и проверим еще раз:
postgres=# alter table city_nsk add constraint check_city check (city = 'NSK'); ALTER TABLE postgres=# explain delete from cities where city = 'SPB'; QUERY PLAN --------------------------------------------------------------- Delete on cities (cost=0.00..25.38 rows=7 width=6) -> Seq Scan on cities (cost=0.00..0.00 rows=1 width=6) Filter: (city = 'SPB'::text) -> Seq Scan on city_spb (cost=0.00..25.38 rows=6 width=6) Filter: (city = 'SPB'::text) (5 rows) |
Теперь все работает как нужно. Сканируется пустая таблица cities (это не дорогая операция), а также таблица city_spb, т. к. запрошены данные только по ней. Отметим и то, что вместо SELECT используется DELETE и это не смущает планировщик.
Параметр конфигурации constraint_exclusion
Вернемся к параметру constraint_exclusion и установим его в значение по умолчанию:
postgres=# reset constraint_exclusion; RESET postgres=# show constraint_exclusion; constraint_exclusion ---------------------- partition (1 row) |
Посмотрим как это отражается на работе с таблицей cities:
postgres=# explain update cities set id = 1 where city = 'NSK'; QUERY PLAN ---------------------------------------------------------------- Update on cities (cost=0.00..25.38 rows=7 width=38) -> Seq Scan on cities (cost=0.00..0.00 rows=1 width=38) Filter: (city = 'NSK'::text) -> Seq Scan on city_nsk (cost=0.00..25.38 rows=6 width=38) Filter: (city = 'NSK'::text) (5 rows) |
Секционирование (теперь уже с UPDATE) работает.
Кстати, секционирование работает и для нашего представления:
postgres=# explain select * from cities_view where city = 'SPB'; QUERY PLAN ---------------------------------------------------------------- Append (cost=0.00..25.38 rows=6 width=36) -> Seq Scan on city_spb (cost=0.00..25.38 rows=6 width=36) Filter: (city = 'SPB'::text) (3 rows) |
Для constraint_exclusion предусмотрено три значения: partition, on, off.
- Значение по умолчанию partition заставляет планировщик проверять ограничения целостности CHECK для дочерних таблиц, а также для таблиц, используемых в запросах с полным объединением (UNION ALL).
- Значение on заставляет делать эту проверку для всех таблиц, что будет увеличивать время работы планировщика на простых запросах.
- При значении off, как не трудно догадаться, проверка не проводится.
Вставка записей
Итак, наследование, ограничения check и параметр constraint_exclusion=partiton позволяют организовать функциональность секционирования для команд SELECT, UPDATE, DELETE.
С INSERT сложнее. Либо мы вставляем записи сразу в нужную секцию (дочернюю таблицу), что не всегда удобно. Либо необходим триггер на родительскую таблицу, в котором вставка перенаправляется в нужную секцию.
Пример триггера:
postgres=# create function city_ins () returns trigger as $$ postgres$# begin postgres$# if new.city = 'MSK' then postgres$# insert into city_msk select new.*; postgres$# elsif new.city = 'SPB' then postgres$# insert into city_spb select new.*; postgres$# elsif new.city = 'NSK' then postgres$# insert into city_nsk select new.*; postgres$# end if; postgres$# return null; postgres$# end; $$ language plpgsql; CREATE FUNCTION postgres=# create trigger city_partition_ins postgres-# before insert on cities postgres-# for each row postgres-# execute procedure city_ins(); CREATE TRIGGER |
Проверяем работу триггера на примере вставки записи для Новосибирска и смотрим, что запись добавлена именно в таблицу city_nsk:
postgres=# insert into cities (city) values ('NSK'); INSERT 0 0 postgres=# select * from city_nsk; id | city ----+------ 1 | NSK 2 | NSK (2 rows) |
Особенности реализации секционирования
Не смотря на то, что запись добавлена, ответ команды INSERT немного смущает. Вставлено 0 записей. Это происходит потому, что в таблицу cities действительно ничего вставлять не нужно, а для этого строчный before триггер (после вставки в одну из секций) должен вернуть null. По этой же причине для команды INSERT INTO cities не будет работать фраза RETURNING.
Если возможно обновление ключа секционирования, например заменить 'MSK' на 'NSK' у ряда записей, то необходим триггер на update. В таком триггере нужно будет удалять запись из таблицы city_msk и вставлять в city_nsk.
Для полноты картины отметим еще ряд особенностей:
- За своевременным появлением новых секций нужно следить, в PostgreSQL нет специальных возможностей по их автоматическому созданию.
- Индексы на дочерние таблицы можно создавать без ограничений. Однако сделать один общий (глобальный) индекс на все секции невозможно, ведь это отдельные таблицы.
- Нельзя сделать ограничение первичный и/или уникальный ключ на секционированную таблицу. По той же причине. Ведь у нас не одна, а несколько таблиц. Хотя логической уникальности можно добиться, если к первичному/уникальному ключу каждой секции добавить столбец с ключом секционирования.
- Раз нет первичного/уникального ключей, значит нельзя сделать внешний ключ на секционированную таблицу.
- Нужно следить за тем, чтобы ограничения CHECK на дочерних таблицах были взаимоисключающими. Это скорее относится к секционированию по диапазонам (дат, чисел), чем к секционированию по списку значений.
- Планировщик уже при построении плана запроса должен знать значения для ключа секционирования. Поэтому фраза WHERE запроса, как правило, должна содержать константы. К примеру, нельзя использовать функции, не объявленные как IMMUTABLE.
Итоги
Мы рассмотрели общие подходы к организации секционирования в PostgreSQL. Механизм наследования, ограничения CHECK на дочерних таблицах, строчный before триггер для вставки записей на родительской таблице и параметр конфигурации constraint_exclusion — вот основные составляющие реализации.
В управлении задачами секционирования может помочь расширение pg_partman.
Во второй части статьи мы рассмотрим некоторые практические примеры работы с секционированными таблицами.