From: | Николай Кобзарев <n(dot)kobzarev(at)aeronavigator(dot)ru> |
---|---|
To: | hjp-pgsql(at)hjp(dot)at |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Purging few months old data and vacuuming in production |
Date: | 2023-01-07 17:38:39 |
Message-ID: | 1673113119.853837448@f758.i.mail.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Суббота, 7 января 2023, 20:23 +03:00 от Peter J. Holzer <hjp-pgsql(at)hjp(dot)at>:
>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(at)hjp(dot)at | -- Charles Stross, "Creative writing
>__/ | http://www.hjp.at/ | challenge!"
So one may consider deleting from child tables, and only after that delete from main table, avoiding enforcing foreign key during delete. Also consider deletes by relatively small chunks, in loop.
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Millas | 2023-01-07 19:46:29 | impact join syntax ?? and gist index ?? |
Previous Message | Peter J. Holzer | 2023-01-07 17:23:44 | Re: Purging few months old data and vacuuming in production |