Как обойти ограниченное количество записей в таблице и сделать одинаковые преобразования?
Когда мы используем команду UPDATE (обновления) в PostgreSQL, то в большинстве примеров из интернет её применяют над всеми возможными записями таблицы. Она ради этого и создавалась.
Представим, что мы хотим сделать обновление 140 миллионов записей в таблице. Вот такое у нас масштабное преобразование. Если запускать эту команду от начала и до конца, то она может серьёзно нагрузить систему или вовсе не успеть завершиться. В случае неудачи, транзакция будет отклонена и данные в базе начнут восстанавливаться. Это очень плохо.
Я это к тому, что чем длиннее по времени запрос в СУБД, тем сложнее ему будет завершиться до конца. Риски возрастают. Было бы гораздо уместнее разделить одно большое обновление на несколько маленьких.
Также бывают ситуации, когда просто хочется проверить скорость обновлений на примере выборки из 100 записей. Если в плане у нас стоит 140 миллионов «замен», то было бы хорошо заранее прикинуть сколько по времени будут обновляться 100 из них. После этого уже можно будет понимать в какой период лучше всего запустить глобальный UPDATE по всем записям.
В общем, есть смысл делить большое на малое. Тем более если мы общаемся с базой из своего приложения через посредников. Было бы очень глупо на долгое время блокировать всю таблицу, чтобы пользователи не смогли нормально работать с приложением.
При первом изучении PostgreSQL очень хочется по логике SELECT вставлять в запрос выражения вроде «LIMIT 100» или «LIMIT 1000«. Но, к сожалению, они не работают в таком виде в UPDATE в SQL, хотя пользователю так гораздо понятнее.
Как делить UPDATE на кусочки?
Команда UPDATE может вызываться с подзапросами. Это значит, что сначала можно построить таблицу с нужным лимитом записей, а уже потом сделать по этим записям преобразование (обновление).
Со стороны это выглядит так, как-будто мы обновляем не все возможные записи, подходящие под обновление, а только часть из них. При этом мы работаем с нужной таблицей, а не с каким-то её клоном.
За эту функциональность отвечает команда WITH, которую нужно прописывать перед UPDATE.
Давайте на каком-нибудь реальном примере поглядим на то, как это работает.
Таблица для примера частичного обновления записей в PostgreSQL
— Тестовая таблица
CREATE TABLE IF NOT EXISTS test.t_2022_11_15 (
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
username text,
CONSTRAINT t_2022_11_15_id_pkey PRIMARY KEY (id)
);
— Вставляем тестовые данные
INSERT INTO test.t_2022_11_15 (username) VALUES (unnest(ARRAY[‘А‘,’Б‘,’В‘,’Г‘,’Д‘,’Е‘,’Ё‘,’Ж‘,’З‘,’И‘,’Й‘,’К‘]));
— Смотрим на результат вставки
SELECT * FROM test.t_2022_11_15;
Так она выглядит в человеческом виде:
Представим, что мы хотим произвести обновление и к некоторым записям с ID более 4 дописать цирфы.
У нас только одно условие. Под это условие попадают записи с ID = 5 по ID = 12. Всего под обновление попадает 8 записей.
Но нам для теста нужно обновлять по 2 записи за 1 вызов. Как это сделать?
Как обновлять записи с лимитом 2 в PostgreSQL?
— Обновляем записи с лимитом 2
WITH subquery AS (
SELECT * FROM test.t_2022_11_15 WHERE id > 4 LIMIT 2
)
UPDATE test.t_2022_11_15 AS dp
SET username = dp.username || ‘-0000’
FROM subquery
WHERE dp.id = subquery.id;
Мы написали WITH и после него название «subquery«. Чтобы было понятно, что обновление будет работать с подзапросом.
В подзапросе работает лимит на количество отбираемых записей — «LIMIT 2«.
После выполнения команды частичного обновления, в интерфейсе pgAdmin мы получаем сообщение об успехе.
Если теперь мы посмотрим на состояние нашей таблицы, то мы наглядно увидим, что две записи успешно обновились из всех восьми возможных.
То есть мы выполнили задачу по лимитированному обновлению записей в таблице PostgreSQL.
Если мы ещё раз применим данную операцию, то скорее всего получим обновление других записей из таблицы, подподающих под наше условие.
Почему так происходит в данном конкретном примере? Почему обновляются другие две записи, если по логике второе частичное обновление также должно было бы обновить ID = 5 и ID = 6.
Всё дело в том, что в нашем подзапросе нет чёткого критерия по ВЫБОРКЕ двух записей с ID более 4. У нас отсутствует сортировка.
PostgreSQL так устроен, что любая выборка возвращает множества, а не последовательности. PostgreSQL заточен на быстрое получение данных из базы. Эта быстрота достигается внутренней логикой хранения данных. Поэтому мы как пользователи можем только задавать ограничения на запросы.
Данные хранятся на диске, и только PostgreSQL помнит куда он их положил. Поэтому если в запросе SELECT мало ограничений на выборку, то собранные результаты могут выдаваться без сортировок, а по принципу «За что проще схватиться, то и заберу».
Могу привести пример из жизни. Представьте, что у вас корзина с яблоками. Я прошу вас дать мне два зелёных яблока. Вы же не полезете в самую глубь корзины, чтобы где-то там поискать зелёные яблоки. Если вы увидите два зелёных яблока на поверхности, то вы мне их тут же и предложите. А вот если я их возьму в руку, потом надкушу и положу куда-то обратно в корзину и попрошу вас ещё раз вытянуть мне два зелёных, то во второй раз скорее всего два других яблока покажутся вам более удачными для выдачи. Надкушенные вы возможно и не возьмёте уже так как они будут лежать подальше от новых двух зелёных.
Как соблюдать очерёдность в частичных обновлениях?
Чтобы со 100% уверенностью двигаться по записям и делать в них последовательные частичные обновления, нужно более точно указывать условия, при которых отборы будут возвращать другие результаты.
В нашем примере мы добавляли четыре нуля к имени пользователя. Поэтому это же правило можно использовать в подзапросе. «Отбери записи где id > 4 И username не включает 0000». Как-то так.
Информационные ссылки
Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org
Официальный сайт СУБД PostgreSQL — https://www.postgresql.org
Команды SQL — https://postgrespro.ru/docs/postgresql/15/sql-commands
Команда UPDATE — https://postgrespro.ru/docs/postgresql/15/sql-update
