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