Ddl postgresql что это

Меняем схему базы данных в PostrgreSQL, не останавливая работу приложения

Авторизуйтесь

Меняем схему базы данных в PostrgreSQL, не останавливая работу приложения

Эта статья описывает опыт Braintree Payments, подразделения PayPal, и рассказывает о том, как им удаётся обновлять схему баз данных PostgreSQL в условиях, когда приостановка работы API для подобных технических работ недопустима — даже если речь идёт о минутах.

В этой статье будут рассмотрены следующие темы:

Немного основ

Ко всему коду и ко всем изменениям баз данных в Braintree выдвигаются следующие требования:

Для всех DDL операций важно, чтобы:

Транзакционный DDL

PostgreSQL поддерживает транзакции при выполнении DDL операций. В большинстве случаев вы можете выполнять несколько DDL запросов внутри одной транзакции и придерживаться стратегии «всё или ничего». К сожалению, у такого подхода есть существенный недостаток: если вы меняете несколько объектов, вам придётся заблокировать их все. Блокировка нескольких таблиц, во-первых, создаёт вероятность взаимной блокировки (deadlock), а, во-вторых, вынуждает пользователей ждать выполнения всей транзакции. Поэтому для каждого запроса рекомендуется использовать отдельную транзакцию.

Заметьте: параллельное создание индексов — это особый случай. PostgreSQL запрещает выполнять CREATE INDEX CONCURRENTLY внутри явно описанной транзакции; вместо этого PostgreSQL самостоятельно создаёт транзакции и управляет ими. Если по каким-то причинам построение индекса прерывается до успешного завершения, то может потребоваться вручную удалить его, прежде чем пробовать ещё раз. Впрочем, такой индекс всё равно никогда не будет использоваться для обслуживания запросов.

Блокирование строк

У PostgreSQL множество разных уровней блокировки. Нас интересуют в основном блокировки уровня таблицы (потому что DDL обычно оперирует на этом уровне):

Все операции DDL обязательно блокируют таблицу одним из этих способов. К примеру, если вы выполните:

Когда вы применяете блокировку такого уровня, ни один из последующих запросов к таблице не выполняется. Вместо этого они откладываются в очередь до тех пор, пока самый долгий из запущенных вами запросов не закончит выполнение. Выполнение запросов, отложенное на определённый срок, нельзя отличить от отключения сервера для выполнения технических работ. Поэтому такой ситуации лучше избегать.

Основные подходы

Вместо того чтобы полагаться на PostrgeSQL, осуществляйте явную блокировку самостоятельно. Это позволяет аккуратно контролировать время, на которое запросы откладываются в очередь. Если у вас не получается осуществить блокировку в течение нескольких секунд, рекомендуется добавить небольшую задержку перед следующей попыткой. Таким образом вы позволите отложенным запросам выполниться и не создавать слишком большую нагрузку в будущем. И, наконец, прежде чем пытаться осуществить блокировку, запросите из pg_locks 1 список долго выполняющихся запросов. Это позволит избежать постановки в очередь команд, которые, скорее всего, не выполнятся.

Обратите внимание: иногда удержание блокировки уровня ACCESS EXCLUSIVE для чего-то большего, чем обновление каталога (или перезаписи) может быть оправдано. Например, если размер таблицы относительно мал. Рекомендуется проверять конкретные случаи использования на реалистичных размерах данных и оборудовании, чтобы увидеть, является ли операция достаточно быстрой. Если вы используете хорошее оборудование, и ваша таблица легко помещается в память, то полное сканирование таблицы или перезапись тысяч строк всё ещё могут быть достаточно быстрыми.

Операции над таблицей

Создание таблицы

В общем случае добавление таблицы — одна немногих операций, о которых не стоит слишком заботиться. Ведь объект, который мы «изменяем», просто технически не может использоваться в этот момент.

Большинство параметров создания таблицы никак не влияют на другие объекты базы данных. Добавление внешнего ключа при определении таблицы заставит PostgreSQL получить блокировку уровня SHARE ROW EXCLUSIVE на упомянутой таблице. Это остановит все DDL запросы, направленные к ней, и модификации строк. Несмотря на то что эта блокировка не должна быть слишком долгой, о ней стоит помнить, как и о любой другой операции, вызывающей блокировку. Рекомендуется разделять эти две операции: создайте таблицу, и только потом добавьте внешний ключ.

Удаление таблицы

Переименование таблицы

Рекомендуется избегать переименований таблиц везде, где это возможно. Но если переименование действительно необходимо, то для безопасности:

Другие подходы, основанные на использовании представлений (views) и/или правил (RULE), также могут вам подойти, всё зависит от производительности, которая вам необходима.

Операции над столбцами

Обратите внимание: установка ограничений, накладываемых на столбцы (например NOT NULL ), и прочих ограничений (например EXCLUDES ) описана в отдельной части статьи.

Добавить столбец

Добавление столбца в существующую таблицу обычно требует короткой блокировки уровня ACCESS EXCLUSIVE на таблице на то время, пока обновляются системные таблицы каталогов (catalog tables).

Значения по умолчанию. Установка значения по умолчанию одновременно с созданием столбца заблокирует таблицу на время установки значений. Вместо этого следует:

Обратите внимание: В недавно выпущенном PostgreSQL 11 эти советы больше не актуальны для неволатильных значений по умолчанию. Теперь добавление столбца со значением по умолчанию требует только обновления таблиц каталогов, а все обращения к строкам без значения будут магическим образом возвращать нужное значение.

Изменить тип столбца

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

Обратите внимание: Несмотря на то что некоторые из исключений выше были введены ещё в версии 9.1, изменение типа индексируемого столбца в этой версии всегда приводит к переписыванию индекса. Начиная с версии 9.2, индекс не переписывается, если не переписывалось содержимое таблицы. Если вы хотите удостовериться, что ваше изменение не инициирует перезапись, вы можете сделать запрос к pg_class 3 и проверить, что столбец relfilenode не изменился.

Если вам требуется изменить тип столбца, и описанные выше исключения к вашему случаю не относятся, то:

Удалить столбец

Заметьте: Удаление столбца потребует от вас обновления всех представлений, триггеров, функций и т. д., которые были завязаны на этот столбец.

Операции над индексами

Создать индекс

Удалить индекс

Переименовать индекс

Произвести переиндексацию

Заметьте: Если индекс, который вам необходимо перестроить, содержал ограничения, не забудьте добавить их и в новый индекс (как это сделать, мы как раз рассмотрим в следующей части).

Ограничения

NOT NULL

Удаление существующего ограничения NOT NULL из столбца требует полной блокировки таблицы. Это не так существенно, так как выполняется простое обновление каталога.

Бонус: сейчас в работе находится патч (и, возможно, он войдёт в релиз PostgreSQL 12), который позволит создавать ограничение NOT NULL без полного просмотра таблицы при ограничении CHECK вроде того, что мы создали выше.

Внешний ключ

Чтобы избежать длительной блокировки, можно поступить следующим образом:

Ограничение проверки (CHECK)

Ограничение уникальности (UNIQUE)

Ограничение-исключение (EXCLUDE)

Перечисляемые типы

CREATE TYPE AS (. ) и DROP TYPE (после проверки, что тип нигде не используются) могут быть безопасно выполнены без всяких блокировок.

Изменение используемых значений

Удаление значений

Перечислимые типы хранятся в виде целых чисел. Пропуски в диапазоне допустимых значений не поддерживаются. Из-за этого удаление одного допустимого значения привело бы к необходимости изменять данные во всех строках, которые использовали этот тип. PostgreSQL в настоящее время не поддерживает удаление одного значения из существующего перечислимого типа.

Библиотека для Ruby on Rails

Braintree Payments, в дополнение к статье, выложили исходный код своей библиотеки для Ruby on Rails — pg_ha_migrations. Этот gem позволяет безопасно использовать DDL в проектах, которые используют Ruby on Rails и/или ActiveRecord. Её основная задача — позволить явно указывать способ выполнения операции, выбирая между различными видами издержек. Подробнее можно прочитать в README проекта.

Примечания

1 Вы можете получить активные запросы, которые долго исполняются, выполнив следующий системный запрос:

2 Посмотреть внутреннюю статистику PostgrSQL об использовании заданной таблицы можно, выполнив следующий запрос:

3 Если вы хотите узнать, вызывает ли DDL перезапись связанных объектов, вам стоит посмотреть меняются ли значения relfilenode после выполнения следующего выражения:

4 Следующее выражение поможет вам найти любые зависимые от столбца объекты (в частности, индексы):

Источник

PostgreSQL DDL

By Sohel Sayyad

Introduction to PostgreSQL DDL

The PostgreSQL DDL is the acronym for Data Definition Language. The Data Definition Language is used to handle the database descriptions and schemas, and it is used to define as well as modify the structure of the data. With the help of the Data Definition Language, we decide how the data should be stored in the database. We can perform operations on the database by creating a new database, altering the existing database, and removing, truncating, or renaming the existing database.

How does the DDL Statement work in PostgreSQL?

List of PostgreSQL DDL Statement

The Data Definition Language consists of the following statements:

Hadoop, Data Science, Statistics & others

1. CREATE Statement

The CREATE statement is used for creating database objects like (database, table, index, views, store procedure, function, and triggers).

Syntax:

CREATE TABLE (column_name_1 datatype,
column_name_2 datatype,
.
.
column_name_n datatype
);

Example:

Code:

CREATE TABLE student
(
student_id INT,
student_name VARCHAR(30),
student_age INT,
student_address VARCHAR(25),
student_phone_no VARCHAR(20)
);

Illustrate the result of the above statement by using the following SQL statement and a snapshot:

Code:

Select * from student;

Output:

2. ALTER Statement

The ALTER statement allows modifying the existing database objects. We can alter or modify the database structure by using an ALTER statement.

We can perform the following operations by using an ALTER statement:

a. Consider the following ALTER statement syntax for adding a PRIMARY KEY on a column in the existing table.

Syntax:

ALTER TABLE
ADD PRIMARY KEY ( );

Example:

Code:

ALTER TABLE
student
ADD PRIMARY KEY (student_id);

b. Consider the following ALTER statement syntax for setting NOT NULL on a column in the existing table.

Syntax:

ALTER TABLE
ALTER COLUMN

SET NOT NULL;

Example:

Code:

ALTER TABLE
student
ALTER COLUMN
student_name
SET NOT NULL;

Output:

c. Consider the following ALTER statement syntax for dropping NOT NULL on a column in the existing table.

Syntax:

ALTER TABLE
ALTER COLUMN

DROP NOT NULL;

Example:

Code:

ALTER TABLE
student
ALTER COLUMN
student_name
DROP NOT NULL;

Output:

d. Consider the following ALTER statement syntax for adding a new column in the existing table.

Syntax:

Example:

Code:

ALTER TABLE
student
ADD email varchar(50);

Illustrate the result of the above statement by using the following SQL statement and a snapshot:

Code:

Select * from student;

Output:

Consider the following ALTER statement syntax for renaming an existing column name to the new column name.

Источник

Возможности PostgreSQL для тех, кто перешел с MySQL

Крутой varanio буквально на прошлой неделе прочитал на DevConf забойный доклад для всех кто пересел на Посгрес с MySQL, но до сих пор не использует новую базу данных в полной мере. По мотивам выступления родилась эта публикация.

Мы рады сообщить, что подготовка к PG Day’17 Russia идет полным ходом! Мы опубликовали полное расписание предстоящего мероприятия. Приглашаем всех желающих прийти и похоливарить с Антоном лично

Поскольку доклад на DevConf вызвал в целом положительные отзывы, я решил оформить его в виде статьи для тех, кто по каким-то причинам не смог присутствовать на конференции.

Почему вообще возникла идея такого доклада? Дело в том, что PostgreSQL сейчас явно хайповая технология, и многие переходят на эту СУБД. Иногда — по объективным причинам, иногда — просто потому что это модно.

Но сплошь и рядом складывается такая ситуация, когда какой-нибудь условный программист Вася вчера писал на MySQL, а сегодня вдруг начал писать на Посгресе. Как он будет писать? Да в целом также, как и раньше, используя лишь самый минимальный набор возможностей новой базы. Практика показывает, что проходят годы, прежде чем СУБД начинает использоваться более менее полноценно.

Не холивар

Сразу disclaimer: это не статья «мускуль vs посгрес». Переходить на посгрес или нет — ваше дело. Uber, к примеру, перешел обратно на MySQL по своим каким-то причинам.

Надо отдать должное Oracle, они явно двигают MySQL в правильном направлении. В 5.7 сделали strict mode по умолчанию. В восьмой версии обещают CTE и оконные функции, а также избавление от движка MyISAM в системных таблицах. Т.е. видно, что в базу вкладываются ресурсы, и хотелки юзеров исследуются очень серьёзно.

Однако в PostgreSQL по прежнему полным полно уникальных фич. В итоге я попытался сделать краткий обзор возможностей базы для разработчика.

Встроенные типы данных

В базу встроено множество типов данных, помимо обычных числовых и строковых. А также операторы для их взаимодействия.

Например, есть типы cidr, inet, macaddr для работы с ip адресами.

Или например, время с таймзоной (timestamptz), интервал времени и т.д.

Когда я готовил этот слайд, я решил из любопытства посмотреть, а какое смещение времени относительно UTC было 100 лет назад, в 1917 году:

Т.е. москвичи жили по времени UTC+02:31:19.

Кроме перечисленных, есть и другие встроенные типы данных: UUID, JSONB, XML, битовые строки и т.д.

Тип array

Отдельно надо рассмотреть тип «array». Массивы давно и хорошо интегрированы в PostgreSQL. Многомерные массивы, слайсы, операторы пересечения, объединения и т.д. Существует множество функций для работы с массивами.

Есть очень удобная функция, которая так и называется: array. В качестве аргумента подается некий SELECT-запрос, на выходе — результат запроса в виде массива.

Есть и обратная функция: unnest. Она берет массив и возвращает его как результат запроса. Это бывает удобно, например, когда нужно вставить вручную несколько одинаковых записей с разными id, но не хочется заниматься копипастой:

Создаем собственные типы

Собственные типы можно создавать тремя способами. Во-первых, если вы знаете язык Си, то вы можете создать базовый тип, наравне с каким-нибудь int или varchar. Пример из мануала:

Т.е. создаете пару функций, которые умеют делать из cstring ваш тип и наоборот. После чего можно использовать этот тип, например, в объявлении таблицы:

Второй способ — это композитный тип. Например, для хранения комплексных чисел:

И потом использовать это:

Третий вид типа, который вы можете создать — это доменный тип. Доменный тип — это просто алиас к существующему типу с другим именем, т.е. именем, соответствующим вашей бизнес-логике.

us_postal_code — это более семантично, чем некий абстрактный text или varchar.

Создаем собственные операторы

Можно делать свои операторы. Например, сложение комплексных чисел (сам тип complex мы определили выше):

Создаем собственные правила для преобразования типов

Давайте сделаем какой-нибудь сферический в вакууме пример. Создадим типы RUR и USD, и правило для преобразования одного типа в другой. Так как я плохо знаю си, то для примера сделаем простой композитный тип:

Собственно, это всё, теперь можно использовать. Сколько там будет 100 баксов в рублях?

Результат будет таким:

Типы в расширениях PostgreSQL

Описаны правила индексирования. Например, тип ip4r (диапазон IP-адресов) можно проиндексировать индексом GIST по оператору && (и другим). Таким образом, можно сделать таблицу для поиска городов по IP.

Индексы

Partial indexes

Понятное дело, что здесь нужен индекс. Но такой индекс будет занимать много места, при этом реально вам нужна из него совсем малая часть.

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

Этот индекс будет хорошо работать на запросах select * from my_money where status = 2 и при этом занимать мало места.

Индексы по выражению

В посгресе можно делать индексы не по одной колонке, а по любому выражению. Например, можно проиндексировать сразу имя с фамилией:

И потом такой запрос будет быстро работать:

Constraints

Помимо стандартных UNIQUE и NOT NULL, в базе можно делать еще и другие проверки целостности. В доменном типе можно прописать check:

который проверяет, что в колонку типа us_postal_code попадут только 5 цифр или 5 цифр, дефис и 4 цифры. Разумеется, сюда можно писать не только регулярки, но и любые другие условия.

Также check можно прописать в таблице:

Т.е. в имени должен быть хотя бы один символ, и не больше 300.

Вообще говоря, сами типы являются также и неким ограничением, дополнительной проверкой, которую делает база. Например, если у вас есть тип complex (смотри выше), состоящий, по сути, из двух чисел, то вы не вставите туда случайно строку:

Таким образом, иногда композитный тип может быть предпочтительнее, чем jsonb, потому что в json вы можете напихать что угодно вообще.

Частичная уникальность и уникальность по выражению

В отличие от простой уникальности UNIQUE или PRIMARY KEY, в посгресе можно сделать уникальность среди определенного набора строк, заданного условием. Например, email должен быть уникальным среди неудаленных юзеров:

Еще забавная штука: можно сделать уникальность не по одному полю, а по любому выражению. К примеру, можно сделать так, что в таблице сумма двух колонок не будет повторяться:

Constraint Exclude

Ключевое слово EXCLUDE позволяет делать так, что при вставке/обновлении строки, эта строка будет сравниваться с другими по заданному оператору. Например, таблица, содержащая непересекающиеся диапазоны IP (проверяется оператором пересечения && ):

Хранимые процедуры

Хранимые процедуры можно писать на SQL, pl/pgsql, javascript, (pl/v8), python и т.д. Например, можно на языке R обсчитать какую-то статистику и вернуть из нее график с результатом.
Это отдельная большая тема, советую поискать доклад Ивана Панченко на этот счет.

CTE (Common Table Expressions)

Это будет и в MySQL 8, но всё равно давайте кратко остановимся на этом.

CTE — это просто. Вы берете какой-то кусок запроса и выносите его отдельно под каким-то именем.

С точки зрения оптимизации запросов, нужно учитывать, что каждый такой CTE-подзапрос выполняется отдельно. Это может быть как плюсом, так и минусом.

Например, если у вас 20 джойнов с подзапросами и группировками, планировщик запросов может не понять ваших намерений и план запроса будет неоптимальным. Тогда можно вынести часть запроса в cte-подзапрос, а остальное уже дофильтровать в основном запросе.

И наоборот, если вы решили просто для читабельности вынести часть запроса в CTE, то иногда это может выйти для вас боком.

В CTE можно использовать не только SELECT-запросы, но и UPDATE.

Пример: обновить юзеров с возрастом > 20 лет, и в том же запросе выдать имена обновленных вместе с какой-нибудь там страной.

Но тут надо понимать, что иногда с помощью CTE можно хорошо выстрелить себе в ногу.
Такой запрос синтаксически верен, но по смыслу полный бред:

Кажется, что мы прибавили рубль, потом отняли рубль, и должно остаться всё как есть.

Но дело в том, что update1 и update2 при своем выполнении будут брать начальную версию таблицы, т.е. по сути получится так, что один update затрет изменения другого. Поэтому с update внутри CTE надо точно знать, что ты делаешь и зачем.

Оконные функции

Про оконные функции я уже когда-то подробно писал здесь: https://habrahabr.ru/post/268983/. Оконные функции тоже обещают в MySQL 8.

Разное

FILTER

К агрегатным функциям (например, COUNT или SUM), можно дописывать условие FILTER, т.е. агрегировать не все строки, а только ограниченные неким выражением:

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

\watch

Materialized View

Это как View, только закешированное (материализованное). Кеш можно обновлять с помощью команды REFRESH MATERIALIZED VIEW. Есть также ключевое слово CONCURRENTLY, чтобы Postgres не лочил при обновлении SELECT-запросы.

Listen / Notify

Я пока что не пробовал это в продакшене, поэтому не знаю, применимо ли это на практике (если кто использовал, поделитесь плиз опытом в комментариях). Суть в том, что можно подписаться на какое то событие, а также можно уведомить подписчиков, что событие произошло, передав при этом строку с доп. сведениями.

Механизм Foreign Data Wrappers позволяет использовать некоторые внешние данные, как простые таблицы. Т.е. к примеру, можно заджойнить постгресовую таблицу, мускульную таблицу, и csv файл.

Sequences

SEQUENCE — это посгресовый аналог MySQL-ного AUTO_INCREMENT. В отличие от MySQL, sequence может существовать отдельно от таблиц или наоборот, «тикать» сразу для нескольких таблиц. Можно задавать различные параметры, например, размер инкремента, зацикливание и проч.

Вместо выводов

Это верхушка айсберга, на самом деле. Есть еще куча нюансов, вообще никак не затронутых в статье, потому что на всё никакой статьи не хватит. По одним только хранимым процедурам можно написать книгу. Или посмотрите, к примеру, полный список sql-команд текущей версии: https://www.postgresql.org/docs/9.6/static/sql-commands.html

Главное, что я хотел показать в статье: несмотря на хайповость, PostgreSQL — очень старая СУБД, в которой очень много чего есть, и которая очень хорошо расширяется. Поэтому при переходе на нее с MySQL рекомендуется полистать мануал, почитать статьи и т.д.

Источник

Ddl postgresql что это

PostgreSQL поддерживает простое секционирование таблиц. В этом разделе описывается, как и почему бывает полезно применять секционирование при проектировании баз данных.

5.11.1. Обзор

Секционированием данных называется разбиение одной большой логической таблицы на несколько меньших физических секций. Секционирование может принести следующую пользу:

В определённых ситуациях оно кардинально увеличивает быстродействие, особенно когда большой процент часто запрашиваемых строк таблицы относится к одной или лишь нескольким секциям. Секционирование по сути заменяет верхние уровни деревьев индексов, что увеличивает вероятность нахождения наиболее востребованных частей индексов в памяти.

Когда в выборке или изменении данных задействована большая часть одной секции, производительность может возрасти, если будет выполняться последовательное сканирование этой секции, а не поиск по индексу, сопровождаемый произвольным чтением данных, разбросанных по всей таблице.

Редко используемые данные можно перенести на более дешёвые и медленные носители.

Всё это обычно полезно только для очень больших таблиц. Какие именно таблицы выиграют от секционирования, зависит от конкретного приложения, хотя, как правило, это следует применять для таблиц, размер которых превышает объём ОЗУ сервера.

PostgreSQL предлагает поддержку следующих видов секционирования:

Таблица секционируется с помощью списка, явно указывающего, какие значения ключа должны относиться к каждой секции. Секционирование по хешу

Таблица секционируется по определённым модулям и остаткам, которые указываются для каждой секции. Каждая секция содержит строки, для которых хеш-значение ключа разбиения, делённое на модуль, равняется заданному остатку.

5.11.2. Декларативное секционирование

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

Сама секционированная таблица является « виртуальной » и как таковая не хранится. Хранилище используется её секциями, которые являются обычными таблицами, связанными с секционированной. В каждой секции хранится подмножество данных таблицы, определяемое её границами секции. Все строки, вставляемые в секционированную таблицу, перенаправляются в соответствующие секции в зависимости от значений столбцов ключа разбиения. Если при изменении значений ключа разбиения в строке она перестаёт удовлетворять ограничениям исходной секции, эта строка перемещается в другую секцию.

5.11.2.1. Пример

Предположим, что мы создаём базу данных для большой компании, торгующей мороженым. Компания учитывает максимальную температуру и продажи мороженого каждый день в разрезе регионов. По сути нам нужна следующая таблица:

Мы знаем, что большинство запросов будут работать только с данными за последнюю неделю, месяц или квартал, так как в основном эта таблица нужна для формирования текущих отчётов для руководства. Чтобы сократить объём хранящихся старых данных, мы решили оставлять данные только за 3 последних года. Ненужные данные мы будем удалять в начале каждого месяца. В этой ситуации мы можем использовать секционирование для удовлетворения всех наших требований к таблице показателей.

Чтобы использовать декларативное секционирование в этом случае, выполните следующее:

Создайте секции. В определении каждой секции должны задаваться границы, соответствующие методу и ключу разбиения родительской таблицы. Заметьте, что указание границ, при котором множество значений новой секции пересекается со множеством значений в одной или нескольких существующих секциях, будет ошибочным.

Секции, создаваемые таким образом, во всех отношениях являются обычными таблицами PostgreSQL (или, возможно, сторонними таблицами). В частности, для каждой секции можно независимо задать табличное пространство и параметры хранения.

В нашем примере каждая секция должна содержать данные за один месяц, чтобы данные можно было удалять по месяцам согласно требованиям. Таким образом, нужные команды будут выглядеть так:

(Как говорилось ранее, границы соседних секций могут определяться одинаковыми значениями, так как верхние границы не включаются в диапазон.)

Если вы хотите реализовать вложенное секционирование, дополнительно укажите предложение PARTITION BY в командах, создающих отдельные секции, например:

При добавлении в родительскую таблицу данных, которые не соответствуют ни одной из существующих секций, произойдёт ошибка; подходящую секцию нужно создавать вручную.

Создавать в таблицах ограничения с условиями, задающими границы секций, вручную не требуется. Такие ограничения будут созданы автоматически.

В данном примере нам потребуется создавать секцию каждый месяц, так что было бы разумно написать скрипт, который бы формировал требуемый код DDL автоматически.

5.11.2.2. Обслуживание секций

Обычно набор секций, образованный изначально при создании таблиц, не предполагается сохранять неизменным. Чаще наоборот, планируется удалять секции со старыми данными и периодически добавлять секции с новыми. Одно из наиболее важных преимуществ секционирования состоит именно в том, что оно позволяет практически моментально выполнять трудоёмкие операции, изменяя структуру секций, а не физически перемещая большие объёмы данных.

Самый лёгкий способ удалить старые данные — просто удалить секцию, ставшую ненужной:

Ещё один часто более предпочтительный вариант — убрать секцию из главной таблицы, но сохранить возможность обращаться к ней как к самостоятельной таблице:

Аналогичным образом можно добавлять новую секцию с данными. Мы можем создать пустую секцию в главной таблице так же, как мы создавали секции в исходном состоянии до этого:

Команда ATTACH PARTITION требует блокировки SHARE UPDATE EXCLUSIVE для секционированной таблицы.

Этот приём можно применять и с ограничениями UNIQUE и PRIMARY KEY ; для них индексы создаются неявно при создании ограничения. Например:

5.11.2.3. Ограничения

С секционированными таблицами связаны следующие ограничения:

Ограничения уникальности (а значит и первичные ключи) в секционированных таблицах должны включать все столбцы ключа разбиения. Это требование объясняется тем, что отдельные индексы, образующие ограничение, могут непосредственно обеспечивать уникальность только в своих секциях. Поэтому сама структура секционирования должна гарантировать отсутствие дубликатов в разных секциях.

Создать ограничение-исключение, охватывающее всю секционированную таблицу, нельзя; можно только поместить такое ограничение в каждую отдельную секцию с данными. И это также является следствием того, что установить ограничения, действующие между секциями, невозможно.

Триггеры BEFORE ROW для INSERT не могут менять секцию, в которую в итоге попадёт новая строка.

Смешивание временных и постоянных отношений в одном дереве секционирования не допускается. Таким образом, если секционированная таблица постоянная, такими же должны быть её секции; с временными таблицами аналогично. В случае с временными отношениями все таблицы дерева секционирования должны быть из одного сеанса.

На уровне реализации отдельные секции связываются с секционированной таблицей средствами наследования. Однако с декларативно секционированными таблицами или их секциями нельзя использовать некоторую общую функциональность наследования, как описано ниже. А именно, секция не может иметь никаких других родителей, кроме секционированной таблицы, к которой она относится, равно как и любая таблица не может наследоваться и от секционированной, и от обычной таблицы. Это означает, что секционированные таблицы и их секции не совмещаются в иерархии наследования с обычными таблицами.

Так как иерархия секционирования, образованная секционированной таблицей и её секциями, является одновременно и иерархией наследования, она содержит tableoid и на неё распространяются все обычные правила наследования, описанные в Разделе 5.10, с некоторыми исключениями:

Использование указания ONLY при добавлении или удалении ограничения только в секционированной таблице поддерживается лишь когда в ней нет секций. Если секции существуют, при попытке использования ONLY возникнет ошибка. С другой стороны, ограничения можно добавлять или удалять непосредственно в секциях (если они отсутствуют в родительской таблице).

Так как секционированная таблица сама по себе не содержит данные, использование TRUNCATE ONLY для секционированной таблицы всегда будет считаться ошибкой.

5.11.3. Секционирование с использованием наследования

Хотя встроенное декларативное секционирование полезно во многих часто возникающих ситуациях, бывают обстоятельства, требующие более гибкого подхода. В этом случае секционирование можно реализовать, применив механизм наследования таблиц, что даст ряд возможностей, неподдерживаемых при декларативном секционировании, например:

При декларативном секционировании все секции должны иметь в точности тот же набор столбцов, что и секционируемая таблица, тогда как обычное наследование таблиц допускает наличие в дочерних таблицах дополнительных столбцов, отсутствующих в родителе.

Механизм наследования таблиц поддерживает множественное наследование.

С декларативным секционированием поддерживается только разбиение по спискам, по диапазонам и по хешу, тогда как с наследованием таблиц данные можно разделять по любому критерию, выбранному пользователем. (Однако заметьте, что если исключение по ограничению не позволяет эффективно устранять дочерние таблицы из планов запросов, производительность запросов будет очень низкой.)

5.11.3.1. Пример

В этом примере будет создана структура секционирования, равнозначная структуре из примера с декларативным секционированием. Выполните следующие действия:

Создайте « главную » таблицу, от которой будут наследоваться все « дочерние » таблицы. Главная таблица не будет содержать данные. Не определяйте в ней никакие ограничения-проверки, если только вы не намерены применить их во всех дочерних таблицах. Также не имеет смысла определять в ней какие-либо индексы или ограничения уникальности. В нашем примере главной таблицей будет measurement со своим изначальным определением:

Создайте несколько « дочерних » таблиц, унаследовав их все от главной. Обычно в таких таблицах не будет никаких дополнительных столбцов, кроме унаследованных. Как и с декларативным секционированием, эти таблицы во всех отношениях будут обычными таблицами PostgreSQL (или сторонними таблицами).

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

Типичные примеры таких ограничений:

Убедитесь в том, что ограничения не пересекаются, то есть никакие значения ключа не относятся сразу к нескольким дочерним таблицам. Например, часто допускают такую ошибку в определении диапазонов:

Это не будет работать, так как неясно, к какой дочерней таблице должно относиться значение 200. Поэтому диапазоны должны определяться следующим образом:

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

После функции мы создадим вызывающий её триггер:

Мы должны менять определение триггерной функции каждый месяц, чтобы она всегда вставляла данные в текущую дочернюю таблицу. Определение самого триггера, однако, менять не требуется.

Но мы можем также сделать, чтобы сервер автоматически находил дочернюю таблицу, в которую нужно направить добавляемую строку. Для этого нам потребуется более сложная триггерная функция:

Определение триггера остаётся прежним. Заметьте, что все условия IF должны в точности отражать ограничения CHECK соответствующих дочерних таблиц.

Хотя эта функция сложнее, чем вариант с одним текущим месяцем, её не придётся так часто модифицировать, так как ветви условий можно добавить заранее.

Примечание

На практике будет лучше сначала проверять условие для последней дочерней таблицы, если строки добавляются в неё чаще всего, но для простоты мы расположили проверки триггера в том же порядке, что и в других фрагментах кода для этого примера.

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

С правилами связано гораздо больше накладных расходов, чем с триггером, но они относятся к запросу в целом, а не к каждой строке. Поэтому этот способ может быть более выигрышным при массовом добавлении данных. Однако в большинстве случаев триггеры будут работать быстрее.

Ещё один недостаток подхода с правилами связан с невозможностью выдать ошибку, если добавляемая строка не подпадает ни под одно из правил; в этом случае данные просто попадут в главную таблицу.

Как уже можно понять, для реализации сложной иерархии таблиц может потребоваться DDL-код значительного объёма. В данном примере нам потребуется создавать дочернюю таблицу каждый месяц, так что было бы разумно написать скрипт, формирующий требуемый код DDL автоматически.

5.11.3.2. Обслуживание таблиц, секционированных через наследование

Чтобы быстро удалить старые данные, просто удалите ставшую ненужной дочернюю таблицу:

Чтобы удалить дочернюю таблицу из иерархии наследования, но сохранить к ней доступ как к самостоятельной таблице:

Чтобы добавить новую дочернюю таблицу для новых данных, создайте пустую дочернюю таблицу так же, как до этого создавали начальные:

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

5.11.3.3. Ограничения

С реализацией секционирования через наследование связаны следующие ограничения:

Система не может проверить автоматически, являются ли все ограничения CHECK взаимно исключающими. Поэтому безопаснее будет написать и отладить код для формирования дочерних таблиц и создания и/или изменения связанных объектов, чем делать это вручную.

Индексы и внешние ключи относятся к определённой таблице, но не к её иерархии наследования, поэтому с ними связаны дополнительные ограничения.

Если вы выполняете команды VACUUM или ANALYZE вручную, не забывайте, что их нужно запускать для каждой дочерней таблицы в отдельности. Команда

обработает только главную таблицу.

Операторы INSERT с предложениями ON CONFLICT скорее всего не будут работать ожидаемым образом, так как действие ON CONFLICT предпринимается только в случае нарушений уникальности в указанном целевом отношении, а не его дочерних отношениях.

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

5.11.4. Устранение секций

Устранение секций — это приём оптимизации запросов, который ускоряет работу с декларативно секционированными таблицами. Например:

Используя команду EXPLAIN и параметр конфигурации enable_partition_pruning, можно увидеть отличие плана, из которого были устранены секции, от плана без устранения. Типичный неоптимизированный план для такой конфигурации таблицы будет выглядеть так:

В некоторых или всех секциях может применяться не полное последовательное сканирование, а сканирование по индексу, но основная идея примера в том, что для удовлетворения запроса не нужно сканировать старые секции. И когда мы включаем устранение секций, мы получаем значительно более эффективный план, дающий тот же результат:

Заметьте, что механизм устранения секций учитывает только ограничения, определённые неявно ключами разбиения, но не наличие индексов. Поэтому определять индексы для столбцов ключа не обязательно. Нужно ли создавать индекс для определённой секции, зависит от того, какую часть секции (меньшую или большую), по вашим представлениям, будут сканировать запросы, обращающиеся к этой секции. Индекс будет полезен в первом случае, но не во втором.

Устранение секций можно отключить, воспользовавшись параметром enable_partition_pruning.

Примечание

5.11.5. Секционирование и исключение по ограничению

Исключение по ограничению — приём оптимизации запросов, подобный устранению секций. Прежде всего он применяется, когда секционирование осуществляется с использованием старого метода наследования, но он может быть полезен и для других целей, включая декларативное секционирование.

Исключение по ограничению работает во многом так же, как и устранение секций; отличие состоит в том, что оно использует ограничения CHECK всех таблиц (поэтому оно так и называется), тогда как для устранения секций используются границы секции, которые существуют только в случае декларативного секционирования. Ещё одно различие состоит в том, что исключение по ограничению применяется только во время планирования; во время выполнения секции из плана удаляться не будут.

То, что исключение по ограничению использует ограничения CHECK (вследствие чего оно работает медленнее устранения секций), иногда может быть и преимуществом. Ограничения могут быть определены даже для декларативно секционированных таблиц, в дополнение к внутренним границам секций, и тогда исключение по ограничению сможет дополнительно убрать некоторые секции из плана запроса.

Применяя исключения по ограничению, необходимо учитывать следующее:

Исключение по ограничению применяется только при планировании запроса, в отличие от устранения секций, которое может осуществляться и при выполнении запроса.

Ограничения секций должны быть простыми, иначе планировщик не сможет вычислить, какие дочерние таблицы не нужно обрабатывать. Для секционирования по спискам используйте простые условия на равенства, а для секционирования по диапазонам — простые проверки диапазонов, подобные показанным в примерах. Рекомендуется создавать ограничения секций, содержащие только такие сравнения секционирующих столбцов с константами, в которых используются операторы, поддерживающие B-деревья. Это объясняется тем, что в ключе разбиения допускаются только такие столбцы, которые могут быть проиндексированы в B-дереве.

При анализе для исключения по ограничению исследуются все ограничения всех дочерних таблиц, относящихся к главной, так что при большом их количестве время планирования запросов может значительно увеличиться. Поэтому устаревший вариант секционирования, основанный на наследовании, будет работать хорошо, пока количество дочерних таблиц не превышает примерно ста; не пытайтесь применять его с тысячами дочерних таблиц.

5.11.6. Рекомендации по декларативному секционированию

К секционированию таблицы следует подходить продуманно, так как неудачное решение может отрицательно повлиять на скорость планирования и выполнения запросов.

Также важно правильно выбрать число секций, на которые будет разбиваться таблица. Если их будет недостаточно много, индексы останутся большими, не улучшится и локальность данных, вследствие чего процент попаданий в кеш окажется низким. Однако и при слишком большом количестве секций возможны проблемы. С большим количеством секций увеличивается время планирования и потребление памяти как при планировании, так и при выполнении запросов, о чём рассказывается далее. Выбирая стратегию секционирования таблицы, также важно учитывать, какие изменения могут произойти в будущем. Например если вы решите создавать отдельные секции для каждого клиента, и в данный момент у вас всего несколько больших клиентов, подумайте, что будет, если через несколько лет у вас будет много мелких клиентов. В этом случае может быть лучше произвести секционирование по хешу ( HASH ) и выбрать разумное количество секций, но не создавать секции по списку ( LIST ) в надежде, что количество клиентов не увеличится до такой степени, что секционирование данных окажется непрактичным.

Вложенное секционирование позволяет дополнительно разделить те секции, которые предположительно окажутся больше других. Также можно использовать разбиение по диапазонам с ключом, состоящим из нескольких столбцов. Но при любом из подходов легко может получиться слишком много секций, так что рекомендуется использовать их осмотрительно.

Источник