CDC, hacking for performance

So far we looked at change data capture by traditional methods, but there is more power to be unlocked by paying the price in complexity.

What is write-ahead log?

When you write to the relational database, you do so in a unit of work called transaction. Each transaction encompasses a set of changes. Database guarantees that transactions are processed atomically - either all changes in transaction end up in the database, or none.

The transactional consistency guarantee is enforced by writing to the disk in several stages:

1) Description of what needs to be done is written to append-only file called write-ahead log, or WAL. Actually, WAL can consist of multiple files that roll over instead of just append, but let's keep things simple.

2) Changes are actually applied to the tables in question and indexes updated.

Why do we need WAL?

Updating tables and indexes potentially touch many disk pages and this can take a long time. The answer, is, of course, performance. It's faster to flush just WAL to disk immediately and flush other disk pages less often.

What does this have to do with change data replication?

If our process locates the WAL file and reads the changes in the file as soon as they are written, it will be served from disk cache and not have to touch the disk at all. By pushing description of changes via network to another process with access to another database, that other process can write the same transactions to the target database with as little as a few seconds delay.

It's a beautiful technique, but one needs to invest time to decode binary WAL format and thoroughly test it.