I have several large tables (1-2Tb) that are 99.9% writes (small number of updates) with a decent commit rate (20K/sec). The basic idea is that it’s generating a lot of data continuously. When the table would reach the thresholds for autovacuum a vacuum would start and would start generating wal write lock wait events. Once I set the freeze age to 500,000 (default is 50,000,000) the vacuums have to touch many fewer pages and is significantly faster without causing any write lock wait events.
The only downside I’ve seen is that this is a global setting and my understanding is that this would cause decreased performance when used with tables with a lot of writes and deletes. Is there a technical reason this setting cannot be applied at the database or table context like other autovacuum settings?
- chris
