Table of Contents

1. The Big Question

When you run BEGIN; INSERT INTO ... ; but have not yet committed, is that data already written to the WAL (Write-Ahead Log)?

Short answer: YES. Every data modification is written to WAL the moment the statement executes, regardless of whether the transaction is later committed or rolled back. The WAL records the action, not the outcome. The commit or rollback is recorded as a separate WAL record afterward.

This post proves it with a hands-on demo — we will insert data, leave it uncommitted, and physically inspect the WAL file on disk to find our data sitting there.

2. What is WAL and Why Does It Exist?

The Write-Ahead Log (WAL) is PostgreSQL’s crash-recovery mechanism. The core rule is simple:

• Before any change is written to the actual data files (heap), the change is first appended to the WAL.
• If the server crashes, PostgreSQL replays the WAL on startup to restore the database to a consistent state.
• Committed transactions are replayed. Rolled-back transactions are undone during recovery using the ABORT records in the WAL.

This means the WAL must record everything — including uncommitted work — so that recovery knows what to undo if the server crashes mid-transaction.

3. Setup — Create Demo Table

postgres=# DROP TABLE IF EXISTS wal_demo;
DROP TABLE
postgres=#

postgres=# CREATE TABLE wal_demo (
    id      SERIAL PRIMARY KEY,
    fname   TEXT
);
CREATE TABLE
postgres=#

4. Check synchronous_commit Setting

This setting controls when WAL is flushed to disk. The default on means every COMMIT waits for the WAL to be physically written to disk.

postgres=# SHOW synchronous_commit;
 synchronous_commit
--------------------
 on   <-------------
(1 row)

postgres=#

Important: Even with synchronous_commit = on, individual INSERT statements within a transaction write to the WAL buffer immediately. The COMMIT is what triggers the flush-to-disk guarantee. The data still hits the WAL file on disk very quickly either way.

5. Record WAL Position Before Any Transaction

We capture the current WAL position (LSN = Log Sequence Number) so we can compare it after our insert.

postgres=# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02E8C0  | 00000001000000050000005F
(1 row)

postgres=#

Our starting point is LSN 5/5F02E8C0 in WAL file 00000001000000050000005F

6. BEGIN + INSERT (Uncommitted) — Check WAL Position

Now we start a transaction and insert a row, but we do NOT commit.

postgres=# BEGIN;
BEGIN
postgres=*#

postgres=*# SELECT txid_current();
 txid_current
--------------
         2384 <-----------
(1 row)

postgres=*#

postgres=*# INSERT INTO wal_demo (fname) VALUES ('SUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY');
INSERT 0 1
postgres=*#

-- The row is visible inside this transaction
postgres=*# SELECT * FROM wal_demo;
 id |                   fname
----+--------------------------------------------
  1 | SUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY
(1 row)

postgres=*#

Now check the WAL position without committing:

postgres=*# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02EBE0  | 00000001000000050000005F
(1 row)

postgres=*#


-- Also check the flush position
postgres=*# SELECT pg_current_wal_flush_lsn();
 pg_current_wal_flush_lsn
--------------------------
 5/5F02EBE0
(1 row)

postgres=*#

The WAL LSN moved! It went from 5/5F02E8C0 to 5/5F02EBE0. The INSERT was written to WAL even though we have not committed. The flush LSN matches, meaning the data is already on disk.

7. Grep the WAL File — Is the Uncommitted Data There?

Let’s physically inspect the WAL file on disk using the strings command.

[postgres@pgdb01 pg_wal]$ strings $PGDATA/pg_wal/00000001000000050000005F | grep -i "SUGIVARDHAN"
WSUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY
[postgres@pgdb01 pg_wal]$ 

[postgres@pgdb01 ~]$ xxd $PGDATA/pg_wal/00000001000000050000005F | grep -i -B2 -A2 "sugiv"
0002e9e0: 800a 0000 4d23 4834 0060 3500 7f06 0000  ....M#H4.`5.....
0002e9f0: 0500 0000 ca01 0100 0000 0000 ff03 0200  ................
0002ea00: 0208 1800 0100 0000 5753 5547 4956 4152  ........WSUGIVAR
0002ea10: 4448 414e 2053 554e 4441 5252 414a 414e  DHAN SUNDARRAJAN
0002ea20: 202d 2055 4e43 4f4d 4d49 5454 4544 2043   - UNCOMMITTED C
[postgres@pgdb01 ~]$

There it is! The uncommitted row’s data is physically present in the WAL file on disk. Anyone with read access to the WAL directory can see it. This is why WAL files should be protected with proper file permissions and encryption.

8. ROLLBACK — Table is Empty, But WAL Remembers

postgres=*# ROLLBACK;
ROLLBACK
postgres=#


-- The table is empty — the row is gone from the user's perspective
postgres=# SELECT * FROM wal_demo;
 id | fname
----+-------
(0 rows)

postgres=#
postgres=# SELECT pg_current_wal_flush_lsn();
 pg_current_wal_flush_lsn
--------------------------
 5/5F02EE18
(1 row)

postgres=#

Our ending point is LSN 5/5F02EE18 in WAL file 00000001000000050000005F

But is the data still in the WAL file?

[postgres@pgdb01 ~]$ strings $PGDATA/pg_wal/00000001000000050000005F | grep -i "SUGIVARDHAN"
WSUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY
[postgres@pgdb01 ~]$

[postgres@pgdb01 ~]$ xxd $PGDATA/pg_wal/00000001000000050000005F | grep -i -B2 -A2 "sugiv"
0002e9e0: 800a 0000 4d23 4834 0060 3500 7f06 0000  ....M#H4.`5.....
0002e9f0: 0500 0000 ca01 0100 0000 0000 ff03 0200  ................
0002ea00: 0208 1800 0100 0000 5753 5547 4956 4152  ........WSUGIVAR
0002ea10: 4448 414e 2053 554e 4441 5252 414a 414e  DHAN SUNDARRAJAN
0002ea20: 202d 2055 4e43 4f4d 4d49 5454 4544 2043   - UNCOMMITTED C
[postgres@pgdb01 ~]$

The data is STILL in the WAL file even after ROLLBACK. The ROLLBACK did not erase the WAL record. Instead, it wrote a new ABORT record to the WAL. During crash recovery, PostgreSQL replays the INSERT and then sees the ABORT, so it undoes the change. The WAL is an append-only log — records are never deleted or modified in place.

9. Inspect WAL with pg_waldump — The Full Story

pg_waldump decodes binary WAL files into human-readable records. Let’s inspect what PostgreSQL wrote for our uncommitted transaction.

[postgres@pgdb01 ~]$ pg_waldump $PGDATA/pg_wal/00000001000000050000005F \
    --start 5/5F02E8C0 --end 5/5F02EE18 \
    | grep -i "Insert\|Commit\|Abort"

rmgr: Heap        len (rec/tot):    102/   102, tx:       2384, lsn: 5/5F02E9D0, prev 5/5F02E998, desc: INSERT+INIT off: 1, flags: 0x00, blkref #0: rel 1663/5/65994 blk 0
rmgr: Btree       len (rec/tot):     64/    64, tx:       2384, lsn: 5/5F02EA98, prev 5/5F02EA38, desc: INSERT_LEAF off: 1, blkref #0: rel 1663/5/66000 blk 1
rmgr: Transaction len (rec/tot):     34/    34, tx:       2384, lsn: 5/5F02EBE0, prev 5/5F02EBA8, desc: ABORT 2026-09-25 19:23:15.559348 +08

[postgres@pgdb01 ~]$

Let’s break down every record in order:

Key Takeaway: Both the heap insertion and the primary key index update were written to the WAL before the transaction ended. When we executed ROLLBACK, PostgreSQL appended an ABORT record. The WAL is an append-only log: rolled-back records are never wiped or overwritten.

Why is this necessary? If the database crashes mid-transaction, PostgreSQL recovery reads the WAL sequentially. It must replay the uncommitted changes and then read the ABORT record to cleanly roll them back. Without logging uncommitted work, crash recovery would have no way to restore consistency.

10. Now COMMIT — Compare the WAL Records

Let’s do the same experiment, but this time we COMMIT.

postgres=# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02EE18  | 00000001000000050000005F
(1 row)

postgres=#

postgres=# BEGIN;
BEGIN
postgres=*#

postgres=*# INSERT INTO wal_demo (fname) VALUES ('RAJASEKHAR AMUDALA - COMMITTED COPY');
INSERT 0 1
postgres=*#

postgres=*# COMMIT;
COMMIT
postgres=#


-- Record the new WAL position
postgres=# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02F080  | 00000001000000050000005F
(1 row)

postgres=#


-- Verify the row is in the table
postgres=# SELECT * FROM wal_demo;
 id |                fname
----+-------------------------------------
  2 | RAJASEKHAR AMUDALA - COMMITTED COPY
(1 row)

postgres=#


-- Check the transaction visibility metadata
postgres=# SELECT
    fname,
    xmin                                      AS inserting_xid,
    xmin::text::bigint < txid_current()       AS is_in_past
FROM wal_demo;
                fname                | inserting_xid | is_in_past
-------------------------------------+---------------+------------
 RAJASEKHAR AMUDALA - COMMITTED COPY |          2385 | t
(1 row)

postgres=#

Grep the WAL file for the committed data:

[postgres@pgdb01 ~]$ strings $PGDATA/pg_wal/00000001000000050000005F | grep -i "RAJASEKHAR"
IRAJASEKHAR AMUDALA - COMMITTED COPYP
[postgres@pgdb01 ~]$

Inspect with pg_waldump:

[postgres@pgdb01 ~]$ pg_waldump $PGDATA/pg_wal/00000001000000050000005F \
    --start 5/5F02EE18 --end 5/5F02F080 \
    | grep -i "Insert\|Commit\|Abort"

rmgr: Heap        len (rec/tot):     54/   222, tx:       2385, lsn: 5/5F02EE80, prev 5/5F02EE18, desc: INSERT off: 2, flags: 0x00, blkref #0: rel 1663/5/65994 blk 0 FPW
rmgr: Btree       len (rec/tot):     53/   133, tx:       2385, lsn: 5/5F02EF60, prev 5/5F02EE80, desc: INSERT_LEAF off: 2, blkref #0: rel 1663/5/66000 blk 1 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx:       2385, lsn: 5/5F02F020, prev 5/5F02EFE8, desc: COMMIT 2026-09-25 19:50:32.487755 +08

[postgres@pgdb01 ~]$

Detailed breakdown of the committed records:

The Proof:

• Uncommitted / Aborted: Heap INSERT → Btree INSERT_LEAF → ABORT
• Committed: Heap INSERT → Btree INSERT_LEAF → COMMIT

The heap modification and index updates were written to the WAL file in both scenarios before the transaction ended. The only structural difference is the final record: ABORT vs COMMIT.

11. Side-by-Side Comparison

Key takeaway: The WAL does not wait for COMMIT to record your data. It writes every modification as it happens. The only difference between a committed and uncommitted transaction in the WAL is the final record: COMMIT vs ABORT. This is by design — if the server crashes mid-transaction, PostgreSQL needs the WAL to know what to undo.

Security implication: Sensitive data (passwords, credit card numbers, PII) written in a transaction that is later rolled back is still present in the WAL files on disk. WAL files should be protected with strict file permissions (0700).

12. Quick Summary Cheatsheet