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,
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.
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 <suds1434@xxxxxxxxx> wrote:
On Thu, May 23, 2024 at 9:00 AM Muhammad Salahuddin Manzoor <salahuddin.m@xxxxxxxxxxx> 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.
