pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Николай Кобзарев <n.kobzarev@aeronavigator.ru>
To: hjp-pgsql@hjp.at
Cc: pgsql-general@lists.postgresql.org
Subject:  Re: Purging few months old data and vacuuming in production
Date: Sat, 07 Jan 2023 20:38:39 +0300
Message-ID: <1673113119.853837448@f758.i.mail.ru> (raw)
In-Reply-To: <20230107172344.7muathowoty3etmg@hjp.at>
References: <SEZPR06MB569032257FDFACE5C906ADB4C2F09@SEZPR06MB5690.apcprd06.prod.outlook.com>
	<a4199207-a5dc-583a-5782-67e1ab8f57c8@gmail.com>
	<20230107172344.7muathowoty3etmg@hjp.at>




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

view thread (13+ messages)

Message-ID: <1673113119.853837448@f758.i.mail.ru>
Permalink:  ../1673113119.853837448@f758.i.mail.ru/
Also on:    postgresql.org/message-id/1673113119.853837448@f758.i.mail.ru

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: n.kobzarev@aeronavigator.ru, hjp-pgsql@hjp.at
  Subject: Re: Purging few months old data and vacuuming in production
  In-Reply-To: <1673113119.853837448@f758.i.mail.ru>
  References: <SEZPR06MB569032257FDFACE5C906ADB4C2F09@SEZPR06MB5690.apcprd06.prod.outlook.com> <a4199207-a5dc-583a-5782-67e1ab8f57c8@gmail.com> <20230107172344.7muathowoty3etmg@hjp.at> <1673113119.853837448@f758.i.mail.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox