On Fri, Dec 30, 2022 at 12:09 PM Ranjith Paliyath <ranjithp@xxxxxxxxxxxxxxx> 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
