What is Write-Ahead Logging (WAL) in PostgreSQL ?

 

Write-Ahead Logging (WAL) in PostgreSQL

Write-Ahead Logging (WAL) is PostgreSQL's mechanism for ensuring durability, crash recovery, and replication.

The core principle is:

Before changing the actual data page on disk, PostgreSQL first writes a record describing the change to the WAL.

This follows the ACID property of Durability.

---

Why WAL Is Needed

Imagine an account table:

UPDATE accounts

SET balance = balance - 1000

WHERE id = 1;

Suppose:

Balance = 5000

New balance:

4000

Now consider:

1. Data page updated in memory

2. Power failure

3. Data page not written to disk

Without WAL:

Transaction committed

Change lost

Database corruption or inconsistency can occur.

---

WAL Principle

Instead of immediately writing the table page:

Transaction

     |

     v

Create WAL Record

     |

     v

Flush WAL to disk

     |

     v

Commit Success

     |

     v

Data page written later

PostgreSQL guarantees:

WAL reaches disk first

Hence the name:

Write Ahead Log

---

Internal Flow

Suppose:

UPDATE employees

SET salary = 10000

WHERE id = 1;

Step 1

Data page loaded into shared buffers.

Buffer Cache

salary=8000

---

Step 2

Update performed in memory.

Buffer Cache

salary=10000

The actual disk page is still unchanged.

---

Step 3

Generate WAL record.

Example (simplified):

Table: employees

Block: 123

Offset: 5

Old Salary: 8000

New Salary: 10000

---

Step 4

Write WAL record into WAL buffer.

Shared Buffers

      +

WAL Buffers

---

Step 5

COMMIT

COMMIT;

Before PostgreSQL returns:

COMMIT successful

it flushes WAL to disk.

pg_wal/

directory.

---

Step 6

Actual table page written later.

Background Writer

Checkpoint

may write table pages minutes later.

---

Crash Recovery Example

Before Crash

Table:

salary=8000

Memory:

salary=10000

WAL:

UPDATE salary=10000

WAL already flushed.

---

Power Failure

Server crashes

Memory lost.

---

Startup Recovery

PostgreSQL reads WAL:

salary=10000

and replays the operation.

Result:

salary=10000

is restored.

---

WAL Records

WAL does not store full tables.

Instead it stores changes.

Example:

Insert tuple

Delete tuple

Update tuple

Create index

Drop table

Everything important is logged.

---

WAL Files

Location:

$PGDATA/pg_wal/

Example:

000000010000000000000001

000000010000000000000002

000000010000000000000003

Typically:

16 MB

per WAL segment.

Check:

SHOW wal_segment_size;

---

WAL and Checkpoints

Without checkpoints:

Recovery would replay years of WAL

Very slow.

PostgreSQL periodically performs checkpoints.

---

Checkpoint

A checkpoint writes dirty pages from memory to disk.

Shared Buffers

        ↓

Disk

and records:

Checkpoint Position

in WAL.

---

Recovery After Checkpoint

Instead of replaying all WAL:

Start from last checkpoint

Apply newer WAL

Much faster.

---

WAL and COMMIT

A common interview question:

Does COMMIT write table data?

No.

COMMIT writes WAL

Not necessarily table pages.

Table pages can be written later.

This makes PostgreSQL much faster.

---

WAL Buffers

Parameter:

SHOW wal_buffers;

Example:

16 MB

Purpose:

Temporary memory

for WAL records

before flushing.

---

WAL Levels

Check:

SHOW wal_level;

Possible values:

minimal

replica

logical

---

minimal

Least WAL generation.

Used for:

Standalone database

---

replica

Default in modern PostgreSQL.

Supports:

Streaming Replication

PITR

Recovery

---

logical

Supports:

Logical Replication

Debezium

CDC

Kafka

Captures enough information to reconstruct row-level changes.

---

WAL and Replication

Primary server:

Generates WAL

Standby server:

Reads WAL

Applies the same changes.

Architecture:

Primary

   |

   | WAL Stream

   v

Standby

This is PostgreSQL Streaming Replication.

---

WAL and Point-In-Time Recovery (PITR)

One of PostgreSQL's strongest features.

Example:

10:00 Good

11:00 Good

11:30 User drops table

Need recovery to:

11:29

Restore:

Base Backup

+

WAL Records

Replay until:

11:29

Table restored.

---

Important WAL Parameters

wal_level

SHOW wal_level;

Usually:

replica

---

max_wal_size

SHOW max_wal_size;

Example:

4GB

8GB

16GB

Controls WAL growth before checkpoint.

---

min_wal_size

Example:

1GB

WAL retained after checkpoints.

---

checkpoint_timeout

Default:

5 minutes

Maximum interval between checkpoints.

---

synchronous_commit

SHOW synchronous_commit;

Values:

on

off

remote_apply

remote_write

---

Performance Trade-off

ON

Safer.

OFF

Faster but may lose a few transactions during crash.

---

Oracle Comparison

Oracle also has a similar mechanism.

PostgreSQL	Oracle
WAL	Redo Logs
WAL Writer	LGWR
Checkpoint	Checkpoint
Crash Recovery	Instance Recovery
PITR	RMAN Recovery
WAL Streaming	Data Guard Redo Shipping

For Oracle DBAs:

PostgreSQL WAL is conceptually equivalent to Oracle Redo Logs.

---

Interview Answer

Write-Ahead Logging (WAL) is PostgreSQL's durability mechanism. Every database change is first recorded in the WAL and flushed to disk before the transaction commit is acknowledged. If the server crashes before data pages are written, PostgreSQL replays the WAL during recovery to restore committed transactions.

One-Line Summary

Data page later, WAL first. PostgreSQL always writes the change record to WAL before writing the actual table page, enabling crash recovery, replication, and point-in-time recovery.