Data Engineering · Real-Time Sync
How we kept PostgreSQL synchronized with OpenSearch using Kafka Connect, while complementing it with monitoring and reconciliation capabilities to ensure business confidence in the data.
Customers expect search results to be fast, relevant, and up to date.
For one of our clients, PostgreSQL served as the system of record for business transactions, while OpenSearch powered the search experience. The challenge was ensuring that changes made in PostgreSQL were reflected in OpenSearch quickly and reliably without impacting application performance.
To address this, we implemented a real-time synchronization architecture using Kafka Connect while complementing it with monitoring and reconciliation capabilities to ensure business confidence in the data.
The client's platform relied heavily on search.
Users were constantly:
While PostgreSQL remained the authoritative source of business data, OpenSearch provided the fast and flexible search experience users expected.
The challenge was straightforward:
How do we keep OpenSearch synchronized with PostgreSQL as data changes throughout the day?
Without a reliable answer, teams face a costly reality:
The solution needed to support:
Rather than building and maintaining custom synchronization services, we leveraged Kafka Connect as the foundation of the integration layer.
The architecture looked like this:
This approach provided several advantages:
By using Kafka Connect, the team could focus on business outcomes rather than building and maintaining data movement infrastructure.
While moving data from PostgreSQL to OpenSearch was important, the real business requirement was trust.
Business users did not ask:
Is Kafka Connect running?
They asked:
Can we trust the search results?
A synchronization pipeline can appear healthy from an infrastructure perspective while still leaving stakeholders concerned about data consistency.
For this reason, we complemented the integration with a reconciliation framework.
As with any distributed system, occasional discrepancies can occur due to downstream outages, deployment issues, schema changes, or operational incidents.
Rather than assuming synchronization would always be perfect, we designed for verification and recovery through continuous data validation.
Our reconciliation framework performed periodic batch comparisons between PostgreSQL (source) and OpenSearch (destination) across key metrics:
When mismatches were detected, the system automatically triggered recovery workflows:
Most importantly, teams had a complete audit trail of what data had been validated and when, enabling them to identify and resolve issues proactively before end users were impacted.
The combination of Kafka Connect and reconciliation delivered two critical capabilities:
Changes made in PostgreSQL were propagated through Kafka Connect to OpenSearch. We tracked:
Reconciliation jobs ran on a configurable schedule (hourly, daily, or on-demand) to validate data integrity:
This transformed synchronization from a "best effort" process into a measurable and observable business capability. Every stakeholder could see:
For teams already operating Kafka Connect at scale: The following section dives into advanced observability patterns across the full CDC stack. If you're just getting started with synchronization, the core principles above (reconciliation + monitoring) are sufficient. But once you're managing multiple connectors in production, this is where the real operational maturity kicks in.
Many teams assume their CDC pipeline is healthy because Kafka Connect reports RUNNING. Unfortunately, we've seen production environments where everything appeared healthy while replication lag continued growing, WAL files accumulated, and downstream systems consumed increasingly stale data.
The challenge wasn't CDC. The challenge was visibility.
A typical CDC pipeline spans multiple systems:
When issues occur, teams often waste hours answering a simple question: Where is the bottleneck? Without proper observability, troubleshooting becomes guesswork:
Most monitoring solutions only answer part of the question.
Instead of monitoring individual components, we built observability across the entire CDC data flow, focusing on metrics that directly impact reliability and data freshness.
Database Visibility — We monitor replication slot lag, WAL generation rate, and disk usage. These metrics help identify CDC pressure before storage issues appear.
Kafka Connect Visibility — We monitor connector status, task status, queue size, batch size, and records per second. This allows us to quickly determine whether connectors are keeping pace with database activity.
Kafka Visibility — We monitor topic lag, produce rate, and consumer lag. This provides insight into downstream processing health and end-to-end latency.
Instead of spending hours tracing issues across multiple systems, teams can immediately identify where delays originate. For example:
The result is faster incident response, improved reliability, and greater confidence operating CDC in production.
Most CDC failures don't begin as outages. They begin as small signals:
Organizations that detect these signals early avoid the operational fire drills that come later.
The solution delivered several tangible benefits:
What this looks like in practice:
Most importantly, teams transition from reactive firefighting (responding to customer-reported issues) to proactive monitoring (catching problems before users feel them). That shift is where real reliability lives.
If you're facing similar challenges with data synchronization between transactional systems and search platforms, we can help you design and implement a comparable solution tailored to your architecture.
Our approach includes:
Kafka Connect proved to be an effective foundation for moving data between PostgreSQL and OpenSearch. However, the real success of the solution came from pairing data movement with reconciliation and observability.
In our experience, reliable search platforms are not built solely by synchronizing data. They are built by ensuring that synchronization can be monitored, validated, and trusted over time.
That combination of automation, visibility, and recoverability is what ultimately enables organizations to deliver high-quality digital experiences at scale.
Reliable search platforms require more than just moving data. They demand observability, reconciliation, and continuous validation to maintain stakeholder confidence and ensure business outcomes at scale.
Is your CDC pipeline struggling with visibility or data confidence? We design and build these architectures as custom engagements.
Topics
Keep Reading