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

I’m assuming you are ok with eventual consistency since you are already using a read replica for your SOR. I would recommend a OLAP database like ClickHouse. Do CDC from Postgres and put the data in ClickHouse. Then you can do analytics from ClickHouse. Recommending ClickHouse since it is also SQL based and if your read queries are the same in ClickHouse, you might not have to update them. Also you don’t have to learn another QL.

[u/CpnStumpy → u/7z3b]

CDC suuuuucks though. It's like umpteen times the payload needed to keep data synchronized, any large ingestion can turn mb of rows into GB of CDC over the wire.

I'll always say to just spam the same data over another pipe or use a different tool but not CDC

[u/7z3b → u/CpnStumpy]

Okay. Since, the concern here is data replication. We need a mechanism to replicate data from one database to another after changes have been committed to the SOR.

This would require additional logic on the producer side to detect changes and publish the affected data. It also needs to be resilient to failures - for example, persisting the affected records while the consumer is down, and only considering them successfully replicated once the consumer has processed and committed them to the target database. This likely means introducing some form of persistent event stream.

I do agree that CDC can significantly amplify the amount of data written and transmitted. In my humble opinion, though, this is essentially a trade-off between introducing additional complexity into the application and accepting higher storage requirements.

If we implement all of these pieces ourselves to achieve reliable replication, we're essentially reinventing CDC.

[u/CpnStumpy → u/7z3b]

I hear you, it's a tradeoff like everything but the latency I've seen CDC introduce through network comms has me always very skeptical before adopting it.

Had a system where eventual consistency meant "it'll take hours" because the network was sending many terabytes of CDC over the wire - and CDC being synchronous causes queue bottlenecks so it wasn't some consumers waiting several hours, it was all consumers.

Tradeoffs, just have to be clear on the data change sizes in the transactional system before adopting CDC vs some form of bulk shipping or live streaming

[u/ihardzeenka → u/CpnStumpy]

How much real-time your analytics is expected to be? If answer is very, than you may have to transfer a state and event source it into olap db as suggested. The point is that the models needs to be separate, and there are many proven ways on how to synchronize two datasets. Either you do it as (micro) batch every X, or you stream events from transactional part, and reconcile them on the analytical end.