How PostgreSQL Replay Uncommitted Data During Crash Recovery
- When PostgreSQL crashes, shared memory is lost but WAL files, data files, CLOG, and pg_control remains on disk.
- On restart, Postmaster reads pg_control, detects state = DB_IN_PRODUCTION (not a clean shutdown), sets state to DB_IN_CRASH_RECOVERY, and forks the Startup Process to handle recovery.
- Startup Process (background process) reads pg_control again to find the last checkpoint LSN (the REDO point) and opens the WAL file at exactly that position to begin replay.
- 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).
- 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 WAL replay never checks CLOG at this stage.
- Before applying any WAL record to a page, Startup Process checks the page’s pd_lsn — if pd_lsn >= WAL record LSN, the page already has that change (BGWriter flushed it before crash) and the record is safely skipped (idempotent replay).
- When Startup Process sees
XLOG_COMMITorXLOG_ABORTrecords in the WAL during replay, it updates CLOG accordingly, marking the transaction as COMMITTED or ABORTED. - When the Startup Process reaches the end of WAL files (the crash point), any transaction that had no
COMMITorABORTrecord is considered as never completed. The Startup Process explicitly marks all those transactions asABORTEDin CLOG right away before allowing any user to connect — this is why you always seeABORTEDand neverIN_PROGRESSafter a crash recovery. - After replay finishes, Startup Process performs an end-of-recovery checkpoint (flushes all dirty buffers including with uncommitted data, write them permanently to the actual database files on disk), updates pg_control with the new checkpoint LSN and sets state = DB_IN_PRODUCTION, and then signals Postmaster to accept client connections.
- Uncommitted data physically exists in the database files on disk, but no user will ever see it: CLOG says those transactions are
ABORTED, so MVCC automatically makes those rows permanently invisible to all queries. Later, VACUUM physically removes the dead tuples 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