Re: Per-Table vacuum_freeze_min_age

On 4/6/22 3:13 PM, Chris Bisnett wrote:
> Hi all!
> 
> 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?

It can:

https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS

Per-table value for vacuum_freeze_min_age parameter.
> 
> - chris
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com