I used to think a database transaction was simple.

If I run:

UPDATE users
SET balance = 10250
WHERE id = 73;

the database finds the row, changes the value on disk, and returns success.

That sounds reasonable. then??

What exactly has happened when the database says:

COMMIT successful

Is the updated row already safely stored inside the actual database file?

Surprisingly, it does not have to be.

The database can successfully commit a transaction even when the actual changed data page has not been written to its final location on disk yet.

At first, that sounds unsafe.

If the data page is still waiting to be written, what happens if the machine crashes?

That question leads to one of the most important ideas in database storage:

Write-Ahead Logging, or WAL.

First, the database is not constantly editing rows on disk

When we think about a database, it is easy to imagine like this:

SQL UPDATE
    ↓
Find row on disk
    ↓
Change row on disk
    ↓
Done

But databases usually do not work directly with individual rows on disk for every operation.

Storage is organized into pages.

A page is a fixed-size block containing database data.

When the database needs some data, the relevant page is usually loaded into memory.

So our update looks more like this:

Disk
  ↓
Page loaded into memory
  ↓
Row changed inside that page

Once the page has been changed in memory but has not yet been written back to disk, it is called a dirty page.

For example, disk may still contain:

user_id = 73
balance = 8750

After our update, memory may contain:

Dirty page in memory:
 
user_id = 73
balance = 10250

But the data page on disk could still contain:

user_id = 73
balance = 8750

Eventually, the database will flush the dirty page back to disk.

But here comes the interesting problem.

What if the database crashes before that happens?

The crash problem

Imagine we transfer ₹1,500 between two accounts.

Account A: ₹12,400 → ₹10,900
Account B: ₹6,300  → ₹7,800

This transaction changes more than one piece of data.

Now imagine the database starts writing those changed pages to disk.

It successfully writes:

Account A = ₹10,900

Then suddenly:

💥 CRASH

The second page was never written.

After restart, we could have:

Account A = ₹10,900
Account B = ₹6,300

₹1,500 has effectively disappeared.

This is exactly the kind of situation a database must protect against.

A transaction should not leave the database halfway between two valid states.

Why not just write every changed page before COMMIT?

There is an obvious solution.

Before returning:

COMMIT successful

the database could force every changed data page to durable storage.

Conceptually:

Transaction
    ↓
Update Page A
    ↓
Write Page A to disk
    ↓
Update Page B
    ↓
Write Page B to disk
    ↓
Update indexes
    ↓
Write those pages
    ↓
COMMIT

This could become expensive.

One transaction may affect several pages.

Those pages may live in different places in storage.

Indexes may introduce even more pages that need to change.

More importantly, forcing all of them to durable storage as part of every commit tightly couples transaction latency to page flushing.

Databases want something better.

They want to be able to say:

"I can guarantee this transaction will survive a crash without forcing every changed data page to its final location right now."

That is where WAL comes in.

Write-Ahead Logging

The basic idea is simple.

Before a changed data page is allowed to reach durable storage, the database first makes sure the relevant log information is safely stored.

Instead of depending only on the data page, the database keeps another structure:

Write-Ahead Log

Suppose we execute:

UPDATE users
SET balance = 10250
WHERE id = 73;

The database changes the relevant page in memory.

It also creates a log record describing the change.

Conceptually, think of it as something like:

Transaction: 817
Page: 34
Change: balance 8750 → 10250

Real database WAL records are more complicated than this, but this is enough for the mental model.

Now the transaction can roughly look like this:

UPDATE
   ↓
Modify page in memory
   ↓
Create WAL record
   ↓
Flush required WAL to durable storage
   ↓
COMMIT succeeds
   ↓
Dirty data page can be flushed later

Notice something important.

The actual table page does not necessarily need to be durable when the transaction is acknowledged.

The required WAL records do.

That is the key idea.

Why is the log written first?

Because memory disappears during a crash.

Suppose the database currently has this:

Memory:
 
user_id = 73
balance = 10250

while the data page on disk still has:

Disk:
 
user_id = 73
balance = 8750

If the machine loses power, the value in memory disappears.

Normally, our update would be lost.

But suppose the WAL already contains durable information about that committed change.

WAL:
 
Transaction: 817
Page: 34
Change: balance 8750 → 10250
COMMIT

After restarting, the database can inspect the log and determine that this committed change needs to exist.

It can then restore the required state.

So instead of requiring the final data page to be durable immediately, the database first makes the information needed to recover it durable.

That is a much better way to think about WAL.

What does COMMIT really mean?

This was the part that changed my mental model.

I originally thought:

COMMIT successful

meant:

Every changed table page
has reached its final location on disk.

That is not necessarily true.

A better mental model is:

COMMIT successful
        ↓
The database has made enough
information durable to guarantee
that this committed transaction
can survive a crash.

The data pages themselves may still be dirty in memory.

They can be written later.

The important ordering rule

There is one detail here that matters.

It is tempting to describe WAL as:

"The database writes the log before changing the data."

That is slightly misleading.

The database may already change a page in memory.

For our example:

Memory:
 
balance = 10250

can exist while the WAL record is still being prepared or flushed.

The important rule is about what reaches persistent storage first.

Conceptually:

WAL record
    ↓
must become durable first
    ↓
corresponding dirty page
can later be written to disk

The database must not allow the persistent data page to get ahead of the log information required to recover it.

This ordering is what makes it a Write-Ahead Log.

The log gets ahead of the persistent data pages.

Why not immediately write the dirty page too?

Because there is usually no need.

Imagine several transactions modifying the same page.

Transaction 817
      ↓
Transaction 818
      ↓
Transaction 819
      ↓
Transaction 820
      ↓
same dirty page in memory
      ↓
eventually flush page

Instead of forcing that page to disk after every small modification, the database can keep working with it in memory.

The WAL protects durability while the database gets flexibility over when dirty pages are written.

This separation is powerful.

Transaction durability and data-page flushing no longer have to happen at exactly the same time.

WAL also has a useful I/O property

There is another benefit.

A WAL is generally append-oriented.

New records keep being added to the log.

Conceptually:

WAL
 
[817][818][819][820][821][822] →

The database does not need to immediately jump between many different data pages just to make every transaction durable.

Instead, it can first append the required log information.

Historically, sequential I/O has been much friendlier to storage than scattered random writes, especially on hard disks.

Modern SSDs change some of the performance details, but the architecture still matters.

WAL gives databases a compact, ordered durability path instead of requiring every modified page to be flushed during commit.

What happens after COMMIT?

Suppose transaction 817 has committed.

We could now have:

WAL:
Transaction 817
balance = 10250        ✓ durable
 
Memory:
balance = 10250        ✓ latest value
 
Data page on disk:
balance = 8750         ← old value

At first glance, this looks wrong.

Three places contain information about the same value, and one of them is old.

But this is not automatically a problem.

The database knows the committed state can be recovered because the required WAL information is durable.

Later, a background process can flush the dirty page.

After that:

WAL:
Transaction 817
balance = 10250
 
Memory:
balance = 10250
 
Data page on disk:
balance = 10250

Now the main data file has caught up.

What if the database crashes before the page is flushed?

This is where WAL becomes useful.

Suppose the state is:

WAL:
 
Transaction 817
balance = 10250
COMMIT                 ✓ durable

But the data page still contains:

Data page:
 
balance = 8750

Then:

💥 POWER FAILURE

Everything that existed only in memory is gone.

The database restarts.

It sees that the data files may not contain every committed change.

But it still has the WAL.

So during recovery, it can use the log to restore changes that must exist.

Conceptually:

Database starts
      ↓
Read recovery information
      ↓
Inspect WAL
      ↓
Find required committed changes
      ↓
Replay them when necessary
      ↓
Database reaches a consistent state

For our example, recovery can restore:

balance = 10250

even though the data page contained:

balance = 8750

when the machine crashed.

This process is often called redo.

What about transactions that never committed?

Now consider another transaction.

Transaction 818 starts after our previous transaction.

It tries to change:

balance = 10250

to:

balance = 14600

The database may create WAL records related to that work.

But before transaction 818 successfully commits:

💥 CRASH

Should 14600 appear after restart?

No.

The transaction never successfully committed.

The database cannot simply assume:

"If it exists somewhere in the WAL,
it must be valid."

Recovery logic needs to understand transaction state.

Conceptually:

Transaction 817 → COMMITTED
Transaction 818 → NOT COMMITTED

The exact recovery mechanism depends on the database engine.

Some systems use redo and undo information.

Others combine WAL with transaction metadata and other storage structures.

The important point is that WAL should not be reduced to:

Just replay everything.

Recovery has to know which work belongs in the final database state.

Then why doesn't the WAL grow forever?

Now we have another problem.

Imagine the database has been running for months.

The WAL could look like:

[817][818][819] ... [58201][58202][58203] ...

If the database crashes, should recovery start from the very first log record ever created?

That would be terrible.

This is where checkpoints become important.

A checkpoint roughly gives the recovery system a useful point from which it can reason about recovery.

Conceptually:

WAL
 
old records
    ↓
old records
    ↓
CHECKPOINT
    ↓
newer records
    ↓
newer records

Or:

|---------- WAL ----------|
 
[old history]
 
      [CHECKPOINT]
 
            [recent changes]

The exact checkpoint behavior differs between database engines.

But the general idea is simple:

recovery should not need to process the entire history of the database every time it starts.

Checkpoints help limit recovery work and allow older log data to be managed appropriately.

WAL is not the same as a backup

This distinction is important.

WAL helps databases recover from situations such as:

process crash
power failure
unexpected restart

But imagine someone intentionally runs:

DROP TABLE users;

and commits it successfully.

From the database's point of view, that is a valid operation.

The WAL may faithfully record it.

WAL does not magically turn into a backup of the old table.

Similarly, imagine the entire storage device is destroyed.

If both the database files and the required local WAL disappear, local WAL alone cannot save the database.

That is why real systems may combine several ideas:

WAL
+
checkpoints
+
replication
+
backups

They may sound related because they all improve reliability.

But they solve different problems.

The full picture

Without WAL, we might imagine transaction durability like this:

Transaction
    ↓
Modify Page A
    ↓
Make Page A durable
    ↓
Modify Page B
    ↓
Make Page B durable
    ↓
Update indexes
    ↓
Make those pages durable
    ↓
COMMIT

That means commit has to wait for all the required page writes.

With WAL, the architecture can behave more like:

Transaction
    ↓
Modify pages in memory
    ↓
Generate WAL records
    ↓
Make required WAL durable
    ↓
COMMIT
    ↓
Return success
    ↓
Flush dirty pages later

That is the important separation.

The database separates:

Making the transaction durable

from:

Writing every changed data page
to its normal location

Those two things initially sound like the same operation.

They are not.

Why this is faster

At first, WAL sounds like extra work.

The database writes information to the log.

Then later it still has to write the actual data pages.

So technically, we are doing more writing.

Why can that be faster?

Because when and how those writes happen matters.

Without WAL, every transaction could require several unrelated data pages to become durable before commit.

With WAL, the critical path can instead focus on making the required log records durable.

Transaction
      ↓
small ordered WAL writes
      ↓
COMMIT

Then data-page writes can happen separately.

Dirty Page 34 ──┐
Dirty Page 51 ──┤
Dirty Page 82 ──┼──→ background flushing
Dirty Page 96 ──┘

The database gains freedom over when those pages are flushed.

That flexibility is a big part of the design.

One subtle thing about WAL

There is an interesting way to look at all of this.

The database has two possible goals.

The obvious goal would be:

"Before I tell the application that the transaction succeeded, I must make the final database files completely correct."

WAL changes the goal.

Instead, the database can say:

"Before I tell the application that the transaction succeeded, I must make sure I can reconstruct the correct state even if I crash."

Those statements sound similar.

But architecturally, they are very different.

The first requires the final state to be completely written immediately.

The second requires enough durable information to recover the final state.

That is the trick behind WAL.

Final mental model

Took away from understanding WAL is this:

A committed transaction does not necessarily mean every changed table page is already in its final place on disk.

Instead, it means the database has satisfied the durability requirements needed to guarantee that the committed transaction can survive a crash.

The actual data pages can catch up later.

So WAL is not simply:

write the same thing twice

A better way to think about it is:

Make recovery possible first.
 
Make the main data files catch up later.

The WAL protects the promise.

The data-page flush completes the physical update.

And that small separation is one of the ideas that makes modern databases both reliable and fast.