Understanding WAL Files in PostgreSQL – For Oracle DBAs

What is WAL?

WAL (Write-Ahead Logging) ensures data durability and crash recovery. Every change is first written to WAL before being flushed to data files. 

1. WAL files are stored in: $PGDATA/pg_wal/
2. WAL files are pre-allocated and have a fixed size (usually 16MB).
3. When a WAL file fills up, PostgreSQL switches to the next.
4. If archive_mode = on, PostgreSQL uses archive_command to copy the full WAL file to an archive location (e.g., /pgArch/...).
5. If archiving is not enabled, WAL files will be reused (overwritten) after they are no longer needed for recovery or replication.

WAL Files = Redo Log Files
WAL Archives = Archive Log Files

Redo Log File Size

• Oracle: Redo log file size ~200MB (default)
• PostgreSQL: WAL file size = 16MB (default and fixed)

Redo Log Files Count

• Oracle: Minimum 2 or more redo log groups
• PostgreSQL: Number of WAL files depends on min_wal_size
• If min_wal_size = 80MB → 80 ÷ 16MB = 5 WAL files (equivalent to 5 redo log files)

Archive Log File Size

• Oracle: Archive log file size = Redo log file size (~200MB)
• PostgreSQL: WAL archive = 16MB (same as WAL file size)

Key Notes:

• WAL files are pre-allocated with a fixed size of 16MB (default)
• If archive_mode is OFF, PostgreSQL will overwrite old WAL files
• WAL files help with crash recovery, point-in-time recovery (PITR), and replication
• WAL file size by default 16MB (wal_segment_size)
• WAL file count depends on (min_wal_size / wal_segment_size )

PostgreSQL vs Oracle Comparison

Minimum Number of WAL Files Required

PostgreSQL maintains a minimum number of WAL segments based on the min_wal_size setting:

Check Configuration

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW wal_segment_size;"
wal_segment_size
------------------
16MB  <----- Each redo log file size
(1 row)

[postgres@lxicbpgdsgv01 ~]$


[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW min_wal_size;"
 min_wal_size
--------------
 80MB <---- 80MB/16MB = 5 <--- Total 5 redo log files with each 16MB
(1 row)

[postgres@lxicbpgdsgv01 ~]$

View WAL files (Redo log files)

postgres=# SELECT current_setting('data_directory') || '/pg_wal' AS wal_directory;
        wal_directory
-----------------------------
 /pgData/pgsql17/data/pg_wal  <---- Redo log file directory
(1 row)

postgres=# 
[postgres@lxicbpgdsgv01 ~]$ ls -lh /pgData/pgsql17/data/pg_wal | grep -E '^-.* [0-9]+ .* [0-9A-F]{24}$'
-rw-------. 1 postgres postgres 16M Oct 8 01:33 000000010000000000000005
-rw-------. 1 postgres postgres 16M Oct 8 01:28 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct 8 01:28 000000010000000000000007
[postgres@lxicbpgdsgv01 ~]$

min_wal_size = 80MB means PostgreSQL will try to keep at least 5 WAL segment files on disk (since 80MB ÷ 16MB = 5 files).

However, in our case, only 3 WAL files are currently present. This is likely because the system is under low load in the testing environment, so fewer WAL files are needed at the moment.

View Archived WAL Files (Archive log files)

postgres=# SHOW archive_command;
        archive_command
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f
(1 row)

postgres=#

[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgArch/pgsql17/arch/
total 64M
-rw-------. 1 postgres postgres 16M Oct  7 23:50 000000010000000000000001
-rw-------. 1 postgres postgres 16M Oct  7 23:51 000000010000000000000002
-rw-------. 1 postgres postgres 16M Oct  8 01:28 000000010000000000000003
-rw-------. 1 postgres postgres 16M Oct  8 01:28 000000010000000000000004
[postgres@lxicbpgdsgv01 ~]$

-- Archive log file generation size equal to Redo log file size 

Best Practices

• Use a dedicated mount point for pg_wal (WAL directory) – eg. /pgWal
• Set min_wal_size to higher value in production (e.g., 1GB or 8GB)
• Keep WAL archive files on a separate mount point for better I/O and space isolation – eg. /pgArch
• Monitor WAL archive directory regularly to avoid disk full errors

Note: WAL file size is fixed at cluster init (default: 16MB). You cannot change it for an existing cluster.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/