Sacrificing resilience for performance with PostgreSQL: WAL & UNLOGGED tables
TLDR: Write-ahead logs are used to increase resilience and restore data after crashes. You can turn it off for some tables to achieve up to 15x performance boost .
In 4xxi we often choose PostgreSQL over other vendors for its versatility. However, as any powerful tool, it requires some degree of understanding of what is going on under the hood.
What is WAL?
WAL stands for write-ahead log, a widely used technique to improve database resilience.
When you write new data into a database, it needs to perform several operations, moving some blocks/pages around.
As a result, INSERTs and UPDATEs are not atomic operations from filesystem’s point of view: if someone were to suddenly unplug your server, the data would end up in an inconsistent state, e. g. become corrupt.
The problem is exacerbated by the fact that to speed up writes PostgreSQL buffers them, keeping some data in memory before writing it to disk, introducing extra risks.
To cope with the risks and increase database’s resilience, PostgreSQL writes every change into a special append-only file. All writes are sequential and thus do not slow down the process as much, and they are also atomic.
After a crash or a power outage, PostgreSQL tries to read this file first and tries to redo unfinished operations, restoring consistency.
What does WAL look like?
WAL is a binary log, so you are going to need a special tool to read it. Fortunately, since 9.3 PostgreSQL distributions contain such a tool: pg_xlog_dump (renamed to pg_waldump in PostgreSQL 10).
Some of the fields:
rmgr is a “resource manager”, to put it simply, is a category of an operation.
lsn stands for “Log Sequence Number”, an id of a log entry.
desc is a detailed description of an operation.
People do all kinds of stuff with it, such as generating timestamps for records and doing continuous backups.
What do I get from it?
In some cases you may want to sacrifice some resilience in order to gain more performance. This might not seem like a good idea at first, but there are some use-cases in which it is justified.
For example, when importing large files for analytics, you might be better off disabling WAL and thus achieving higher write performance.
PostgreSQL allows you to disable WAL for individual tables by marking it as UNLOGGED.
As you can see, you can alter existing tables too (since 9.5).
You can find all UNLOGGED tables by running this query:
relpersistence = ‘u’ limits results to UNLOGGED and relkind = ‘r’ filters out everything that is not an ordinary table (indexes can be UNLOGGED too).
Caveats
Marking tables as UNLOGGED makes them, well, unlogged, so if your database experiences a sudden crash, data in such tables will be lost. However, they are persisted during a normal restart.
UNLOGGED tables do not support GiST indexes, i. e. PostGIS indexes or some other indexes on geometrical data or ranges.
Standby replication is performed through WAL, so UNLOGGED tables are not suited for standby replication.
Conclusions
Relational databases are complex software systems, aggregating and incorporating a lot of academic knowledge since 1980s.
There are plenty of tricks you can use to improve performance of your database — but it is often a tradeoff, so you need to understand what you gain and what you lose.
Performance differs from project to project. Various sources (1, 2) suggest that expecting 15x performance boost is not unrealistic.
Links
On WAL:
- Heikki Linnakangas, “Welcome to Write-Ahead Log”
- Heikki Linnakangas, “PostgreSQL 9.5 WAL Format”
- Hironobu Suzuki, “The Internals of PostgreSQL”, chapter 9
On UNLOGGED tables:
