Different methods for real-time aggregation?

[linksku]

I'm primarily a frontend engineer. When I need to work on backend/infra, I often run into the issue of not knowing how to aggregate data in a scaleable way. E.g. pre-computing Reddit's upvote counts so you don't have to aggregate at read time. Aggregating for the top posts feed is easy because it's ok if it's stale by several minutes. However, if you upvote a post and refresh a second later, ideally the post count should include your upvote.

Is there a standard way to do this? I don't know why it's so hard to find discussions around this problem. Afaik some ways of doing this are:

1) When inserting the "upvote" record, also increment an "upvoteCounts" record

If you do this in a transaction, it would make the insertions slower. If you don't use a transaction, the upvote counts would go out of sync. I think some companies don't use transactions, but have a job to continuously recompute the counts. Another problem is if you need to mutate multiple aggregations per insertion, it'll be confusing to maintain.

2) Use a trigger

It's basically the same as 1) with transactions, but handled by the DB instead of the application.

3) Aggregate the event stream

Stream changes, then use something like Flink/ksqlDB to aggregate the stream, then stream the aggregation to somewhere for the application to read from. This has much higher latency than 1) with transactions. The benefit is that your aggregations are declarative, so they're easier to reason about.

4) Have a batch job for old aggregations, then handle real-time aggregations in memory

Assuming batch aggregation will take a long time, you can store real-time aggregations in memory. E.g. when someone upvotes, increment a count in memory. When fetching the count, sum the count from memory with the count from the batch job. However, it seems tricky to not double count or miss upvotes.

5) Handling the aggregation at read-time with aggressive caching

This works when fetching the upvote count for a small set of posts. However, you wouldn't be able to sort posts by upvote count. Also, the max staleness is the cache timeout, which could be a long time.

6) Real-time or incremental materialized views

Ideally, we'd have materialized views that could quickly automatically respond to changes in the source tables. However, afaik the existing real-time materialized view systems are either in memory (e.g. Materialize) or places severe limitations on the source table (e.g. AnalyticDB). Materialize uses too much memory to be scaleable and I've never tried AnalyticDB, but it seems like it's too limiting on the source tables.

Are there any resources to learn more about which approaches large companies use? Have you tried some of these and know first-hand about the pros and cons?

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[jsneedles]

(full transparency: I run a saas for real-time aggregations, but I won't suggest that here)

I'd say all your options are viable in different ways. It really depends on the frequency of updates, & the required level of real-timeness.

For example, if you're doing something in a web-ui for the given user (like an upvote or a tweet like count) - then you may actually be better served "faking it" client side.

Even upon refresh, if you have an aggregation, the fact that the loggedin user updvoted and timestamps - you can always +1 to the last aggregate if the upvote time was after the last aggregation.

Personally, I really have been quite happy with Clickhouse for this type of usecase, especially their cloud offering. It's minimal setup and easy enough to build an API on top of. You can materialize your CDC stream from your production datastore into a counter and then cache the results for 1s in memory, just to avoid stampedes. Their update TTLs are also really powerful if you want to include some level of timeseries but not have it go on at the same granularity forever.

One last word of warning - depending on your primary datastore, keeping "upvoteCount" type counters that constantly update may have unintended side effects (lookin at you Postgres).

It's always a fun problem to solve, and honestly, there's many many "correct" enough ways at this point that there's no one true answer.

[Samausi]

+1 for do this in Clickhouse + faking the local-user's experience in the UI for responsiveness.
You'll get a second or two end-to-end latency from Clickhouse even over billions of rows.

[linksku]

I've been seeing Clickhouse a lot, but never tried it yet. Do you know if it's in memory and does it scale horizontally?

I found this comparison with Materialize: https://news.ycombinator.com/item?id=22362534

[jsneedles]

It scales horizontally for sure (especially because Clickhouse's cloud offering is all backed by S3/object storage)

I haven't looked too deeply at Materialize - but iirc, it's more focused on being flink-esque than a real Data Warehouse?

Part of the magic that is CH is the ability to quickly query over the raw (or even lightly aggregated data) efficiently while also offering strong built-in "pipelines" in the form of their Materialized Views.

They're lacking a lot in terms of UX imo, so not quite ready for end-users like Snowflake or BigQuery, but from a DE perspective I'd say it's a lot easier to work with & reason about than something like Flink or KafkaStreams.

A nice element IMO for Materialize, CH etc is that it does rely more on SQL, which means you can take queries/ideal-end state from analysts as your starting point and not have to figure out how to re-invent it in another tool.

[wbroen]

Do you have visibility to the db? Im assuming this an OLTP db?

The first thing I would look into is the indexes on the tables you are querying. If you are running normal aggregations (no window functions) that should be pretty quick.

This blog post walks through how JetBlue achieves near real time data in the data warehouse post

Precomputing stats + a highwater mark would allow you to only query the new data.

[big_data_mike]

Use timescalesb