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

 

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

 

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)

            {

                        ...

 

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

 

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

 

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.