2

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.

4
  • A delete won't lock any rows (as there is nothing to lock once they are gone). Other transactions can still access (select, insert, update, delete) that table, provided they don't try to e.g update the rows that are being deleted.
    – user1822
    Aug 22, 2018 at 11:03
  • @a_horse_with_no_name Yes, the locking is only an issue with the update, i.e., the second thing that I need. Still, my delete queries take more than 1hr (until I kill them, so I don't know the exact time) which seems far too long and I'm obviously doing something wrong here. Aug 22, 2018 at 11:08
  • You refer to "the update top". What is that? Since you already have a copy of production to be used for testing, why don't you do the dry run there? Doing a dry run by rollback in production is expensive, and also not very effective as you can't really evaluate the outcome very effectively when the changes are invisible to other sessions.
    – jjanes
    Aug 22, 2018 at 14:09
  • @jjanes My mistake; update top(number) exists for other DB(s?), Microsoft's, for example. I meant a similar functionality. As for dry runs, this code is supposed to be run from time to time and I wanted to have a simple dry run option without copying the prod DB each time. Also, running it like this gives me a good impression of what would happen with the real run, with all the other load of the app (which is not present when running on a copy). Aug 22, 2018 at 15:10

3 Answers 3

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.

5
  • Actually, I have two slaves. Can the latter be extended to delete operations from two tables (the same condition applies to both of them)? Aug 22, 2018 at 11:11
  • @VedranŠego: yes, see my edit.
    – user1822
    Aug 22, 2018 at 11:14
  • The delete is still taking over 1 hours. Just counting of rows in master takes some 10min, so an hour to delete them and related slaves' rows seems too long Am I expecting too much of this? Aug 22, 2018 at 12:40
  • 1
    @VedranŠego Deleting is a lot more work than counting, so yes, I think you might be expecting too much. But posting an EXPLAIN (ANALYZE, BUFFERS) could help clarify that.
    – jjanes
    Aug 22, 2018 at 14:04
  • The app was made a long time before I took over. Now I altered the tables to make foreign keys delete on cascade and this went smoothly (under half an hour), so I consider this one solved. The update is still an issue. Aug 22, 2018 at 14:50
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.