Re: shared_buffers, wal_buffers, WAL files, data files

Guillaume Lelarge <guillaume(at)lelarge(dot)info> writes:
> I try to answer a simple question : what happens when I do a simple
> "INSERT" on a just started PostgreSQL server.

> From what I understand with the INSERT statement, here is what happens :
> * backend loads first (and only) block from footable file into a shared
> buffer
> * it modifies this block on the shared buffer, and sets it as dirty

Right, and it also makes a WAL log entry about this action.

> After checkpoint_timeout seconds or after a manual CHECKPOINT (there's
> also some other ways to have a checkpoint but in my simple example,
> these two seem to be the most probable ones) :
> * bgwriter runs CreateCheckPoint
> * it will, beyond other things, flush this block from shared buffer to
> disk, fsync, sets the block as non-dirty...

Right.

> ... and here is my first question : flushes to the current XLOG file or
> to footable data file ?

To the data file. The WAL entry got flushed to disk during (or before)
commit of the INSERT transaction.

> I think it is to XLOG file but I don't find
> where in the code the content of WAL files is flushed to data files.

There's no such thing as "flushing WAL to data". During normal
operation the WAL is write-only; we never look at it again once
we've made a log entry.

> From other parts of the documentation, it seems checkpoints are also in
> charge of flushing WAL files to data files.

No, they're just in charge of removing no-longer-needed sections of WAL.
Once we've checkpointed a particular data file change, there's no need
to keep around the part of the WAL that logged that change.

regards, tom lane