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.