On 2023-01-07 05:33:33 +0000, Ranjith Paliyath wrote: > > > This is because the deletion step is executed record > > by record in main table, with its connected record(s) > > delete executions in rest of tables? > > > I don't know if you have ON DELETE CASCADE. Even if you do, > > you'll have to manually delete the tables not linked by FK. > > I'd write a PL/pgSQL procedure: pass in a PK and then delete > > records from the 9 tables in the proper order so as to not > > throw FK constraint errors. > > Ok, in the case of our specific 9 tables it would finding and deleting > linked records in 8 tables based on the record chosen in the main > table. That is going and deleting records one by one.
If I understood correctly, you have to delete about 3 million records
(worst case) from the main table each day. Including the other 8 tables
those are 27 million DELETE queries each of which deletes only a few
records. That's about 300 queries per second. I'd be worried about
impacting performance on other queries at this rate.
I'd go for a middle ground: Instead of expiring once per day, use a
shorter interval, maybe once per hour or once per minute. That will
(probably) make each expire job really quick but still create much less
load overall.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | h...@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
