Why we moved our search to Elasticsearch and kept Postgres is a question that comes down to one architectural principle: each system should do what it is genuinely good at. We did not abandon Postgres because it “could not handle” our data, and we did not adopt Elasticsearch as a trendy alternative to SQL. The db we trusted with transactions, consistency, and business logic stayed where it was. But our search experience — faceted, typo-tolerant, and ranking-heavy — had outgrown what a relational database should reasonably be asked to deliver. The bridge between these two worlds turned out to be change data capture, or CDC, and it changed how we think about indexing, availability, and the meaning of “ground truth.”
The Search Problem That Postgres Couldn’t Solve Alone
Our product is a document-heavy platform where users search across millions of records with filters, fuzzy matching, and relevance scoring. Postgres full-text search was good enough for the first few thousand documents. It handled basic tokenization, stemming, and even the occasional tsvector index. But as our catalog grew and our users began expecting instant, typo-tolerant results, the cracks became obvious. ILIKE queries were slow, ranking was primitive, and every query seemed to eat CPU on our primary database.
We considered the obvious alternatives: build more Postgres indexes, add a read replica, or move to a managed search service. The read replica helped report queries but did nothing for phrase matching or relevance. The search service solved the search problem but created a new one: how do you keep a denormalized search index in sync with a normalized Postgres schema without going insane?
That is the part most blog posts skip. Everyone expects you to say “we chose Elasticsearch and life was perfect.” In practice, the hard part is not the search engine itself. The hard part is the plumbing.
Two Indexes, One Truth: Why Dual-Writes Failed Us
Our first instinct was the classic dual-write pattern. Every time an application service updated a row in Postgres, it would also send the same document to Elasticsearch. Simple in theory, painful in practice. We quickly discovered that dual-writes are a lie. A transaction can commit in Postgres while the Elasticsearch request fails. Or the Elasticsearch request succeeds while the Postgres transaction rolls back. You end up with two systems that disagree, and there is no way to know which one is right.
We tried compensating jobs, reconciliation scripts, and “just retry the failures.” It worked about eighty percent of the time, which means it failed constantly. The real problem was architectural: the correctness of our search index depended on application code remembering to maintain it, and application code is famously forgetful when it is being pressed for new features.
We needed something that observed changes at the database level, not at the application level. We needed CDC.
The CDC Tipping Point: Logical Replication and Decoupling
Change data capture is not a new idea. For years, people have used triggers or audit tables to track row changes. But in the modern Postgres world, logical replication gave us something better: a clean, transaction-ordered stream of changes that we could consume without touching the performance-critical parts of the primary database.
Postgres logical replication works by decoding the write-ahead log (WAL) into a stream of row-level changes. New rows become inserts. Updated rows produce a new version, and deletes are captured with the old key. The key insight is that this happens natively inside Postgres, so it reflects the actual truth of committed transactions — not the best intentions of our application layer.
For our architecture, the critical piece was the integration between logical replication and a distributed log. We settled on a pipeline that looks like this: Postgres logical replication streams events to a lightweight broker, and a dedicated consumer transforms those events into Elasticsearch documents. The consumer is idempotent, so replaying an event produces the same final state. That gave us two properties we desperately needed:
- Decoupling from application code: If a service goes down or a deploy goes wrong, the CDC stream keeps flowing. The index catches up when the consumer is ready.
- Transaction ordering: Logical replication preserves commit order, so we never index a child row before its parent, and we never miss an update because a delete arrived first.
Choosing the right CDC tool in 2026 is easier than it used to be. Debezium remains the battle-tested default, but we chose a lighter path using native Postgres logical decoding with a small custom consumer. The tradeoff is the same for every team: battle-tested frameworks give you connectors and error handling out of the box, while custom consumers give you control over mapping, batching, and ordering. We chose control because our document shape is complex and we wanted one codebase, not two.
Building the Pipeline: From WAL to Elasticsearch
The pipeline has three stages, and each one taught us something about state synchronization.
1. Capture: Logical Replication Slots
Postgres was configured with wal_level = logical, and we created a replication slot that tracks the position in the WAL. The slot is the key to reliability: it tells Postgres not to discard WAL segments until the consumer has acknowledged them. The danger is a slow consumer causing WAL growth, so we monitor lag religiously. If the consumer is down for more than a few minutes, Postgres storage starts filling up. We learned that the hard way during a failed upgrade, and now our alerting checks replication lag before it checks anything else.
2. Transform: From Rows to JSON Documents
The raw CDC event is a row-oriented change: a JSON object with before and after states. That is useful, but it is not yet a search document. Our Elasticsearch index is denormalized: an order document contains customer name, line items, and current status, all in one JSON blob. The transform stage is where we join, aggregate, and shape. It is also where we handle soft deletes. When a soft-deleted product row changes in Postgres, the transform stage marks the Elasticsearch document as inactive rather than removing it. This gives us the flexibility to hide and restore documents without losing history.
3. Load: Idempotent Indexing
Indexing is straightforward: the consumer takes the transformed document and writes it to Elasticsearch using the document id derived from the Postgres primary key. The whole pipeline is at-least-once by nature, so the consumer must be able to process the same event multiple times without corrupting the index. Because we use deterministic document ids and full-document replacement, re-indexing a stale event is harmless. The final write wins, and since the stream is ordered, the final write is always the most recent commit.
The 2026 Operational Reality: Schema Drift, Backfills, and Idempotency
The pipeline works. But a working pipeline in development is not the same as a working pipeline in production. Here are the three problems that defined our path this year.
Schema changes in Postgres are a fact of life. A new column is added, a column is renamed, or a table is partitioned. CDC handles most of this gracefully because a physically replicated row contains only the columns that exist at that moment. But the transform stage has a mapping that must be versioned. We now treat the transform code as versioned schema, and we store the version inside the document. When our search application needs to know whether a document understands a new field, it checks the version, not the deployment timestamp.
Backfills are not a one-time event. The first migration from the old dual-write system required a full re-index of three million documents. We built a backfill utility that reads from Postgres in chunks and feeds the same transform stage as the CDC pipeline. The trick is making sure the backfill and the live CDC stream do not race. The simplest fix is to run the backfill first, then open the replication slot and let the stream catch up from a known WAL position. In practice, a little overlap is fine because the indexing is idempotent. A stale backfill write is simply overwritten by a newer CDC event.
Idempotency is not just about retries. It is also about how you handle a search document that depends on multiple source rows. A single transaction in Postgres can touch five rows that all map to the same Elasticsearch document. The transform stage must aggregate them into one write, or the index ends up in a transient partial state. We solved this by grouping events by their target document id and flushing the group when the transaction boundary is reached. Postgres logical replication includes commit and transaction markers, and we take advantage of them.
What We Learned (and What I’d Do Differently)
If we had it to do over again, we would still move search to Elasticsearch and keep Postgres. The CDC architecture is the right call because it respects the strength of each system. Postgres remains the source of truth for facts, transactions, and relationships. Elasticsearch remains the source of truth for searchable, denormalized projections. Neither one has to pretend to be the other.
What we would do differently is invest in observability sooner. The first version of the pipeline was a black box. We knew the lag in seconds, but we did not know which index aliases were stale or which transform version was currently being written. In 2026, we instrument every stage with a correlation id that matches the Postgres transaction id. When a user complains that search does not show a record that was edited an hour ago, we can trace it back to the exact CDC event and see whether the issue sits in capture, transform, or loading.
We also wound up simplifying our search domain model. Early on, we tried to make Elasticsearch documents mirror the full Postgres schema. That was a mistake. A search index is not a replication of your database; it is a projection shaped by your most important queries. We stripped away fields that users never filtered on, and the index became smaller, faster, and far easier to reason about during pipeline debugging.
A Balanced Architecture, Not a Migration
The phrase “we moved our search to Elasticsearch” implies a migration, but this was never about moving away from Postgres. It was about adding a specialized engine to our stack without sacrificing transactional integrity. CDC bridges the gap with a continuous, ordered, auditable stream of changes that both systems can trust. The search experience is dramatically better, the transactional database is no longer polluted with search-oriented denormalization, and the operational cost is one clearly defined pipeline with a small set of alarms. For any team facing the same tension, the question is not “which database is best” but “which parts of the data lifecycle belong to each engine.” We answered that question with CDC, and it is the architectural decision we would make again.
