PostgreSQL works like this, if I have understood things correctly:
- 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 kind of log sounds like a recipe for disaster, and I cannot imagine that any database ever has done this. Imagine if somebody makes a transaction of a million dollars right when the power is cut and then they lost a million dollars from their account but the other person received no money! Not even a database software from the 1940s probably worked like that...