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. |_|_) | | | | | hjp@xxxxxx | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature