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 < pry...@telsasoft.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 >
