Hacker News new | ask | show | jobs
by amluto 37 days ago
Wow, Home Assistant should try something along these lines. Home Assistant’s current handling of time series data is comically poor.

Another decent option might be Clickhouse. Sadly, as far as I know, DuckDB has no real understanding of sorted or ordered data, so it might be challenging to avoid absurd amounts of read amplification.
1 comments
ul5255 37 days ago
I have several years worth of timestamped sensor data in a SQlite DB that is 12+GB and growing. One idea was to experiment with DuckDB. Your comment about DuckDB has me worried as time ranged queries are common. Any link for me to dig deeper?
link
amluto 37 days ago
Not a specific link, but a DuckDB table is a bag of rows (logically — it’s physically arranged as a column store), and those rows are not ordered in a way that is expressed in the schema. If you do a big analytic query, DuckDB will (extremely efficiently) scan the whole table and will blow many other tools out of the water while doing so. But if you want to see the sensor value of a specific sensor at a specific time, you want an index of some sort, not a full table scan. And if you want to do a rollup of some but not all sensors, you end up modifying some stuff in the middle of a table, which is not amazingly efficient. DuckDB has an optional index, but I don’t think it’s meant for this.

You could certainly create a directory with a Parquet file for each (entity id, time range), and you could probably convince the DuckDB query engine to understand that (using Ducklake? raw Hive can only barely do this), but I don’t think that DuckDB will binary search for you. (And binary search is actually pretty lousy for this use case.)

Clickhouse has explicitly ordered tables:

https://clickhouse.com/docs/engines/table-engines/mergetree-...

link
pjot 37 days ago
DuckDB has indexes. One specifically for highly selective point lookups. The structure is called an adaptive radix tree. The workloads you’re describing are sort of duckdb’s bread and butter (even the parquet stuff it’ll handle out of the box)

https://duckdb.org/2022/07/27/art-storage

link