2

Suppose I have a simple table called events like this:

timestamp event_id action_count
apr 5 01:05:00 foo 10
apr 5 01:04:15 bar 20
apr 5 01:06:10 baz 200
apr 5 01:04:30 baz 5
apr 5 01:03:00 baz 1
apr 5 01:03:02 foo 3
apr 5 01:03:02 bar 100
apr 5 01:01:00 bar 34

Given this table, for any given interval, such as last minute, last hour, last 10 hours and so on, I'd like to extract the following data:

event_id total_action_count
foo 500
bar 499
baz 300

Simple enough, right?

SELECT event_id,
       SUM(event_count) as total_action_count
FROM events
GROUP BY event_id
ORDER BY total_action_count DESC

However, I'm finding that this doesn't scale, at least not well enough for querying it every second, for multiple intervals, when dealing with millions of rows per day, and tens of thousands of unique event_id.

With a setup pretty similar to this one, using timescaledb with Postgres, a query on 2.5m events and 30k unique event_id over a 24 hour period is taking anywhere from 2 to 5 seconds. Considering I want to run this query every second or so for 10 different intervals, this approach simply doesn't work.

From my perspective, my bottleneck is the fact that I can't get around having to read every single row to SUM action_count per event_id. In this example I could in theory have one row for each action_count instead, but for my real use case I can't because action_count can be a number anywhere from 1 to 10 billion.

So, my question is, is there any way I could model this in such a way that I can run these queries much faster?

Keep in mind, it's no use for me to keep track of events on a hourly/daily timeframe, since I'm always querying on last N minutes/hours instead of hour N or day N. With timescaledb I could bucket my data into minute/hour intervals, but since I have tens of thousands of unique event_id I end up running into the same bottleneck of having to read and sum millions of rows at a time.

Another caveat is that rows are usually, but not always, coming in the correct order. So I may insert and event for April 5 followed by an event for March 30 and then April 5 again.

I can think of a few solutions, but they all seem extremely contrived and error prone. I could also use a memory database, but it seems to me that there's got to be a better solution to all of this, right?

So, I'm asking you, how would you approach this?

13
  • I would prepare the results of the query when registering the events. Commented Aug 27, 2021 at 18:48
  • @GerardH.Pille That was my first instinct, ad that would be fine if I needed data for a fixed interval like "day N" or "hour N", but since I need "last N hours" or "last N minutes" that doesn't really work, right? I can group events into buckets, like "minute apr-5-05:05" and hour "april-5-05", which timescaledb can do for me, but I still end up with too much data to run fast queries. It might very well be that there's no way around this though, I'm just hoping there's a magical pattern out there that I'm overlooking.
    – xddz9
    Commented Aug 27, 2021 at 18:55
  • 1
    @GerardH.Pille I see what you mean. While that works, I end up back with the same problem given a long enough interval. My current performance for 5 minutes or 1 hour is fine, but from 12 hours on things get tricky. I'm looking into continuous aggregates on timescaledb now, which could accomplish something similar to what you suggested. Thanks!
    – xddz9
    Commented Aug 27, 2021 at 19:23
  • 2
    "..since I need "last N hours" or "last N minutes" that doesn't really work, right?.." No. If you have stored running total for each event_id then you can calculate the sum of action_count for arbitrary interval as a difference of two running_totals at its boundaries.
    – Kondybas
    Commented Aug 27, 2021 at 21:00
  • 1
    "Another caveat is that rows are usually, but not always, coming in the correct order. So I may insert and event for April 5 followed by an event for March 30 and then April 5 again." Is there time period after which you can be confident no more stragglers will be showing up?
    – jjanes
    Commented Aug 28, 2021 at 18:47

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.