4 ms·

Help on database for efficient group bys

1 points by daniel56hdz 3y ago

I’m having trouble finding a database that can handle my use case: Select device_id, min(signal_date_time) from signals where signal_date_time >= ‘specific_date_time_value’ group by device_id; There are more where conditions, but essentially I can have millions of rows per device. After my where conditions I know I can have around 100000 devices. I need to be able to do this query in under 1 second for concurrent queries. So far I’ve tried modeling this in: - clickhouse - Postgres - mongodb Any help would be greatly appreciated!

al2o3cr 3y ago
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?
stop50 3y ago
I don't think the database does much if it doesn't get the resources and an schema that supports it. As an relative beginner these are my thoughts: 1. Indices: signal_date_time and device_id should get indices, reading entries is speed up, but writing is a bit slower. 2. Ressources: Ram and cpu are the two most important resources, the database stores records in ram if it can and that speeds up the database. 3. Your tables may benefit from partitioning. So the data is not stored in one table but in multiple and reduces the amount of data that cannot be accessed because some other connection currently writes is lower. The partitions need to be created before the are used.
- daniel56hdz 3y ago
  Yes these tables have indices but it doesn’t mean much once we try to find the earliest dateTime for each device using a group by.
hodgesrm 3y ago
In ClickHouse you could do this with a materialized view provided the specific_date_time_value is on some boundary like hour. If it's not you could union the results from a small fraction of the source data that does not fit the time slot you chose with the materialized view. I would also construct the ORDER BY in the source table to box the device data by hour or some other time increment. Either way this would be extremely fast and would consume minimal memory. ClickHouse was designed for this kind of use case.
- daniel56hdz 3y ago
  Could you please expand on this more? I could really use the understanding!

1 points by daniel56hdz 3y ago

al2o3cr 3y ago

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?

stop50 3y ago

I don't think the database does much if it doesn't get the resources and an schema that supports it. As an relative beginner these are my thoughts: 1. Indices: signal_date_time and device_id should get indices, reading entries is speed up, but writing is a bit slower. 2. Ressources: Ram and cpu are the two most important resources, the database stores records in ram if it can and that speeds up the database. 3. Your tables may benefit from partitioning. So the data is not stored in one table but in multiple and reduces the amount of data that cannot be accessed because some other connection currently writes is lower. The partitions need to be created before the are used.

daniel56hdz 3y ago
Yes these tables have indices but it doesn’t mean much once we try to find the earliest dateTime for each device using a group by.

hodgesrm 3y ago

In ClickHouse you could do this with a materialized view provided the specific_date_time_value is on some boundary like hour. If it's not you could union the results from a small fraction of the source data that does not fit the time slot you chose with the materialized view. I would also construct the ORDER BY in the source table to box the device data by hour or some other time increment. Either way this would be extremely fast and would consume minimal memory. ClickHouse was designed for this kind of use case.

daniel56hdz 3y ago
Could you please expand on this more? I could really use the understanding!