Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why Not to Build a Time-Series Database

outlyer.com

Post reply on HN
Print view View original

41–50 of 128 posts

Re: Why Not to Build a Time-Series Database

#41

Post by manigandham »

misframer wrote: post #32
manigandham wrote: post #11

"time-series database" is some of the most overhyped nonsense since noSQL. Time-series is just data with time as a primary component. It comes in all shapes and volume, but if you have a lot of data and are running heavy OLAP queries than we already have an entire class of capable databases. Use any modern distributed relational column-oriented database, set primary key to metric id + timestamp, and you'll be able to…

It really depends. Capabilities like time-series-specific compression, automatic rollups, complex aggregations and/or ranking, stable storage in S3, clustering, and replication vary a lot and I think that's why we see so many TSDBs out there. I maintain a list of TSDBs[0] and it started as an evaluation of what already available for my previous employer to use. We didn't find one that fit our exact use case, so we en…

All the features you mentioned are already part of distributed column-oriented databases. S3 storage is orthogonal and unrelated to time-series data. It's usually not included in shared-nothing local storage architecture of databases but you can definitely mount S3 storage in a variety of ways.

Many options on your list are not TSDBs, like Aerospike, Elasticsearch, Cassandra, Kudu, GridGain/Ignite. EventQL and Riak are obsolete. Apache Apex is a stream processing framework. Many of the others are just extensions to Prometheus built-in mini-storage or offer time-series indexing on top of existing databases.

Re: Why Not to Build a Time-Series Database

#42

Post by car10s »

ghc wrote: post #14
manigandham wrote: post #11

"time-series database" is some of the most overhyped nonsense since noSQL. Time-series is just data with time as a primary component. It comes in all shapes and volume, but if you have a lot of data and are running heavy OLAP queries than we already have an entire class of capable databases. Use any modern distributed relational column-oriented database, set primary key to metric id + timestamp, and you'll be able to…

While I understand your point, you are quite mistaken if you think that time is just another key. Dealing with time properly requires a concept of point distance, similar to GIS systems requiring 2d distance understanding. You cannot do joins on time with SQL databases unless you want to throw away important data. As an example, in the industry I work in, you may have no readings for days or weeks, and then hundreds…

Vertica has this functionality and it has been there for years. Fully functional database and you can do time series joins, gap filling with linear interpolation or constant. You can define the intervals at what you want the data points. And you can scale from a few gigs to petabytes of data. https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/...

Re: Why Not to Build a Time-Series Database

#43

Post by bra-ket »

PopeDotNinja wrote: post #37
bra-ket wrote: post #27

We used a combination of Kafka + Hbase+ Phoenix ( http://phoenix.apache.org/ ) for similar purpose. It takes some effort to setup initial Hbase cluster but once you do it manually once and automate with Ansible /systemd it's pretty robust in operation. All our development was around query engine using plain JDBC/SQL to talk to Hbase via Phoenix. Scaling is as simple as adding a node in the cluster.

Does Kafka have timestamps? I didn't see any when I looked, but I was working with an older client version & didn't get far into it.

we didnt rely on it, or on the ordering of messages received from kafka, timestamps and transaction IDs were generated by the client app/kafka publisher and were part of the message put into kafka topic, when we consume that message with one of the parallel kafka consumers and save a row in hbase table that original timestamp + transactionId becomes part of the rowkey string, other parts being attributes that we wanted to index (secondary indices are supported in hbase/phoenix but we didnt use them too much, basically the composite rowkey is the index). Then when querying hbase it works as a parallel scanning machine and can do a time range scan + filtering +aggregation very fast.

On a separate note we didn't use joins even though they are supported in Phoenix, data was completely denormalized into one big table.

Re: Why Not to Build a Time-Series Database

#44

Post by coder543 »

qaq wrote: post #34
coder543 wrote: post #23

Earlier quoted context omitted.

> No special "TSDB" needed. I think that is a large simplification. I recommend this (relatively) short article: https://blog.timescale.com/timescaledb-vs-6a696248104e The first graph is particularly salient.

You do realize there are say ClickHouse clusters that ingest in a few days more than largest timescale cluster can handle as it's max size.

Considering that TimescaleDB has only been available for a relatively short period of time, I would love to see a source for that statement. It sounds like a really fun article to read.

Since TimescaleDB is creating a new partition for each chunk of time, it should be able to maintain its ingestion rate consistently for as long as you have storage to store that data. Perhaps it won't keep up with distributed, eventually consistent databases, but such databases generally have very limited analytical power, and if you're using them for anything but time series data, that whole "eventual consistency" thing requires a lot of careful thought.

Re: Why Not to Build a Time-Series Database

#45

Post by misframer »

manigandham wrote: post #41
misframer wrote: post #32

Earlier quoted context omitted.

It really depends. Capabilities like time-series-specific compression, automatic rollups, complex aggregations and/or ranking, stable storage in S3, clustering, and replication vary a lot and I think that's why we see so many TSDBs out there. I maintain a list of TSDBs[0] and it started as an evaluation of what already available for my previous employer to use. We didn't find one that fit our exact use case, so we en…

All the features you mentioned are already part of distributed column-oriented databases. S3 storage is orthogonal and unrelated to time-series data. It's usually not included in shared-nothing local storage architecture of databases but you can definitely mount S3 storage in a variety of ways. Many options on your list are not TSDBs, like Aerospike, Elasticsearch, Cassandra, Kudu, GridGain/Ignite. EventQL and Riak a…

> All the features you mentioned are already part of distributed column-oriented databases.

I disagree, but even if that was the case, not all of them perform well. For example, we could've used Cassandra for our use case at my previous employer but the lack of push-down aggregations (at the time, not sure if they're supported now) would've been terrible for our top-K aggregate queries.

Re: Why Not to Build a Time-Series Database

#46

Post by manigandham »

coder543 wrote: post #40
manigandham wrote: post #38

Earlier quoted context omitted.

Yes, I understand this as the "last value recorded" concept in my comment. KDB+ supports this with "asof" joins. Others can just do it by scanning a wider time frame or the entire table. Null gaps in a columnstore can be skipped over basically instantaneously and usually are just zone map/index lookups. Again I question how common this query is and whether it's really worth limiting yourself to a special TSDB because…

> Others can just do it by scanning a wider time frame or the entire table. "Scanning the entire table" for every request to have the last value recorded is rarely a practical option. > KDB+ supports this with "asof" joins. > [...] > Again I question how common this query is and whether it's really worth limiting yourself to a special TSDB because of it. KDB literally markets itself as a time series database. What's…

Distributed relational column-oriented databases are best at large data volumes and OLAP queries. KDB+ is one of those, even though they call it a TSDB in marketing terminology because of its FinTech customer base.

TimescaleDB is not a TSDB, it's an extension to add automatic partitioning to PostgreSQL tables. Timescale helps Postgres get more performance, but it does not give you the full capabilities of a real distributed column-oriented system. If you must use PostgreSQL though then it's a good compromise.

The query you posted does not match the discussion about the last value at a specific instant in time, only the last value ever recorded in the table for that key.

Re: Why Not to Build a Time-Series Database

#47

Post by ghc »

manigandham wrote: post #28
ghc wrote: post #14

Earlier quoted context omitted.

While I understand your point, you are quite mistaken if you think that time is just another key. Dealing with time properly requires a concept of point distance, similar to GIS systems requiring 2d distance understanding. You cannot do joins on time with SQL databases unless you want to throw away important data. As an example, in the industry I work in, you may have no readings for days or weeks, and then hundreds…

I'm not sure where the complexity is that you claim, nor what it has to do with data historians? So what if there are missing rows? This doesn't affect the database and any aggregations will work fine. Databases don't "fill-in" data, but you can definitely write whatever SQL you need to create averages and buckets to smooth out results. From reading your website, it seems you're talking about the "last value recorded…

Relational databases represent a column/row-oriented architecture. Data historians are a specialized, non-relational, time-oriented architecture. Using time as a key in a relational index implies that only ordering is important, but that is not the case. Distance between points in time is extremely important because time operates on a continuous 1d line and data points are represented at varying distances between each other on that line. Data historians are architected to both preserve this temporal relationship and take advantage of this by eliminating duplicate data, employing temporal compression techniques to be able to store millions of readings per second for years worth or data.

> From reading your website

My website doesn't have much to do with this because Sentenai isn't a time series database system. I did, however, spend most of my time in research working on temporal data systems, and have been fortunate to collaborate with or learn from researchers who have spent decades solving the unique problems that temporal data presents. What you might consider uncommon for your use cases is extremely common in manufacturing, defense and other areas.

There's a decades-old industry around database systems that handle time natively. And while many support SQL as a lingua franca, and some are column stores, they're not relational by any means as they either extend SQL to support time, or limit non-temporal joins to ensure performance. StreamBase, Kdb, Aurora and many other specialized architectures exist because one size does not fit all. Michael Stonebraker, whose work has included StreamBase, Vertica, Tamr, Postgres, Aurora, and many others, famously published this paper about the very problem: https://cs.brown.edu/~ugur/fits_all.pdf .

Further reading that might be illuminating:

  1. http://cs.brown.edu/research/aurora/vldb03_journal.pdf
  2. http://www.cs.rochester.edu/u/james/Papers/AllenFerguson-events-actions.pdf
  3. https://books.google.com/books?id=BK6oCAAAQBAJ&pg=PA9&source=gbs_toc_r&cad=4#v=onepage&q&f=false (excerpt)

Re: Why Not to Build a Time-Series Database

#48

Post by manigandham »

misframer wrote: post #45
manigandham wrote: post #41

Earlier quoted context omitted.

All the features you mentioned are already part of distributed column-oriented databases. S3 storage is orthogonal and unrelated to time-series data. It's usually not included in shared-nothing local storage architecture of databases but you can definitely mount S3 storage in a variety of ways. Many options on your list are not TSDBs, like Aerospike, Elasticsearch, Cassandra, Kudu, GridGain/Ignite. EventQL and Riak a…

> All the features you mentioned are already part of distributed column-oriented databases. I disagree, but even if that was the case, not all of them perform well. For example, we could've used Cassandra for our use case at my previous employer but the lack of push-down aggregations (at the time, not sure if they're supported now) would've been terrible for our top-K aggregate queries.

What features do you disagree about?

Cassandra is not a distributed relational column-oriented database, so yes, it will be bad at OLAP queries.

Cassandra is a "wide-column" or "column-family" database, which is unfortunately confusing industry jargon but better referred to as an advanced/nested key-value store. It comes from the original Dynamo whitepaper, along with similar systems like HBase, BigTable, DynamoDB, Azure Table Storage, etc. They can sometimes handle time-series queries with good data modeling because of fast prefix scans but the lack of a real query language makes them a bad choice for analytics scenarios.

Re: Why Not to Build a Time-Series Database

#49

Post by unixhero »

objektif wrote: post #29

Not very knowledgable in the area but can someone please explain how does kdb fit within this class of time series dbs and whether there are any alternatives available to kdb.

Kdb is a priprietary db that runs using the processor level 2 cache as memory. Therefore it is crazy fast. Also therefore, it does not handle large datasets.

Re: Why Not to Build a Time-Series Database

#50

Post by manigandham »

coder543 wrote: post #44
qaq wrote: post #34

Earlier quoted context omitted.

You do realize there are say ClickHouse clusters that ingest in a few days more than largest timescale cluster can handle as it's max size.

Considering that TimescaleDB has only been available for a relatively short period of time, I would love to see a source for that statement. It sounds like a really fun article to read. Since TimescaleDB is creating a new partition for each chunk of time, it should be able to maintain its ingestion rate consistently for as long as you have storage to store that data. Perhaps it won't keep up with distributed, eventua…

Timescale is an extension to add automatic partitioning to PostgreSQL to give you some scalability and performance benefits. It is nowhere near the performance potential of a real distributed column-oriented database, which are strongly consistent, have rich SQL support, and even support transactions.
Post reply on HN