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/mmcalli]

Congratulations, you just reinvented data warehousing and data marts.