Fun Fact - When you commit a transaction in PostgreSQL, the database doesn’t immediately write your actual data pages to disk. Instead, it follows this sequence
- Write to WAL
- Force WAL to disk using fsync
- Return a successful commit to your application
- Write the actual data pages to disk in the background
This is the “fast commit” behaviour of PostgreSQL and the key is that the WAL contains enough information to reconstruct any committed transaction, even if the data pages haven’t been written yet.
If the database crashes after step 3 but before step 4, PostgreSQL can replay the WAL during recovery to restore all committed changes. Yes, recovery will take longer in this case, but it’s a relatively rare event.
This approach offers several performance advantages:
- multiple txn grouped together in WAL flushes
- during commit only seq WAL writes and not random data page writes
- predictable commit time - now it depends only on WAL write speed, not complex data page operations
btw, this came up in our open discussion on day 1 of my systems design June cohort, by the way. These concepts seem tough, but they’re not, especially when you build the right intuition. We will be digging a little deeper on this in subsequent weeks.
super happy that the discussions are getting deeper and better with every cohort :)