Re: Reg data purging/archiving

On Fri, Apr 5, 2024 at 3:14 PM srinivasan s <srinioracledba7(at)gmail(dot)com>
wrote:

> Dear all,
>
> I hope this message finds you well.
>
> I would greatly appreciate your input and suggestions regarding the
> removal of data from our largest table, which currently exceeds 23TB and
> contains several years worth of data. Our objective is to retain only the
> data from the past two years in this table. It is important to note that
> this table is not partitioned.
>

> Considering the critical nature of our business database, it is imperative
> that the purging process does not adversely affect the ongoing production
> performance. Therefore, I am seeking the best approach to accomplish this
> task without compromising system performance.
>
> One potential solution I am considering is to create a new table and
> gradually transfer the data from the large table to the new one in smaller
> batches. Once the data transfer is complete, we can then rename the tables
> accordingly and perform a final cutover. However, I am uncertain if this
> approach will yield the desired results, and I believe it is necessary to
> conduct thorough testing before proceeding.
>

Is the table ever updated? An INSERT-only table would make such a scheme
much easier to implement.

If any of you have suggestions or ideas on how to efficiently purge data
> older than two years from such a massive table without impacting production
> performance, I would greatly appreciate your insights.
>

1. What PG version are you running?
2. How many indices?
3. Does an index on the relevant date field exist? That also would make
such a scheme much easier to implement.
4. How much down time can you get?
4. If nothing else, chip away at the old data, a few "table days" every
calendar day". Naturally, a supporting index is required, and
vacuum+analyze required after a round of deletes.