wal_level: What is the difference?

03.2014 / Category: / Tags: | |

To use PostgreSQL replication it is necessary to change the wal_level from “minimal” to “hot_standby”. But, which impact does this change have on the amount of WAL actually written to disk? And which impacts does it have in performance? Many people have been asking this question in the past, so I thought I'd give it a try and do a small benchmark.

The test setup

The test is pretty simple: I have used PostgreSQL 9.3.3. In each case I used a freshly created test instance ...



We are performing this test with a FILLFACTOR of 70% to make sure that we are as realistic as possible. A scale factor of 100 will provide us with 10 mio rows of test data, which is also a reasonable amount of data to test with.

Importing data

After running initdb and pgbench we do our first check to see, how much xlog we have written:



Many may ask why there is a 35 fold increase in the amount of xlog here?

The reason is actually very simple and can be found deep inside the code of pgbench itself:



To import things faster pgbench truncates the underlying tables before inserting data. In case of wal_level = minimal this will make sure that PostgreSQL does not have to write xlog for a table at all (transaction log bypass). Using truncate is a neat trick to speed up imports in general.

In addition to a sharp increase in xlog creation from “minimal” --> “archive” we see that the amount of xlog created by “archive” and “hot_standby” are virtually the same. This is pretty much expected. All “hot_standby” does on top of “archive” is to store some information about running transactions - during an import this is really not a big deal.

Running OLTP

In the next step we run a little test to see which differences there are for a typical OLTP workload.

Our test is pretty simple:


This leaves us with a total of 160.000 transactions.


In a normal OLTP test there is basically no difference. I assume that the slightly smaller numbers for hot_standby are related to timing issues during checkpoints towards the end of the test. A normal OLTP test writes pretty straight forward logs so there is no real difference. There are no magic things such as xlog bypasses and so on.

The conclusion here is that the WAL level really only makes a difference when you are about to benefit from the WAL bypassing codes.

Find out more crucial PostgreSQL logging tips and tricks in our special blog section all about WAL.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram