From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Purging few months old data and vacuuming in production
Date: Fri, 30 Dec 2022 08:47:45 -0600
Message-ID: <6f623e3a-d93d-bc89-b553-d2f87a82bd73@gmail.com> (raw)
In-Reply-To: <SEZPR06MB569032257FDFACE5C906ADB4C2F09@SEZPR06MB5690.apcprd06.prod.outlook.com>
References: <SEZPR06MB569032257FDFACE5C906ADB4C2F09@SEZPR06MB5690.apcprd06.prod.outlook.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: Purging few months old data and vacuuming in production
In-Reply-To: <6f623e3a-d93d-bc89-b553-d2f87a82bd73@gmail.com>
References: <SEZPR06MB569032257FDFACE5C906ADB4C2F09@SEZPR06MB5690.apcprd06.prod.outlook.com> <6f623e3a-d93d-bc89-b553-d2f87a82bd73@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox