Oracle Redo Logs Postgres WAL and MySQL Binlog in One Managed CDC Pipeline
Unify Oracle, Postgres, and MySQL change logs into one consistent data stream.
Fan-in CDC replication from multiple operational databases into a single warehouse uses engine-specific log readers feeding a shared normalization and delivery layer. Oracle redo logs, Postgres WAL, and MySQL binlog can supply one consistent stream of row changes, but reliable consolidation also requires retention monitoring, schema reconciliation, and idempotent warehouse writes. A managed pipeline abstracts those engine differences while leaving source access, schema governance, and destination design with the data team.
Why all three database engines write changes to a log before anything else
Durable logs let a database preserve changes without relying on an immediate update to every affected data file. Oracle and Postgres use write-ahead logging, while MySQL exposes changes for replication through its binlog. Recovery and replication provide the foundation for CDC. A log-based consumer reads the change history instead of querying application tables over and over, so ongoing capture needs no full table scans or reads against live rows. Initial snapshots remain a separate operation.
The useful common ground emerges after decoding. Each engine can yield row-level INSERT, UPDATE, and DELETE events with before/after images, transaction identifiers, and ordering markers, provided its logging configuration supplies the necessary information. Oracle uses an SCN, Postgres an LSN, and MySQL a binlog position. These markers differ, but the events describe equivalent operations. That similarity gives a managed pipeline a logical structure it can normalize into one stream.
How Oracle CDC works: redo logs, supplemental logging, and the LogMiner vs. XStream choice
Oracle has the most consequential setup choices of the three engines. Its Log Writer process flushes redo entries, including committed and uncommitted changes, from the redo buffer to physical files, persisting committed changes on commit. In ARCHIVELOG mode, filled files are archived for continued mining. The System Change Number is the ordering marker. Enabling ARCHIVELOG on a production database takes coordination over maintenance as well as a connector setting.
Recovery information alone is not enough for CDC. Minimal supplemental logging, enabled with ALTER DATABASE ADD SUPPLEMENTAL LOG DATA, supplies the required baseline. Tables without a clean primary key need all-column supplemental logging to capture full before/after images for UPDATE and DELETE events, using ALTER TABLE … ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS. When supplemental data is missing, downstream updates can be incomplete even if the job shows no clear failure.
The capture mechanism then determines where processing happens. Built-in LogMiner exposes decoded results on demand through V$LOGMNR_CONTENTS, with parsing work on Oracle’s SQL engine and commit-and-poll-driven latency. With Oracle-buffered capture, the database does more of the transaction processing, while consumer-buffered capture puts transaction assembly and state in the connector. A Databricks connector uses LogMiner in uncommitted transaction mode. This is a concrete case of a managed integration making the capture choice for the team.
XStream, introduced in Oracle 11gR2 and built on Streams infrastructure, sends committed, structured row changes through an outbound server with low latency and full transaction context, but it requires a GoldenGate license. Direct redo parsing instead bypasses LogMiner and can read from a standby, trading reduced production impact for compatibility work as Oracle internals change. Neither approach removes the retention requirement. If RMAN deletes archive files before capture reads them, the missing history cannot be recovered from those files.
How Postgres CDC works: logical replication, replication slots, and the WAL retention trap
Postgres presents a cleaner capture interface through logical replication. CDC requires wal_level=logical, which provides the information needed for logical decoding. A replication slot then protects WAL segments the consumer still needs. That protection creates its own operational hazard. When a consumer stops progressing, retained WAL can accumulate until the disk fills and Postgres refuses writes, making slot-lag monitoring essential.
Failover introduces a separate, version-dependent correctness boundary. On Postgres 16 and earlier, logical replication slots are not preserved through failover without additional handling, potentially forcing a full re-snapshot when the consumer loses its position. Postgres 17 and later support failover continuity for logical slots created with the failover option. The distinction changes the deployment requirements. Earlier versions can use pg_failover_slots, but that extension is one more component to run.
Logical replication also has a structural blind spot. It emits row changes, including INSERT, UPDATE, DELETE, and TRUNCATE, but does not deliver ALTER TABLE as a corresponding schema-change event. The source schema can then change without the warehouse being told about it. The next row may expose the mismatch. A correctly configured slot protects the change history, but it does not reconcile the structure of the data being delivered.
How MySQL CDC works: binlog format, server ID, and the retention window that cannot be recovered
MySQL has the most straightforward source configuration of the three, but the binlog format must be explicit. CDC needs binlog_format=ROW so events hold the changed row values. STATEMENT does not provide those row images, while MIXED switches formats according to the statement. Full images are also important for replay correctness. ClickHouse’s ClickPipes connector requires binlog_row_image='FULL' because MINIMAL and NOBLOB prevent idempotent update replay and safe primary-key rewrite handling.
Retention is the sharper production risk. binlog_expire_logs_seconds, or its older equivalent, purges files on a timer whether a delayed CDC consumer has read them or not. Once a required file expires, the missing change history is gone. Recovery requires a full re-snapshot. The retention window must last well beyond the longest downstream outage or maintenance window that could realistically occur.
A read replica can absorb the CDC connection instead of the primary, but it needs binlogging enabled independently and its own unique server_id. Cloning a replica without changing that identifier can cause replication conflicts. The operational pattern now spans all three engines. Oracle and MySQL can delete unread history, while Postgres slots preserve it at the risk of exhausting storage. For each engine, the team must manage how far the consumer falls behind as the log grows.
What actually differs across the three engines: configuration surface, not core problem
Oracle exposes supplemental logging, archive configuration, and capture APIs with different licensing and processing costs. Postgres exposes logical decoding, slot retention, and version-specific failover behavior. MySQL exposes row format, row-image completeness, server identity, and timed expiry. These controls differ because the engines expose their logs differently.
After decoding, the pipeline handles an operation type, table identifier, row images, and a source ordering marker. A unified offset abstraction can hide SCNs, LSNs, and binlog positions from downstream consumers without making destination code interpret each engine’s internal markers. Retention monitoring can also show how far the consumer has read and how much history is left in the same terms, even though it measures different things at each source.
A buildup in a Postgres slot needs a different response from a MySQL retention window that is about to expire, even if both show up as lag alerts. A managed layer can centralize that knowledge and apply engine-specific handling behind a shared interface. Its value comes from preserving those distinctions internally while removing them from routine downstream work.
Schema evolution: the failure that surfaces after the pipeline is running, not during setup
Correct log configuration establishes capture, but source schemas keep changing. Postgres logical replication does not emit the DDL needed to explain an altered row structure. Oracle LogMiner can encounter dictionary drift when an extracted offline dictionary no longer matches the redo being decoded. MySQL records DDL as statement-based events. Full row images alone do not describe the schema.
These differences converge at the warehouse schema boundary. An unreconciled source change can cause the pipeline to reject subsequent rows or deliver data incompatible with the destination’s expectations. Queries and dashboards can then break despite uninterrupted transport. Recovery may require manual warehouse DDL or a pipeline restart and re-snapshot. Automatic propagation of compatible changes addresses an ongoing correctness problem.
Disciplined schema governance substantially reduces this exposure. Forward-compatible DDL, schema registries, and additive-only change policies can prevent many disruptive transitions before the pipeline encounters them. They do not eliminate the residual risk. A handling strategy is still necessary for changes that escape those controls, including how the destination schema is reconciled. Delivering every event exactly once cannot compensate for interpreting it against the wrong structure.
Exactly-once delivery: why it is a sink problem, not a log feature
If a consumer crashes after reading events but before durably committing its offset, it can read those same events again after restart. The log preserves the records, leaving correctness of warehouse application to the sink. Replay is therefore a normal recovery condition. The pipeline must make repeated delivery safe.
The practical architecture combines at-least-once capture with an idempotent sink. Destination writes use primary keys or row hashes to find and deduplicate repeated events so they have exactly-once effects at the application level. Durable offset tracking ties how far the consumer has read to that write logic. Partial commits make the coordination especially important. If only part of a batch reaches the warehouse before a crash, recovery must detect and reapply it without duplicating rows already committed.
Schema changes complicate that recovery path further. An event replayed after the warehouse schema changes may not apply idempotently under the new structure, so deduplication must be schema-aware. This requirement holds whether the destination is Snowflake, BigQuery, Redshift, Databricks, or ClickHouse. Exactly-once semantics belong to the complete source-to-sink design, with the hardest reconciliation work at the destination.
What a managed CDC pipeline abstracts and what it does not
A well-built managed pipeline handles validation, monitoring, and recovery for each source. It checks supplemental logging, WAL level, and binlog settings, exposing unsupported or incomplete configurations as explicit errors. It tracks retention headroom and consumer lag before the history is gone. Postgres requires slot continuity, MySQL requires position tracking through replica promotion, and Oracle requires archive-sequence handling across restarts.
The shared layer also owns destination correctness. Catalog polling can detect schema changes when the capture stream does not provide sufficient DDL information, allowing compatible changes to propagate without restarting capture. Idempotent writes, durable offsets, and partial-commit recovery belong in the same managed design. These capabilities are concrete evaluation criteria. Support for an engine name alone says little about how a service behaves during failover, schema changes, or replay.
Management does not confer control over every dependency. Enabling Oracle ARCHIVELOG, changing Postgres WAL level, or adjusting MySQL binlog format still requires database access and coordination, sometimes through a maintenance window. The service can validate prerequisites without being authorized to change them. Source-side schema governance also remains with the organization. A pipeline can handle a change after it occurs, but it cannot prevent poorly coordinated DDL from temporarily disrupting downstream consumers.
How do you consolidate five production databases into one Snowflake warehouse?
Consolidating five production databases uses the same fan-in pattern, but destination naming becomes a team decision. An Oracle orders table and a MySQL orders table must share a destination name only by choice. The team must define the namespace deliberately. Options include separate schemas per source, a merged schema with a source-identifier column, or materialized views that present a unified view.
Initial snapshots create the next correctness boundary. Large Oracle or MySQL tables can take hours to capture, and the snapshot must align with the redo or binlog position where streaming begins. Otherwise, changes can fall between the baseline and ongoing replication. That gap leaves a permanently inconsistent starting state. Snapshot planning therefore concerns completeness as well as load and duration.
Lag in seconds or rows, retention headroom, and replication-slot health make heterogeneous sources understandable without constant translation between engine markers. Data handling remains a separate responsibility. For PII or regulated data, the replication layer should process events in flight without storing customer data at rest, especially when HIPAA or SOC 2 in-scope sources cross network or cloud boundaries.
The practical acceptance test follows from those boundaries. On an already configured source, the team should be able to add a table from any of the three engines and see changes in the warehouse within minutes, without changing log settings, writing consumer code, or restarting an existing pipeline. That is the operational payoff of abstraction. Engine-specific capture becomes a managed dependency, while the team retains control of what the consolidated data means.
