Re: Purging few months old data and vacuuming in production

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.