Large update and delete based on another table I have a very active PostgreSQL 10.4 DB, dealt with through SQLAlchemy, so SQL is not my forte.

Among its tables, there are master with a primary key id and some nonindexed string column value, and slave with a foreign key mid towards master.id. The table master is just a bit short of 100M rows and is very frequently accessed by the app (usually several times per second, but always just a few rows at a time), while slave has roughly double that and is accessed in a similar manner.

I want to do two things:


Delete from slave and master:

delete from slave
    using master
    where
        slave.mid = master.id
        and master.value in ('some', 'set', 'of', 'values')
delete from master
    where value in ('the', 'same', 'set', 'of', 'values')


SQLAlchemy didn't create any cascade delete rules for master, so I cannot just run the latter of the two queries, as it causes a foreign key constraint violation.
Update master:

update master
    set value=&lt;case expression&gt;
    where value in ('a', 'different', 'set', 'of', 'values')



The number of rows to be deleted is estimated to be few millions (maybe a mil or 2 mils; certainly not much more).

I have another constraint that I'd like to keep (but could ditch it if absolutely necessary): a dry run is done the same way as the live execution, except that I call rollback instead of commit. So, for example, doing the update top would end up in an infinite loop if I kept rolling back after each call.

The above codes work nicely on a small DB used to just test if the code works as intended, but they are super-slow on a copy of the production DB. So, my first issue here is the performance.

My second issue would be locking, because the production is hit a lot and it would be nice to be able to do this seamlessly, without causing any downtime and/or timeouts for the app.

This got me to start thinking about doing deletes/updates in batches, but this goes beyond my knowledge of PostgreSQL (or SQL in general). How can I get these to work the way I want them to?

Edit

After some consideration, I altered the slave tables (there is two of them) to delete on cascade, which solved the deletion problem. There is still an issue of efficient updating, most likely in chunks.
A delete with an IN condition might be faster than the JOIN: 

delete from slave
where mid in (select master.id
              from master 
              where master.value in ('some', 'set', 'of', 'values'));

delete from master
    where value in ('the', 'same', 'set', 'of', 'values');


Another option that might be faster is to delete both tables in a single statement. This can also be used to delete multiple slaves.

with removed_master (id) as (
  delete from master
  where value in ('some', 'set', 'of', 'values')
  returning id
), remove_slave1 as (
  delete from slave_one
  where mid in (select id from removed_master)
)
delete from slave_two
where mid in (select id from removed_master);


This works as the foreign key constraints are evaluated when the statement is finished, not when each individual row is deleted. This has the additional benefit that you have to specify the "master values" only once.