3 ms·
Relational 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
by osigurdson 3y ago
Relational 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.