Hacker News Reader

Comment by rewmie | Hacker News Reader

> (...) 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.

View on HN

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.

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?

You might want to check out their columnar compression & their continuous aggregate blog posts:

https://www.timescale.com/blog/timescaledb-2-3-improving-col...

https://www.timescale.com/blog/massive-scale-for-time-series...

Have used it for finance, can recommend. No need for kdb, at least for now.

Their Slack is very responsive too, top service.

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.

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.

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.

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.

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.

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.

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.

Reply on news.ycombinator.com