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