Re: Purging few months old data and vacuuming in production

On Fri, Dec 30, 2022 at 12:09 PM Ranjith Paliyath <ranjithp(at)suntecgroup(dot)com>
wrote:

> Hi,
>
> We have a PostgreSQL (slightly old version, something like - PostgreSQL
> 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
> 4.8.5-36), 64-bit) production, where one particular table and its related 5
> tables need to be purged of 3 months prior data. Each of these tables'
> daily record increment is on an average 2 to 3 million.
>
> Approach needed is to do a daily purge of 90days prior data. Probable
> purge processing window is expected to be 2hrs. Observed test timing for
> deletion is exceeding 2-3hrs and we are trying to do vacuuming after the
> deletes, which is again taking exceeding another 2hrs.
> There is a suggestion for re-creating the tables with partitions, and as
> purge approach could then be a deletion/dropping of these partitions, which
> would not really require a vacuuming later on.
>
> When we go for a Daily purge approach it should not put a strain on other
> processes which could be affecting this same set of tables, like these
> tables should not get locked because of the purge.
>
> Questions are -
> (a) Should we recommend PostgreSQL upgrade, if possible, to v15.1? Could
> this bring in some benefits related to vacuuming?
> (b) Would partitioning be an optimal approach?
>
> Thank you,
> Regards
>
>
> This electronic mail (including any attachment thereto) may be
> confidential and privileged and is intended only for the individual or
> entity named above. Any unauthorized use, printing, copying, disclosure or
> dissemination of this communication may be subject to legal restriction or
> sanction. Accordingly, if you are not the intended recipient, please notify
> the sender by replying to this email immediately and delete this email (and
> any attachment thereto) from your computer system...Thank You.
>
>
>
Partitioning would definitely help, if you partition by date .. Also, if my
memory serves me right, v 15 would definitely help if you have large number
of partitions, which in your case it's most likely to be the case.

Deleting from single table would be putting a strain on your existing table.

Amitabh