When Databases Do the Wrong Job: How Datadog Built a CDC Replication Platform on Kafka
How Datadog overcame 7-second database latency by building a Change Data Capture (CDC) platform using PostgreSQL WAL, Debezium, Kafka, and search engines.
On this page
In distributed software architecture, one of the most common and insidious performance traps occurs when an organization forces its primary transactional database to do a job it was never physically designed to perform.
At Datadog, the Metrics platform maintains an extensive catalog of metric metadata: tracking metric names, tags, host assignments, retention policies, and customer configurations.
On the web UI, the Metrics Summary Page allows users to filter, sort, search, and inspect these configurations in real time.
Behind the scenes, this single dashboard page required joining an active table containing 82,000 metrics with an associated configurations table containing 817,000 rows.
As Datadog’s customer base scaled, this query ground the platform to a halt:
- P90 query latency degraded to over 7 seconds.
- Complex multi-column filtering and fuzzy text sorting pegged the PostgreSQL database CPU at 100%.
- Routine metric updates were blocked behind long-running analytical query locks.
Datadog’s engineers initially attempted traditional database tuning: tweaking indexes, rewriting SQL joins, and optimizing autovacuum parameters. None of it worked.
The root cause was architectural: PostgreSQL was being forced to act as both an ACID transactional store and a real-time full-text search engine simultaneously.
To resolve this, Datadog engineered a company-wide Change Data Capture (CDC) Replication Platform built on PostgreSQL Write-Ahead Logs, Debezium, and Apache Kafka.
+-------------------------------------------------------------------------------+
| DATADOG CDC REPLICATION PLATFORM |
+-------------------------------------------------------------------------------+
[OLTP Application Service]
|
v (Transactional ACID Writes: INSERT / UPDATE / DELETE)
[Primary PostgreSQL Database]
|
| Physical Write-Ahead Log (WAL Logical Decoding: pgoutput)
v
[Debezium Kafka Connect Cluster]
|
v (Streams normalized row mutations)
[Apache Kafka: cdc.metrics.mutations (Compacted Topic)]
|
+======================+======================+
| Sink Connector | Sink Connector |
v v v
[Elasticsearch / OpenSearch] [Analytical Data Lake] [Read-Optimized Redis Cache]
(Powers Metrics Summary UI: (Nightly Reporting) (Sub-millisecond lookups)
Sub-50ms Search Latency!)
1. Why SQL Tuning Failed to Save the Database
The naive response to slow database queries is adding indexes. Datadog’s engineering team discovered that adding indexes actually made the overall system worse:
THE INDEXING PARADOX IN OLTP DATABASES
Query Requirement: Users want to sort and filter metrics by:
- Metric Name (Prefix search)
- Tag Key/Value pairs
- Integration namespace
- Last updated timestamp
- Aggregation method
Database Reality:
- To make all search permutations fast, PostgreSQL needed 8 compound indexes.
- Every incoming metric ingestion mutation (thousands of writes per second)
now had to update 8 separate B-tree disk indexes!
- Write throughput cratered; WAL disk I/O saturated; autovacuum could not keep up!
PostgreSQL’s storage engine (B-trees and tuple heap pages) is optimized for row-based transactional point reads and writes. It is fundamentally ill-suited for arbitrary multi-faceted text search and dynamic aggregation across hundreds of thousands of denormalized records.
2. The Solution: Log-Based Change Data Capture (CDC)
Rather than forcing PostgreSQL to handle search queries, Datadog decoupled the architecture:
- PostgreSQL remains the authoritative system of record for writes.
- A dedicated Search Engine (Elasticsearch / OpenSearch) handles all complex user queries, filters, and sorts.
To synchronize data between PostgreSQL and Elasticsearch with zero application coupling, Datadog deployed Log-Based Change Data Capture via Debezium:
+-------------------------------------------------------------------------------+
| HOW LOG-BASED CDC CAPTURES MUTATIONS |
+-------------------------------------------------------------------------------+
[Application executes: UPDATE metrics SET status = 'ACTIVE' WHERE id = 42;]
|
v
[PostgreSQL Engine commits transaction & writes to Write-Ahead Log (WAL)]
|
v (PostgreSQL Logical Replication Stream: `pgoutput` plugin)
[Debezium Kafka Connect Worker]
|-- Reads binary WAL stream continuously.
|-- Extracts before/after row state without querying database tables!
|-- Zero impact on PostgreSQL query execution engine!
v
[Kafka Topic: cdc.production.metrics]
Emits Structured JSON Event:
{
"before": { "id": 42, "status": "PENDING" },
"after": { "id": 42, "status": "ACTIVE" },
"op": "u",
"ts_ms": 1794744200124
}
Why Log-Based CDC Outperforms Query Polling
Legacy synchronization systems relied on polling: querying SELECT * FROM metrics WHERE updated_at > NOW() - 5m.
- Polling hammers the database with continuous table scans.
- Polling cannot detect hard
DELETEoperations (deleted rows vanish from query results). - Log-based CDC reads the storage engine’s native write-ahead log directly, capturing 100% of mutations, inserts, and deletes with near-zero CPU overhead on the primary database.
3. Streaming Synchronization and Search Projection
Mutations published to Kafka are consumed by Kafka Sink Connectors that stream data into the search cluster:
- Denormalization on the Fly: Streaming consumers join metric metadata with tag configurations in memory, assembling a rich, denormalized JSON document.
- Sub-Millisecond Search: Elasticsearch stores metrics in inverted indexes and columnar doc-values. Searching, sorting, and multi-tag filtering across 800,000 metrics executes in under 40 milliseconds.
- Reversible Projections: If the search index corrupts or the team adopts a new search technology, they simply deploy a new consumer group and replay the compacted Kafka topic from offset zero to rebuild the entire search index from scratch.
Production Results
| Performance Dimension | Monolithic PostgreSQL (Old) | CDC to Elasticsearch Platform (New) |
|---|---|---|
| Metrics Summary UI Latency | 7,000 ms (P90) | < 45 ms (150x Speedup!) |
| Primary Database CPU Load | Saturated at 100% | Stable at ~25% (Writes only) |
| Search Query Failure Rate | Frequent timeout aborts | 0% query timeouts |
| Application Coupling | High (Dual-write spaghetti) | Zero (Asynchronous log streaming) |
Summary and Key Takeaways
- Stop forcing databases to do the wrong job: Use relational databases for transactional integrity, and stream mutations to dedicated search engines for complex filtering.
- Avoid dual-writing in application code: Writing to a database and a search engine inside an application controller leads to data drift and race conditions during partial failures.
- Leverage log-based Change Data Capture: Read database write-ahead logs via Debezium to stream row-level mutations with near-zero overhead on production databases.
- Capture deletes naturally: Log-based CDC captures hard deletes and schema changes that query polling pipelines miss.
- Decouple through Kafka event streams: Buffering database mutations in Kafka allows multiple independent downstream systems (search engines, caches, data lakes) to consume data at their own pace.


/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.