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
bepurged 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.
Observedtest timing for deletion is exceeding 2-3hrs and we are trying to do vacuuming after the deletes, which is
againtaking exceeding another 2hrs.
There is a suggestion for re-creating the tables with partitions, and as purge approach could then be a
deletion/droppingof 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
setof 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
individualor entity named above. Any unauthorized use, printing, copying, disclosure or dissemination of this
communicationmay be subject to legal restriction or sanction. Accordingly, if you are not the intended recipient,
pleasenotify the sender by replying to this email immediately and delete this email (and any attachment thereto) from
yourcomputer system...Thank You.