On Wed, Apr 6, 2022 at 6:24 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> 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.kla...@aklaver.com > Oh I must have missed this. Is this missing from the documentation here ( https://www.postgresql.org/docs/current/runtime-config-autovacuum.html)? I can try again, but I’m pretty sure this option was rejected when I attempted to set it via an alter table command.
