writer and wal writer PostgreSQL processes explained

By Kaarel Moppel - Look at the processes list on a server where Postgres is running, and you'll see two writer processes among other background and client processes.

One process is more specifically named wal writer. It's still confusing that there are 2 similarly named writers. What do they do? From my experiences from training, this similar naming always causes a lot of confusion. So I thought it wouldn’t hurt to lay out the working principles of these processes again in simple terms. I'll describe them, together with the pertinent server configuration parameters.

The writer a.k.a. background writer  process

Note that we assume that the high level concept of “checkpoints” together with the checkpointer process and its parameters are already familiar to you (as they have far more effect, compared to the writers). When not, I’d recommend digging into the Postgres documentation here.

So to the writer. The introductory sentence in the documentation tells us:

There is a separate server process called the background writer, whose function is to issue writes of "dirty" (new or modified) shared buffers. It writes shared buffers so server processes handling user queries seldom or never need to wait for a write to occur. However, the background writer does cause a net overall increase in I/O load, because while a repeatedly-dirtied page might otherwise be written only once per checkpoint interval, the background writer might write it several times as it is dirtied in the same interval. ...

In short - the writer moves some of the changed data (dirty buffers) already to the disk in the background, so that checkpoint process, happening at regular intervals, would have less work to do. All of this with the point that in the end user/application queries wouldn’t need to suffer too much when checkpointer kicks in with its heavy IO requirements, when there are lots of buffers to be processed or checkpoint_completion_target is set too small. All this is relevant of course only when we’re running a relatively busy database - for idling databases it wouldn’t be a problem at all.

A small gotcha

But did you also catch the possible pitfall from the wording? Basically it warns you about the following – repeatedly dirtied page might be written to disk multiple times during one checkpoint interval, causing unnecessary extra IO load! So here, one should thoroughly think about the database workload first, before tuning any relevant parameters to offload the checkpointer. Typical problem scenario would be for example where you have some “counter” like tables that get incremented for every page view or event, resulting in writer re-writing the same page many-many times during one checkpoint cycle. Due to this possible pitfall Postgres default parameters for the writer are also pretty non-intrusive. So my advice is to generally let them stay as they are, but nevertheless a short “translation” for the parameters visible from documentation:

bgwriter_delay [200ms by default , 10ms – 10s possible] – time to sleep after cleaning up a maximum of bgwriter_lru_maxpages (see below) dirty pages.

bgwriter_lru_maxpages [100 pages by default, 0 – 1000 possible] – maximum amount of pages cleaned per one writer activity round.

bgwriter_lru_multiplier [2.0 by default, 0-10.0 possible] – multiplier ratio determining how many pages should be cleaned for every incoming dirty page, based on counts from last delay periods. Everything > 1.0 means we try to clean more pages than actually dirtied, up to the “maxpages” limit.

From those default settings, we can also calculate the maximum amount of bytes per second that the writer can clean. 200ms delay translates to 5 times per second, multiplied with 100 8KB pages gives us ~ 4MB. Pretty conservative indeed! But due to good reasoning as mentioned previously.

The “wal writer” process

Now what’s the deal here? This is a simpler story actually – first, wal writer has only meaning when “synchronous_commit” is set to “off”, with default being “on”, so most setups can already ignore it.  More about “synchronous_commit” and it’s different values can be read from one of our recent blogposts here .

But in short - with “synchronous_commit=off” (generally used as a performance crutch) Postgres commits are managed by this wal writer process. They are actually not flushed directly to the transaction log (a.k.a. WAL or XLog), but rather just sent to the operating system and fsync (i.e. real commit, guaranteeing durability) is requested only after the “wal_writer_delay” period has passed from the last real commit. The default value is here 200ms again, same as “bgwriter_delay”. One could increase this parameter if needed, but please take into account the friendly warning from the docs, that in worst case the delay could triple. Only set it the number of seconds/milliseconds (1ms – 10s range available) that you’re willing to lose data in case of a crash. (You needn't fear of data corruption here, though.) This is what setting “synchronous_commit=off” means. Hope it helps!