Continuously Updated Counts Across All Time

TimescaleDB and PostgreSQL
1

I’m looking to setup a very simple analytics dashboard and am curious if TimescaleDB supports continuous updating of rollup/summary data.

Given a table like the following that is continuously populated by analytic data from web browsers (similar to Google Analytics):

CREATE TABLE events (
  id UUID NOT NULL,
  client_id UUID NOT NULL,
  session_id UUID NOT NULL,
  name TEXT NOT NULL,
  path TEXT NOT NULL,
  user_id UUID,
  created_at TIMESTAMPTZ NOT NULL
);

I want to be able to have a query like the following that loads quickly:

SELECT count(*) 
FROM events 
WHERE name = "site:user_signedUp"
AND campaign_id = "foobarbaz";

Note the lack of any time_bucket in the query. I want the total across all time. I was hoping continuous aggregates would be able to accomplish this by keeping a running total but it’s unclear. Does TimescaleDB support such a use case? If not, any pointers?

2

I was able to get some help on Timescale’s community Slack. Two suggestions:

  1. Create a continuous aggregate with a time bucket of something sensible for the use case (day, week, month) and then sum the total:
CREATE MATERIALIZED VIEW user_signedup_total
WITH (timescaledb.continuous) AS 
  SELECT time_bucket('1 day', created_at) as bucket,
    count(1) as total
  FROM events
  WHERE name = 'site:user_signedUp'
  AND campaign_id = 'foobarbaz'
  GROUP BY bucket;

SELECT sum(total) AS total FROM user_signedup_total;
  1. Take a look at stats aggs

Here’s the Slack thread for additional context.

3

@fortytwo

This could be useful.

Regards,