From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Purging few months old data and vacuuming in production |
Date: | 2023-01-06 14:57:26 |
Message-ID: | 90262fac-d6c7-e253-6988-a081c9e47a15@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 1/6/23 08:27, Ranjith Paliyath wrote:
> Thank you very much for the response.
>
> > Can you do online purging?
>
> > For example, get a list of the main table's primary keys to be deleted, and
> > then nibble away at them all day: in one transaction delete all the records
> > for one logically related set of records. Do that N million times, and
> > you've purged the data without impacting production.
>
> So, with this approach, is the advantage like, manual vacuuming worry may be set aside, because auto-vacuuming would deal with the dead rows?
Theoretically, manual vacuuming is never necessary. I'd occasionally do
manual vacuums (after purging a couple of weeks of data, for example).
Disable autovacuum on a table, vacuum it, then reenable autovacuum.
ALTER TABLE table_name SET (autovacuum_enabled = false);
VACUUM table_name;
ALTER TABLE table_name SET (autovacuum_enabled = true);
> This is because the deletion step is executed record by record in main table, with its connected record(s) delete executions in rest of tables?
I don't know if you have ON DELETE CASCADE. Even if you do, you'll have to
manually delete the tables not linked by FK. I'd write a PL/pgSQL
procedure: pass in a PK and then delete records from the 9 tables in the
proper order so as to not throw FK constraint errors.
> Due to the infra capability that is there in this instance,
What is "infra capability"?
> the impact could be almost none!!??
It'll use /some/ resources, because it's a thread deleting records, but most
of the records and index nodes won't be where new records are being inserted.
Note, though, that this will generate a lot of WAL records.
--
Born in Arizona, moved to Babylonia.
From | Date | Subject | |
---|---|---|---|
Next Message | Brad White | 2023-01-06 23:31:48 | Re: Updating column default values in code |
Previous Message | Ranjith Paliyath | 2023-01-06 14:27:56 | Re: Purging few months old data and vacuuming in production |