March 1, 2025
Summary: in this tutorial, you will learn how to troubleshooting WAL accumulation in the pg_wal directory in PostgreSQL.
Table of Contents
Background
One of the most frequent concerns for PostgreSQL users is: Why is my pg_wal growing? or pg_wal is filling up the filesystem, or pg_wal does not release the space, and so on.
In PostgreSQL, the pg_wal directory plays a crucial role in managing the Write-Ahead Logging (WAL) mechanism, which ensures the durability of transactions and crash recovery. WAL files are also required for replication purposes (if any).
Administrators may encounter situations where the pg_wal directory accumulates files, gradually consuming disk space and potentially leading to filesystem issues and database crashes.
When the pg_wal disk filled up, this meant that there was no more space to write additional changes, so PostgreSQL did the only thing it could: it panicked and terminated the server and all connections; there is not much use for a database which cannot make any more changes.
It is important to keep in mind that even if PostgreSQL has shut down due to a full pg_wal disk and will not start up again, you don’t have a corrupted database at this point. You will still be able to take some actions to bring the database up without losing or corrupting data.
This tutorial will cover some common reasons why the pg_wal directory has increased its size, how to troubleshoot, and strategies to mitigate the problem.
Brief overview of how WAL archiving works
- When PostgreSQL needs to make a change to data, it first records this change in the Write-Ahead-Log (WAL) and
fsyncs the WAL to the disk.fsyncis a system call that guarantees that the data that was written has actually been written fully to persistent storage, not just stored in the filesystem cache. Once this WAL has been successfully written to disk, even if the database crashes between this write and the actual change to the data directory the database will be able to recover to a consistent state by replaying changes stored in the WAL. - At some future point, the database will issue a
CHECKPOINT, which flushes all of the modified buffers to disk and stores the changes permanently in the data directory. When aCHECKPOINThas been completed, the database records the WAL position at which this occurs and knows that all of this data up to this point has been successfully written to disk. (It is worth noting that any changes that are written to disk via theCHECKPOINTalready had their contents recorded previously in the WAL stream, so this is just a way of ensuring that the changes recorded by the WAL data are in fact applied to the actual on-disk relation files.) - WAL is stored in on-disk files in 16MB chunks; as the database generates WAL files, it runs the
archive_commandon them to handle storing these outside of the database. Thearchive_command’s exit code will determine whether PostgreSQL considers this to have succeeded or not. Once thearchive_commandcompletes successfully, PostgreSQL considers the WAL segment to have been successfully handled and can remove it or recycle it to conserve space. If thearchive_commandfails, Postgres keeps the WAL file around and tries again indefinitely until this command succeeds. Even if a particular segment file is failing, PostgreSQL will continue to accumulate WAL files for all additional database changes and continue retrying to archive the previous file in the background.
As you can see, if there is an issue with the archive_command, this can lead up to a build-up in WAL, since PostgreSQL will not remove any segments until they have been confirmed to be received by the archive_command.
When the PostgreSQL server starts up, it will look at the pg_control file to determine when the last checkpoint was, and will replay all WAL that was generated in the database since the last checkpoint. This ensures that the database will reach a consistent state that will reflect all data changes made and recorded in the WAL.
WAL files are named sequentially, with multiple portions of the filename defined based on the ordering of changes made in the database. You can think of the entirety of the history of the database’s changes as made from playing all of the WAL files from the very beginning. However since over time there would be too many changes to replay, PostgreSQL uses CHECKPOINTs as basically a point to determine that it is safe to remove/recycle WAL files and pick up from this point.
Just like a failing archive_command can keep WAL files around indefinitely, a replication slot that is not getting updated due to the replication slot consumer being offline will prevent WAL from being removed and be a factor for filling up the disk. PostgreSQL 13 onward can protect against this specific scenario by using the max_slot_wal_keep_size setting. This has the benefit of limiting the amount of disk that will ever be taken up by WAL (thus preventing the primary server from shutting down due to insufficient storage), with the tradeoff that if you do hit this limit, the replica will need to be rebuilt since the primary would have already removed the WAL needed for it to progress.
High transaction rate spikes
Problem: High transaction rates or spikes. WAL segments are generated due to PostgreSQL processing transactions before the data files are written. If the transaction rate exceeds the capacity of the system to archive or remove these segments, they accumulate in the pg_wal directory, leading to disk space exhaustion.
Although the archiving speed is generally unimportant, it is expected to keep pace with the average WAL generation rate in the pg_wal directory. If the archiving speed falls significantly behind the WAL segment creation rate for too long, pg_wal will start accumulating files until they are archived. If pg_wal does not have enough space to hold some uncommon/unplanned load, it can run out of space.
Troubleshooting: Identify the WAL segment creation speed in pg_wal and compare it with the archiving speed.
Solution: If the archiving speed is not fast enough, we should improve the archiving IO subsystem (or network latency when working with S3 cloud services). Otherwise, assessing how long the workload spikes last and adding additional space for pg_wal.
Inefficient checkpoint
Problem: Checkpoints in PostgreSQL are crucial for flushing modified data from memory to disk and recycling obsolete WAL segments. However, inefficient checkpointing strategies, such as too-infrequent or too-aggressive checkpoints, can impact the accumulation of WAL files. Infrequent checkpoints result in prolonged retention of WAL segments, while overly aggressive checkpoints may lead to excessive disk I/O and WAL generation.
Troubleshooting: Assessing the checkpoint and WAL parametrization regarding the database workload (min_wal_size, max_wal_size, wal_keep_size/wal_keep_segments, bgwriter_lru_maxpages, bgwriter_delay, etc).
Solution: Finding a proper trade-off for checkpoint frequency and bgwriter efficiency.
Replication lag
Scope: This only applies when streaming replication is set.
Problem: Delays in applying changes on standby servers can exacerbate the accumulation of WAL files on the primary server. A standby server might fall behind its primary due to network issues/slowness, high load, or hardware resource constraints, so the primary server retains WAL segments until they are successfully replayed on the standby. This delay can strain disk space availability on the primary server.
The above reasons and a misconfiguration in wal_keep_size/wal_keep_segments parameters might contribute to the space exhaustion.
Abandoned replication slots will hold WAL segments indefinitely.
Troubleshooting: Verify the replication lag between primary and standbys. Verify the configuration of wal_keep_size/wal_keep_segments (depending on your database version). Looking for abandoned replication slots in the primary server.
Solution: Improving the network performance or IO performance on the standby server (or any hardware bottleneck). Dropping any abandoned replication slots. Adjusting the wal_keep_size/wal_keep_segments configuration according to the replication performance and pg_wal directory capacity (in primary).
Failing WAL archive
Scope: This only applies when the database runs in continuous archiving (archive_mode is set to on, and archive_command is also set).
Problem: If the archiver process fails to perform the command in archive_command, WAL segments will remain on the pg_wal until the archiver succeeds.
The archive_command can fail for different reasons; it could be out of disk space on the archive repository, unreachable destinations, ssh keys could have been changed, the archive tool version could have been updated on the repository server without a corresponding update on the primary database server, or many other possibilities.
Troubleshooting: Whenever archive_command fails, you will get an error message in the PostgreSQL log.
Solution: It depends on identifying the reason for archiving failure and fixing it.
WAL retention policies
Problem: Misconfigured or inadequate retention policies for WAL archives can also contribute to accumulating files in the pg_wal directory. If archival processes fail to remove obsolete WAL segments promptly, the directory may become bloated with unnecessary files, consuming disk space that could be used for other purposes.
Troubleshooting: Reviewing the aforementioned min_wal_size, max_wal_size, wal_keep_size/wal_keep_segments parameters. Reviewing PostgreSQL log for failing archive events.
Solution: Improving parametrization and fixing failing archive reasons.
Conclusion
The pg_wal directory in PostgreSQL is critical for ensuring data durability and recovery, but its unchecked growth can lead to disk space constraints and operational challenges. By understanding the common reasons behind the accumulation of files in the pg_wal directory and implementing appropriate strategies such as tuning checkpoint settings, monitoring replication lag, and enforcing efficient retention policies, administrators can effectively manage disk space usage and maintain the stability and performance of their PostgreSQL databases.
Since WAL segments play a lead role in PostgreSQL databases, you should never manually remove WAL segments from pg_wal. It might lead to database crashes, failures on crash recoveries, failures on WAL archive events, and incomplete backup copies.