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
andmaster
: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=<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.
update
, i.e., the second thing that I need. Still, mydelete
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.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 byrollback
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.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).