Re: Need help understanding WAL and checkpoints

drew_hunt wrote:
> I'm trying to get my head around WAL and checkpoints and need to ask a co=
uple of questions before I
> get a headache.
>=20
> 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#BAC=
KUP-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:
>=20
> "Checkpoints are points in the sequence of transactions at which it is gu=
aranteed that the heap and
> index data files have been updated with all information written before th=
e checkpoint. At checkpoint
> time, all dirty data pages are flushed to disk and a special checkpoint r=
ecord is written to the log
> file. (The changes were previously flushed to the WAL files.)"
>=20
> ( see: http://www.postgresql.org/docs/current/static/wal-configuration.ht=
ml )
>=20
> "a special checkpoint record is written to the log file."
> =A0=A0=A0 -> which log file is meant here?

The WAL.

> "The changes were previously flushed to the WAL files."
> =A0=A0 -> does "previously" here mean "at a previous point in time" or "i=
n previous PostgreSQL versions"?

The former.

> =A0=A0 -> 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 :
>=20
> =A0 UPDATE foo SET bar=3D'baz'
>=20
> are the following assumptions correct?
>=20
> - The first time this changed data hits the disk, it is as an entry in th=
e WAL log
>=20
> - At some point a checkpoint occurs, and the changed data is written to t=
he actual data file from
> system memory (the "dirty data pages"?)
>=20
> - 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 checkpoin=
t?

All three are correct.

Yours,
Laurenz Albe