RE: Purging few months old data and vacuuming in production

Thank you very much again.

> 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);

Ok. For the record by record delete approach, autovacuum-ing could be the natural option.

> 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.

Ok, in the case of our specific 9 tables it would finding and deleting linked records in 8 tables based on the record chosen in the main table. That is going and deleting records one by one.

> Due to the infra capability that is there in this instance,

> What is "infra capability"?

You had a query like how beefy the hardware is - was trying to refer to the hardware 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.

Ok, thanks.
We were weighing on pros and cons of the table partitioning approach. But, input on the experience you had with the partitioned approach is something we'll need to very much consider. We'll try to see if the per record delete could be tried out once, and how it affects the DB load, with its present WAL setting.

Thank you...