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