Re: checkpoint occurs very often when vacuum full running

I'm just trying to under the logic in some environments that I faced (some
has 9.6 version and wal level is replica and some has 9.2v and wal_level is
set to archive. I'm not sure regarding your answer because I believe that
there is a connection between the VACUUM FULL and the checkpoints that I
saw during the vacuum full. Laurenz Albe from cybertec sent a good
explanation about it to the pgsql-admins list You should check it out.

‫בתאריך יום ה׳, 15 בנוב׳ 2018 ב-22:46 מאת ‪Justin Pryzby‬‏ <‪
pryzby(at)telsasoft(dot)com‬‏>:‬

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