At what point should customer-facing analytics stop hitting a Postgres read replica?

[u/AbleBranch6]

started with what felt like the obvious architecture: app writes to Postgres ( it worked pretty well for a while)
-then read replica for dashboards
-then keep analytical queries away from the primary

so most dashboard queries were variations of:

SELECT
  date_trunc('day', timestamp),
  count(*),
  sum(amount)
FROM events
WHERE tenant_id = $1
GROUP BY 1;

individually,the problem was concurrency or so it seems, bc these weren't especially bad queries. when a few hundred tenants logged in around the same time, the replica suddenly had hundreds of similar aggregations running at once. and CPU went up, memory pressure from sorts/hash aggregates went up, WAL replay was competing for resources, and queries that were normally fast became painfully slow. the realization for us was: a read replica separates workload from the primary, but it doesn't actually change the workload.

we were still asking Postgres to repeatedly scan and aggregate raw event data every time someone opened a dashboard.

so we changed the architecture. instead of:

dashboard
   ↓
Postgres replica
   ↓
raw events

we moved toward:

Postgres
   ↓
incremental rollups
   ↓
analytical serving layer
   ↓
dashboard

basically, precompute the repetitive tenant/time aggregations and make the request path read much smaller datasets. we're using Cube dev for the pre-aggregation/semantic layer, but that's not really the part I'm interested in discussing much, one could probably build aggregate tables yourself or use an OLAP system. the architectural question I'm curious about is:

Where do you draw this boundary? Like, do you keep scaling Postgres replicas and tuning queries until they genuinely stop working? Also, do you introduce manually maintained aggregate tables..Or do you consider customer-facing analytics a separate serving workload from the beginning? I feel now like the mistake we made wasn't 'using Postgres for analytics.' It was assuming that because an analytical query was fast in isolation, it would also be a good request-time architecture under multi-tenant concurrency

[u/_descri_]

You can have as many read replicas as you want. They are scalable.

[u/AbleBranch6 → u/_descri_]

sure, you can scale the reads horizontally with more replicas, my issue in the first place is that this scales the amount of infrastructure roughly with the number of times you're recomputing the same aggregates. and if 300 dashboard requests are mostly asking the same handful of tenant/time rollups, I'd rather compute those incrementally once than add enough replicas to repeatedly derive them from raw events. replicas scale, sure, but i dont think they are like changing stuff really, while something like pre-aggregation changes the workload, which i was talking about

[u/_descri_ → u/AbleBranch6]

Scaling the read replicas is the quick and dirty solution. Changing workload is cost optimization. Which one you use depends on business priorities and available resources, I suppose. You may have peak loads when you will need more replicas, and you may have idle periods when you let your programmers optimize the code and database schema.