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

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

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

• Транзакционный DDL (Data Definition Language);
• Блокирование строк;
• Операции над таблицей;
• Операции над столбцами;
• Операции над индексами;
• Ограничения;
• Перечисляемые типы;
• Бонус: библиотека для Ruby on Rails.

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

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

• Актуальный код и схемы БД должны иметь прямую совместимость с новым кодом и новыми схемами. Это позволяет вносить изменения постепенно, а не сразу на всех используемых серверах.
• Новый код и новые схемы должны быть обратно совместимы с актуальным кодом и схемами. Это позволяет без особых проблем отменить любые изменения, в случае возникновения непредвиденных ошибок.

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

• Любые блокировки таблиц или индексов держались не более двух секунд.
• Способы отмены вносимых изменений не требовали откатывать схему базы данных к предыдущей версии.

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

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

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

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

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

• ACCESS EXCLUSIVE: запрещено любое использование заблокированной таблицы.
• SHARE ROW EXCLUSIVE: запрещены команды DDL, выполняющиеся параллельно, а также модификация строк (чтение разрешено).
• SHARE UPDATE EXCLUSIVE: запрещены только команды DDL, выполняющиеся параллельно.

Заметьте: Понятие “команды DDL, выполняющиеся параллельно” в данном контексте включают в себя операции VACUUM и ANALYZE.

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

ALTER TABLE foos ADD COLUMN bar INTEGER; PostgreSQL попытается получить блокировку уровня ACCESS EXCLUSIVE на всей таблице foos.

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

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

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

Начиная с PostgreSQL 9.3, вы можете настроить параметр lock_timeout, чтобы контролировать то, насколько долго PostgreSQL будет ожидать получения контроля над таблицей. Если вы вдруг используете версию 9.2 или более раннюю (они, к слову, не поддерживаются, вам стоит обновиться!), вы можете добиться того же результата, используя параметр statement_timeout с явным выражением LOCK<table>.

Зачастую блокировка уровня ACCESS EXCLUSIVE действительно необходима только на очень короткий период, который требуется PostgreSQL, чтобы обновить его catalog tables (таблицы с метаинформацией). Ниже мы рассмотрим случаи, когда достаточно более слабой блокировки или когда можно применить альтернативные подходы, чтобы избежать длительной приостановки SELECT/INSERT/UPDATE/DELETE.

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

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

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

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

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

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

Удаление таблицы по понятным причинам требует блокировки уровня ACCESS EXCLUSIVE. Если таблица уже не используется, вы можете спокойно её удалить. Но прежде чем на самом деле выполнить DROP TABLE ... вам следует проверить документацию и код, чтобы удостовериться, что все упоминания о ней на самом деле стёрты. Чтобы перепроверить это, вы можете запросить у PostgreSQL статистику использования таблицы (используя представление pg_stat_user_tables2).

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

Вероятно, ни для кого не станет открытием, что переименование таблицы требует ACCESS EXCLUSIVE. Очень маловероятно, что ваш код сможет безопасно обработать переименование таблицы прямо налету — это возможно только если в таблицу никто не пишет и не берёт из неё данные.

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

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

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

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

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

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

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

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

• Добавить новый столбец (без значений по умолчанию).
• Назначить столбцу значение по умолчанию.
• Заполнить этим значением уже существующие строки по отдельности.

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

Ограничения not null. Добавление столбца с ограничением NOT NULL возможно только в двух случаях: если в таблице нет строк или если был указан DEFAULT. Первый случай тривиален — потребуется только изменение каталога. Во втором же случае следует проделать все описанные выше действия для значений по умолчанию.

Заметьте: После добавления нового столбца все запросы вида SELECT * FROM ... начнут возвращать новый столбец. Важно, чтобы код, который будет работать с этой таблицей, мог безопасно обработать новый столбец. Лучше просто не использовать *, всегда указывая столбцы явно.

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

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

• Приведение VARCHAR к типу TEXT (начиная с версии 9.1); а точнее всегда, когда старый тип бинарно совместим с новым типом и для преобразования не требуется никаких фактических операций.
• Старый тип является частным случаем нового (начиная с версии 9.1).
• Когда увеличивается или удаляется заданное ограничение на длину или точность: например VARCHAR(5) → VARCHAR(10) и VARCHAR(5) → VARCHAR (начиная с версии 9.2).

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

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

• Добавьте новый столбец new_<column>.
• Осуществляйте запись одновременно в оба столбца (например, с помощью триггеров BEFORE INSERT/UPDATE).
• Заполните новый столбец копиями значений из старого.
• Переименуйте <column> в old_<column>, а new_<column>, соответственно, в <column>; делайте это внутри единой транзакции и явного выражения LOCK <table>.
• Удалите старый столбец.

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

Удалять столбец нужно с крайней осторожностью. Для обновления каталога оно требует полной блокировки таблицы, но не влечёт за собой физического изменения строк. Если в настоящее время столбец не используется, вы можете безопасно удалить его. Важно, однако, проверить, что на этот столбец не ссылаются никакие зависимые объекты (которые небезопасно удалять). В частности, любые индексы, использующие столбец, должны быть удалены отдельно с использованием безопасного DROP INDEX CONCURRENTLY. В противном случае, они будут автоматически удалены вместе со столбцом, и всё это время будет действовать блокировка уровня ACCESS EXCLUSIVE. Чтобы проверить, есть ли у вас такие объекты, вы можете сделать запрос к pg_depend4.

Прежде чем запускать ALTER TABLE ... DROP COLUMN ... на продакшне, стоит удостовериться, что все ссылки на этот столбец в документации и коде были окончательно убраны. Это позволит безопасно откатиться к релизам, выпущенным до того, как был удалён столбец.

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

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

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

Если вы просто запустите CREATE INDEX ..., то получите блокировку уровня ACCESS EXCLUSIVE на всей индексируемой таблице. А вот если вы выполните CREATE INDEX CONCURRENTLY ... , то блокировка будет всего лишь уровня SHARE UPDATE EXCLUSIVE. Правда, вместо одного сканирования таблицы придётся выполнить два. При уровне блокировки во втором случае будут разрешены и чтение, и запись в таблицу.

Предостережения:

• Несколько созданий индексов, выполняющиеся параллельно на одной таблице, не завершат выполнение ни одного из CREATE INDEX CONCURRENTLY ...  до тех пор, пока самый медленный из них ещё работает.
• CREATE INDEX CONCURRENTLY ... не может быть выполнен внутри транзакции, вместо этого транзакциями неявно управляет PostgreSQL. Из-за этого никакие auto-vacuum’ы не смогут очистить ненужные кортежи, которые появились после начала построения индекса, и до завершения этого процесса. Если у таблицы большой объём изменений (особенно плохо, если сама таблица при этом мала), это может привести к крайне неоптимальному времени выполнения запроса.
• CREATE INDEX CONCURRENTLY ... завершит выполнение только после того, как завершатся все транзакции, использующие таблицу.

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

Стандартное выражение DROP INDEX ... получает ACCESS EXCLUSIVE на всей таблице на всё время удаления индекса. Для небольших индексов это может не быть проблемой — это должна быть весьма короткая операция. Однако для огромных индексов работа с файловой системой может занять значительное время. Нам на помощь придёт DROP INDEX CONCURRENTLY ..., которая потребует блокировку уровня SHARE UPDATE EXCLUSIVE; запись и чтение будут продолжаться, пока мы удаляем индекс.

Подводные камни использования DROP INDEX CONCURRENTLY ...:

• Этот запрос не может быть использован для удаления индекса, который поддерживал какое-либо ограничение (например PRIMARY KEY или UNIQUE).
• Он не может быть использован как часть транзакции, ими управляет PostgreSQL “под капотом”. Из-за этого никакие auto-vacuum’ы не смогут очистить ненужные кортежи, которые появились после начала построения индекса, и до завершения этого процесса. Если у вас есть таблица с большим объёмом изменений (особенно плохо, если сама таблица при этом мала) это может привести к крайне неоптимальному времени выполнения запроса.
• Запрос завершит выполнение только после того, как завершатся все транзакции, использующие таблицу.

Обратите внимание: DROP INDEX CONCURRENTLY ... был добавлен только в Postgres 9.2. Если вы всё ещё работаете с версией 9.1 или ниже, вы можете добиться примерно такого же результата, если отметите индекс как некорректный (invalid) и не готовый к записи; затем вам нужно будет сбросить буфер с помощью расширения pgfincore. После этого можно просто удалять индекс.

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

ALTER INDEX ... RENAME TO ... требует блокировку уровня ACCESS EXCLUSIVE на переименовываемом индексе, блокируя чтение и запись в соответствующую таблицу. Однако коммит, который должен стать частью PostgreSQL 12 понижает это требование до SHARE UPDATE EXCLUSIVE.

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

REINDEX INDEX ...  также требует ACCESS EXCLUSIVE на индексе. Чтобы этого не допускать, рекомендуется следующий алгоритм:

• Создать новый индекс, как это описано выше, который повторял бы существующий.
• Удалить старый индекс, наименее затратным способом (описан выше).
• Переименовать новый индекс, чтобы он повторял имя старого.

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

Ограничения

NOT NULL

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

А вот добавление ограничения NOT NULL к существующему столбцу требует ACCESS EXCLUSIVE на время проведения полного скана таблицы, чтобы удостовериться, что в ней нет null-значений. Вместо этого вам следует:

• Добавить ограничение проверки CHECK, которое бы требовало от значений столбца не быть null-ами. Сделать это можно с помощью ALTER TABLE <table> ADD CONSTRAINT <name> CHECK (<column> IS NOT NULL) NOT VALID;. Здесь NOT VALID сообщает PostgreSQL, что нет необходимости проводить полную проверку, чтобы удостовериться, что все строки соответствуют условию.
• Вручную проверить, что все строки содержат значения, отличные от null.
• Валидировать наложенное ограничение с помощью ALTER TABLE <table> VALIDATE CONSTRAINT <name>;. Выполнение этого выражения заблокирует получение других EXCLUSIVE блокировок таблицы, но не будет мешать записи или чтению.

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

Внешний ключ

ALTER TABLE ... ADD FOREIGN KEY требует блокировку SHARE ROW EXCLUSIVE (по крайней мере с версии 9.5) на обеих таблицах — и на изменяемой, и на той, на которую мы ссылаемся. Такая блокировка, конечно, не будет блокировать запросы SELECT, однако длительный запрет на внесение изменений тоже неприемлем.

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

• ALTER TABLE ... ADD FOREIGN KEY ... NOT VALID: добавит внешний ключ и начнёт применять ограничение ко всем новым выражениям INSERT/UPDATE. При этом существующие строки не будут проверены на соответствие ограничению. Это операция тоже требует SHARE ROW EXCLUSIVE, но лишь на очень короткое время.
• ALTER TABLE ... VALIDATE CONSTRAINT <constraint> проверит все существующие строки на соответствие указанному ограничению. Проверка требует  только SHARE UPDATE EXCLUSIVE , поэтому может работать параллельно с чтением данных и записью.

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

ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) требует блокировку уровня ACCESS EXCLUSIVE. Однако, как и в случае с внешними ключами, эту операцию можно разделить на две:

• ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) NOT VALID добавит ограничение проверки и начнёт применять ограничение ко всем новым выражениям INSERT/UPDATE. При этом существующие строки проверяться не будут. Это операция требует ACCESS EXCLUSIVE.
• ALTER TABLE ... VALIDATE CONSTRAINT <constraint> проверит все существующие строки. Проверка требует SHARE UPDATE EXCLUSIVE на таблице. Если ограничение ссылается на другую таблицу, на ней будет установлена блокировка уровня ROW SHARE. Напомним, она лишь откладывает операции, которые требуют полной блокировки таблицы.

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

ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (...) требует блокировку уровня ACCESS EXCLUSIVE. И снова делим операцию на две:

• Конкурентно создайте индекс с ограничением на уникальность (как описано выше). Это действие само по себе будет требовать уникальности значений. Однако, если вам нужно именно ограничение в смысле constraint (или первчиный ключ), то вы можете добавить его следующим шагом.
• ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX <index> создаст ограничение, используя уже существующий индекс. Операция всё ещё требует ACCESS EXCLUSIVE, но лишь для быстрых операций над каталогом.

Обратите внимание: если вы указываете PRIMARY KEY вместо UNIQUE, то все столбцы, которые могли содержать null‘ы получат ограничение NOT NULL. Это потребует полного скана таблицы, и на данный момент этого никак нельзя избежать. Детали описаны в разделе про NOT NULL.

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

ALTER TABLE ... ADD CONSTRAINT ... EXCLUDE USING ... требует блокировки ACCESS EXCLUSIVE. Если вы добавите ограничение исключительности, то это потянет за собой создание индекса, и, к сожалению, не получится использовать уже сформированный индекс (как мы делали с ограничением уникальности выше).

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

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

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

В PostgreSQL добавили выражение ALTER TYPE <enum> RENAME VALUE <old> TO <new>. Оно не требует блокировки таблиц, которые используют перечислимый тип.

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

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

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

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

Примечания

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

			SELECT
  psa.datname as database,
  psa.query as current_query,
  clock_timestamp() - psa.xact_start AS transaction_age,
  array_agg(distinct c.relname) AS tables_with_locks
FROM pg_catalog.pg_stat_activity psa
JOIN pg_catalog.pg_locks l ON (psa.pid = l.pid)
JOIN pg_catalog.pg_class c ON (l.relation = c.oid)
JOIN pg_catalog.pg_namespace ns ON (c.relnamespace = ns.oid)
WHERE psa.pid != pg_backend_pid()
  AND ns.nspname != 'pg_catalog'
  AND c.relkind = 'r'
  AND psa.xact_start < clock_timestamp() - '5 seconds'::interval
GROUP BY psa.datname, psa.query, psa.xact_start;
		

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

			SELECT
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_tup_ins,
  n_tup_upd,
  n_tup_del
FROM pg_catalog.pg_stat_user_tables
WHERE relname = '<table>';
		

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

			SELECT
  relname,
  relfilenode
FROM pg_catalog.pg_class
WHERE relname in (
  '<table>',
  '<index>'
)
-- Сортируем по oid для удобства, если вы проверяете несколько отношений.
ORDER BY oid;
		

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

			SELECT
  d.objid::regclass AS owning_object,
  d.refobjid::regclass AS dependent_object,
  a.attname AS dependent_column,
  d.deptype -- Значение этого дипа задокументировано на https://www.postgresql.org/docs/current/catalog-pg-depend.html
FROM pg_catalog.pg_depend d
LEFT JOIN pg_catalog.pg_attribute a ON d.refobjid = a.attrelid
  AND d.refobjsubid = a.attnum
WHERE refobjid = '<table>'::regclass
AND a.attname = '<column>';