How to quickly delete a large number of rows in postgresql?

Question

PostgreSQL

I have a table with 2 million positions. There is a list of id (200 thousand pieces) that must be deleted.
With a simple query "DELETE FROM table WHERE id = ?" in a loop (from java) about 90 positions per minute are removed.
How can this operation be accelerated?

Answer 1

Safe approach for large tables:

create unlogged table list_for_delete (id int);
insert into list_for_delete values ....

with to_rm as (
    select id from list_for_delete limit 10000
), rm as (
    delete from list_for_delete where id in (select id from to_rm)
)
delete from tablename  where id in (select id from to_rm);

vacuum analyze tablename;
drop table list_for_delete ;

repeat cte with delete until affected rows = 0. Look at the replication lag, add delays between requests and increase / decrease the pack size depending on the impact on the prod.
vacuum at the end, you can walk additionally after half. With such a volume, you can not make a vacuum at all with your hands and leave it to autovacuum. And for tables it is more at mass change it makes sense.

Answer 2

Are you calling a single row delete request each time? Then 90 requests is still a good result, not a single enemy dudos is terrible with such methods, your own is enough.
Use other conditions (for example, formalize the conditions under which you received your list of deleted ids) or create a temporary table with the necessary ids, then use 1 query:
where dellisttable is a temporary table that stores a list of id's to be deleted