Re: Need help understanding WAL and checkpoints

Hi Albe

--- On Wed, 2013/2/6, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

> drew_hunt wrote:
> > I'm trying to get my head around WAL and checkpoints and need to ask a couple of questions before I
> > get a headache.
> >
> > Firstly, I see the terms "WAL log", "WAL file" and "transaction log" all over the place - are these
> > the same thing (i.e. files in the pg_xlog directory)?
>
> Usually they mean the same thing.
> For exact definitions, read
> http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL
> "WAL" consists of a stream of "WAL records" and is physically
> represented as "WAL segment files" (in pg_xlog).
>
> > I'm a bit confused by this paragraph in the docs:
> >
> > "Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and
> > index data files have been updated with all information written before the checkpoint. At checkpoint
> > time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log
> > file. (The changes were previously flushed to the WAL files.)"
> >
> > ( see: http://www.postgresql.org/docs/current/static/wal-configuration.html )
> >
> > "a special checkpoint record is written to the log file."
> >     -> which log file is meant here?
>
> The WAL.
>
> > "The changes were previously flushed to the WAL files."
> >    -> does "previously" here mean "at a previous point in time" or "in previous PostgreSQL versions"?
>
> The former.
>
> >    -> at what point are changes flushed to WAL files?
>
> The change must be on disk in a WAL segment before the
> transaction can commit.
>
> > So say I perform an operation like :
> >
> >   UPDATE foo SET bar='baz'
> >
> > are the following assumptions correct?
> >
> > - The first time this changed data hits the disk, it is as an entry in the WAL log
> >
> > - At some point a checkpoint occurs, and the changed data is written to the actual data file from
> > system memory (the "dirty data pages"?)
> >
> > - the only time the actual data files will be updated from the WAL log (i.e. not from system memory)
> > will be after a crash, when the logs are replayed from the last checkpoint?
>
> All three are correct.

Many thanks for the answers, its cleared things up for me :)

- drew