Многие статьи на тему секционирования заканчиваются «и теперь у вас есть партиции». А что дальше — когда нужно обновить данные или отсоединить секцию?
Причины секционирования таблиц
Секционирование — разбиение строк, логически относящихся к одной большой таблице, на более мелкие физические части, называемые секциями. Для приложения (на логическом уровне: для команд SQL SELECT/INSERT/DELETE) секционированная таблица неотличима от обычной таблицы. Прозрачность для приложений (за исключением перемещения строки в другую секцию при UPDATE) — основное преимущество секционирования, по сравнению с другими способами, например, созданием множества таблиц или материализованных представлений. Секционирование используется, чтобы:
1) обойти ограничения PostgreSQL по объему хранимых данных
2) упростить администрирование: команды DDL над меньшими (по размеру) объектами выполняются быстрее.
Секционирование есть в СУБД других производителей, логика и способы управления секционированными таблицами просты в изучении и использовании. Даже, если ограничения не достигаются, секционирование используют для упрощения администрирования хранения данных в таблицах. Секционирование можно рассматривать для таблиц, размер которых сравним с размером кэша буферов PostgreSQL.
Польза секционирования моет быть, например, в быстром удалении большого числа строк. Если строки хранятся в таблице , то удаление строк долгое — обновление по одной индексной записи, построчное удаление строк в блоках таблиц. При использовании секций, удаление содержимого секции выполняется на уровне объекта, если удачно выбраны ключевые столбцы, по которым строки распределяются по секциям и тип секционирования.
При удачном распределении строк по секциям, планировщик может исключать из сканирования целые секции, что ускоряет выполнение запросов, а также использовать Seq Scan по строкам, хранящимся в секциях, вместо индексного поиска по большому числу блоков, где хранятся все строки таблицы.
Компактность хранения строк в секциях повышает вероятность того, что востребованные данные (секции таблицы и их индексы) будут загружены в буферный или страничный кэш.
Можно быстро добавить строки в секционированную таблицу: подготовив строки в обычной таблице и, прицепив эту таблицу, как секцию (ATTACH PARTITION).
Обычно, данные, через какое-то время, теряют актуальность и доступ к ним становится менее активным. Секции с наименее актуальными данными можно выводить из таблицы или переносить в табличные пространства с меньшей стоимостью хранения или даже перегружать в другие базы данных и, затем, использовать как внешние таблицы.
Ошибка при смене строкой секции
Преимущество секционирования в прозрачности для приложения: можно секционировать таблицы и код приложения продолжит работать. Но есть случай, когда работа команд меняется и появляется ошибка при выполнении UPDATE или DELETE, если строка была перемещена в другую секцию параллельно выполнявшейся командой.
Если транзакция перемещает строку в другую секцию, а во время выполнения транзакции вторая транзакция (или одиночная команда) попытается обновить или удалить перемещаемую строку, то вторая транзакция получит ошибку.
Пример (номера транзакций 1 и 2):
1 begin;
1 update t set id = 12, reg = 12 where id = 2;
2 update t set id = 12, reg = 12 where id = 2;
1 commit;
2 ERROR: tuple to be locked was already moved to another partition due to concurrent update
Если бы таблица была несекционированной:
drop table if exists t;
create table t (id bigint GENERATED BY DEFAULT AS IDENTITY, reg int, primary key (id, reg));
insert into t (reg) select generate_series(1,2);
то вторая транзакция не получила бы сообщение, что команда успешно выполнена и обновлено ноль строк:UPDATE 0
После снятия блокировки команда перечитывает строку и видит, что она уже не подходит под условие where. Если первая транзакция откатится, то команда обновит одну строку и с секционированной и обычной таблицей.
Проблема в том, что если UPDATE или DELETE меняют более, чем одну строку, то с секционированной таблицей сбойнёт вся команда. Из-за этого внедрение секционирования не полностью прозрачно для приложения.
Чтобы избежать ошибки либо приложение не должно менять значения в столбцах секционирования или нужно изменить логику приложения: использовать перед UPDATE и DELETE команду SELECT FOR UPDATE SKIP LOCKED; а это усложнит код приложения.
Аналогичная ошибка будет при шардинге, если строка переместится в другой шард.
Преобразование таблицы в секционированную
Команды конвертации ALTER TABLE несекционированной таблицы в секционированную нет. Если нужно сделать из несекционированной секционированную, то придётся это делать вручную по шагам:
1) Добавить ограничение CHECK с опцией NOT VALID, соответствующее строкам таблицы и будущему условию FOR VALUES секции, в виде которой эта таблица будет подсоединена к будущей секционированной таблице. Не забыть вставить в условие NOT NULL, если в списке значений секции оно будет отсутствовать или секция будет RANGE или HASH.
2) Валидировать добавленные ограничения CHECK. Смысл разнести добавление и валидацию в том, что на время проверки устанавливается не эксклюзиваная блокировка, а более слабая блокировка SHARE UPDATE EXCLUSIVE. Более того, одной командой можно валидировать сразу несколько ограничений типов CHECK, NOT NULL, FOREIGN KEY.
3) Если в новой таблице будет другой первичный ключ, то удалить первичный ключ с опцией CASCADE и добавить новый. Перед добавлением нового ключа можно заранее создать для него уникальный индекс в режиме CONCURRENTLY. Важно, чтобы набор столбцов и их порядок следования в ключе и уникальном индексе были такими же, как в секционированной таблице.
4) Открыть транзакцию
5) Переименовать таблицу в название секции. Таблица будет заблокирована.
6) Сохранить значение автоинкрементальных столбцов и удалить на столбцах секции свойство IDENTITY, если оно есть
7) Переименовать ограничения целостности, в соответствии с именем секции
8) Создать секционированную таблицу с именем исходной таблицы. Переустановить значение последовательности для автоинкрементальных столбцов.
9) Присоединить переименованную таблицу, как секцию
10) Зафиксировать транзакцию
11) Если на таблице были внешние ключи или они нужны, то добавить их. Если первичный ключ был изменён, то внешние ключи тоже придётся изменить.
Добавленное ограничение целостности CHECK может остаться на секции, его можно удалить. В начале транзакции можно установить таймаут: setstatement_timeout = ‘5s’; чтобы защититься от того, что какая-то команда будет выполняться долго из-за того, что что-то не было учтено и выполняется долгая проверка или создаётся индекс, при том, что таблица заблокирована. Таймаут позволит прервать выполнение и исследовать проблему. Пример команд:
drop table if exists t;create table t (id bigint GENERATED BY DEFAULT AS IDENTITY, reg int, primary key (id, reg));alter table t ADD CONSTRAINT t1_id_check CHECK (id IS NOT NULL AND id >= '1'::bigint AND id < '10'::bigint) NOT VALID;alter table t VALIDATE CONSTRAINT t1_id_check; begin; set statement_timeout = '5s'; alter table t rename to t1;alter table t1 rename constraint t_pkey to t1_pkey;alter table t1 rename constraint t_id_not_null to t1_id_not_null;alter table t1 rename constraint t_reg_not_null to t1_reg_not_null;alter table t1 ALTER COLUMN id DROP IDENTITY;create table t (id bigint GENERATED BY DEFAULT AS IDENTITY, reg int, primary key (id, reg)) PARTITION BY RANGE (id); alter table t ALTER COLUMN id RESTART WITH 3;alter table t ATTACH PARTITION t1 FOR VALUES FROM (1) TO (10);commit;
Отладочные сообщения
При правильном выражении ограничения CHECK, при подсоединении секции, проверка строк в секции не будет выполняться. Это можно определить по скорости подсоединения. Также можно определить по отладочным сообщениям:
set client_min_messages = debug1;
Пример сообщения о проверке строк:
alter table t ADD CONSTRAINT t1_id_check CHECK (id >= 1 AND id < 10);
DEBUG: verifying table "t"
Отладочные сообщения не идеальны: по сообщению не виден уровень блокировки и не видно преимущество добавить ограничение NOT VALID и валидировать отдельной командой.
Пример сообщения, когда проверка не выполняется (достаточно ограничений):
alter table t ATTACH PARTITION t1 FOR VALUES FROM (1) TO (10);
DEBUG: partition constraint for table "t1" is implied by existing constraints
Если поменять ограничение на следующее:
alter table t ADD CONSTRAINT t1_id_check CHECK (id >= 1 AND id < 100);
DEBUG: verifying table "t"
то при присоединении секции будет вторая проверка:
alter table t ATTACH PARTITION t1 FOR VALUES FROM (1) TO (10);
DEBUG: verifying table "t"
Пример сообщений, если на будущей секции не будет создано уникального индекса или первичного ключа:
create table t (id bigint NOT NULL, reg int NOT NULL);
то при подсоединении секции, на неё будет создан уникальный индекс. В секции много данных и индекс будет создаваться долго, на время создания таблица будет заблокирована для изменений строк, так как индекс создаётся без CONCURRENTLY:
alter table t ATTACH PARTITION t1 FOR VALUES FROM (1) TO (10);
DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
DEBUG: building index "t1_pkey" on table "t1" serially
DEBUG: index "t1_pkey" can safely use deduplication
Замена первичного ключа
С маленькими объемами данных секционирование не используется. С большими объемами данных долгие блокировки нежелательны, поэтому используются более сложные процедуры замены первичного ключа и добавления ограничений целостности.
Если в новой таблице будет другой первичный ключ, то удалить первичный ключ с опцией CASCADE и добавить новый. Перед добавлением нового ключа можно заранее создать для него уникальный индекс в режиме CONCURRENTLY:
create UNIQUE index CONCURRENTLY t1_pkey ON t(id, reg);
Важно, чтобы набор столбцов и их порядок следования в ключе и уникальном индексе были такими же, как в секционированной таблице. Другой первичный ключ может понадобиться из-за того, что первичный ключ должен включать столбцы ключа секционирования. У таблицы может быть только один первичный ключ. Если понадобится в первичный ключ добавить столбец ключа секционирования, то придётся удалить старый первичный ключ и добавить новый:
alter table t drop constraint t_pkey;
alter table t add constraint t1_pkey primary key using index t1_pkey;
При этом будет удалён старый уникальный индекс и создан новый уникальный индекс. Создание индекса занимает время.
Так как первичный ключ кроме уникальности, которая физически обеспечивается уникальным индексом, добавляет ещё и NOT NULL на все свои столбцы, то стоит заранее добавить ограничение NOT NULL в режиме NOT NULL на столбцы, которые должны войти в первичный ключ и потом валидировать это ограничение:
ALTER TABLE t ADD CONSTRAINT t1_reg_not_null NOT NULL reg NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT t1_reg_not_null;
Названия ограничений и индекса, которые приведены в командах, приведены в предположении, что таких объектов ещё нет. Если имена уже используются, то они должны быть другими.
Если на старый первичный ключ ссылались внешние ключи, то их придётся удалить и создать новые. Но, внешние ключи создаются на уникальные и первичные и должны иметь тот же набор столбцов. Если в таблице, на которой был внешний ключ нет столбца reg, то придётся его добавить, либо не создавать внешний ключ. Пример последовательности команд:
drop table if exists t;create table t(id bigint GENERATED BY DEFAULT AS IDENTITY,reg int,primary key (id));ALTER TABLE t ADD CONSTRAINT t1_reg_not_null NOT NULL reg NOT VALID; alter table t ADD CONSTRAINT t1_id_check CHECK (id IS NOT NULL AND id >= '1'::bigint AND id < '10'::bigint) NOT VALID;ALTER TABLE t VALIDATE CONSTRAINT t1_reg_not_null;alter table t VALIDATE CONSTRAINT t1_id_check;create UNIQUE index CONCURRENTLY t1_pkey ON t(id, reg);begin;alter table t drop constraint t_pkey;alter table t add constraint t1_pkey primary key using index t1_pkey;alter table t rename to t1;alter table t1 rename constraint t_id_not_null to t1_id_not_null;alter table t1 ALTER COLUMN id DROP IDENTITY;create table t (id bigint GENERATED BY DEFAULT AS IDENTITY, reg int, primary key (id, reg)) PARTITION BY RANGE (id); alter table t ALTER COLUMN id RESTART WITH 3; alter table t ATTACH PARTITION t1 FOR VALUES FROM (1) TO (10);commit;
Присоединение секций
Команда присоединения существующей таблицы как секции:
ALTER TABLE имя ATTACH PARTITION секция {FOR VALUES границы | DEFAULT};
Присоединяемая секция может быть секционированной таблицей (иметь секции).
Таблицу можно присоединить в качестве секции по умолчанию (DEFAULT).
Для каждого индекса (кроме индексов ONLY), который определён на родительской в таблице, в присоединяемой таблице будет создан индекс. Если аналогичный индекс уже есть, то он будет присоединён к родительскому индексу.
На родительской таблице могут существовать индексы с опцией ONLY, созданные командой:
CREATE [UNIQUE] INDEX индекс ON ONLY таблица;
После присоединения секции, к такому родительскому индексу можно присоединить заранее созданный индекс секции командой ALTER INDEX индекс_родителя ATTACH PARTITION индекс_секции.
Если присоединяется внешняя таблица (FOREIGN), то её нельзя присоединить к таблице, на которой есть уникальный индекс.
Для каждого триггера FOR EACH ROW родительской таблицы, будет создан такой же триггер на присоединяемой таблице.
Присоединяемая таблица:
1) Должна иметь те же столбцы, что и родительская и никаких других; более того, типы столбцов должны совпадать.
2) Должны быть те же ограничения NOT NULL и CHECK, что и в родительской таблице (кроме ограничений, которые в родительской таблице помечены как NO INHERIT). Ограничения FOREIGN KEY не учитываются и не проверяются.
3) В присоединяемой таблице будут созданы ограничения целостности PRIMARY KEY и UNIQUE, как на родительской таблице, если их ещё нет. Если уже есть, то индексы этих ограничений будут присоединены к родительскому индексу.
Одновременно можно присоединять только одну секцию. Команда присоединения секции не комбинируется с другими командами ALTER TABLE.
Ускорение присоединения секции
Если на подсоединяемой таблице есть ограничение CHECK, соответствующее условию FOR VALUES, то при подсоединении секции, проверка строк в секции на соответствие выражению FOR VALUES команды присоединения не будет проводиться. Лучше это проверить на тестовых данных, так как гарантировать, что условие в ограничении будет сопоставлено с выражением нет.
После подсоединения секции ограничение CHECK может быть автоматически удалено, но нет гарантий: это происходит не всегда.
Добавлять ограничение как NOT VALID, затем валидировать отдельной командой. Если добавлять с проверкой, то на время проверки таблица будет заблокирована в режиме ACCESS EXCLUSIVE так, что с ней не сможет работать ни одна команда.
Структура первичных и уникальных ключей должна полностью соответствовать той таблице, к которой выполняется подсоединение.
Вместо ключей, до подсоединения можно создать уникальные индексы, столбцы которых соответствуют уникальным индексам таблицы, к которой выполняется подсоединение. Тогда индексы смогут встроиться в иерархию наследования индексов. Если будут различия, то будут создаваться индексы, что увеличит время выполнения команды подсоединения секции.
Так как объемы могут быть большими, то лучше проверить процедуру присоединения на тестовых данных. Присоединение должно выполняться моментально, сразу после получения блокировок. Если длительность подсоединения зависит от объема данных, то это значит, что выполняется проверка данных или создание индексов, а этого можно избежать.
Присоединение секций с подсекциями. Опасность отсоединения секций c CONCURRENTLY
В присоединенную секцию добавляются правила generated by default as identity, которые есть на столбцах родительской таблицы. Если присоединяемая таблица имеет подсекции, то в подсекции правило IDENTITY не добавляется.
Добавить это правило нельзя:
ALTER TABLE t21 ALTER COLUMN id ADD generated by default as identity;
ERROR: cannot add identity to a column of a partition
Отсутствие правила создаёт проблемы — нельзя выполнять команды:
ALTER TABLE t ALTER COLUMN id SET GENERATED BY DEFAULT RESTART;
ERROR: column "id" of relation "t31" is not an identity column
А при использовании CONCURRENTLY таблица переходит в неработоспособное состояние:
ALTER TABLE t DETACH PARTITION t3 CONCURRENTLY;
ERROR: column «id» of relation «t31» is not an identity column
ALTER TABLE t DETACH PARTITION t3;
ERROR: cannot detach partition «t3»
DETAIL: The partition is being detached concurrently or has an unfinished detach.
HINT: Use ALTER TABLE … DETACH PARTITION … FINALIZE to complete the pending detach operation.
ALTER TABLE t DETACH PARTITION t3 FINALIZE;
ERROR: column «id» of relation «t31» is not an identity column
ALTER TABLE t ALTER COLUMN id drop identity;
ALTER TABLE t3 ALTER COLUMN id drop identity;
ERROR: cannot alter partition «t3» with an incomplete detach
Чтобы добавить правило и устранить проблему (но не проблему с невозможностью выполнить FINALIZE, с которой придётся удалить подсекцию), придется отсоединить все подсекции и подсоединить их заново:
ALTER TABLE t2 DETACH PARTITION t21; ALTER TABLE t2 DETACH PARTITION t22;
ALTER TABLE t2 ATTACH PARTITION t21 FOR VALUES IN (10,11,12);
ALTER TABLE t2 ATTACH PARTITION t22 FOR VALUES IN (13,14);
После этого ветвь t2, t21, t22 будет подсоединена так же, какой она была до отсоединения. После усечения (TRUNCATE) таблицы, последовательность, используемая для генерации значений IDENTITY столбцов, не сбрасывается в начальное значение. Сбросить последовательность в начальное значение можно командой:
ALTER TABLE t ALTER COLUMN id SET GENERATED BY DEFAULT RESTART;
Недостатки столбцов IDENTITY по сравнению с bigserial при секционировании
Столбцы serial/bigserial (заполняемые выражением Default) появились до IDENTITY столбцов. Логика GENERATED столбцов сложнее и, из-за этого, приходится учитывать больше вариантов использования таких столбцов. Большая сложность — больше багов. Скорость работы одинакова, есть несущественные отличия по поддержке этих способов генерации значений. При использовании IDENTITY с подсекциями, в версиях, где баг не исправлен, в том, что GENERATEDASIDENTITYне добавляется в подсекции присоединяемой таблицы:
drop table if exists t;CREATE TABLE t (id bigint GENERATED BY DEFAULT AS IDENTITY, reg int, primary key (id, reg)) PARTITION BY RANGE (id);CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (20) PARTITION BY RANGE(reg);CREATE TABLE t11 PARTITION OF t1 FOR VALUES FROM (1) TO (10);ALTER TABLE t DETACH PARTITION t1 CONCURRENTLY;ALTER TABLE t ATTACH PARTITION t1 FOR VALUES FROM (1) TO (20); Для добавления нужно отсоединять все подсекции и добавлять их отдельно:ALTER TABLE t1 DETACH PARTITION t11; ALTER TABLE t1 ATTACH PARTITION t11 FOR VALUES FROM (1) TO (10);
При использовании serial/bigserial такой проблемы нет, в подсекции добавляется выражение Default. Можно отсоединить секцию, присоединить её и результат будет такой же, как до отсоединения (обратимость команд):
\d t11 Table "public.t11" Column | Type | Nullable | Default --------+--------+----------+--------- id | bigint | not null | nextval('t_id_seq'::regclass)
Использование IDENTITY неудобно тем, что команды ограничены в работе с этими столбцами:
ALTER TABLE t11 ALTER COLUMN id ADD generated by default as identity;
ERROR: cannot add identity to a column of a partition
ALTER TABLE t11 ALTER COLUMN id drop identity;
ERROR: cannot drop identity from a column of a partition
При использовании serial/bigserial таких ограничений нет, управляемость лучше.
Заключение
В статье рассмотрены особенности преобразования несекеционированной таблицы в секционированную. Команды преобразования нет и процедура многошаговая. Приведена последовательность команд для секционирования с минимизацией простоя. Секционирование, обычно, прозрачно для приложения, но есть особенность, при которой при внедрении секционирования появляется ошибка, которой нет при работе с несекционированными таблицами. Приведены примеры, когда отсоединение секции в режиме CONCURRENTLY приведёт к невозможности продолжать работать с таблицей из-за багов; когда GENERATED AS IDENTITY имеет недостатки по сравнению с serial/bigserial.
Ближайшая конференция по PostgreSQL
Особенности использования PostgreSQL описываются в статьях и докладах на конференциях. Ближайшая конференция пройдёт 10 сентября 2026г. в Москве. Кроме докладов, конференции — это площадка для очного общения. На конференции Tantor JAM можно пообщаться с авторами статей хаба PostgreSQL и блога Тантор Лабс. Компания Тантор Лабс приглашает читателей Хабра. Участие в конференции бесплатно.
ссылка на оригинал статьи https://habr.com/ru/articles/1064322/