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