Есть ли штатный способ разбить большой UPDATE на транзакционные части?

Нужно выполнить апдейт столбца в таблице на 10 млн позиций.
т.к. update транзакционный то пока все строки не заапдейтятся.
Вопрос. Можно ли штатными средствами сделать так, чтобы UPDATE шел или построчно или какими-то кусками?
Да можно написать кучу:
UPDATE .... WHERE ID > 0 AND ID < 1000
UPDATE .... WHERE ID > 1000 AND ID < 2000

Но может можно как-то попроще?
  • Вопрос задан
  • 314 просмотров
Пригласить эксперта
Ответы на вопрос 3
@d-stream
Готовые решения - не подаю, но...
Долго ковырял подобное, в итоге пришел примерно к такой реализации:
- таблица pk для обновления
- прикладная часть запрашивает диапазоны из этой таблицы и дёргает процедуру обновления с указанием диапазона
- старт идёт с короткого диапазона, если время выполнения не превышает психологический порог - на следующей итерации - берётся порция большего размера и так до порога времени выполнения

Правда это было несколько специфичным - в рамках десктопного приложения и отображением прогрессбара, но вот такой вариант адаптации размера "порции" оказался наиболее универсальным для разных серверов
Ответ написан
Комментировать
mayton2019
@mayton2019
Bigdata Engineer
Можно так попробовать

CREATE EXTENSION pgcrypto;

BEGIN;
 // Partition 1 of 16
 UPDATE .... WHERE left(digest(ID, 'sha256'),1) = '0' 
COMMIT;

BEGIN;
 // Partition 2 of 16
 UPDATE .... WHERE left(digest(ID, 'sha256'),1) = '1' 
COMMIT;


У Oracle была более простая функция hash(...) которая разделяла сет ключей сразу на известное число партишенов. Там второй аргумент указывался. Но в PG я не знаю аналога. Может знающие подскажут.
Ответ написан
Melkij
@Melkij
PostgreSQL DBA
Как делить одну большую операцию на части - необходимо базе объяснять.

PG14 - нагенерировать запросов
update ... where ctid between '(N,1)' and '(N+1000,1)'

где N от 0 до relpages из pg_class и запустить.

Если хочется - можно обернуть в анонимку DO, каждую очередную итерацию делая commit.

На более старых версиях - выбрать какой-нибудь индекс скоррелированный с физическим расположением данных и идти вдоль него, нагенерировав запросов
update ... where somecolumn between ? and ?
кусками некоторого размера. Не имеет решающего значения соблюдение размера одного обновления, лишь бы выполнялся за разумное время.

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

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы