How does the database guarantee reliability using write-ahead logging?

2461 views Database Engineering

Any persistent database needs to guarantee the reliability, implying that any update/delete fired on the database is reliably stored on the disk. The alterations on the data should not be affected by power loss, OS failure, or hardware failure.

The changes to the data once committed should do to non-volatile storage like Disk making them outlive any outage or crash. Although the flows seem simple enough to say that- hey let’s just flush the changes to the disk; it is a little more complicated than that.

Disk writes are complicated

When we perform the disk write, the changes are not directly written to the disk sectors, instead, it goes through a variety of buffers like RAM, OS Cache, Disk Cache, and then finally to the Disk sectors. So, if the changes are in any of these intermediate caches and the process crashes, our changes are lost.

So, while guaranteeing reliability we have to skip all of these caches and flush our changes as quickly as possible on the disk sectors. But if we do that, we are impacting the throughput of the system given how expensive disk writes are.

Write-ahead Logging

Write-ahead logging is a standard way to ensure data integrity and reliability. Any changes made on the database are first logged in an append-only file called write-ahead Log or Commit Log. Then the actual blocks having the data (row, document) on the disk are updated.

If we update any row or a document of a database, updating the data on disk is a much slower process because it would require on-disk data structures to rebalance, indexes to be updated, and so much more. So, if we skip the OS cache, and other buffers, and directly update the rows to the disk every time; it will hamper the overall throughput and performance of the database.

Hence, instead of synchronously flushing the row updates to the disk, we synchronously just flush the operation (PUT, DEL) in a write-ahead log file leading to just one extra disk write. The step will guarantee reliability and durability as although we do not have the row changes not flushed but at least the operation is flushed. This way if we need to replay the changes, in case of a crash, we can simply iterate through this simple file of operations and recover the database.

Once the operation is logged, the database can do its routine work and make the changes to the actual row or document data through the OS cache and other buffers. This way we guarantee reliability and durability in as minimal of a time as possible while not affecting the throughput much.

Advantages of using WAL

The advantages of using WAL are

  • we can skip flushing the data to the disk on every update
  • significantly reduce the number of disk writes
  • we can recover the data in case of a data loss
  • we can have point-in-time snapshots

Data integrity in WAL

WAL also needs to ensure that any operation flushed in the log is not corrupted and hence it maintains its integrity using a CRC-32 and flushes it on the disk with every entry. This CRC is checked during reading the entry from the disk, if it does not match the DB throws an error.

Arpit Bhayani

Arpit's Newsletter

CS newsletter for the curious engineers

❤️ by 17000+ readers

If you like what you read subscribe you can always subscribe to my newsletter and get the post delivered straight to your inbox. I write essays on various engineering topics and share it through my weekly newsletter.

Other essays that you might like

What are Embedded Databases?

2349 views 101 likes 2022-03-25

Embedded databases are coupled with the application they are part of and operate in a confined space. They are designed ...

How does the database guarantee reliability using write-ahead logging?

2461 views 135 likes 2022-03-21

Any persistent database needs to guarantee reliability. No matter how big or small the changes are, they should survive ...

How do indexes make databases read faster?

4312 views 303 likes 2022-03-16

In this video, we discuss how indexes make a database operate faster. While discussing that, we dive deep into how the d...

How to handle database outages?

2427 views 160 likes 2022-03-14

In this video, we talk about why a database goes down, what happens when the database is down, a few short-term solution...

Be a better engineer

A set of courses designed to make you a better engineer and excel at your career; no-fluff, pure engineering.

System Design Masterclass

A masterclass that helps you become great at designing scalable, fault-tolerant, and highly available systems.

Enrolled by 700+ learners

Details →

Designing Microservices

A free course to help you understand Microservices and their high-level patterns in depth.

Enrolled by 17+ learners

Details →

GitHub Outage Dissections

A free course to help you learn core engineering from outages that happened at GitHub.

Enrolled by 67+ learners

Details →

Hash Table Internals

A free course to help you learn core engineering from outages that happened at GitHub.

Enrolled by 25+ learners

Details →

BitTorrent Internals

A free course to help you understand the algorithms and strategies that power P2P networks and BitTorrent.

Enrolled by 42+ learners

Details →

Topics I talk about

Being a passionate engineer, I love to talk about a wide range of topics, but these are my personal favourites.

Arpit's Newsletter read by 17000+ engineers

🔥 Thrice a week, in your inbox, an essay about system design, distributed systems, microservices, programming languages internals, or a deep dive on some super-clever algorithm, or just a few tips on building highly scalable distributed systems.

  • v12.4.4
  • © Arpit Bhayani, 2022

Powered by this tech stack.