Tag Archives: Crash Recovery

How PostgreSQL Replay Uncommitted Data During Crash Recovery

PostgreSQL, , , , , , , , , , ,

How PostgreSQL Replay Uncommitted Data During Crash Recovery

  1. When PostgreSQL crashes, everything in memory is lost but the important files on disk survive ā€” WAL files (transaction history), data files (actual tables), CLOG (who committed or not) andĀ pg_controlĀ (database status file).
  2. When PostgreSQL restarts, theĀ PostmasterĀ (the main process) first readsĀ pg_controlĀ file ā€” if it sees the database was not shut down cleanly, it understands a crash happened and starts theĀ Startup ProcessĀ to fix the database.
  3. TheĀ Startup ProcessĀ readsĀ pg_controlĀ to find the last checkpoint position (think of it as the last known good save point) and goes to that exact position in the WAL files to start reading from there.
  4. TheĀ Startup ProcessĀ reads WAL files one record at a time ā€” for each record, it finds the related data page, loads it into memory (Shared Buffers), applies the change to that page in memory and marks it as dirty (modified but not yet written to disk).
  5. TheĀ Startup ProcessĀ applies every single WAL record to memory pages ā€” it does not care whether the transaction was committed or not ā€” both committed and uncommitted changes are loaded into Shared Buffers because the WAL replay never checks CLOG at this stage.
  6. Before applying any change to a page, theĀ Startup ProcessĀ checks if that page was already updated before the crash (by checkingĀ pd_lsnĀ on the page) ā€” if the page is already up to date it simply skips that WAL record and moves to the next one ā€” this makes replay safe to run multiple times.
  7. When theĀ Startup ProcessĀ sees aĀ COMMITĀ orĀ ABORTĀ record in the WAL, it immediately updates the CLOG file ā€” marking that transaction asĀ COMMITTEDĀ orĀ ABORTEDĀ so PostgreSQL knows the final outcome of that transaction.
  8. When theĀ Startup ProcessĀ reaches the end of WAL files (the crash point), any transaction that had noĀ COMMITĀ orĀ ABORTĀ record is considered as never completed ā€” the Startup ProcessĀ explicitly marks all those transactions asĀ ABORTEDĀ in CLOG right away before allowing any user to connect ā€” this is why you always seeĀ ABORTEDĀ and neverĀ IN_PROGRESSĀ after a crash recovery.
  9. Once WAL replay is done, a final checkpoint runs ā€”Ā BGWriterĀ andĀ CheckpointerĀ take all the dirty pages sitting in Shared Buffers (including pages with uncommitted data) and write them permanently to the actual database files on disk ā€” thenĀ pg_controlĀ is updated to say the database is healthy and users are allowed to connect.
  10. Even though uncommitted data physically exists in the database files on disk, no user will ever see it ā€” because CLOG says those transactions areĀ ABORTED, MVCC automatically hides those rows from every query ā€” and laterĀ VACUUMĀ comes along and physically cleans up those dead rows from the pages and frees up the space.
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: linkedin.com/in/rajasekhar-amudala

WAL Commit Process

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

PostgreSQL WAL Commit Process

1. The Modification (In-Memory)

When a user executes a data-modifying query (like INSERT, UPDATE, or DELETE):

  • The change is made to the table or index data inside the Shared Buffers (RAM). The page in memory is now marked as “dirty.”

  • Simultaneously, a record of this exact change is constructed and written sequentially into the WAL Buffers (also in RAM).

2. The COMMIT Command Issued

When the client sends the COMMIT command, PostgreSQL must guarantee that this change will survive a sudden power outage or crash before it can tell the user “Success.”

3. Flushing to Disk (XLogFlush)

To ensure durability without the massive overhead of writing entire data pages to disk immediately, Postgres uses the Write-Ahead Logging protocol:

  • The internal function XLogFlush() is called.

  • It identifies the exact position (Log Sequence Number, or LSN) of the commit record in the WAL Buffer.

  • It issues a synchronous write to flush all WAL buffers up to that LSN out of RAM and into the current 16MB WAL segment file on permanent storage.

  • An fsync() system call is issued to ensure the OS cache actually commits the data to physical disk platters or flash memory.

4. Acknowledgment to the Client

Once the operating system confirms that the WAL record is safely written to the physical storage, the transaction status is updated to “committed” in the commit log (CLOG), and PostgreSQL sends a success acknowledgment back to the client application.

Crucial Architectural Concepts

  • Write-Ahead Rule: The core rule of WAL is that changes to data pages must not be written to permanent database files on disk until the log records describing those changes have been flushed to stable storage. If the server crashes, Postgres reads the WAL from the last checkpoint forward and reapplies the changes (“redoes” them).

  • Asynchronous Commit Alternative: If you set the configuration parameter synchronous_commit = off, Postgres will acknowledge the client’s COMMIT before the WAL buffer is flushed to disk (relying on the WAL Writer background process to flush it within roughly 3 times wal_writer_delay). This massively increases write throughput but introduces a risk of losing up to a split-second of recent transactions if the server suddenly loses power.

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: linkedin.com/in/rajasekhar-amudala