patternsqlMinor
What makes it so much faster to write to the "WAL" compared to directly to the "real database"?
Viewed 0 times
realthemuchwhatmakeswritedatabasefasterdirectlywal
Problem
PostgreSQL works like this, if I have understood things correctly:
The explanation I've been given for this seemingly strange ritual is that it's much faster to write to the WAL than it is to write to the database files directly for each query.
But why is this? I assume that PG's WAL log file is more sophisticated than just "adding lines to the end of a text file", since that could result in two queries writing to it at once and thus losing or corrupting data. So it has to be more sophisticated than that. Which means more overhead. At which point I wonder what real performance benefit there is to doing this, since both, at the end of the day, involve writing to a slow HDD/SSD disk.
However, I'm not doubting this. I'd just like to know why it's so much faster.
It seems to me that the problems with writing to the database files are the same with writing to the WAL. In both cases, you need to make sure that it's done orderly and properly. My only guess is that maybe various constraint checks and whatnot are expensive to look up.
Apparently, the concept of a "WAL" is unique to PostgreSQL, but it sounds like just common sense to keep at least some sort of "tabs" on what queries have been executed in order to account for power losses and software crashes. Just blindly writing to the database file each time but not even keep any
- I make an INSERT, DELETE or UPDATE query to a table in my PG database.
- PG immediately writes this query and its parameters to the HDD/SSD in the form of a raw (internal) log, probably in binary format.
- Assuming that the table is small enough to store in RAM, PG now INSERTs/DELETEs/UPDATEs this "RAM table" to reflect the change, but does not actually change the database files.
- At some later point, perhaps in seconds, perhaps in minutes, and perhaps even hours or days later, when it "has time", PG updates the database files with the information from this "WAL" log file, and deletes the relevant line(s)/entries from the WAL log file.
The explanation I've been given for this seemingly strange ritual is that it's much faster to write to the WAL than it is to write to the database files directly for each query.
But why is this? I assume that PG's WAL log file is more sophisticated than just "adding lines to the end of a text file", since that could result in two queries writing to it at once and thus losing or corrupting data. So it has to be more sophisticated than that. Which means more overhead. At which point I wonder what real performance benefit there is to doing this, since both, at the end of the day, involve writing to a slow HDD/SSD disk.
However, I'm not doubting this. I'd just like to know why it's so much faster.
It seems to me that the problems with writing to the database files are the same with writing to the WAL. In both cases, you need to make sure that it's done orderly and properly. My only guess is that maybe various constraint checks and whatnot are expensive to look up.
Apparently, the concept of a "WAL" is unique to PostgreSQL, but it sounds like just common sense to keep at least some sort of "tabs" on what queries have been executed in order to account for power losses and software crashes. Just blindly writing to the database file each time but not even keep any
Solution
You must have missed the answer while reading documentation:
Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. The log file is written sequentially, and so the cost of syncing the log is much less than the cost of flushing the data pages. This is especially true for servers handling many small transactions touching different parts of the data store. Furthermore, when the server is processing many small concurrent transactions, one fsync of the log file may suffice to commit many transactions.
Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. The log file is written sequentially, and so the cost of syncing the log is much less than the cost of flushing the data pages. This is especially true for servers handling many small transactions touching different parts of the data store. Furthermore, when the server is processing many small concurrent transactions, one fsync of the log file may suffice to commit many transactions.
Context
StackExchange Database Administrators Q#274729, answer score: 7
Revisions (0)
No revisions yet.