4 ms·

> (...) and to be honest I never completely understood the need for specialized time series databases. The need for specialized time series databases is explai

by rewmie 3y ago

> (...) and to be honest I never completely understood the need for specialized time series databases. The need for specialized time series databases is explained by the basis of any software development process: reuse ready-made high-level abstractions instead of having to reinvent the wheel, poorly. There are a few key features of time series databases that you either go without (i.e., data compression) or have to reinvent the wheel (querying, resolution-based retention periods, statistical summary of time periods,etc). You might live without those features,but in some applications there's a hefty price tag tied to not having them. If you adopt a time series database, you benefit from all those features without having to implement a single one of them. Perhaps that has no value to you, but it does have for the world.

starfallg 3y ago
I think what OP was getting at is why it has to be specialized. Time-series features can be implemented in a general purpose DB engine like postgres or mongo.
- nonameiguess 3y ago
  How? I just tried to look for time series in Postgres and it looks like TimescaleDB is the plugin you'd want? At least web search seems to love it. Their landing page is just marketing fluff and says next to nothing about their feature set. In a database that isn't column-oriented, how can they do automatic downsampling? Or do they just not?
  - hnmullany 3y ago
    You might want to check out their columnar compression & their continuous aggregate blog posts: https://www.timescale.com/blog/timescaledb-2-3-improving-columnar-compression-for-time-series-on-postgresql/ https://www.timescale.com/blog/timescaledb-2-3-improving-col... https://www.timescale.com/blog/massive-scale-for-time-series-workloads-introducing-continuous-aggregates-for-distributed-hypertables-in-timescaledb-2-5/ https://www.timescale.com/blog/massive-scale-for-time-series...
    - lordnacho 3y ago
      Have used it for finance, can recommend. No need for kdb, at least for now. Their Slack is very responsive too, top service.
- marginalia_nu 3y ago
  They kinda can't though, not without losing a lot of performance and capability. Time series databases should be viewed in the context of being a fairly niche product however. The archetypal use case is in finance where you have a constant firehose of tick data and trades across a wide array of instruments you need to be able to index instantaneously and aggregate easily. The sheer volume of data can't be overstated here. To make matters worse, the records are often both wide and sparsely populated. A non-specialized really DBMS doesn't cut it in this case. You need to build the DBMS from the ground up to cater to this sort of a usecase. Slapping a columnar backing store onto something existing does not cut it.
- nhourcard 3y ago
  Time-series features can be implemented in a general purpose DB engine, but the architecture of the database will be limiting for lots of use cases requiring performance, especially for high cardinality datasets. A columnar database engineered from the ground up for time-series should have better foundations to allows fast ingest (to the tune of million of rows/sec per server) and also be very efficient for time-based queries that can be done via languages such as SQL. Using QuestDB as an example: Data is stored in chronological order and is optimized for sequential ingestion with a timestamp component (re-ordering data on the fly if it comes out-of-order). The data is also partitioned by time. The InfluxDB Line Protocol is better suited for streaming type of ingest versus transactional inserts via Postgres.
weinzierl 3y ago
I'm curious to research if a dedicated TSDB is worth it for my project. Not to waste time could you recommend one that in your opinion would be worth it over just Postgres? I used InfluxDB a couple of years a ago but didn't really see the value, even though I loved the product. Should I give it another chance? Application is storage of a diverse set of (irregular) events. Lifecycle management and archival is more important than compression. Volume is moderate.
- kvmet 3y ago
  I'll spare you my rant on why I don't like InfluxDB lol. In the end it depends how much data you're dealing with. If you're only ingesting dozens to hundreds of records per second per writer you might actually like AWS Time stream. If you're dealing with a million + rows per second from a low number of writers (e.g. scientific data) then you probably want Influx or Timescale. If you want to host it yourself and you have less than 1TB of data then Influx is okay or if you want it cloud hosted then Influx may be a good option. If you need full control, scalable, TB or PB of data, etc. Then Timescale is great but it's definitely more work.
  - weinzierl 3y ago
    Thanks s lot, that is a really helpful reply. I'm curious on your concerns about InfluxDB. I mean not to bash them, but it really comes down to trade-offs and it's always good to know the things they don't emphasize in their marketing.
- zX41ZdbW 3y ago
  There is no need for specialized time-series databases, because normal OLAP databases work better for time-series. I see it from my experience with ClickHouse. It might sound paradoxical, but specialized time-series databases, such as TimescaleDB or InfluxDB, are not good at time-series, compared to normal (boring) OLAP databases.
- jononor 3y ago
  We used standard Postgres for 200k rows/day (100M rows/year) time series. Worked just fine, even without partitioning. Maybe use partitioning (on time) from the start though, it was a bit tedious to migrate to it later. Now handling 10x these rates, probably good for another 10-100x. We have TimeScaleDB extension enabled now, but not using that many of the features yet, mostly stock Postgres.

by rewmie 3y ago

starfallg 3y ago

I think what OP was getting at is why it has to be specialized. Time-series features can be implemented in a general purpose DB engine like postgres or mongo.

nonameiguess 3y ago
How? I just tried to look for time series in Postgres and it looks like TimescaleDB is the plugin you'd want? At least web search seems to love it. Their landing page is just marketing fluff and says next to nothing about their feature set. In a database that isn't column-oriented, how can they do automatic downsampling? Or do they just not?
- hnmullany 3y ago
  You might want to check out their columnar compression & their continuous aggregate blog posts: https://www.timescale.com/blog/timescaledb-2-3-improving-columnar-compression-for-time-series-on-postgresql/ https://www.timescale.com/blog/timescaledb-2-3-improving-col... https://www.timescale.com/blog/massive-scale-for-time-series-workloads-introducing-continuous-aggregates-for-distributed-hypertables-in-timescaledb-2-5/ https://www.timescale.com/blog/massive-scale-for-time-series...
  - lordnacho 3y ago
    Have used it for finance, can recommend. No need for kdb, at least for now. Their Slack is very responsive too, top service.
marginalia_nu 3y ago
They kinda can't though, not without losing a lot of performance and capability. Time series databases should be viewed in the context of being a fairly niche product however. The archetypal use case is in finance where you have a constant firehose of tick data and trades across a wide array of instruments you need to be able to index instantaneously and aggregate easily. The sheer volume of data can't be overstated here. To make matters worse, the records are often both wide and sparsely populated. A non-specialized really DBMS doesn't cut it in this case. You need to build the DBMS from the ground up to cater to this sort of a usecase. Slapping a columnar backing store onto something existing does not cut it.
nhourcard 3y ago
Time-series features can be implemented in a general purpose DB engine, but the architecture of the database will be limiting for lots of use cases requiring performance, especially for high cardinality datasets. A columnar database engineered from the ground up for time-series should have better foundations to allows fast ingest (to the tune of million of rows/sec per server) and also be very efficient for time-based queries that can be done via languages such as SQL. Using QuestDB as an example: Data is stored in chronological order and is optimized for sequential ingestion with a timestamp component (re-ordering data on the fly if it comes out-of-order). The data is also partitioned by time. The InfluxDB Line Protocol is better suited for streaming type of ingest versus transactional inserts via Postgres.

weinzierl 3y ago

I'm curious to research if a dedicated TSDB is worth it for my project. Not to waste time could you recommend one that in your opinion would be worth it over just Postgres? I used InfluxDB a couple of years a ago but didn't really see the value, even though I loved the product. Should I give it another chance? Application is storage of a diverse set of (irregular) events. Lifecycle management and archival is more important than compression. Volume is moderate.

kvmet 3y ago
I'll spare you my rant on why I don't like InfluxDB lol. In the end it depends how much data you're dealing with. If you're only ingesting dozens to hundreds of records per second per writer you might actually like AWS Time stream. If you're dealing with a million + rows per second from a low number of writers (e.g. scientific data) then you probably want Influx or Timescale. If you want to host it yourself and you have less than 1TB of data then Influx is okay or if you want it cloud hosted then Influx may be a good option. If you need full control, scalable, TB or PB of data, etc. Then Timescale is great but it's definitely more work.
- weinzierl 3y ago
  Thanks s lot, that is a really helpful reply. I'm curious on your concerns about InfluxDB. I mean not to bash them, but it really comes down to trade-offs and it's always good to know the things they don't emphasize in their marketing.
zX41ZdbW 3y ago
There is no need for specialized time-series databases, because normal OLAP databases work better for time-series. I see it from my experience with ClickHouse. It might sound paradoxical, but specialized time-series databases, such as TimescaleDB or InfluxDB, are not good at time-series, compared to normal (boring) OLAP databases.
jononor 3y ago
We used standard Postgres for 200k rows/day (100M rows/year) time series. Worked just fine, even without partitioning. Maybe use partitioning (on time) from the start though, it was a bit tedious to migrate to it later. Now handling 10x these rates, probably good for another 10-100x. We have TimeScaleDB extension enabled now, but not using that many of the features yet, mostly stock Postgres.