Message-ID: <8042edc6f769a8577ee36b1167e355f254141d97.camel@cybertec.at>
Subject: Re: VACUUM freeze
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Arnav <justdba03@gmail.com>, pgsql-admin <pgsql-admin@postgresql.org>
Date: Mon, 20 Apr 2020 10:55:56 +0200
In-Reply-To: 
 <CAF0ZvMxPdYVwDck0MemYNe2GFcmQpGH3cp4x05sBopieSdeEeQ@mail.gmail.com>
References: 
	<CAF0ZvMxPdYVwDck0MemYNe2GFcmQpGH3cp4x05sBopieSdeEeQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Sat, 2020-04-18 at 23:55 +0530, Arnav wrote:
> what is VACUUM freeze , does it not allow autovacuum to happen ever.
> if yes till how long?

VACUUM (FREEZE) marks all rows that are visible to everybody as
unconditionally visible.
All rows that live long have to get frozen at some point, so that they
can survive transaction wraparound without data corruption, because the
internal transaction ID that created them is stored in the row metadata.

This can be useful for insert-only tables, since autovacuum won't have
to touch frozen rows ever again unless they get updated or deleted.

The negative aspect of freezing all rows is that all rows that are frozen
get modified, which means that the block containing them becomes dirty
and has to be written to disk again.  Now if any such row is modified or
deleted later, freezing the row has generated unnecessary I/O.

This is why by default VACUUM will freeze only rows whose creating
transaction is more then 50 million transactions in the past, in the
hope that such rows have less chance to be modified again than more
recent rows.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com