3 ms·
At the risk of shoehorning SQLite into this as a solution, would it? Does SQLite handle this type of data (big multidimensional time series sensor data) well?
by xarope 3y ago
At the risk of shoehorning SQLite into this as a solution, would it? Does SQLite handle this type of data (big multidimensional time series sensor data) well?
- jofer 3y agoNo. The issue is that you quickly have trillions of rows. You don't need a relational db setup or fancy non-relationary db solutions. Both are well known anti-patterns. One row per sample falls over quickly for regularly sampled data. What does work for large regularly sampled datasets is array storage solutions like hdf, or image formats, etc etc. It's the same reason and same approach for your vacation photos. You wouldn't put each pixel in the db, but you might very well have a db with a row for each photo. Use a db for the non-regular portions (e.g. an index of datasets) and array storage for the actual data itself.
- osigurdson 3y agoRelational DBs aren't great for timeseries data which is why solutions like TimescaleDB, InfluxDB and other time series databases exist. Of course, it can work perfectly fine if the you don't have too much data. The fundamental problem with time series data is often that the insert pattern is the exact opposite of typical retrieval patterns. For example, you may insert 1000s of properties at once (most efficiently stored as interleaved data), yet typical access patterns involve obtaining a single property over many timestamps. Of course, it can work fine despite the inefficiency. If you have a few 100 sensors and are sampling them every minute, storing data for a month, it would likely be fine. If you plan on storing audio samples as records in a DB (i.e. one record per sample), it will fail. As another commenter mentions, HDF5 doesn't really help much of this. It is an unnecessary rabbit hole to some extent. It mostly just a glitchy b-tree implementation with no WAL log + some compression algorithms.
- zX41ZdbW 3y agoIt's not correct to say that relational DBMS are not suitable for time series. ClickHouse is a relational DBMS, and it works for time series better than specialized systems (like TimescaleDB or InfluxDB). You can quickly get trillions of records for time series, but it is not a problem. For example, see https://www.youtube.com/watch?v=JlcI2Vfz_uk https://www.youtube.com/watch?v=JlcI2Vfz_uk If you try TimescaleDB in this scenario, it will barely be crawling. PS. I am developer of ClickHouse, and I use it for these scenarios every time.
- tpetry 3y agoI guess it was just wrong names of the parent. He shared that OLTP databases are not good for the use-case but OLAP databases designed for analytical purposes like ClickHouse or Timescale are perfect for this. However, Timescale is not a real OLAP database - they are something in-between.
- osigurdson 3y agoWhen I say "Relational DB's aren't great for time series", I mean Postgres, MySQL, Oracle and SQL server just using traditional tables.
- osigurdson 3y agoI'm gonna try ClickHouse since you posted.
- IshKebab 3y agoI've used it for this. It worked way better than it had any right to. Obviously you don't use one sample per row like `jofer` assumed, but you can easily build something on top of SQLite to store compressed blocks of matrices as rows. That's how HDF5 works too (it splits matrices into blocks and compresses them independently). It's obviously a bit more work than relying on HDF5 to do it for you, but not that bad since you don't need to implement a full general solution.