Search & Analytics Dashboard R&D
Exploring separate read paths for complex search and interactive analytics: PostgreSQL CDC feeds OpenSearch and ClickHouse, each serving a different query workload.
Backend engineering R&D
Recent research & development
PostgreSQL, CDC, OpenSearch, ClickHouse
Separate read paths for search and analytics
Search and analytics need different read paths
The dashboard needed more than record lookup: users needed to change filters, explore facets, and group data for analytical views. Alongside that, the search feature needed to support complex queries.
In this R&D, I used PostgreSQL CDC to feed OpenSearch for search and ClickHouse for analytics. The two stores are separate destinations for source changes, rather than a sequence where analytics depends on the search index.
One source, two destinations
PostgreSQL is the source database. Change Data Capture carries source changes into the search and analytics stores.
OpenSearch holds the data used by the search feature, serving the queries needed to find matching records.
ClickHouse holds the data used by analytical views, supporting dynamic filtering, facets, and grouping.
Choose the read store for the workload
I chose ClickHouse for its performance on the dashboard's analytical workload, especially when filters and grouping change as users explore the data. OpenSearch remains responsible for the complex search feature.
This work is presented as R&D: the focus is the architecture and technology choice, rather than a quantified production performance claim.
A sales performance dashboard recomputed everything on demand against a legacy system I was not allowed to touch, query directly, or ask for a push feed. Here is what I could change, and what it cost.