Hey guys! Been loving what you guys are doing with QuestDB <3
The current issue i’m having is with trying to have rollup MVs for a price system. I have a base table that has ~700M rows (between 1-10M rows per partition, which are by DAY here)
CREATE TABLE ‘accounting_delta’ (
timestamp TIMESTAMP,
user varchar,
asset varchar,
is_deleted BOOLEAN,
amount_approx DOUBLE,
amount_part1 LONG,
amount_part2 LONG,
amount_part3 LONG,
amount_part4 LONG,
amount_part5 LONG,
amount_part6 LONG,
amount_part7 LONG,
amount_part8 LONG,
is_inbound BOOLEAN,
tx_id varchar
) timestamp(timestamp) PARTITION BY DAY WAL
WITH maxUncommittedRows=500000, o3MaxLag=600000000us
DEDUP UPSERT KEYS(timestamp, user, asset, tx_id);
My first MV works great:
CREATE MATERIALIZED VIEW ‘holdings_hourly’ WITH BASE ‘accounting_delta’ REFRESH INCREMENTAL AS (
SELECT
timestamp,
asset,
user,
sum(amount_approx) as amount_approx,
sum(CASE WHEN is_inbound THEN amount_part1 ELSE -amount_part1 END) AS amount_part1,
sum(CASE WHEN is_inbound THEN amount_part2 ELSE -amount_part2 END) AS amount_part2,
sum(CASE WHEN is_inbound THEN amount_part3 ELSE -amount_part3 END) AS amount_part3,
sum(CASE WHEN is_inbound THEN amount_part4 ELSE -amount_part4 END) AS amount_part4,
sum(CASE WHEN is_inbound THEN amount_part5 ELSE -amount_part5 END) AS amount_part5,
sum(CASE WHEN is_inbound THEN amount_part6 ELSE -amount_part6 END) AS amount_part6,
sum(CASE WHEN is_inbound THEN amount_part7 ELSE -amount_part7 END) AS amount_part7,
sum(CASE WHEN is_inbound THEN amount_part8 ELSE -amount_part8 END) AS amount_part8
FROM accounting_delta
SAMPLE BY 1h
) PARTITION BY DAY;
for holdings_hourly, I have around 1-5M rows per partition (DAY).
What i want is being able to create this MV, that uses as its base the hourly MV result set, narrowing down aggregations, and being able to be more realtime.
CREATE MATERIALIZED VIEW ‘holdings_daily’ WITH BASE ‘holdings_hourly’ REFRESH INCREMENTAL AS (
SELECT
timestamp,
asset,
user,
sum(amount_approx) as amount_approx,
sum(amount_part1) AS amount_part1,
sum(amount_part2 ) AS amount_part2,
sum(amount_part3 ) AS amount_part3,
sum(amount_part4 ) AS amount_part4,
sum(amount_part5 ) AS amount_part5,
sum(amount_part6 ) AS amount_part6,
sum(amount_part7 ) AS amount_part7,
sum(amount_part8 ) AS amount_part8
FROM holdings_hourly
SAMPLE BY 1d
) PARTITION BY DAY;
This would result in about 1-3M rows per partition (day too), but everytime I do run it, I either get an RSS OOM error (I have ~40G ram, 15 cores), or a timeout (Materialized View is invalid [-1] timeout, query aborted)
Any recommendations on how doing cascading MVs or achieving some sort of continuous aggregation like I’m trying to achieve?
** Note that the MVs work great when I create them while the first base table is empty, then backfill the whole thing, maybe this is related to the initial full mv refresh?
Thanks guys, appreciate it
