Re: Per-Table vacuum_freeze_min_age

On Wed, Apr 6, 2022 at 6:24 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)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.