pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Purging few months old data and vacuuming in production
Date: Fri, 6 Jan 2023 04:30:28 -0600
Message-ID: <1984ec34-26fa-a585-3312-ef38814b5f42@gmail.com> (raw)
In-Reply-To: <SEZPR06MB56905EB1343165E6D1C8B5B4C2FB9@SEZPR06MB5690.apcprd06.prod.outlook.com>
References: <SEZPR06MB569032257FDFACE5C906ADB4C2F09@SEZPR06MB5690.apcprd06.prod.outlook.com>
	<6f623e3a-d93d-bc89-b553-d2f87a82bd73@gmail.com>
	<SEZPR06MB56905EB1343165E6D1C8B5B4C2FB9@SEZPR06MB5690.apcprd06.prod.outlook.com>

On 1/6/23 02:44, Ranjith Paliyath wrote:
> Thank you for the details, experience shared and the suggestions.
> Apologies for the delay in collecting the response for the queries.
>
> (1)Are the tables tied together by FK?
>    - Overall there are 9 tables (sorry not 6 as mentioned originally) that are being purged. Only 4 tables would be having FK relationship.
>
> (2)How big are the rows?
>    - The 9 tables now occupy almost 2TB space. Below is the rowsize (in bytes) and record-count details -
>    
>      236	188,055,675
>      297	296,941,261
>      371	58,673,649
>        95	57,477,553
>      904	296,743,680
>      234	188,161,891
>      414	430,411,653
>      707	735,895,015
>      128	155,104,922
>
> (3)Is there an index on the date field?
>    - Yes. But only in one table, which is the main table (records to purge in rest of the tables is based on this table).

Can you do online purging?

For example, get a list of the main table's primary keys to be deleted, and 
then nibble away at them all day: in one transaction delete all the records 
for one logically related set of records.  Do that N million times, and 
you've purged the data without impacting production.


-- 
Born in Arizona, moved to Babylonia.





view thread (13+ messages)  latest in thread

Message-ID: <1984ec34-26fa-a585-3312-ef38814b5f42@gmail.com>
Permalink:  ../1984ec34-26fa-a585-3312-ef38814b5f42@gmail.com/
Also on:    postgresql.org/message-id/1984ec34-26fa-a585-3312-ef38814b5f42@gmail.com

 · 

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: ronljohnsonjr@gmail.com
  Subject: Re: Purging few months old data and vacuuming in production
  In-Reply-To: <1984ec34-26fa-a585-3312-ef38814b5f42@gmail.com>
  References: <SEZPR06MB569032257FDFACE5C906ADB4C2F09@SEZPR06MB5690.apcprd06.prod.outlook.com> <6f623e3a-d93d-bc89-b553-d2f87a82bd73@gmail.com> <SEZPR06MB56905EB1343165E6D1C8B5B4C2FB9@SEZPR06MB5690.apcprd06.prod.outlook.com> <1984ec34-26fa-a585-3312-ef38814b5f42@gmail.com>

* 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