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@xxxxxxxxxxx
