Need help understanding WAL and checkpoints

From: drew_hunt1976(at)yahoo(dot)co(dot)jp
To: pgsql-general(at)postgresql(dot)org
Subject: Need help understanding WAL and checkpoints
Date: 2013-02-06 11:01:20
Message-ID: 864819.76832.qm@web4512.mail.ogk.yahoo.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi there

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

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 changes were previously flushed to the WAL files."
   -> does "previously" here mean "at a previous point in time" or "in previous PostgreSQL versions"?

   -> at what point are changes flushed to WAL files?

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?

thanks for any help on this!

- drew

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bèrto ëd Sèra 2013-02-06 11:36:03 Re: DEFERRABLE NOT NULL constraint
Previous Message Pavel Stehule 2013-02-06 10:55:16 Re: function for setting/getting same timestamp during whole transaction