On 12/30/22 00:39, Ranjith Paliyath wrote:
Hi,
We have a PostgreSQL (slightly old version, something like - PostgreSQL 11.2
You know, of course, that you should update to the latest version. It's
quick and painless.
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.
270M rows isn't that much. Deleting 3M rows should not take 3 hours, even
when there are six tables.
Are the tables tied together by FK?
How big are the rows?
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.
Is there an index on the date field?
Can you drop unneeded indices during the window, and then rebuild them
afterward?
How beefy is your hardware?
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?
We tried this with Postgresql 12.x declarative partitioning on tables with
synthetic keys. Query performance suffered, since the date field needs to
be added to the PK, and Pg tends to scan all the partitions, even when the
date field is part of the WHERE clause. Thus, we departitioned all but the
two with large bytea columns.
--
Born in Arizona, moved to Babylonia.
