Timescale continuous aggregates over top of continuous aggregates - Caggs over top of caggs (POC)

hypertable_trigger.sql

DROP TABLE ticks CASCADE;
DROP TABLE ohlc_1s CASCADE;
CREATE TABLE ticks ( time TIMESTAMP NOT NULL, symbol varchar, price decimal, volume int);
CREATE TABLE ohlc_1s ( time TIMESTAMP NOT NULL, symbol varchar, o decimal, h decimal, l decimal, c decimal, v int);
SELECT create_hypertable('ticks', 'time');
SELECT create_hypertable('ohlc_1s', 'time');

CREATE OR REPLACE FUNCTION feed_ohlc_1s() RETURNS trigger AS
$BODY$
DECLARE
    last_time timestamp;
BEGIN
   SELECT time_bucket('1 second', time) INTO last_time
   FROM ticks WHERE symbol = NEW.symbol
   ORDER BY time DESC LIMIT 1;

   -- When turn next second
   IF NEW.time - last_time >= INTERVAL '1 second' THEN
      INSERT INTO ohlc_1s (time, symbol, o, h, l, c, v)
        SELECT time_bucket('1 second', time) as time,
          symbol,
          FIRST(price, time) as open,
          MAX(price) as high,
          MIN(price) as low,
          LAST(price, time) as close,
          SUM(volume) as volume FROM ticks
        GROUP BY 1, 2 ORDER BY 1 DESC LIMIT 1;
  END IF;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER feed_ohlc_every_new_second
               BEFORE INSERT
               ON ticks
               FOR EACH ROW
               EXECUTE PROCEDURE feed_ohlc_1s();

INSERT INTO ticks VALUES 
('2021-08-26 10:09:00.01'::timestamp, 'SYMBOL', 10.1, 100),
('2021-08-26 10:09:00.08'::timestamp, 'SYMBOL', 10.0, 100),
('2021-08-26 10:09:00.23'::timestamp, 'SYMBOL', 10.2, 100),
('2021-08-26 10:09:00.40'::timestamp, 'SYMBOL', 10.3, 100);
table ticks;
table ohlc_1s;
INSERT INTO ticks VALUES 
('2021-08-26 10:09:01.02'::timestamp, 'SYMBOL', 10.0, 100),
('2021-08-26 10:09:01.04'::timestamp, 'SYMBOL', 14.0, 200),
('2021-08-26 10:09:01.42'::timestamp, 'SYMBOL', 12.3, 200),
('2021-08-26 10:09:01.62'::timestamp, 'SYMBOL', 8.3, 200),
('2021-08-26 10:09:02.80'::timestamp, 'SYMBOL', 9.0, 500);
table ticks;
table ohlc_1s;

Sample output:

DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
┌─────────────────────┐
│  create_hypertable  │
├─────────────────────┤
│ (63,public,ticks,t) │
└─────────────────────┘
(1 row)

┌───────────────────────┐
│   create_hypertable   │
├───────────────────────┤
│ (64,public,ohlc_1s,t) │
└───────────────────────┘
(1 row)

CREATE FUNCTION
CREATE TRIGGER
INSERT 0 4
┌────────────────────────┬────────┬───────┬────────┐
│          time          │ symbol │ price │ volume │
├────────────────────────┼────────┼───────┼────────┤
│ 2021-08-26 10:09:00.01 │ SYMBOL │  10.1 │    100 │
│ 2021-08-26 10:09:00.08 │ SYMBOL │  10.0 │    100 │
│ 2021-08-26 10:09:00.23 │ SYMBOL │  10.2 │    100 │
│ 2021-08-26 10:09:00.4  │ SYMBOL │  10.3 │    100 │
└────────────────────────┴────────┴───────┴────────┘
(4 rows)

┌──────┬────────┬───┬───┬───┬───┬───┐
│ time │ symbol │ o │ h │ l │ c │ v │
├──────┼────────┼───┼───┼───┼───┼───┤
└──────┴────────┴───┴───┴───┴───┴───┘
(0 rows)

INSERT 0 5
┌────────────────────────┬────────┬───────┬────────┐
│          time          │ symbol │ price │ volume │
├────────────────────────┼────────┼───────┼────────┤
│ 2021-08-26 10:09:00.01 │ SYMBOL │  10.1 │    100 │
│ 2021-08-26 10:09:00.08 │ SYMBOL │  10.0 │    100 │
│ 2021-08-26 10:09:00.23 │ SYMBOL │  10.2 │    100 │
│ 2021-08-26 10:09:00.4  │ SYMBOL │  10.3 │    100 │
│ 2021-08-26 10:09:01.02 │ SYMBOL │  10.0 │    100 │
│ 2021-08-26 10:09:01.04 │ SYMBOL │  14.0 │    200 │
│ 2021-08-26 10:09:01.42 │ SYMBOL │  12.3 │    200 │
│ 2021-08-26 10:09:01.62 │ SYMBOL │   8.3 │    200 │
│ 2021-08-26 10:09:02.8  │ SYMBOL │   9.0 │    500 │
└────────────────────────┴────────┴───────┴────────┘
(9 rows)

┌─────────────────────┬────────┬──────┬──────┬──────┬──────┬─────┐
│        time         │ symbol │  o   │  h   │  l   │  c   │  v  │
├─────────────────────┼────────┼──────┼──────┼──────┼──────┼─────┤
│ 2021-08-26 10:09:00 │ SYMBOL │ 10.1 │ 10.3 │ 10.0 │ 10.3 │ 400 │
│ 2021-08-26 10:09:01 │ SYMBOL │ 10.0 │ 14.0 │  8.3 │  8.3 │ 700 │
└─────────────────────┴────────┴──────┴──────┴──────┴──────┴─────┘
(2 rows)```

The write amplification on something like this at 1-second is likely to get pretty out of hand. This trigger is going to fire for every row that's inserted which is almost assured to eventually cause some kind of blocking under load. How near-time does this need to be?

That is a good question! this was an example of a high-frequency trading system. Probably in a real scenario, you'd aggregate it in the application level to not bring this boring throughput to the database. I'll try to benchmark it to see how it performs.