Date: Thu, 15 Nov 2018 14:46:13 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>,
	Sergei Kornilov <sk@zsrv.org>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: checkpoint occurs very often when vacuum full running
Message-ID: <20181115204612.GS16896@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <7817341542313720@myt6-fe24916a5562.qloud-c.yandex.net>
 <CA+t6e1=-OJ8XEitrH9GVDoHXQKNTkYN27O4cKmq5vkiGWbp3zA@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

Hi,

Please don't cross post to multiple lists.

On Thu, Nov 15, 2018 at 08:53:14PM +0200, Mariel Cherkassky wrote:
> Can someone explain the logic behind it ? I know that vacuum full isnt
> something recommended but I found out that whenever I run vacuum full on my
> database checkpoint occurs during that time every second ! well I know that
> VACUUM FULL duplicates the data into new data files and then it deletes the
> old data files. The writing the vacuum does, is it with the checkpoint
> process ?

It's a good question.  What version postgres are you using, and what is the
setting of wal_level ?

On Thu, Nov 15, 2018 at 11:28:40PM +0300, Sergei Kornilov wrote:
> Row location is data. For example, index lookup relies on TID (tuple id, hidden ctid column) - physical row address in datafile.

But, since VAC FULL has an exclusive lock, and since it's atomic (it's either
going to succeed and use the new table or interrupted or otherwise fail and
continue using the old table data), I it doesn't need to write to WAL, except
if needed for physical replication.  Same as CREATE TABLE AS and similar.  In
my test, setting wal_level=minimal seemed to avoid WAL writes from vac full.

https://www.postgresql.org/docs/current/populate.html#POPULATE-PITR

Justin