Understanding the PostgreSQL transaction log

Before digging into the world of replication, it makes sense to stop for a while and focus our attention on the PostgreSQL transaction log. Many people have heard about the transaction log but surprisingly few have understood the basic principles behind this highly important data structure which is capable of making PostgreSQL as robust as it is.

The purpose of the transaction log

In theory, the purpose of the PostgreSQL transaction log or Write Ahead Log (WAL) is pretty simple. It makes sure that a database instance survives crashes. In case the power goes out or the operating system crashes for some reason, the transaction log is always there to repair the system and to save system administrators from the disaster. So, why does a database instance need a repair in case of a crash anyway?

Well, let's assume a simple example. We got a table, say, of 10 GB and we want to add a simple row, as shown in the following code:

INSERT INTO foo VALUES ('abcdefg...