Working With Postgres WAL Made Easy 101

Write-Ahead Log (WAL) is a very important term in transaction processing. In PostgreSQL, it is also known as a transaction log. A log is a record of all the events or changes and WAL data is just a description of changes made to the actual data. So, it is ‘data about data’ or metadata. Using the Postgres WAL entries, you can restore the database back to its state at any previous point in time.

The term ‘Write-Ahead Log’ implies that any change that you make to the database must first be appended to the log file, and then the log file should be flushed to disk. What is the consequence of this? The basic purpose of Write-Ahead Logging is to ensure that when there is a crash in the operating system or PostgreSQL or the hardware, the database can be recovered. This is because, with the help of the log records, you can recover all the transactions to the data pages.

The goal of this article is to give you a good, introductory idea of all the important concepts that you might encounter when working with Postgres WAL files.

Working With Postgres WAL Files

With most installations and packages, 16 MB is the size of the wal segments. Unless your transaction rate is through the roof, 16 MB size is good enough. You can change this size by adjusting the –with-wal-segsize configure option.

So, as long as you keep making changes to the data in your database, WAL files will keep building up in your pg_wal directory. (pg_wal directory is used to store WAL files in PostgreSQL 10. In the previous versions, the directory was called pg_xlog). You can, of course, control the number of these files you want to store by using wal_keep_segments. Or you can control the total size of WAL files by using max_wal_size.

As new records are written, they are appended to WAL logs. Its position is defined by a Log Sequence Number. The Log Sequence Number (LSN) is a unique identifier in the transaction log. It represents a position in the WAL stream. That is, as records are added to the Postgres WAL log, their insert positions are described by the Log Sequence Number. pg_lsn is the datatype in which a Log Sequence Number is returned.

You can look at two LSN values and based on the difference, determine how much WAL data lies in between them. This will let you estimate the advancement of recovery.

WAL Configuration

Postgres WAL configuration parameters affect how the database performs. Two subsystems: checkpointer and background writer are very important in the context of PostgreSQL WAL. Let us first look into some memory area concepts in the RAM to understand where those subsystems operate and what their function is.

Here is what you will cover:

• Shared Buffers
• Dirty Pages
• Checkpointer
• Background Writer

Shared Buffers

Inside the postgresql.conf file, there is a parameter called shared_buffers. They are called ‘shared’ buffers because all of the background servers can access them. This parameter determines the amount of memory allocated to PostgreSQL for caching data. Dealing with memory is faster than reading from or writing to the files and hence it improves performance. A back-end process requests the buffer manager for a page and the buffer manager either loads the required page from persistent storage or returns it from the buffer pool if it is available. Now the back-end process can access the page.

Dirty Pages

When data is modified in memory, the page in which this data is stored is called a ‘dirty page’, as long as the modifications are not written to disk. So if there is a page that is different in the shared buffer and the disk, it is called a dirty page. The buffer manager flushes the dirty pages to storage with the assistance of two subsystems called – checkpointer and background writer.

Checkpointer

As the name suggests, checkpointer creates checkpoints in the Write-Ahead Log. These checkpoints are used for recovery. A checkpoint indicates that all the information prior to the checkpoint has been updated. So at every checkpoint, dirty pages are flushed to disk. How often a checkpoint is begun depends on checkpoint_segments and checkpoint_timeout.

The integer, checkpoint_segments indicates the maximum number of log segments between two checkpoints. The default value is 3 segments, where each segment is usually 16 MB. This value can be adjusted in the postgresql.conf file.

On the other hand, checkpoint_timeout is an integer that indicates the maximum time between two checkpoints. The default time is 5 minutes/ 300 seconds. This value can also be adjusted in the postgresql.conf file.

Since there are two parameters, a checkpoint is created when any one of the default conditions is met.

The file, postgresql.conf can be edited to increase the frequency of checkpoints, by reducing the checkpoint_segments and the checkpoint_timeout parameters. By reducing the integer value of these parameters, you will be flushing dirty pages more frequently, which is not desirable because checkpoints are costly. But on the other hand, after a crash, the recovery will be quicker.

In the case of bulk operations, you might have to adjust a parameter called checkpoint_warning, which will warn you when the checkpoints are too frequent. Taking a cue from the warnings, you can increase the checkpoint_segments value.

Background Writer

The background writer is a subsystem that assists the checkpointer. What it does is, move some of the modified data to the disk offloading the checkpointer. You should however be careful before tuning it as it increases the I/O load, especially in cases where there is some page that is being dirtied repeatedly. In such a case the background writer would write it repeatedly within a checkpoint interval.

WAL Archiving And Back-up

Since the main purpose of Postgres WAL is to secure data integrity, let us look at its role and significance in performing backups.

The PostgreSQL documentation lists three approaches to backup data in PostgreSQL. They are:

• SQL Dump
• File System Level Backup
• Continuous Archiving

SQL Dump

The pg_dump utility of PostgreSQL exports the database and dumps the contents into a file. This file is used as a backup. These dumps are consistent internally, which means that they represent the state of the database, the way it was when pg_dump started running. Refer the following code for recovery using SQL Dump file.

$ psql -d mydb_new < mydb.sql
$ PGOPTIONS='--client-min-messages=warning' psql -X -q -1 -v ON_ERROR_STOP=1 --pset pager=off -d mydb_new -f mydb.sql -L restore.log

File System Level Backup

The next backup approach and a faster one is file system level backup. This involves directly copying the files to store them so that they can be copied back when needed. But this method only works for complete backup and not for the backup of some individual databases. Read more about File System Level Backup here.

Continuous Archiving

You have already seen that Postgres WAL files are generated in the pg_wal directory to record all the modifications to the data in the database. When there is a crash in the system or there is a major error, you could go back to the last checkpoint, replay all the log entries and restore the database to whichever point you want. This is called point-in-time recovery (PITR). Just like file-system-level-backup, this approach also works for the complete backup of a database cluster.

To enable WAL archiving, wal_level must be set to ‘replica’ or higher. The archive_mode should be enabled and archive_command must be specified. The archive_command moves a completed Postgres WAL file to the location specified by the administrator. The archive_command is executed by the server and a Postgres WAL file is archived.

Design the archive_command so that it checks to see if there is a pre-existing archive file in the specified location, to avoid overwriting. Read more about continuous archiving here.

Performance of Postgres WAL

PostgreSQL WAL files are written one by one during the regular work of server. The functionality of HDD also works conveniently, but this kind of workload is different from the data accessing load. It is always recommended to store Postgres WAL files on a different physical disk mounted to the file system of your server.

WAL files can be written in two modes:

1. Synchronous Commit: With this commit, you will not be able to continue our work until all the WAL files are stored on the disk. With synchronous commit, you can get 900 transactions per second.
2. Asynchronous Commit: It allows faster completion of the transaction at the cost of data loss. With this commit, it returns success as soon as the transaction is completed logically, even before the WAL records it, they make their way to disk. You get 1500 transactions per second with asynchronous commit mode.

Benefits of WAL in PostgreSQL

Here are some of the benefits of using PostgreSQL WAL:

1. As only log files are flushed to disk during transnational commit, it reduces the number of disk writes.
2. The cost of syncing your log file is less as the log files are written sequentially.
3. It adds data page consistency .
4. Postgres WAL offers on-line backup and point-in-time recovery.

Conclusion

This article introduced you to Write-Ahead Logging, its purpose, and a few of the most important concepts related to it. You can hopefully move on to more concepts like archiving, restoring, and replication with this foundation.

Handling huge amounts of varied data can be a real pain. Ensuring data integrity and simplicity for analytics can be a lot of elaborate work. But it does not have to be a frustrating experience. Hevo Data can make your life a lot easier. It is a No-code platform and is fully automated.

Visit our Website to Explore Hevo

In fact, you can try it out for free and decide only after using it. Sign Up for the free 14-day trial. No commitments!

Share with us, your experiences working with Postgres WAL files in the comments. We would love to hear from you!