Fun Fact 2. Force WAL to disk… | Arpit Bhayani

Fun Fact – When you commit a transaction in PostgreSQL, the database would not instantly write your precise knowledge pages to disk. Instead, it follows this sequence

1. Write to WAL
2. Force WAL to disk utilizing fsync
3. Return a profitable decide to your utility
4. Write the precise knowledge pages to disk within the background

This is the “fast commit” behaviour of PostgreSQL and the secret is that the WAL comprises sufficient data to reconstruct any dedicated transaction, even when the information pages have not been written but.

If the database crashes after step 3 however earlier than step 4, PostgreSQL can replay the WAL throughout restoration to revive all dedicated modifications. Yes, restoration will take longer on this case, nevertheless it’s a comparatively uncommon occasion.

This method provides a number of efficiency benefits:

– a number of txn grouped collectively in WAL flushes
– throughout commit solely seq WAL writes and never random knowledge web page writes
– predictable commit time – now it relies upon solely on WAL write velocity, not complicated knowledge web page operations

btw, this got here up in our open dialogue on day 1 of my methods design June cohort, by the way in which. These ideas appear robust, however they are not, particularly once you construct the precise instinct. We can be digging slightly deeper on this in subsequent weeks.

tremendous completely happy that the discussions are getting deeper and higher with each cohort 🙂