wal_level: What is the difference?

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 ...

            initdb -D $PGDATA
            createdb $DB
            pgbench -F 70 -s 100 -i $DB

1

2

3

initdb -D $PGDATA

createdb $DB

pgbench -F 70 -s 100 -i $DB

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:

           wal_level:                  xlog position:              xlog in bytes:

            minimal:                   0/2440820                   38012960
            archive:                   0/50118B68                  1343327080
            hot_standby:               0/5011A5D8                  1343333848

1

2

3

4

5

wal_level: xlog position: xlog in bytes:

minimal: 0/2440820 38012960

archive: 0/50118B68 1343327080

hot_standby: 0/5011A5D8 1343333848

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:

            fprintf(stderr, "creating tables...n");

            executeStatement(con, "begin");
            executeStatement(con, "truncate pgbench_accounts");

            res = PQexec(con, "copy pgbench_accounts from stdin");
            if (PQresultStatus(res) != PGRES_COPY_IN)
            {
                        ...

1

2

3

4

5

6

7

8

9

fprintf(stderr, "creating tables...n");

executeStatement(con, "begin");

executeStatement(con, "truncate pgbench_accounts");

res = PQexec(con, "copy pgbench_accounts from stdin");

if (PQresultStatus(res) != PGRES_COPY_IN)

{

...

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:

        pgbench -j 32 -M prepared -t 5000 $DB -c 32

1	pgbench -j 32 -M prepared -t 5000 $DB -c 32

This leaves us with a total of 160.000 transactions.

            wal_level                   xlog_position:                        xlog used in test:

            minimal                     0/3DC80778                            998506328
            archive                     0/8B991280                            998737688
            hot_standby                 0/8B96F8F8                            998593312

1

2

3

4

5

wal_level xlog_position: xlog used in test:

minimal 0/3DC80778 998506328

archive 0/8B991280 998737688

hot_standby 0/8B96F8F8 998593312

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.