Re: Purging few months old data and vacuuming in production

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
havingFK 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.