Large update and delete based on another table

Question

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:

1. 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.

2. Update master:

   update master
       set value=<case expression>
       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.

Answer 1

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.

Answer 2

Sadly, PostgreSQL doesn't have an UPDATE TOP(n) or UPDATE...LIMIT n

You would have to emulate by using something like:

update master set ... where pk in (select pk from master where ... LIMIT n);

What you would really like to do is "run the update until you detect someone is waiting on you, then end it without throwing an error". That way you wouldn't have to guess about what the correct value of n would be. But there is no way to do that. And it wouldn't be easy to implement, especially since FK checking is queued up and executed at the end of the update, so simply ending without an error could potentially take a very long time.

Answer 3

I've finished this quite successfully. The final runs (both the dry run and the real thing) took roughly 13min for updating some 40M rows and deleting almost 1M. I'll try to recap what I did.

First, as mentioned above, I altered my tables to have cascade deleting, which reduced my delete across 3 tables to a very simple delete from ... where, while still making it very efficient. Also, I did a bunch of where column = value queries instead of one massive where column in ('list', 'of', 'values'). I will explain why below.

Second, my updates were basically done the way a_horse_with_no_name suggested in his answer for deletes because it turned out that I can chain them too. So,

with update_master (id) as (
    select id from master
    where column = {old_value}
    limit {limit}
), update_slave as (
    update slave
    set column2={new_value2}
    where mid in (select id from update_master)
)
update master
set
    column = {new_value}
where
    id in (select id from update_master)

The {xxx} values are Python formats that I inject directly in my SQL expressions. I did escape them with value.replace("'", "''"), but it's crucial here that they are simple strings fed by my app, not by users, so it's not risky, and injecting them directly made it easier to debug them. Any user-provided data and this part would be better done by binding.

The third thing that I did, which is visible above, was replacing my original set value = <case expression> with a simple value = {new_value}, forcing me to run many queries.

Now, both the replacements (of in with = in the first part of this, and <case expression> with = in the third part) was done to fragment the requests. On relatively small datasets, in and case optimise things because you get to run one query, instead of many. However, updating millions of rows at ones can exceed allocated RAM of the DB server and slow things down significantly, while also locking too many rows at once and thus disrupting the app. Also, in production, too many rows means that the DB could be waiting too long to even be able to lock them, as the app itself is constantly accessing many of them.

That is why I asked about partitioning, which was explained by jjanes in his answer. So, replacing in and case...end with = (and more SQL calls) was a step towards partitioning. Using limit in the updates was done to further partition things, as without it I could still hit hundreds of thousands of rows.

At this point, it is useful to know some technical details. This was all run on Heroku, using their standard-7 PostgreSQL database which has 244 GiB RAM. It turns out that the limit of 100k rows is good. I may have gone for more, but I've had enough of testing. For comparison, on premium-0 (4 GiB RAM), even 10k was too much.

Now, for the part outside of SQL... The standard-7 plan that I ran this on has 32 vCPUs that can process these requests. Since most of the work is on the DB and not my app, there is no reason to do these sequentially, so I launched a multiprocessor.Pool with 10 processes and ran all the queries in there. I likely could've gone with 20 or even 30, but I wanted to avoid disrupting the app in production, and this proved sufficient.

It took me quite a while to get to this point, but I'm happy with the result. I hope this'll help someone, like a_horse_with_no_name and jjanes helped me.