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

About Remac: Remac is a high-performance, database-agnostic transaction log processor. It turns native database transaction logs into governed data streams, replicas, audit records, recovery data, and replayable events.

Understanding what each native log records is the first step in understanding that work.

A database log is an ordered record that an engine uses for durability, recovery, replication, or another internal purpose. A suitable log and access method can also provide the source data for Change Data Capture.

PostgreSQL has WAL. MySQL uses a binary log for replication and an InnoDB redo log for crash recovery. MongoDB replica sets use an oplog, while WiredTiger uses a journal for recovery. These logs differ in content, timing, retention, access, and native purpose.

The sections below compare each log's role, position model, and CDC access path. For the broader processing category, start with What is Transaction Log Processing.

---

Two Kinds of Database Logs

Two broad jobs help explain the differences.

Crash-recovery logs implement the write-ahead pattern: record the recovery information before the related data-page writes reach durable storage. The database can then use the log to recover after a crash. PostgreSQL's WAL, MySQL's InnoDB redo log, and MongoDB's WiredTiger journal serve this purpose.

Replication logs record changes in a form used to propagate them to replicas or external consumers. Their exact relationship to transaction commit differs by engine. 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 main log for crash recovery and replication. For WAL-logged relations, PostgreSQL records the recovery information that a changed data page needs before that page can reach durable storage. Temporary and unlogged relations have different durability rules. The WAL lives in the pg_wal directory as a series of 16 MB segment files by default. The segment size is configurable at initialization with --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 the information that PostgreSQL needs for logical decoding. It is required for the PostgreSQL logical CDC path described here. It can increase WAL volume, and the actual increase depends on the workload and table settings.

Each level includes everything from the levels below it.

CDC Access

To use PostgreSQL's built-in pgoutput CDC path, 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).

The following capacity values are examples. Size them for the required slots and concurrent replication connections.

wal_level = logical
max_replication_slots = 4    # enough for the logical slots you require
max_wal_senders = 4          # enough for concurrent replication connections

---

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.

For transactional changes, MySQL accumulates binary-log events while the transaction executes and writes them to the binlog as part of the commit path. The binlog is a server-level logical log, distinct from InnoDB's redo log.

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 an identifier that is unique across the replication topology. Its usual form is server_uuid:transaction_number. For example:

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

GTIDs identify transactions independently of one binary-log filename and offset. This often makes them the preferred position model when a topology can change.

Row Format and Row Image

Row-level CDC normally uses row-based binary logging. MySQL 8.4 still documents row, statement, and mixed formats, with row format as the default. The binlog_format setting is deprecated, and MySQL recommends row-based logging for new replication setups.

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

• FULL (default): All columns in the applicable before and after images. This gives a consumer the most complete row image.
• 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 that need complete before and after row images commonly require row-based logging with binlog_row_image = FULL. A consumer with narrower data requirements can support a different row-image policy.

---

MongoDB: The Operation Log (Oplog)

The oplog is MongoDB's replication log. Like the binlog, it is not the crash-recovery mechanism. The WiredTiger journal is MongoDB's crash-recovery log. Since MongoDB 6.1, journaling is always enabled. WiredTiger uses it to recover changes after the last checkpoint.

The oplog is a capped collection in the local database (local.oplog.rs). On Unix and Windows, WiredTiger uses 5 percent of free disk space by default, subject to a 990 MB minimum and a 50 GB maximum. On 64-bit macOS, the documented default is 192 MB. Unlike an ordinary capped collection, the oplog can grow past its configured limit to avoid deleting the majority commit point. MongoDB records oplog operations so replica application produces the intended state when an operation is applied again.

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 provide a structured change API and can be scoped to a collection, database, or deployment. Each event includes a resume token. A consumer can use it to resume when the required oplog history and resume conditions remain available.

Direct oplog tailing reads raw oplog entries. It requires privileges, topology handling, resume logic, and parsing of MongoDB's internal replication format. Change streams provide the supported application-facing change interface.

Replica-set members have an oplog. In a sharded cluster, each replica-set shard has its own oplog; change streams provide the supported cluster-wide change interface and preserve total order across shards. A standalone MongoDB instance has no oplog, though it still recovers from crashes through the journal.

---

Comparison

---

Why This Matters for CDC

A native change log can provide deletes, positions, ordering, and transaction metadata that table polling often loses. The exact result depends on source configuration, selected tables and operations, row-image settings, retention, and the consuming protocol.

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.

Join the Remac waitlist for product updates.

External reference note: The external technical information and linked references in this article were current when we published it. External systems, documentation, and defaults can change after publication.

---

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 MySQL 8.4 documentation on Global Transaction Identifiers.
• MySQL Binary Logging Options. MySQL 8.4 configuration reference for binary-log 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.