3 ms·

millions of rows per device. After my where conditions I know I can have around 100000 devices That's going to be a fairly heavy bit of data; my wild-guess

millions of rows per device. After my where conditions I know I can have around 100000 devices That's going to be a fairly heavy bit of data; my wild-guess estimate is around 1TB for 100k devices with 1M rows each of 12 bytes (4-byte ID and 8-byte timestamp). Is there anything special about "specific_date_time_value"? An efficient architecture may be able to use that to avoid considering every row every time - for instance, using a queue to surface the "next" event easily. So far I’ve tried modeling this in: - clickhouse - Postgres - mongodb How did those turn out? What worked and what didn't? You've already collected a lot of info, sharing it will help!

daniel56hdz 3y ago
Yes, the significance of the “specific_date_time_value” is that certain users will want to query based on that to determine if it’s a device that they should remediate. A large percent of the queries though would be based on now() vs a specific date. In all of these approaches the issue occurs when I add the grouping stage as it doesn’t seem to be a way to just get the earliest value for each device without reading all records for each device. Could you explain the queue to surface the next event easily?