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?
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:
The solution delivered several tangible benefits:
Most importantly, the client gained a search platform that could evolve with growing business demands while maintaining confidence in the underlying data.
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.
Topics
Keep Reading