Databases like PostgreSQL, MySQL, SQLite etc ensure reliability with the help of Write-ahead Logging. It is a standard way to ensure data integrity and reliability. In this post we will understand the beauty of Write-ahead Logging.
Let’s say a transaction begins.
INSERT INTO users (name, username, age) VALUES ('Mizanur Lahin', 'lahin31', 30);As we know, our data is stored on the disk. The database doesn’t execute the query directly on the disk. Storing something directly on to the disk is considered as costly since lots of random i/o required for multiple transactions. This way we cannot go Point in Time Recovery if anything bad happens to database. If we directly store on the disk then it can hamper performance and most importantly reliability.
Think that way before executing the query on disk it store the query in a log file, we call it Write-ahead Log. It sits on disk.
Firstly query stores in the log file. Then the query eventually goes to the disk in order to be processed on the disk sequentially. It is considered as performant than random i/o.
If everything is fine then no issue can occur. However, during the query pass from the Log File if something goes down or the database crashes, then what happens?
In that case our database can read Point in Time Failure from the Log File and will pass the query to disk again.
This is how databases ensure reliability.