On every commit, postgres flushes WAL to disk through direct IO (configurable via wal_sync_method) to guarantee durability. Data files only need to be in shared buffers, can be flushed to disk later.
My question is, on every commit, does postgres also fire a "write system call" to flush data files as well? It shouldn’t impact write latency, cuz write is async, kernel only moves data from shared buffer to kernel page cache, there’s no immediate IO involved.
In another word, does postgres only flush data files to disk during checkpoint, or are there other mechanisms to flush data files also?
https://youtu.be/1VWIGBQLtxo?si=ZNGu17nm2xj8JpBY&t=291
This video suggests transaction commit doesn’t write data files to disk.
>Solution :
During a transaction, data are usually not written to the data files, only to WAL.
Most of the writing to data files happens during a checkpoint, but there is also the background worker that slowly writes some dirty buffers out to disk, so that there are always enough clean buffers available for client backend processes that need room in shared buffers.