What is a Transaction Log? WAL, Binlog, and Oplog Explained

A transaction log is an ordered, durable record of every change made to a database. It's the mechanism that makes crash recovery, replication, and Change Data Capture possible.

Every major production database keeps one. PostgreSQL calls it the WAL. MySQL calls it the binlog. MongoDB calls it the oplog. But these three logs are not the same thing. They differ in what they record, when they record it, and what role they play in the database's architecture.

This article is the reference guide. If you're looking for the broader picture of what you can do with these logs, start with What is Transaction Log Processing.

---

Two Kinds of Database Logs

Before looking at each database individually, it helps to understand that these logs fall into two categories with different jobs.

Crash-recovery logs implement the write-ahead pattern: record every change before it's applied to the data files, so the database can replay the log and recover after a crash. PostgreSQL's WAL, MySQL's InnoDB redo log, and MongoDB's WiredTiger journal all serve this purpose.

Replication logs record committed changes so they can be sent to other servers or consumed by external systems. MySQL's binlog and MongoDB's oplog serve this purpose.

PostgreSQL is the exception that makes this distinction easy to miss. Its WAL handles both roles: crash recovery and replication (physical and logical). MySQL and MongoDB split these jobs across two separate logs. This matters because the log you read for CDC is not always the log that handles crash recovery.

---

PostgreSQL: The Write-Ahead Log (WAL)

The WAL is PostgreSQL's single log for both crash recovery and replication. Every change is recorded in the WAL before being applied to the data files. The WAL lives in the pg_wal directory as a series of 16MB segment files (configurable at initialization via --wal-segsize).

Position Tracking: LSN

PostgreSQL tracks position in the WAL using Log Sequence Numbers (LSN). An LSN is a 64-bit integer representing a byte offset in the WAL stream, displayed as two hexadecimal values separated by a slash:

SELECT pg_current_wal_lsn();
-- Result: 16/B374D848

You can subtract two LSNs to get the number of bytes between them. This is how monitoring tools calculate replication lag in bytes.

WAL Levels

The wal_level setting controls how much information the WAL contains:

• minimal: Only what's needed for crash recovery. No replication, no archiving. The least WAL volume.
• replica (default): Adds enough for physical replication and WAL archiving. Supports read replicas and point-in-time recovery.
• logical: Adds row-level change information needed for logical decoding. Required for CDC. Increases WAL volume, especially with many UPDATEs and DELETEs on tables using REPLICA IDENTITY FULL.

Each level includes everything from the levels below it.

CDC Access

To read the WAL for CDC, set wal_level = logical, create a replication slot and a publication, then connect using the logical replication protocol. The built-in pgoutput output plugin decodes WAL records into structured messages (Begin, Relation, Insert, Update, Delete, Commit, among others).

Key configuration for CDC:

wal_level = logical
max_replication_slots = 4    # at least 1 per CDC consumer
max_wal_senders = 4          # at least 1 per replication connection

---

MySQL: The Binary Log (Binlog)

The binlog is MySQL's replication and point-in-time recovery log. It is not the crash-recovery mechanism. That job belongs to InnoDB's redo log, which implements the write-ahead pattern at the storage engine level.

The binlog records changes at commit time at the MySQL server level (above the storage engine). This is what makes it readable by external consumers: it contains logical change records, not physical page-level mutations.

Position Tracking: File Offset and GTID

MySQL offers two position tracking methods:

File-based positioning uses the binlog filename and a byte offset within it. For example, mysql-bin.000003 at position 4578. Simple, but fragile across server changes.

GTID (Global Transaction Identifier) assigns each transaction a globally unique ID in the format server_uuid:transaction_number. For example:

7d73b822-75e1-11ef-a4da-4455e16762b4:553

GTIDs survive server failovers and topology changes. Every modern CDC setup should use GTID-based positioning.

Row Format and Row Image

For CDC, MySQL must be configured with row-based binary logging. As of MySQL 8.0.34, binlog_format is deprecated and row-based will become the only format.

The binlog_row_image setting controls how much of each row is recorded:

• FULL (default): All columns in both before and after images. Required by most CDC tools (Debezium, Maxwell) for complete change capture.
• MINIMAL: Only the columns needed to identify the row (before image) and the columns that changed (after image). Smaller logs, but insufficient for CDC tools that need the full row state.
• NOBLOB: Same as FULL but excludes unchanged BLOB/TEXT columns. A practical middle ground for schemas with large binary fields.

CDC Access

CDC tools connect as a replication client, request the binlog in row-based format with binlog_row_image = FULL, and parse the binary events into structured change records.

---

MongoDB: The Operation Log (Oplog)

The oplog is MongoDB's replication log. Like the binlog, it is not the crash-recovery mechanism. Crash recovery is handled by the WiredTiger journal, which records every write between checkpoints (taken every 60 seconds) and replays them if the process crashes.

The oplog is a capped collection in the local database (local.oplog.rs), sized by default to 5% of free disk space (capped at 50 GB). Operations are recorded as idempotent BSON documents, meaning replaying them produces the same result regardless of how many times they're applied.

Position Tracking: Timestamps

Each oplog entry carries a ts field of type Timestamp: two 32-bit integers representing seconds since epoch and an increment to disambiguate entries within the same second. To resume reading, you store the last processed ts and query for entries after it.

Change Streams vs. Oplog Tailing

There are two ways to read the oplog for CDC:

Change Streams are the recommended API. They output clean, structured JSON documents, handle cluster elections gracefully, use standard RBAC permissions, and can be scoped to a specific collection, database, or the full deployment. Each event includes a resume token that allows the consumer to restart the stream from the exact point it left off, even after a disconnect or failover.

Direct oplog tailing reads the raw oplog entries. This gives access to lower-level operation details, but requires admin access, manual handling of primary elections, and parsing of raw oplog format. It's useful for environments where change streams have limitations, but for most CDC use cases, change streams are the better choice.

One critical requirement: the oplog only exists on replica sets and sharded clusters. A standalone MongoDB instance has no oplog (though it still recovers from crashes via the journal), so there's nothing to read for CDC.

---

Comparison

---

Why This Matters for CDC

The transaction log is the most reliable source of what changed in your database. Reading it is non-intrusive (no queries against the source database, though it does require resources like replication slots or oplog access that need monitoring). The log captures every change, including deletes, in commit order.

But which log you read, how you connect to it, and what configuration it requires varies by database. Understanding these differences is the first step toward building a reliable CDC pipeline.

For the full picture of what you can do with these logs, see What is Transaction Log Processing. For a deep dive into PostgreSQL's WAL specifically, see PostgreSQL WAL: How It Works.

We built Remac to handle these differences under a single interface. Remac currently supports PostgreSQL, with MySQL, MongoDB, SQL Server, and Oracle on the roadmap. Take a look at remac.io.

---

Further Reading:

• PostgreSQL WAL Internals. Official documentation on WAL segment structure and internals.
• PostgreSQL Logical Replication Protocol. Protocol specification for the pgoutput output plugin.
• MySQL GTID Format and Storage. Official documentation on Global Transaction Identifiers.
• MySQL Binary Logging Options. Configuration reference for binlog format and row image settings.
• MongoDB Replica Set Oplog. Official documentation on oplog structure, sizing, and behavior.
• MongoDB Change Streams. Official documentation on the recommended API for reading changes from MongoDB.
• MongoDB Journaling. Official documentation on WiredTiger's crash-recovery mechanism.