To optimize PostgreSQL autovacuum, focus on adjusting global and table-specific parameters like autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, and autovacuum_freeze_max_age to proactively manage dead tuples and prevent transaction ID wraparound. Ensure sufficient autovacuum_max_workers and appropriate autovacuum_vacuum_cost_delay to allow timely processing.
Here's a breakdown of key settings and strategies:
1. Global Configuration (postgresql.conf):
These settings affect all tables unless overridden. Adjust them based on your overall workload.
autovacuum_vacuum_scale_factor: The percentage of table size that needs to change to trigger a VACUUM. Default is 0.2 (20%). For busy tables, reduce this to 0.05 or 0.1 to trigger vacuuming more frequently.
autovacuum_vacuum_threshold: The minimum number of dead tuples to trigger a VACUUM. Default is 50. This works in conjunction with scale_factor.
autovacuum_freeze_max_age: The maximum age (in transactions) a table's relfrozenxid can reach before an aggressive anti-wraparound VACUUM is forced. Default is 200 million. For large, long-running systems, increasing this to 500 million or 1 billion can reduce forced vacuums, provided regular autovacuum keeps up.
autovacuum_max_workers: The maximum number of autovacuum processes that can run concurrently. Default is 3. Increase this based on your CPU cores and I/O capacity, e.g., 5-10, to handle more concurrent vacuuming.
* autovacuum_vacuum_cost_delay: The delay in milliseconds between vacuum cycles. Default is 2ms. Lowering this makes autovacuum more aggressive but can increase I/O impact. Increasing it reduces I/O impact but makes autovacuum less aggressive.
```sql
-- Check current global settings
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_freeze_max_age;
SHOW autovacuum_max_workers;
SHOW autovacuum_vacuum_cost_delay;
```
```ini
# Example settings in postgresql.conf
autovacuum = on
autovacuum_max_workers = 5
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 50
autovacuum_freeze_max_age = 500000000
```
2. Table-Specific Overrides:
For frequently updated or deleted tables, global settings might not be aggressive enough. Use ALTER TABLE to override parameters for specific tables.
```sql
-- Example for a very busy table 'orders'
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- Trigger at 1% dead tuples
autovacuum_vacuum_threshold = 1000, -- Or a higher absolute number for very large tables
autovacuum_freeze_max_age = 100000000 -- Freeze more aggressively if needed
);
-- To reset to global defaults
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, autovacuum_freeze_max_age);
```
3. Monitoring:
Regularly check pg_stat_all_tables to identify tables needing attention. Pay close attention to n_dead_tuples (indicating potential bloat) and xid_age (indicating proximity to transaction ID wraparound).
```sql
SELECT
relname,
last_autovacuum,
autovacuum_count,
n_dead_tuples,
n_live_tuples,
age(relfrozenxid) AS xid_age,
reloptions
FROM
pg_stat_all_tables
WHERE
schemaname = 'public' -- Or your specific schema
ORDER BY
n_dead_tuples DESC, xid_age DESC;
```