PostgreSQL – Does PostgreSQL Write First to WAL and Then to Data Files?

postgresql

This is regarding the synchronous_commit in Postgres.

The synchronous_commit documentation says:

Specifies whether transaction commit will wait for WAL records to be
written to disk before the command returns a "success" indication to
the client

So my understanding is that a transaction will be written to the database first and then WAL files. synchronous_commit determines whether this WAL write is done synchronously or asynchronously.

My problem is, from following documentation:

Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage.

I got the understanding that Postgres first writes transactions to WAL files and then to the datafile (database).

Which one is the correct? Does Postgres write first to WAL and then to data files? Can any one give more details about how synchronous commits happen and how this relates to WAL?

Best Answer

Your understanding is not correct.

Specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a "success" indication to the client

means that the transaction will be marked as committed without waiting for the success message. This may come somewhat later (usually the time difference is very-very small), do to some magic done by the storage (caching, for example). This means that you issue the COMMIT;, then the WAL data is written to the cache and only after this written actually to the disk. Writing to the data files may happen later, but your data have been persisted at this point.

Depending on the type of the cache and other things, there is a short time window with synchronous_commit = off when you may lose data if power is lost, for example.

Related Question