Re: Effect of COMMIT on WAL-Buffers + Effect of Check pointer.

Hi,

No, a checkpoint record does not directly force a write of the
Write-Ahead-Logging (Wal) buffer to the WAL files. Instead, the checkpoint
concludes with writing a checkpoint record to the WAL, but the WAL records
for transactions committed before the checkpoint have already been written
and flushed to the WAL files on disk.

Here is a breakdown of the process in PostgreSQL:

Transactions are written to the WAL buffer. When a transaction occurs, the
changes are first written to the WAL buffer, which is a shared memory area.

WAL is flushed at commit. When a transaction commits, its WAL records are
flushed from the WAL buffer to the WAL files on disk. This guarantees that
committed transactions are durable, surviving a system crash.

The checkpointer process is triggered. A checkpoint is a background process
that is triggered periodically by time, log size, or an explicit command.
The main purpose of a checkpoint is to flush all dirty data pages from the
shared buffers to disk.

The checkpoint record is written last. After all the data pages have been
flushed to disk, the checkpointer writes a special "checkpoint record" to
the WAL. This record's location is saved in the pg_control file and serves
as a point of reference for crash recovery.

Older WAL segments are removed. After a checkpoint, any WAL segments that
came before the checkpoint record are no longer needed for crash recovery
and can be recycled.

*Note:*
Regular WAL flushing- The WAL buffer is routinely flushed to the permanent
WAL files on disk during regular operation. This primarily happens at
transaction commit time (unless synchronous_commit is off), and also by a
dedicated WAL writer background process.

*Commit in PostgreSQL*
● WAL Writes- Backend processes write WAL records from WAL Buffers to File
System buffer cache.
● WAL Flush- The WAL Records gets flushed/written to WAL Segments on Disk.
● Commit-> WAL Writes + WAL Flush (synchronous_commit)
● With async commit, the WAL Writer flushes the WAL records and NOT the
Backend processes
● WAL Record Inserts (local): WAL records are first created in WAL
buffers(XLogInsertRecord). Since multiple
backend processes will be creating the WAL records at a time, it is
properly protected by locks. The writing of
WAL records in wal_buffers gets continuously written/flushed(XLogFlush) to
WAL segments by different
backend processes(WAL Writes). If the sychronous_commit is completely off,
the flush won’t be happening
immediately but relies on wal_writer_delay settings
● How much data we lose if we opt for full asynchronous commit
(synchronous_commit = off)
● The answer is slightly complex, and it depends on wal_writer_delay
settings. By default it is 200ms. That means
WALs will be flushed in every wal_writer_delay to disk. The WAL writer
periodically wakes up and calls
XLogBackgroundFlush(). This checks for completely filled WAL pages. If they
are available, it writes all the buffers up to that point
● commit_delay-Sets the delay in microseconds between transaction commit
and flushing WAL to disk
* Flushes WAL Records from WAL Buffers (3% of shared_buffers) to WAL
Files/Segments on disk
(wal_segment_size=16MB) . If a transaction is too large and exceeds WAL
Records > wal_buffer_size even
uncommitted changes will get flushed to WAL Segments on disk. But during
applying WAL Records to data files
*during crash/instance recovery* only committed records since last
checkpoint will get applied (the CLOG records help to identify committed
transactions)
● PG 17- Increased the WAL segment size from 16MB to 64MB. This enhancement
has resulted in a 10%-20% performance improvement with various workloads.
● So WAL Records are flushed from WAL Buffers to Disk not only during
transaction commit but also when
WAL buffers get filled.
● Every Checkpoint maintains a Checkpoint record in WAL Segments so that
the WAL Records prior to the
checkpoint record can be reused/deleted when WAL segments need to be
overwritten. Also Archiving will need to archive only completely filled WAL
Segments before they get overwritten/recycled. But WAL Segments can be
switched without getting full either by setting archive_timeout or
pg_switch_wal.

Best Regards,
Abhishek Singh
M.E., Coburg University, Germany

Profile: *https://linkedin.com/in/abhi15 <https://linkedin.com/in/abhi15>*

------------------------------------

On Tue, 7 Oct, 2025, 4:53 pm Subramanian,Ramachandran, <
ramachandran(dot)subramanian(at)alte-leipziger(dot)de> wrote:

> Hello,
>
>
>
>
>
>
>
> Coming from a Db2 – mainframe world trying to understand Postgres. Kindly
> forgive my ignorance and the somewhat long winded question.
>
>
>
>
>
> When a particular transaction TRAN1, inserts/updates/deletes data, the
> changes are made to the memory blocks in the Shared Buffer ( data buffers
> ) and corresponding Undo and Redo Logs are written to the Log buffers.
> While
>
> TRAN1 is running , TRAN2 TRAN3 …. TRAN4 can run concurrently and be
> writing information tot he WAL-Buffers.
>
>
>
>
>
> Let us assume that TRAN1 began at 0000 Hours and at has updated 1 rows
> at 0001 Hours.
>
>
>
> Let us further assume for simplicity that TRAN1 TRAN2 TRAN3 and TRAN4
> have updated 1 row each and written 2 WAL-Records each in the WAL-Buffer
> BUT NOT issued a COMMIT yet.
>
>
>
> Now at 0002 Hours TRAN4 alone has issued a COMMIT.
>
>
>
> Will all the 8 WAL-Buffer records be written to the WAL files? Obviously
> TRAN1 2 and 3 are IN-FLIGHT ( un committed ) at 0002 Hours, while TRAN4 is
> committed. ( This is how DB2 works . When a COMMIT is issued by any
> transaction ALL the log buffers are written to disk, immaterial of if they
> are commited or not. There is a BEGIN Unit of Recovery Log record, a END
> Unit of Recovery log Record associated with each transaction . Each Unit of
> Recovery is an unique identifier. Every log record that belongs to this
> Unit of Recovery ID has this identifier in it. So after a crash, the logs
> are scanned forward since the last check point and only those logrecords
> with a matching BEGIN UR and END UR are redone, and those with just a BEGIN
> UR and no matching END UR are rolled back.
>
>
>
> Does a COMMIT even cause the ALL the WAL-Buffers to be written to
> WAL-Files in Postgres?
>
>
>
> If not what exactly does a COMMIT do? how can one force a write of the
> WAL-Buffers to disk with a SQL command?
>
>
>
>
>
>
>
>
> Additionally, after the check pointer externalizes all the comitted Shared
> Buffer Data to disk, does it write a check point record to the WAL-Buffer
> alone?
>
>
>
> if the check point information is just written to the WAL-Buffer by the
> Check-Pointer background process and before it is copied down to a file on
> the disk, Postgres crashes, is this check point not lost ? Does a Check
> point record force a WAL-Buffer write to WAL-Files ?
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Thank you for your time.
>
>
>
>
>
> Ram
>
>
>
> Freundliche Grüße
>
> *i. A. Ramachandran Subramanian*
>
> Zentralbereich Informationstechnologie
>
> Alte Leipziger Lebensversicherung a. G.
>
> Hallesche Krankenversicherung a. G.
>
> ______________________
>
> ALH Gruppe
> Alte Leipziger-Platz 1, 61440 Oberursel
> <https://www.google.com/maps/search/Alte+Leipziger-Platz+1,+61440+Oberursel?entry=gmail&source=g>
> Tel: +49 (6171) 66-4882
> Fax: +49 (6171) 66-800-4882
> E-Mail: ramachandran(dot)subramanian(at)alte-leipziger(dot)de
> www.alte-leipziger.de
> www.hallesche.de
>
> Alte Leipziger Lebensversicherung a. G., Alte Leipziger-Platz 1, 61440
> Oberursel
> <https://www.google.com/maps/search/Alte+Leipziger-Platz+1,+61440+Oberursel?entry=gmail&source=g>
>
> Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn
> (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen
> Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
>
> Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d.
> H. HRB 1583 · USt.-IdNr. DE 114106814
>
> Hallesche Krankenversicherung a. G., Löffelstraße 34-38, 70597 Stuttgart
> <https://www.google.com/maps/search/L%C3%B6ffelstra%C3%9Fe+34-38,+70597+Stuttgart?entry=gmail&source=g>
>
> Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn
> (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen
> Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
>
> Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 ·
> USt.-IdNr. DE 147802285
>
> Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen
> nicht der Versicherungsteuer (§ 4 (1) Nr. 5 b VersStG) ·
> Versicherungsleistungen sowie Umsätze aus
> Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
>
> Pflichtangaben <https://www.alte-leipziger.de/impressum> der ALH Gruppe
> gemäß § 35a GmbHG bzw. § 80 AktG
>