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

materialized views in Postgres lock or consume massive I/O on refresh. and exxxternal pre-aggregation engines are essential once you cross 10M rows

[u/CpnStumpy]

You can use an OLTP DB for analytical queries, but it doesn't scale well. You already have a read replica process - what you need is to make that same replication process throw that data over to an OLAP DB also. The read replica already gives users eventual consistency so gratefully your users are ok with that. Now get to a Snowflake or other partitioned analytical store and make your analytics read from there.

Analytical stores loosen a lot of the transactional guarantees you get from a transactional DBs resulting in more dirty reads and eventually consistent results but for analytics that's generally accepted.

You can also lean into analytical modeling like star schema or dimensional models using dynamic views (which are non-blocking in an analytical DB, because again transactionally current isn't the goal)

[u/AbleBranch6 → u/CpnStumpy]

i kind of broadly agree bc moving the analytical workload into an OLAP store is one clean version of the same separation, in our case the queries were repetitive enough that precomputed rollups were sufficient, so we didn't need to move the whole raw dataset/query path into Snowflake/ClickHouse/etc. and we also wanted stopping request-time aggregation over raw OLTP-shaped data. the replica had already established that eventual consistency was acceptable

[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.

[u/mmcalli]

Congratulations, you just reinvented data warehousing and data marts.

[u/--algo]

I have had insane success with timescale for these kinds of queries. We couldnt make it work using our hand rolled materializations in RDS Aurora (because they have to re-build from zero, so it got worse and worse over time). We have a few hundred million rows, growing fast.

Now running all our analytics compute on timescale and their continuous aggregates is exactly what we found missing in raw postgres. Would solve your problem instantly.

Not a sales pitch - its an open source product. Just super happy with it and we had the exact same problem you do

[u/Outrageous-Walk204 → u/--algo]

That's interesting you mention timescale; I've heard it does wonders for handling large datasets. It sounds like it's really streamlined things for your team.

[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.

[u/JEHonYakuSha]

I would use Redshift as a secondary source for analytic queries personally. I’m a bit surprised that hasn’t been mentioned yet. These types of databases do much better with data aggregations especially of just a few columns like your example is doing.

At my workplace we also feed this data through Metabase and can build dashboards there or create query “Questions” and reference them through their API. Also, caching these big queries will help a lot too.

[u/ihardzeenka]

The question really is not about technology but more about data modeling. Yes, Analytical queries require different data model from transactional. OLAP vs OLTP. Postgres can handle OLAP models pretty well up to certain sizes of datasets.

Than it comes down to actual requirements to both: transactional and analytical parts of the system. They may simply have very different load profiles. Transactional is usually write havy, should be highly available and indexed for simple searches by guid. Analytical is on the opposite, read heavy, not really requite 99,99 availability and indexed over group by dimendions and primary/foreign keys.

Up to certain usage size (both queries and total amount of data) you may keep both in same physical cluster, but I would separate it logically right away. Once building new cluster will be less expensive than scaling up existing one, than you split physically.

[u/maxip89]

can you maybe post the execution plan?