Re: Purging few months old data and vacuuming in production

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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"