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(dot)klaver(at)aklaver(dot)com