Re: Purging few months old data and vacuuming in production

On 2023-01-07 07:40:01 -0600, Ron wrote:
> On 1/7/23 05:29, Peter J. Holzer wrote:
>     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.
>
>
> 300 records/second.  Fewer DELETE statements if there are one-many
> relationships with the child tables.

Nope:

| Each of these tables' daily record increment is on an average 2 to 3
| million

I am assuming that the main table is typical, so there will be 2 to 3
million DELETEs from the main table and also from each of the other 8
tables (which may delete 0, 1, or more records). Also, it was mentioned
that only some of these tables have a direct FK relationship, so the
DELETE queries against the other tables may be (much) more expensive
than a simple `delete from my_table where main_id = :1`.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"