Суббота, 7 января 2023, 20:23 +03:00 от Peter J. Holzer <hjp-pg...@hjp.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.
>|_|_) | |
>| | | h...@hjp.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.
