demo.sql

drop table if exists t1;

create table t1 (
   id   integer      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   name varchar(255) NOT NULL UNIQUE
);

insert into t1 (name)
select md5(i::text)
from generate_series(1, 200000) AS i;

table t1;

select count(*)
from t1 as t
where name like 'a%';

/*
БЫЛО
Количество удаляемых записей неограничено, время выполнения запроса непредсказуемо.
При большом кол-ве удаляемых записей в большом количестве параллельных транзакций
подобные запросы создают большую нагрузку на БД, очереди и взаимоблокировки.
*/
delete from t1 where name like 'a%';

-- СТАЛО
with options AS (
    select 4096 as batch_limit -- на следующей итерации цикла приложения вместо 4096 нужно подставить значение next_limit
),
s1 as (
    select id
    from t1 as t
    where name like 'a%'
    limit (select batch_limit from options)
    for update of t -- пытаемся заблокировать строки таблицы от изменения в параллельных транзакциях
    skip locked -- если строки заблокировать не удалось, пропускаем их (они уже заблокированы в параллельных транзакциях)
),
s2 as (
    -- такой же запрос, как выше, только без `for update of t skip locked`
    select id
    from t1 as t
    where name like 'a%'
    limit (select batch_limit from options)
),
d1 as (
    -- удаляем заблокированные записи
    delete from t1
    where id in (select id from s1) -- наиболее эффективно удаление по первичному ключу
    returning id
),
d2 as (
    -- если заблокированых записей нет, удаляем "остатки" записей
    -- при наличии заблокированных записей в параллельной транзакции этот запрос встанет в очередь
    delete from t1
    where not exists(select from s1)
          and id in (select id from s2) -- наиболее эффективно удаление по первичному ключу
    returning id
)
select
    r1.locked_deleted + r2.remains_deleted as deleted, -- кол-во удалённых записей
    -- вычисляем лимит для следующего выполнения этого запроса в цикле приложения; если 0, то выходим из цикла
    case
        -- если нет удалённых записей, то возвращаем ноль
        when (r1.locked_deleted + r2.remains_deleted) = 0 then 0
        --если время выполнения запроса > 1 секунды, то уменьшаем лимит в 2 раза, иначе увеличиваем лимит в 2 раза
        else (case when clock_timestamp() - now() > '1s' then (o.batch_limit / 2)::int else o.batch_limit * 2 end)
    end as next_limit
from (select count(*) as locked_deleted from d1) as r1,
     (select count(*) as remains_deleted from d2) as r2,
     options AS o;