Greetings, Running `VACUUM table_name;` on a partitioned table will vacuum each partition individually, not the whole table as a single unit.
Yes, running `VACUUM table_name;` frequently on tables or partitions with heavy DML is recommended. Regular `VACUUM` does not lock the table for reads or writes, so it won't disrupt ongoing 24/7 data operations. "optimize autovacuum" Yes. Adjust following parameters as per your system/environment requirement autovacuum_max_workers, autovacuum_freeze_max_age , autovacuum_vacuum_cost_delay Following need to be first tested thoroughly in a test environment. Recommended Alert Threshold Alert at 50% Usage: Set the alert threshold at 1 billion used XIDs. This provides a significant buffer, giving you ample time to take corrective action before reaching the critical limit. Calculation Rationale Daily XID Usage: Approximately 4 billion rows per day implies high XID consumption. Buffer Time: At 1 billion XIDs, you would still have 1 billion XIDs remaining, giving you roughly 12 hours to address the issue if your system consumes 200 million XIDs per hour. *Salahuddin (살라후딘**)* On Thu, 23 May 2024 at 09:48, sud <suds1...@gmail.com> wrote: > On Thu, May 23, 2024 at 9:00 AM Muhammad Salahuddin Manzoor < > salahuddi...@bitnine.net> wrote: > >> Greetings, >> >> In high-transaction environments like yours, it may be necessary to >> supplement this with manual vacuuming. >> >> Few Recommendations >> >> Monitor Long-Running Queries try to optimize. >> Optimize Autovacuum. >> Partitioning. >> Adopt Vacuum Strategy after peak hours. >> >> We have these big tables already partitioned. So does "vacuum table_name" > will endup scanning whole table or just the latest/live partition which is > getting loaded currently? and do you mean to say running command "vacuum > table_name;" frequently on selective tables that are experiencing heavy DML > ? Hope this won't lock the table anyway because the data will be > written/read from these tables 24/7. > > When you say, "optimize autovacuum" does it mean to set a higher value of > "autovacuum_max_workers" > and "autovacuum_freeze_max_age"? > > Considering we have ~4 billion rows inserted daily into the table and > there is limit of ~2billion to the "Maximumusedtxnids", what threshold > should we set for the alerting and to have enough time at hand to fix this > issue? > >
