5.11. Разделение таблиц#
5.11. Разделение таблиц
Tantor SE поддерживает базовое разделение таблиц. В данном разделе описывается, почему и как реализовать разделение в качестве части проектирования вашей базы данных.
5.11.1. Обзор
Разделение относится к разделению логически одной большой таблицы на более мелкие физические части. Разделение может предоставить несколько преимуществ:
Производительность запросов может быть значительно улучшена в определенных ситуациях, особенно когда большая часть часто используемых строк таблицы находится в одном разделе или в небольшом количестве секций. Разделение эффективно заменяет верхние уровни дерева индексов, что делает более вероятным, что часто используемые части индексов помещаются в память.
Когда запросы или обновления обращаются к большому проценту одной раздела, производительность может быть улучшена путем использования последовательного сканирования этого раздела вместо использования индекса, который требовал бы случайного доступа к чтению, разбросанного по всей таблице.
Большие пакетные загрузки и удаления могут быть выполнены путем добавления или удаления секций, если использование учтено при проектировании разделения. Удаление отдельного раздела с использованием команды
DROP TABLEили выполнение командыALTER TABLE DETACH PARTITIONгораздо быстрее, чем массовая операция. Эти команды также полностью устраняют издержки операций очисткиVACUUM, вызванной массовым удалениемDELETE.Редко используемые данные могут быть перенесены на более дешевые и медленные носители данных.
Эти преимущества обычно оправданы только в случае, когда таблица в противном случае была бы очень большой. Точка, с которой таблица начинает получать выгоду от разделения, зависит от приложения, хотя правило гласит, что размер таблицы должен превышать физическую память сервера базы данных.
Tantor SE предлагает встроенную поддержку для следующих форм разделения:
- Range Partitioning
Таблица разделена на “диапазоны”, определенные по ключевому столбцу или набору столбцов, без перекрытия диапазонов значений, назначенных разным разделам. Например, можно разделить по диапазонам дат или по диапазонам идентификаторов для конкретных бизнес-объектов. Границы каждого диапазона понимаются как инклюзивные снизу и исключительные сверху. Например, если диапазон одного раздела - от
1до10, а диапазон следующего - от10до20, то значение10принадлежит второму разделу, а не первому.- List Partitioning
Таблица разделена путем явного перечисления значений ключа(ей), которые присутствуют в каждом разделе.
- Hash Partitioning
Таблица разделена путем указания модуля и остатка для каждого раздела. Каждый раздел будет содержать строки, для которых значение хеша ключа раздела, деленное на указанный модуль, даст указанный остаток.
Если вашему приложению требуется использовать другие формы разделения, не указанные выше, вместо этого можно использовать альтернативные методы, такие как наследование и представления UNION ALL. Такие методы предлагают гибкость, но не обладают некоторыми преимуществами производительности встроенного декларативного разделения.
5.11.2. Декларативное разделение
Tantor SE позволяет объявить, что таблица разделена на секции. Таблица, которая разделена, называется разделенной таблицей. Объявление включает метод разделения, описанный выше, а также список столбцов или выражений, которые будут использоваться в качестве ключа разделения.
Сама разделенная таблица является “виртуальной” таблицей, не имеющей собственного хранилища. Вместо этого хранилище принадлежит секциям, которые являются обычными таблицами, связанными с разделенной таблицей. Каждая секция хранит подмножество данных, определенное его границами секции. Все строки, вставленные в разделенную таблицу, будут направлены в соответствующий раздел на основе значений столбца(ов) ключа раздела. Изменение ключа раздела строки приведет к перемещению ее в другой раздел, если она больше не удовлетворяет границам раздела ее исходного раздела.
секции могут быть определены как сами по себе разделенные таблицы, что приводит к подразделению. Хотя все секции должны иметь те же столбцы, что и их родительская разделенная таблица, секции могут иметь свои собственные индексы, ограничения и значения по умолчанию, отличные от других секций. См. CREATE TABLE для получения более подробной информации о создании разделенных таблиц и секций.
Существует невозможность преобразования обычной таблицы в разделенную таблицу и наоборот. Однако, возможно добавить существующую обычную или разделенную таблицу в качестве раздела разделенной таблицы или удалить раздел из разделенной таблицы, превратив его в самостоятельную таблицу. Это может упростить и ускорить многие процессы обслуживания. См. ALTER TABLE для получения дополнительной информации о подкомандах ATTACH PARTITION и DETACH PARTITION.
Секции могут также быть внешними таблицами, однако требуется значительная осторожность, поскольку тогда пользователь несет ответственность за то, чтобы содержимое внешней таблицы удовлетворяло правилу разделения. Есть и другие ограничения. См. CREATE FOREIGN TABLE для получения дополнительной информации.
5.11.2.1. Пример
Предположим, что мы создаем базу данных для крупной компании по производству мороженого. Компания ежедневно измеряет максимальные температуры и продажи мороженого в каждом регионе. Концептуально, мы хотим создать таблицу, подобную:
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
Мы знаем, что большинство запросов будут обращаться только к данным за последнюю неделю, месяц или квартал, так как основное использование этой таблицы будет подготовка онлайн-отчетов для руководства. Чтобы уменьшить объем старых данных, которые нужно хранить, мы решаем хранить только самые последние 3 года данных. В начале каждого месяца мы будем удалять самые старые данные за месяц. В этой ситуации мы можем использовать разделение, чтобы помочь нам удовлетворить все наши различные требования к таблице измерений.
Для использования декларативного разделения в этом случае выполните следующие шаги:
Создайте таблицу
measurementв виде разделенной таблицы, указав предложениеPARTITION BY, который включает метод разделения (RANGEв данном случае) и список столбцов, которые будут использоваться в качестве ключа разделения.CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate);Создайте секции. Определение каждой секции должно указывать границы, соответствующие методу разделения и ключу секции родительской таблицы. Обратите внимание, что указание границ таким образом, что значения новой секции перекрываются с одним или несколькими существующими секциями, вызовет ошибку.
Созданные таким образом секции являются полностью нормальными таблицами (или, возможно, внешними таблицами) Tantor SE. Возможно указать отдельное табличное пространство и параметры хранения для каждой секции.
Для нашего примера каждое разделение должно содержать данные за один месяц, чтобы соответствовать требованию удаления данных за один месяц за раз. Таким образом, команды могут выглядеть следующим образом:
CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01'); ... CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01'); CREATE TABLE measurement_y2007m12 PARTITION OF measurement FOR VALUES FROM ('2007-12-01') TO ('2008-01-01') TABLESPACE fasttablespace; CREATE TABLE measurement_y2008m01 PARTITION OF measurement FOR VALUES FROM ('2008-01-01') TO ('2008-02-01') WITH (parallel_workers = 4) TABLESPACE fasttablespace;(Помните, что смежные секции могут иметь общее значение границы, так как верхние границы диапазона рассматриваются как исключительные границы).
Если вы хотите реализовать подчастичное разделение, снова укажите
PARTITION BYв командах, используемых для создания отдельных секций, например:CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01') PARTITION BY RANGE (peaktemp);После создания секций
measurement_y2006m02, любые данные, вставленные вmeasurementи отображаемые наmeasurement_y2006m02(или данные, которые непосредственно вставлены вmeasurement_y2006m02, что допускается при условии удовлетворения его ограничения раздела), будут дополнительно перенаправлены в один из его разделов на основе столбцаpeaktemp. Ключ раздела может перекрываться с ключом родительского раздела, хотя следует быть осторожным при указании границ подраздела, чтобы набор данных, который он принимает, составлял подмножество того, что позволяют границы самого раздела; система не пытается проверить, действительно ли это так.Вставка данных в родительскую таблицу, которая не соответствует одной из существующих секций, вызовет ошибку; соответствующий раздел должен быть добавлен вручную.
Необходимо вручную создавать ограничения таблицы, описывающие граничные условия секций. Такие ограничения будут созданы автоматически.
Создайте индекс на ключевом столбце(ах), а также любые другие индексы, которые вы можете захотеть, на разделенной таблице. (Индекс ключа не является строго необходимым, но в большинстве сценариев он полезен). Это автоматически создает соответствующий индекс на каждом разделе, и любые секции, которые вы создадите или присоедините позже, также будут иметь такой индекс. Индекс или уникальное ограничение, объявленное на разделенной таблице, является “виртуальным” так же, как и сама разделенная таблица: фактические данные находятся в дочерних индексах на отдельных разделах таблиц.
CREATE INDEX ON measurement (logdate);
Убедитесь, что параметр конфигурации enable_partition_pruning не отключен в файле
postgresql.conf. Если он отключен, запросы не будут оптимизированы так, как требуется.
В приведенном выше примере мы будем создавать новое разделение каждый месяц, поэтому было бы разумно написать скрипт, который автоматически генерирует необходимую DDL команду.
5.11.2.2. Обслуживание секций
Обычно набор секций, установленных при первоначальном определении таблицы, не предполагается статическим. Часто требуется удалить секции, содержащие старые данные, и периодически добавлять новые секции для новых данных. Одним из самых важных преимуществ разделения является возможность выполнения этой в противном случае трудоемкой задачи практически мгновенно путем манипулирования структурой секций, а не физического перемещения больших объемов данных.
Самый простой вариант удаления старых данных - удалить раздел, который больше не нужен:
DROP TABLE measurement_y2006m02;
Это может очень быстро удалить миллионы записей, потому что он не требует индивидуального удаления каждой записи. Однако обратите внимание, что вышеуказанная команда требует получения ACCESS EXCLUSIVE блокировки на родительской таблице.
Еще один вариант, который часто предпочтительнее, - это удалить раздел из разделенной таблицы, но сохранить доступ к нему как к отдельной таблице. Это имеет две формы:
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02; ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;
Это позволяет выполнить дополнительные операции с данными перед их удалением. Например, это часто полезно для создания резервных копий данных с помощью команды COPY, приложения pg_dump или аналогичных инструментов. Также это может быть полезным моментом для агрегации данных в более компактные форматы, выполнения других манипуляций с данными или запуска отчетов. Первая форма команды требует ACCESS EXCLUSIVE блокировку на родительской таблице. Добавление квалификатора CONCURRENTLY во второй форме позволяет выполнить отсоединение с использованием только блокировки SHARE UPDATE EXCLUSIVE на родительской таблице, но см. ALTER TABLE ... DETACH PARTITION для получения подробной информации о ограничениях.
Аналогично мы можем добавить новое разделение для обработки новых данных. Мы можем создать пустое разделение в разделенной таблице так же, как и оригинальные разделения были созданы выше:
CREATE TABLE measurement_y2008m02 PARTITION OF measurement
FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
TABLESPACE fasttablespace;
В качестве альтернативы, иногда более удобно создать
новую таблицу вне структуры секций, и присоединить ее как
секцию позже. Это позволяет загружать новые данные, проверять их и
преобразовывать до того, как они появятся в партицированной таблице.
Более того, операция ATTACH PARTITION требует
только блокировку SHARE UPDATE EXCLUSIVE на
партицированной таблице, в отличие от блокировки ACCESS
EXCLUSIVE, которая требуется командой CREATE TABLE
... PARTITION OF, поэтому она более дружелюбна к параллельным
операциям на партицированной таблице.
Опция CREATE TABLE ... LIKE полезна,
чтобы избежать утомительного повторения определения родительской таблицы:
CREATE TABLE measurement_y2008m02
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
TABLESPACE fasttablespace;
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
Перед выполнением команды ATTACH PARTITION рекомендуется создать CHECK-ограничение на таблице, которая будет присоединена, соответствующее ожидаемому ограничению раздела, как показано выше. Таким образом, система сможет пропустить сканирование, которое в противном случае требуется для проверки неявного ограничения раздела. Без CHECK-ограничения таблица будет сканироваться для проверки ограничения секции при удержании ACCESS EXCLUSIVE-блокировки на этом разделе. Рекомендуется удалить теперь избыточное CHECK-ограничение после завершения команды ATTACH PARTITION. Если присоединяемая таблица сама является разделенной таблицей, то каждый из ее подсекций будет рекурсивно блокироваться и сканироваться до тех пор, пока не будет найдено подходящее CHECK-ограничение или не будут достигнуты конечные секции.
Аналогично, если у разделенной таблицы есть раздел DEFAULT,
рекомендуется создать ограничение CHECK,
которое исключает ограничение раздела, который будет присоединяться. Если
этого не сделать, то раздел DEFAULT будет
просканирован для проверки, что в нем нет записей, которые должны находиться в
присоединяемом разделе. Эта операция будет выполнена при удержании блокировки ACCESS EXCLUSIVE на разделе
DEFAULT. Если раздел DEFAULT сам является разделенной таблицей, то каждый из его секций будет
рекурсивно проверен таким же образом, как таблица, которая присоединяется, как
упоминалось выше.
Как уже объяснялось выше, возможно создание индексов на разделенных таблицах таким образом, чтобы они автоматически применялись ко всей иерархии. Это очень удобно, поскольку индексирование будет применяться не только к существующим разделам, но и к любым разделам, созданным в будущем. Однако есть ограничение: нельзя использовать квалификатор CONCURRENTLY при создании такого разделенного индекса. Чтобы избежать длительных блокировок, можно использовать команду CREATE INDEX ON ONLY для разделенной таблицы; такой индекс помечается как недействительный, и секции не получают автоматического применения индекса. Индексы на разделах можно создавать отдельно с использованием CONCURRENTLY, а затем присоединять их к индексу на родительской таблице с помощью команды ALTER INDEX .. ATTACH PARTITION. Как только индексы для всех секций присоединены к родительскому индексу, родительский индекс автоматически помечается как действительный. Пример:
CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);
CREATE INDEX CONCURRENTLY measurement_usls_200602_idx
ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
ATTACH PARTITION measurement_usls_200602_idx;
...
Эта техника может быть использована с ограничениями UNIQUE и
PRIMARY KEY тоже; индексы создаются
неявно при создании ограничения. Пример:
ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);
ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...
5.11.2.3. Ограничения
Следующие ограничения применяются к разделенным таблицам:
Для создания уникального или первичного ключевого ограничения на разделенной таблице, ключи разделения не должны содержать выражений или вызовов функций, и столбцы ограничения должны включать все столбцы ключа разделения. Это ограничение существует, потому что отдельные индексы, составляющие ограничение, могут непосредственно обеспечивать уникальность только в своих собственных разделах; поэтому сама структура разделения должна гарантировать отсутствие дубликатов в разных разделах.
Нет способа создать исключающее ограничение, охватывающее всю разделенную таблицу. Возможно только установить такое ограничение на каждый листовой раздел отдельно. Это ограничение снова обусловлено невозможностью применения ограничений между разделами.
BEFORE ROWтриггеры наINSERTне могут изменить, в какое разделение будет помещена новая строка.Смешивание временных и постоянных отношений в одном дереве секций не допускается. Следовательно, если разделенная таблица является постоянной, то таковыми должны быть и ее секции, и наоборот, если разделенная таблица является временной. При использовании временных отношений все члены дерева секций должны быть из одной сессии.
Индивидуальные секции связаны с их разделенной таблицей с использованием наследования за кулисами. Однако невозможно использовать все общие функции наследования с декларативно разделенными таблицами или их секциями, как обсуждается ниже. В частности, секция не может иметь других родителей, кроме разделенной таблицы, которой он является разделом, и таблица не может наследовать как от разделенной таблицы, так и от обычной таблицы. Это означает, что разделенные таблицы и их секции никогда не имеют общей иерархии наследования с обычными таблицами.
Поскольку иерархия секций, состоящая из разделенной таблицы и ее секций, по-прежнему является иерархией наследования, tableoid и все обычные правила наследования применяются, как описано в Раздел 5.10, с несколькими исключениями:
Частиции не могут иметь столбцы, которых нет в родительской таблице. Невозможно указывать столбцы при создании частей с помощью
CREATE TABLE, также невозможно добавлять столбцы к частям после создания с помощьюALTER TABLE. Таблицы могут быть добавлены в качестве части с помощьюALTER TABLE ... ATTACH PARTITIONтолько если их столбцы полностью совпадают с родительской таблицей.Оба ограничения
CHECKиNOT NULLунаследуются всеми разделами таблицы-потомка. ОграниченияCHECK, помеченные какNO INHERIT, не могут быть созданы на разделенных таблицах. Вы не можете удалить ограничениеNOT NULLна столбце раздела, если такое же ограничение присутствует в родительской таблице.Использование
ONLYдля добавления или удаления ограничения только на разделенную таблицу поддерживается, пока нет разделов. Как только разделы существуют, использованиеONLYприведет к ошибке для любых ограничений, кромеUNIQUEиPRIMARY KEY. Вместо этого ограничения на сами разделы могут быть добавлены и (если они отсутствуют в родительской таблице) удалены.Поскольку у разделенной таблицы нет собственных данных, попытки использовать команду
TRUNCATEONLYдля разделенной таблицы всегда будут вызывать ошибку.
5.11.3. Разделение с использованием наследования
В то время как встроенное декларативное разделение подходит для большинства обычных случаев использования, есть некоторые обстоятельства, когда более гибкий подход может быть полезен. Разделение может быть реализовано с использованием наследования таблицы, что позволяет использовать несколько функций, не поддерживаемых декларативным разделением, таких как:
Для декларативного разделения, секции должны иметь точно такой же набор столбцов, как и разделенная таблица, в то время как при наследовании таблицы, дочерние таблицы могут иметь дополнительные столбцы, отсутствующие в родительской.
Таблица наследования позволяет использовать множественное наследование.
Декларативное разделение поддерживает только разделение по диапазону, списку и хешу, в то время как наследование таблиц позволяет разделить данные по выбору пользователя. (Однако следует отметить, что если исключение ограничений не может эффективно устранить дочерние таблицы, производительность запросов может быть низкой).
5.11.3.1. Пример
Этот пример создает структуру разделения, эквивалентную примеру декларативного разделения выше. Используйте следующие шаги:
Создайте таблицу “root”, от которой будут наследоваться все таблицы-“child”. Эта таблица не будет содержать данных. Не определяйте никаких проверочных ограничений для этой таблицы, если вы не намерены, чтобы они применялись одинаково ко всем дочерним таблицам. Также нет смысла определять для нее индексы или уникальные ограничения. В нашем примере корневая таблица - это таблица
measurement, как она была изначально определена:CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int );Создайте несколько дочерних таблиц, каждая из которых наследует от корневой таблицы. Обычно эти таблицы не добавляют никаких столбцов к набору, унаследованному от корня. Как и в случае с декларативным разделением, эти таблицы являются обычными таблицами (или внешними таблицами) Tantor SE во всех отношениях.
CREATE TABLE measurement_y2006m02 () INHERITS (measurement); CREATE TABLE measurement_y2006m03 () INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 () INHERITS (measurement); CREATE TABLE measurement_y2007m12 () INHERITS (measurement); CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
Добавьте неперекрывающиеся ограничения таблицы к дочерним таблицам, чтобы определить допустимые значения ключей в каждой.
Типичными примерами могут быть:
CHECK ( x = 1 ) CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' )) CHECK ( outletID >= 100 AND outletID < 200 )
Убедитесь, что ограничения гарантируют отсутствие перекрытия между значениями ключей, разрешенными в разных дочерних таблицах. Частая ошибка заключается в установке диапазонных ограничений, например:
CHECK ( outletID BETWEEN 100 AND 200 ) CHECK ( outletID BETWEEN 200 AND 300 )
Это неправильно, так как не ясно, в какой дочерней таблице ключевое значение 200 принадлежит. Вместо этого, диапазоны должны быть определены в таком стиле:
CREATE TABLE measurement_y2006m02 ( CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) ) INHERITS (measurement);Для каждой дочерней таблицы создайте индекс на ключевом столбце(ах), а также любые другие индексы, которые вам могут понадобиться.
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
Мы хотим, чтобы наше приложение могло сказать
INSERT INTO measurement ...и чтобы данные были перенаправлены в соответствующую дочернюю таблицу. Мы можем добиться этого, присоединив подходящую функцию триггера к корневой таблице. Если данные будут добавляться только в последнюю дочернюю таблицу, мы можем использовать очень простую функцию триггера:CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN INSERT INTO measurement_y2008m01 VALUES (NEW.*); RETURN NULL; END; $$ LANGUAGE plpgsql;После создания функции мы создаем триггер, который вызывает функцию-триггер:
CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();Необходимо переопределять функцию триггера каждый месяц, чтобы она всегда вставляла данные в текущую дочернюю таблицу. Однако, определение триггера не требуется обновлять.
Мы можем захотеть вставить данные и позволить серверу автоматически определить дочернюю таблицу, в которую должна быть добавлена строка. Мы можем сделать это с помощью более сложной функции триггера, например:
CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ... ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN INSERT INTO measurement_y2008m01 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;Определение триггера остается прежним. Обратите внимание, что каждый тест
IFдолжен точно соответствовать ограничениюCHECKдля его дочерней таблицы.В то время как эта функция более сложная, чем в случае с одним месяцем, она не требует такой частой обновлений, так как филиалы могут быть добавлены заранее, до того, как они понадобятся.
Примечание
На практике, возможно, лучше проверить сначала самого нового потомка, если большинство вставок происходит в этого потомка. Для простоты мы показали тесты триггера в том же порядке, что и в других частях этого примера.
Другой подход к перенаправлению вставок в соответствующую дочернюю таблицу - это настройка правил, а не триггера, на корневой таблице. Например:
CREATE RULE measurement_insert_y2006m02 AS ON INSERT TO measurement WHERE ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) DO INSTEAD INSERT INTO measurement_y2006m02 VALUES (NEW.*); ... CREATE RULE measurement_insert_y2008m01 AS ON INSERT TO measurement WHERE ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) DO INSTEAD INSERT INTO measurement_y2008m01 VALUES (NEW.*);Правило имеет значительно большие издержки, чем триггер, но эти издержки оплачиваются один раз за запрос, а не за каждую строку, поэтому этот метод может быть выгодным для ситуаций массовой вставки. Однако в большинстве случаев метод с использованием триггера будет предлагать лучшую производительность.
Будьте внимательны, что команда
COPYигнорирует правила. Если вы хотите использоватьCOPYдля вставки данных, вам нужно будет скопировать их в правильную дочернюю таблицу, а не напрямую в корневую.COPYвызывает срабатывание триггеров, поэтому вы можете использовать его нормально, если используете подход с триггерами.Еще одним недостатком подхода с использованием правил является отсутствие простого способа вызвать ошибку, если набор правил не покрывает вставку даты; данные будут молча помещены в корневую таблицу.
Убедитесь, что параметр конфигурации constraint_exclusion не отключен в файле
postgresql.conf; в противном случае, дочерние таблицы могут быть доступны ненужным образом.
Как мы видим, сложная иерархия таблиц может потребовать значительного количества DDL. В приведенном выше примере мы создаем новую дочернюю таблицу каждый месяц, поэтому было бы разумно написать скрипт, который автоматически генерирует необходимый DDL.
5.11.3.2. Обслуживание для наследования разделения
Чтобы быстро удалить старые данные, просто удалите дочернюю таблицу, которая больше не нужна:
DROP TABLE measurement_y2006m02;
Чтобы удалить дочернюю таблицу из иерархии наследования, но сохранить доступ к ней как к отдельной таблице:
ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
Чтобы добавить новую дочернюю таблицу для обработки новых данных, создайте пустую дочернюю таблицу так же, как были созданы исходные дочерние таблицы выше:
CREATE TABLE measurement_y2008m02 (
CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);
В качестве альтернативы, можно создать и заполнить новую дочернюю таблицу перед добавлением ее в иерархию таблиц. Это может позволить загружать, проверять и преобразовывать данные перед их отображением в запросах к родительской таблице.
CREATE TABLE measurement_y2008m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); \copy measurement_y2008m02 from 'measurement_y2008m02' -- possibly some other data preparation work ALTER TABLE measurement_y2008m02 INHERIT measurement;
5.11.3.3. Пояснения
Примечания к разделению, реализованному с использованием наследования:
Нет автоматического способа проверить, что все ограничения
CHECKвзаимоисключающие. Безопаснее создавать код, который генерирует дочерние таблицы и создает и/или изменяет связанные объекты, чем писать каждый вручную.Индексы и ограничения внешних ключей применяются только к отдельным таблицам, а не к их наследующим таблицам, поэтому они имеют некоторые особенности, о которых следует знать.
Схемы, показанные здесь, предполагают, что значения ключевого столбца(ов) строки никогда не изменяются, или по крайней мере не изменяются достаточно, чтобы требовать перемещения в другое разделение.
UPDATE, пытающийся сделать это, завершится неудачей из-за ограниченийCHECK. Если вам нужно обрабатывать такие случаи, вы можете установить соответствующие триггеры обновления на дочерние таблицы, но это делает управление структурой гораздо более сложным.Если вы используете команды
VACUUMилиANALYZEвручную, не забудьте, что их нужно запускать отдельно для каждой дочерней таблицы. Команда может выглядеть так:ANALYZE measurement;
будет обрабатывать только корневую таблицу.
Операторы
INSERT, содержащиеON CONFLICTвряд ли будут работать так, как ожидается, поскольку действиеON CONFLICTвыполняется только в случае уникальных нарушений на указанном целевом отношении, а не на его дочерних отношениях.Для маршрутизации строк в желаемую дочернюю таблицу потребуются триггеры или правила, если приложение явно не знает о схеме разделения. Написание триггеров может быть сложным и они будут гораздо медленнее, чем внутренняя маршрутизация кортежей, выполняемая декларативным разделением.
5.11.4. Разделение на части
Обрезка секций - это техника оптимизации запросов, которая улучшает производительность для таблиц с декларативным разделением. В качестве примера:
SET enable_partition_pruning = on; -- the default SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
Без обрезки секций, указанный запрос будет сканировать каждый из секций таблицы measurement. При включенной обрезке секций, планировщик будет анализировать определение каждого раздела и доказывать, что раздел не нужно сканировать, потому что он не может содержать строки, удовлетворяющие условию WHERE запроса. Когда планировщик может это доказать, он исключает (обрезает) раздел из плана запроса.
С помощью команды EXPLAIN и параметра конфигурации enable_partition_pruning можно показать разницу между планом, для которого была выполнена обрезка секций, и планом, для которого она не была выполнена. Типичный неоптимизированный план для такой таблицы выглядит следующим образом:
SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
QUERY PLAN
-----------------------------------------------------------------------------------
Aggregate (cost=188.76..188.77 rows=1 width=8)
-> Append (cost=0.00..181.05 rows=3085 width=0)
-> Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2006m03 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
...
-> Seq Scan on measurement_y2007m11 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2007m12 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
Некоторые или все секции могут использовать индексные сканирования вместо последовательного сканирования всей таблицы, но здесь главное в том, что нет необходимости сканировать старые секции для ответа на этот запрос. Когда мы включаем обрезку секций, мы получаем значительно более дешевый план, который даст тот же ответ:
SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
QUERY PLAN
-----------------------------------------------------------------------------------
Aggregate (cost=37.75..37.76 rows=1 width=8)
-> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate >= '2008-01-01'::date)
Обратите внимание, что обрезка секций осуществляется только на основе ограничений, определенных неявно ключами секций, а не наличием индексов. Поэтому нет необходимости определять индексы на ключевых столбцах. Необходимость создания индекса для определенного раздела зависит от того, ожидается ли, что запросы, сканирующие раздел, в основном будут сканировать большую часть раздела или только небольшую часть. Индекс будет полезен в последнем случае, но не в первом.
Выполнение отсева секций может осуществляться не только во время планирования
конкретного запроса, но и во время его выполнения. Это полезно, так как это может
позволить отсеять больше секций, когда предложения содержат оператора,
значения которых неизвестны на этапе планирования запроса, например,
параметры, определенные в операторе PREPARE, используя
значение, полученное из подзапроса или используя параметризованное значение на
внутренней стороне соединения вложенного цикла. Отсев секций во время выполнения
может выполняться в любое из следующих временных интервалов:
Во время инициализации плана запроса. Здесь может выполняться обрезка секций для значений параметров, которые известны на этапе инициализации выполнения. секции, которые были обрезаны на этом этапе, не будут отображаться в выводе запроса
EXPLAINилиEXPLAIN ANALYZE. Возможно определить количество секций, которые были удалены на этом этапе, наблюдая свойство “Subplans Removed” в выводеEXPLAIN.Во время фактического выполнения плана запроса также может выполняться обрезка секций для удаления секций, использующих значения, которые известны только во время выполнения запроса. Это включает значения из подзапросов и значения из параметров времени выполнения, таких как параметры из параметризованных вложенных соединений циклов. Поскольку значение этих параметров может изменяться много раз во время выполнения запроса, обрезка секций выполняется каждый раз, когда изменяется один из используемых обрезкой секций параметров выполнения. Определение, были ли обрезаны секции во время этой фазы, требует тщательного изучения свойства
loopsв выводеEXPLAIN ANALYZE. Подзапросы, соответствующие разным разделам, могут иметь разные значения для этого свойства в зависимости от того, сколько раз каждый из них был обрезан во время выполнения. Некоторые могут быть показаны как(никогда не выполнялись), если они были обрезаны каждый раз.
Отключение обрезки секций может быть выполнено с помощью параметра enable_partition_pruning.
5.11.5. Разделение и исключение ограничений
Исключение ограничений - это метод оптимизации запросов, аналогичный обрезке секций. В основном он используется для разделения, реализованного с помощью устаревшего метода наследования, но может использоваться и для других целей, включая декларативное разделение.
Исключение ограничений работает очень похожим образом на обрезку секций, за исключением того, что оно использует ограничения CHECK каждой таблицы — отсюда и его название — в то время как обрезка секций использует границы секций таблицы, которые существуют только в случае декларативного разделения. Еще одно отличие заключается в том, что исключение ограничений применяется только на этапе планирования; не предпринимается попытка удалить секции на этапе выполнения.
Факт того, что исключение ограничений использует ограничения CHECK,
что делает его медленнее по сравнению с разделением на секции, иногда может
использоваться в качестве преимущества: поскольку ограничения могут быть определены
даже для таблиц с декларативным разделением, в дополнение к их внутренним
границам секций, исключение ограничений может иметь возможность
исключить дополнительные секции из плана запроса.
По умолчанию (и рекомендуется) значение constraint_exclusion не является ни on, ни off, а является промежуточным значением, называемым partition, что приводит к применению техники только к запросам, которые, вероятно, работают с унаследованными разделенными таблицами. Значение on заставляет планировщик проверять CHECK-ограничения во всех запросах, даже в простых, которые, вероятно, не принесут пользы.
Следующие пояснения относятся к исключению ограничений:
Исключение ограничений применяется только во время планирования запроса, в отличие от обрезки секций, которая также может применяться во время выполнения запроса.
Ограничение исключения работает только тогда, когда в предложении
WHEREзапроса содержатся константы (или внешне предоставленные параметры). Например, сравнение с неизменяемой функцией, такой какCURRENT_TIMESTAMP, не может быть оптимизировано, поскольку планировщик не может знать, в какую дочернюю таблицу может попасть значение функции во время выполнения.Держите ограничения разделения простыми, иначе планировщик может не суметь доказать, что дочерние таблицы, возможно, не нужно посещать. Используйте простые условия равенства для списочного разделения или простые тесты диапазона для разделения по диапазону, как показано в предыдущих примерах. Хорошее правило заключается в том, что ограничения разделения должны содержать только сравнения столбцов разделения с константами, используя операторы, которые могут быть использованы с B-деревом, потому что только столбцы, которые могут быть использованы с B-деревом, разрешены в ключе разделения.
Все ограничения на все дочерние таблицы родительской таблицы проверяются во время исключения ограничений, поэтому большое количество дочерних таблиц может значительно увеличить время планирования запросов. Поэтому унаследованное разделение на основе наследования будет работать хорошо с до, пожалуй, сотни дочерних таблиц; не пытайтесь использовать множество тысяч дочерних таблиц.
5.11.6. Лучшие практики декларативного разделения
Выбор способа разделения таблицы следует делать тщательно, так как производительность планирования и выполнения запросов может быть отрицательно затронута неправильным проектированием.
Одним из самых важных проектных решений будет выбор столбца или столбцов, по которым будет производиться разделение данных. Часто лучшим выбором будет разделение по столбцу или набору столбцов, которые наиболее часто встречаются в предложениях WHERE запросов, выполняемых на разделенной таблице. Предложения WHERE, совместимые с ограничениями границ секций, могут использоваться для отсечения ненужных секций. Однако требования к PRIMARY KEY или ограничению UNIQUE могут принудить вас принять другие решения. Удаление нежелательных данных также является фактором, который следует учесть при планировании стратегии разделения. Целую партицию можно отсоединить достаточно быстро, поэтому может быть полезно разработать стратегию разделения таким образом, чтобы все данные, которые нужно удалить сразу, находились в одном разделе.
Выбор целевого количества секций, на которые должна быть разделена таблица, также является важным решением. Недостаточное количество секций может означать, что индексы остаются слишком большими, а локальность данных остается низкой, что может привести к низким показателям попадания в кеш. Однако, разделение таблицы на слишком много секций также может вызвать проблемы. Слишком много секций может означать более длительное время планирования запросов и более высокое потребление памяти как во время планирования, так и во время выполнения запросов, как далее описано.
При выборе способа разделения таблицы также важно учесть возможные изменения в будущем. Например, если вы выбираете один раздел на каждого клиента и у вас в настоящее время есть небольшое количество крупных клиентов, рассмотрите последствия, если через несколько лет у вас вместо этого окажется большое количество маленьких клиентов. В этом случае может быть лучше выбрать разделение по HASH и выбрать разумное количество секций, а не пытаться разделить данные по списку (LIST) и надеяться, что количество клиентов не превысит практический предел разделения данных.
Подчастицирование может быть полезным для дальнейшего разделения секций, которые ожидаются станут больше, чем другие секции. Другой вариант - использовать диапазонное разделение с несколькими столбцами в ключе раздела. Любой из этих вариантов может легко привести к избыточному количеству секций, поэтому рекомендуется сдержанность.
Важно учитывать издержки на разделение при планировании и выполнении запросов. Планировщик запросов обычно хорошо справляется с иерархиями секций, содержащими до нескольких тысяч секций, при условии, что типичные запросы позволяют планировщику запросов отсеять все, кроме небольшого количества секций. Время планирования увеличивается, а потребление памяти становится выше, когда после отсеивания секций планировщиком остается больше секций. Еще одна причина беспокоиться о большом количестве секций заключается в том, что потребление памяти сервера может значительно увеличиваться со временем, особенно если множество сессий работает с большим количеством секций. Это происходит потому, что каждая партиция требует загрузки своих метаданных в локальную память каждой сессии, который с ним взаимодействует.
С данными типа хранилища данных может иметь смысл использовать большее количество секций, чем с типом рабочей нагрузки OLTP. В общем, в хранилищах данных время планирования запросов менее важно, так как большая часть времени обрабатывается во время выполнения запроса. С любым из этих двух типов рабочей нагрузки важно принимать правильные решения заранее, так как повторное разделение больших объемов данных может быть мучительно медленным. Симуляции предполагаемой рабочей нагрузки часто полезны для оптимизации стратегии разделения. Никогда не предполагайте, что больше секций лучше, чем меньше, и наоборот.