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
slave with a foreign key
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 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 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?
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.