StarRocks OLAP: Cutting Analytics Query Time from 1.5 Seconds to 250ms

When I was at Cognisian, I owned the analytics dashboard for a healthcare platform: around 150 charts covering visits by department, average wait time, diagnosis trends over time. At first every query ran straight against Postgres, on the same database that served the operational HIS system. One chart took 1.5 seconds to load. Multiply that by 150 charts on a page and it was close to unusable.

1. Postgres (OLTP) doesn’t suit analytics workloads

Postgres is a row store optimized for OLTP: reading and writing individual records, short transactions, indexes on primary keys. But an analytics query like “total visits by department, by month, over the last 2 years” has to scan tens of millions of rows while needing only a few columns: department, date, count. Columnar storage exists to solve exactly this.

With a row store, every query reads the whole row, including columns it doesn’t need like doctor’s notes or JSON metadata, and only then filters down to the columns it wants. A huge waste of I/O. And it gets worse: multi-join, multi-level group-by queries ran on the same Postgres instance as operational traffic, meaning the write path for medical records. Lock contention and CPU spikes came back to hit the core system.

2. Why StarRocks

We evaluated a few options (ClickHouse, Druid, BigQuery) but picked StarRocks for three reasons specific to my problem, not because it was trendy:

• MPP (Massively Parallel Processing): a query is split up and run in parallel across many nodes, using all the cluster’s CPU instead of running sequentially.
• Columnar storage + vectorized execution: it reads only the columns it needs and processes in batches (vectors) instead of row by row, which is noticeably faster for aggregate queries.
• Real-time ingestion via CDC (Change Data Capture) from Postgres, so the dashboard stays near real time (a few seconds to a few minutes of lag) without us building a complex ETL pipeline ourselves.

3. Architecture: separate OLTP and OLAP, sync through CDC

Instead of querying Postgres directly, the new architecture separates the two paths completely:

Postgres (OLTP, source of truth)
   │  CDC (Debezium → Kafka)
   ▼
StarRocks (OLAP, serves the dashboard)
   ▲
Next.js Analytics Dashboard (~150 charts)

Every change in Postgres, whether insert, update or delete, is captured by Debezium through the WAL and pushed to Kafka. StarRocks’s Routine Load consumes it continuously to update the target tables. The dashboard no longer touches Postgres. All analytics queries run on StarRocks, fully isolated from operational traffic.

4. The three optimizations that paid off most

a. Materialized views for repeated aggregates

Many charts differ only in the time range they filter on (day, week, month) while the aggregate logic is identical: count, average, sum. Having each query compute from the detailed fact table is wasteful. We created a materialized view that pre-aggregates by day:

CREATE MATERIALIZED VIEW mv_visits_daily
DISTRIBUTED BY HASH(department_id)
AS
SELECT
  department_id,
  DATE(visit_time) AS visit_date,
  COUNT(*) AS visit_count,
  AVG(wait_minutes) AS avg_wait_minutes
FROM fact_visits
GROUP BY department_id, DATE(visit_time);

StarRocks automatically routes a query to a matching materialized view when the pattern fits. This is called query rewrite. Most of the dashboard code didn’t need to change. Add the materialized view and the queries get faster on their own.

Here’s the path a dashboard query takes:

flowchart LR
  D["Dashboard query<br/>aggregate by day/week/month"] --> RW{"Does the pattern match the MV?"}
  RW -- "match" --> MV["mv_visits_daily<br/>pre-aggregated by day"]
  RW -- "no match" --> F["fact_visits"]

b. Partition and bucket by time

The fact table (fact_visits) is partitioned by month (PARTITION BY RANGE(visit_time)), and within each partition it’s bucketed by department_id (DISTRIBUTED BY HASH(department_id) BUCKETS 16). These two layers do two things:

• Queries filtered by time range (almost every dashboard has a date-range filter) only scan the relevant partitions and skip older data entirely.
• Queries that group by department benefit from bucket pruning, which cuts the amount of data to read even further.

Stacked together, the amount of data to read shrinks layer by layer:

flowchart TB
  Q["Query with a date-range filter"] --> P["Pick the right monthly partitions<br/>PARTITION BY RANGE(visit_time)"]
  P --> B["Bucket pruning by department_id<br/>BUCKETS 16"]
  B --> RD["Only the data that's needed is left to read"]

c. Denormalize the fact table instead of joining at query time

In Postgres, a normalized model with many joined tables makes sense for OLTP. For OLAP it’s the opposite: joins at query time are one of the slowest things you can do. We denormalized. The fact_visits table stores department_name and doctor_name as strings directly, instead of just department_id and doctor_id joined to dimension tables at query time.

The trade-off is more storage, since we store repeated strings instead of just IDs, and having to reprocess data if a department or doctor is renamed. But this is a read-only warehouse, and storage is much cheaper than CPU and latency. We accepted it.

5. Measuring before and after

Metric	Before (Postgres)	After (StarRocks)
p50 latency/chart	~900ms	~120ms
p95 latency/chart	~1.5s	~250ms
CPU load on the operational DB	Spikes at dashboard peak hours	Unaffected (isolated)
Data freshness	Real-time	~a few seconds to a few minutes (via CDC)

At a p95 of ~250ms, users barely notice any delay when switching tabs between charts. Before, at 1.5 seconds, the dashboard froze every time you re-filtered the time range.

6. Trade-offs we had to accept

Nothing is free. The three main trade-offs we accepted:

• Eventual consistency: data in StarRocks lags Postgres by a few seconds to a few minutes through the CDC pipeline. For a reporting dashboard (which doesn’t need to be strictly real-time) that’s acceptable, but you have to communicate it clearly to stakeholders to avoid the “why don’t the dashboard numbers match the operational system instantly?” confusion.
• One more database system to operate: a StarRocks cluster needs monitoring, backups, and someone who understands how to tune it (partitions, buckets, compaction). That’s a real operational cost, not just an infrastructure cost.
• The CDC pipeline is a new point of failure: if Debezium or Kafka has an incident, the data in StarRocks stalls. You need separate alerting to catch abnormal ingestion lag, not only alerting on StarRocks itself.

Splitting OLTP and OLAP isn’t always needed from day one. For a dashboard with a few charts, Postgres with good indexes is still fine. But past a certain threshold (here, ~150 charts over tens of millions of records), the cost of running a dedicated OLAP system is far cheaper than the cost of a dashboard nobody can use because it’s too slow.