Write-Ahead Logging

Every persistent database must ensure dependability. Once the modifications are committed, they should endure any reboots, OS crashes, or hardware failures, regardless of how significant or minute they may be. The write-ahead logging mechanism is used by all persistent databases to ensure this reliability without impacting performance.

For any database, dependability is critical, which implies that the database does everything possible to ensure the success of any transaction. MYSQL and MS SQL are two common examples.

When we Commit anything to database.

Data/updates are kept on disc (non-volatile storage), making us immune to errors.

1. Power outage.
2. OS Error.
3. Failure of hardware.

Let's assume with below diagram.

Actual disk writes are a fairly sophisticated operation, we will try to imagine the steps involved before data is actually written on disk using the picture below.

Now we'll get to the hero of this edition: the Write Ahead Log. In general, this is a conventional approach to assure data integrity.

The basic idea behind write ahead log is that before making changes to actual data files such as tables and indexes, these changes should be logged somewhere and described.

WAL flow will be like- once update or insert has been triggered.

1. Log the entry into WAL file.
2. Make changes into table and indexes.

Advantages of WAL

1. We don't have to flush the data updates after each commit.
2. We can recover from a crash by replaying the WAL log files.
3. Reduces the # of disk writes.

Because write operations in WAL files are sequential, the cost of logging the change is substantially lower than in data files.

• WAL log- 1 disk write.
• data file- 1. Update table 2. update indexes 3. tree re-balance.

Point-in-time recovery or snapshot is possible with WAL file, with little bit if time travelling and few rows of data loss.

Data Integrity in WAL.

This is the major advantages let's discuss how..?

WAL protects each individual record with CRC-32(Cyclic Redundancy Check). That is, we can tell if the record content is correct by CRC checking it during crash recovery and replication.

WAL is by default enabled by all the databases vendors.

WAL file structure.

Bonus Points:

Why SELECT * is slow

1. No Index-only scan.
2. Database Deserialisations.
3. Not all columns are inline
4. Network cost.
5. Client Deserialisations.

That's the wrap buddy 💡.