To my understanding, database can postpone writing to table files to boost IO performance. When transaction is COMMITted, data are written to the WAL files.

I'm curious, how much delayed the writing to table files can be. In particular, when I use a simple SELECT, e.g.

SELECT * from myTable;

after COMMIT, is it possible that database has to retrieve data from the WAL files in addition to the table files?

CodePudding user response：

The documentation talks about being able to postpone the flushing of data pages:

If we follow this procedure, we do not need to flush data pages to disk on every transaction commit.

What WAL files allow an RDBMS to do is keeping "dirty" data pages in memory and flushing them to disk at a later time. It does not work in a way that the data pages are modified at a later time.

So the answer to your question is "No, a SELECT is always retrieving data from the data pages, not from the WAL files".

CodePudding user response：

PostgreSQL does not read from WAL for this purpose during normal operations. It would only read WAL in order to apply changes to the data files after a crash (or during replication onto another server).

When data from ordinary data files is changed, the pages of the data files are kept in shared memory (in shared_buffers) until they are written to disk. Any other processes wanting to see that data will find it in that shared memory, and it will be in its changed form. They will always look for it in shared_buffers before they try to read it from disk, so no one will ever see the stale on-disk version of the data, except for the recovery process after a crash.