Блог

Недавно я добавил новый столбец в свою таблицу Postgres с 40 миллионами строк (версия 9.6) с

 ALTER TABLE queries
   ADD COLUMN ml_partition_source UUID;
 

Затем в другой транзакции я выполнил

 ALTER TABLE queries
   ALTER COLUMN ml_partition_source
      SET DEFAULT public.gen_random_uuid();
 

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

Теперь я хотел бы заполнить этот столбец для всех query s, которые существовали до добавления нового столбца, без блокировки таблицы. Один из способов сделать это — использовать CRUD API, который у меня есть, но некоторые грубые расчеты показывают, что это займет ~ 22 дня (возможно, производительность моего API можно улучшить, но это совсем другой вопрос). Вместо этого я попытался написать функцию postgres:

 CREATE OR REPLACE FUNCTION backfill_partition_source()
  RETURNS void AS $
declare
  query_ record;
BEGIN
  for query_ in
  select * from api_mldata.queries where ml_partition_source is null
  loop
    update api_mldata.queries SET ml_partition_source = public.gen_random_uuid() where id = query_.id;
  end loop;
END;
$ LANGUAGE plpgsql;
 

и выполнил это с select backfill_partition_source(); помощью . Но это также привело к блокировке таблицы.

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

РЕДАКТИРОВАТЬ: одна из моих идей заключается в том, чтобы «разбить» скрипт Postgres на части для одновременной работы со 100 тыс. строк или что-то в этом роде, а затем выполнить скрипт в цикле. Таким образом, оператор select станет

 select * from api_mldata.queries
where ml_partition_source is null
limit 100000;
 

Вы не можете обойтись без блокировки вообще, но вы можете сохранить блокировки достаточно короткими.

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

 UPDATE api_mldata.queries
SET ml_partition_source = DEFAULT
WHERE id BETWEEN 1 AND 999999;
 

Вот id первичный ключ таблицы.

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

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