SQL-Ex blog

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

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

Общие рекомендации по обновлению таблицы в PostgreSQL

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

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

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


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


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


• Данные, хранящиеся в TOAST, не перезаписываются при обновлении строки.


• Преобразование некоторых типов данных не требует полной перезаписи таблицы, начиная с Postgres 9.2. Например, преобразование из VARCHAR(32) в VARCHAR(64).

Стратегии обновления таблицы в PostgreSQL

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

1. Инкрементные обновления

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

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

2. Создание новой таблицы

Самый быстрый способ обновления большой таблицы - это создать новую.

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

CREATE TABLE new_tbl

(

  field1 int,

  field2 int,

  ...

);

INSERT INTO new_tbl(field1, field2, ...)

(

  SELECT FROM ... -- использование новой логики для вставки обновленных данных

);

CREATE INDEX -- добавление ваших ограничений и индексов для new_tbl

DROP TABLE tbl;

ALTER TABLE tbl_new RENAME TO tbl;

Повторное создание существующей таблицы

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

Если ваша таблица помещается в памяти, вам следует увеличить temp_buffers во время выполнения этой транзакции. Использование RAM вместо диска для хранения временной таблицы очевидно улучшит производительность:

SET temp_buffers = 3000MB; -- измените это значение на свое

-- создание и заполнение временной таблицы

CREATE TEMP TABLE tmp_tbl

(

  field1 int,

  field2 int,

  ...

);

INSERT INTO tmp_tbl(field1, field2, ...)

(

  SELECT FROM ...

)

-- удаление индексов для tbl, если необходимо

TRUNCATE tbl;

-- изменение tbl и добавление новых столбцов, если необходимо

INSERT INTO tbl

SELECT * FROM tmp_tbl; -- обратная вставка строк

-- пересоздание индексов, если необходимо

4. Обработка одновременных записей

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

Наиболее простой способ сделать это - наложить блокировку SHARE LOCK на таблицу на время транзакции:

LOCK TABLE tbl IN SHARE MODE;

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

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

CREATE RULE deleted_rule AS ON DELETE

TO tbl

DO INSERT INTO tbl_deletes VALUES

(

  OLD.id

);

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

Заключение

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