Modern Data Ingestion: Batch Processing vs. Change Data Capture (CDC)

1. Executive Summary & Core Conflict

For decades, the standard pipeline for synchronizing operational OLTP datastores with downstream data warehouses (Snowflake, BigQuery) and search engines (Elasticsearch) was the nightly Batch ETL job. Every midnight, scheduled orchestrators execute heavyweight SQL queries (SELECT * FROM orders WHERE updated_at >= NOW() - INTERVAL '24 HOURS'), extract modified rows into CSV or Parquet files, and load them into downstream analytical lakes.

In modern enterprise platforms, this periodic polling model breaks down completely.

Batch query extraction imposes severe, predictable failure modes: it hammers production database buffer pools during extraction windows, misses intermediate state transitions that occur between intervals, struggles to detect hard row deletions, and forces businesses to tolerate 24-hour data staleness.

The modern successor is Log-Based Change Data Capture (CDC). Instead of querying SQL tables through the relational planner, CDC engines (such as Debezium) tap directly into the database engine's low-level binary replication stream (PostgreSQL Write-Ahead Log, MySQL Binary Log). Every insert, update, and delete is converted into an asynchronous event stream with sub-second latency.

However, CDC is not an effortless silver bullet. The core conflict between Batch and CDC centers on the trade-off between operational simplicity and architectural coupling. Batch polling treats the database as a black-box SQL endpoint, while log-based CDC deeply binds infrastructure to database storage internals, creating vulnerabilities such as replication slot disk exhaustion, schema drift failures, and out-of-order event streams.

2. Deep Technical Breakdown

Batch Extraction: Query-Based Polling Mechanics

Query-based batch ingestion relies on polling database tables using tracking columns:

SELECT * FROM customers 
WHERE updated_at > :last_sync_timestamp 
ORDER BY updated_at ASC 
LIMIT 10000;

Why Query-Based Polling Fails at Scale:

1. Missed Intermediary States: If an order transitions from PENDING to PROCESSING to CANCELLED between two 1-hour polling intervals, the batch query only observes the final CANCELLED state. Business metrics requiring duration-in-state or historical lifecycle tracking are permanently lost.
2. Invisible Hard Deletes: If an application executes DELETE FROM customers WHERE id = 42;, no row remains to carry an updated_at timestamp. The downstream warehouse continues serving the deleted record indefinitely unless costly full-table diffs are executed.
3. Database Buffer Pool Eviction: Running a sequential table scan across 100 million rows to extract changes flushes hot application working sets out of RAM buffer caches (e.g., PostgreSQL shared_buffers), degrading live OLTP transaction p99 latencies for hours.

Log-Based Change Data Capture (CDC)

Log-based CDC bypasses the SQL execution engine entirely. In relational systems, every mutation is synchronously written to a sequential Write-Ahead Log (WAL) on disk before it is applied to table pages, guaranteeing durability.

+-------------------------------------------------------------------------+
| PRIMARY RELATIONAL DATABASE (PostgreSQL)                                |
|                                                                         |
|  [ Client UPDATE ] ---> [ Write-Ahead Log (WAL) ] ---> [ Table Heap ]  |
|                                |                                        |
|                         Logical Decoding                                |
|                         (pgoutput plugin)                               |
+--------------------------------|----------------------------------------+
                                 | Replication Stream
                                 v
+-------------------------------------------------------------------------+
| DEBEZIUM / KAFKA CONNECT ENGINE                                         |
|                                                                         |
|  - Reads WAL LSN bytes                                                  |
|  - Reconstructs {before, after, op, timestamp, transaction_id}          |
|  - Commits confirmed LSN offset back to replication slot                |
+--------------------------------|----------------------------------------+
                                 |
                                 v
+-------------------------------------------------------------------------+
| APACHE KAFKA CLUSTER (Topic: dbserver1.inventory.orders)                |
+-------------------------------------------------------------------------+

The PostgreSQL Logical Decoding Protocol

1. PostgreSQL writes internal row changes to the WAL as binary log sequence numbers (LSN).
2. A Logical Replication Slot is registered on the primary node. A decoding plugin (e.g., pgoutput) reads the binary WAL stream and converts physical tuple deltas into logical change events.
3. The CDC connector (Debezium) continuously streams these events over the replication protocol:

{
  "before": { "id": 101, "status": "PENDING", "amount": 49.99 },
  "after":  { "id": 101, "status": "SHIPPED", "amount": 49.99 },
  "source": { "version": "2.4", "connector": "postgresql", "ts_ms": 1725800000 },
  "op": "u",
  "ts_ms": 1725800005
}

Because WAL reading is sequential disk I/O, CDC introduces near-zero overhead on production table indexes and leaves RAM buffer pools untouched.

The Initial Snapshot Dilemma: Watermark Reconciliation

When deploying CDC onto an existing database with 500 million pre-existing rows, the connector must establish an initial baseline without blocking production writes.

Modern CDC engines employ Incremental Snapshotting with Signaling Tables:

1. The connector pauses streaming, records the current low watermark LSN, and begins chunking table reads using primary key ranges (WHERE id BETWEEN 0 AND 10000).
2. Concurrently, live application writes continue generating WAL events.
3. The connector interleaves snapshot chunks with live WAL streaming, using the primary key to reconcile collisions: any WAL mutation with a higher timestamp supersedes snapshot data.

3. Production Edge Cases & Failure Modes

Edge Case 1: Replication Slot Invalidation and Disk Exhaustion

In PostgreSQL, a logical replication slot guarantees that WAL files will never be recycled or pruned by the database until the registered consumer confirms it has read and processed them.

If the downstream Kafka Connect cluster crashes, or network partitions disconnect the CDC connector for 12 hours, PostgreSQL accumulates gigabytes of un-pruned WAL files in pg_wal. When the disk volume reaches 100% capacity, PostgreSQL immediately initiates emergency shutdown, crashing the production database.

Mitigation: Always configure max_slot_wal_keep_size (e.g., 50GB). If the connector lags beyond this limit, PostgreSQL automatically invalidates the slot and resumes pruning, saving the database from an emergency disk crash at the expense of requiring a connector re-snapshot.

Edge Case 2: Out-of-Order Delivery in Partitioned Streams

If CDC events are routed to an Apache Kafka topic, and the partition key is misconfigured or null, events for a single row (INSERT, UPDATE, DELETE) land on different topic partitions. Because ordering is only guaranteed within a partition, a downstream consumer may process the UPDATE before the INSERT, or re-insert a deleted row.

Mitigation: Enforce deterministic partitioning using the source table's primary key (e.g., customer_id) as the Kafka message key.

Edge Case 3: Schema Drift and Serialization Deserialization Traps

When a developer executes ALTER TABLE orders DROP COLUMN legacy_discount;, the WAL immediately begins outputting records without that column. If downstream Kafka consumers rely on static Avro or Protobuf schemas that have not been registered in the Schema Registry, downstream ingestion pipelines crash instantly on deserialization errors.

Mitigation: Implement strict Schema Registry compatibility rules (BACKWARD_TRANSITIVE) and mandate non-breaking migrations (add columns with defaults; deprecate before dropping).

4. Comparative Markdown Table

5. Pragmatic Decision Framework / Checklist

Follow this architectural checklist when selecting an ingestion strategy:

Choose Batch Processing when:

• Business consumers only require daily or weekly aggregated reporting (e.g., financial ledger consolidation).
• The source datastore is a third-party SaaS API that does not expose binary transaction logs.
• Source tables are small (< 500,000 rows) and indexed table scans complete in seconds without impacting production workloads.

Choose Log-Based CDC when:

• Real-time data freshness (< 5 seconds) is mandatory for downstream search indexes, fraud detection, or event-driven microservices.
• The architecture requires capturing every transient state transition and hard deletion.
• Production database query load must remain strictly isolated from analytical extraction.

Operational CDC Guardrails:

• Configure max_slot_wal_keep_size on PostgreSQL to prevent unmonitored connector outages from filling server disks.
• Implement automated alerts on Replication Slot Lag Bytes (pg_replication_slots.wal_status). Alert when lag exceeds 10 GB.
• Ensure all CDC-enabled tables possess an immutable, explicit Primary Key to guarantee deterministic stream partitioning.
• Configure automated schema management with Confluent Schema Registry or AWS Glue to prevent unexpected ALTER TABLE breaking changes.