Comment by gtrubetskoy | Hacker News Reader

Comment by gtrubetskoy | Hacker News Reader

I've been in search of a good time-series database solution for some time now, and have pretty much given up on it and am in the process of rolling my own: https://github.com/grisha/timeriver

My issues with the present state of TS isn't the volume. I was looking for using TS outside of the DevOps world. Everyday things like your heart rate over time, price of gas at the nearest station, number of people in line at your coffee shop, etc. All these are interesting, and Graphite/RRDTool/InfluxDB/etc did not seem like appropriate storage, because to use it with your other data (which is most likely in a relational DB of some kind) you need to export/import it and who wants that.

I call this problem "data seclusion". When data exists in some kind of an incompatible format (e.g. Whisper files), it will end up ignored because of that extra conversion step necessary to link it with your other data. Data in Graphite and such is mostly good for generating charts, but TS analysis is so much more than that, even at its simplest.

I think that the good old relational database is fine storage for TS and we gave up on it way too early, especially given what's new in PostgreSQL. Making it horizontally scalable, distributed, using consensus protocols, etc - these are not time series problems, these are database problems and we do not yet have a good solution for these. (We have many that "kind of" work, support some features but not others e.g. Cassandra). Projects like InfluxDB are mired in solving the wrong problem which will eventually get solved at the DB level.

More thoughts on the subject: http://grisha.org/blog/2015/03/28/on-time-series/ http://grisha.org/blog/2015/09/23/storing-time-series-in-pos... and http://grisha.org/blog/2015/05/04/recording-time-series/

Can't agree more. Time-series data analysis should be considered the most important, not storage. Storage is a solved problem. Let's imagine that we're getting ECG data from many patients. What most time-series databases is able to do with this data? They can draw a nice graph. OK. They can resample this data. Well.. this is mostly meaningless for ECG. Can graphite/opentsdb/whatsoever find specific patterns indicating a disease (for example T-wave inversion)? No, but this is OK, because they're not designed for this. We should build new tools to solve new problems, not the old ones (like monitoring in DevOps).

> Can graphite/opentsdb/whatsoever find specific patterns indicating a disease (for example T-wave inversion)? No, but this is OK, because they're not designed for this. We should build new tools to solve new problems, not the old ones (like monitoring in DevOps).

You know, that sounds like exactly the kind of thing you'd want to do in a DevOps world (leverage your data and new ideas to predict problems).

So, you're not just right that we need to look at timeseries because there are many interesting new problems that we might solve with them, but also because there are still many problems we want to solve in DevOps.

I mean, that's sort of a "solved' solution. DevOps (well they were called greybeard Perl developers, but they effectively filled the role of devops 20 years ago) data munged with Perl and Unix tools to 'leverage' your data (i.e., everything from simple transforms, all the way to complicated aggregate analysis. Did you know you could topologically build a di-graph with an out of the box default UNIX tool 'tsort'? Yeah you can).

It's kind of now accessible to the masses (previously you'd have to pay SAP a few million for BusinessObjects then EMC another million and a half for enough storage to setup a DW), but open source tools have been available for at least a decade. Now it just has a fancy new marketable name "predictive analysis" that Oracle et al can charge an extra couple million for with their price-gouging RAC licenses.

I'm responsible for a solution at $dayjob where I ended up feeding the data into multiple sources. I basically have incoming "events" and they get fed into Sentry (which in turn stores them in MySQL and Redis), InfluxDB (to use with Graphana), and soon Elastic Search (to use with Kibana). Each of these just provides a different way to visualize the data.

I find it confusing that these solutions seem to conflate time series storage/querying with visualization. Is there a reason for this?

I recall that Kibana is a plugin for Elastic Search but don't know about the others. Isn't it possible to connect a hypothetical standalone visualization frontend to any TSDB?

Kibana 3 and earlier was a plugin for Elasticsearch. However, Kibana 4 is a stand alone application. Also, Kibana 4 uses Elasticsearch aggregations instead of Lucene Facets.

Grafana is sort of that. It's a visualization tool that can use a bunch of different TSDBs as the backend. I use it with InfluxDB to track and plot sensor data from my home IoT setup. Basically all sensors (primarily temp because it controls my thermostat) are connected to an isolated WLAN and broadcast do their readings via MQTT. A process on the server collects them and stores them in Influx, which Grafana can use to make nice plots. It's quite easy to use actually.

The TSDBs I tested when I was building this setup (Graphite, OpenTSDB and InfluxDB) all had some sort of basic plotting facilities, but they were extremely minimal and all pointed towards Grafana if you wanted something more substantial.

> sensor data from my home IoT setup

Have you documented your setup anywhere?

I'm DIY-ing something similar and I'm always curious how others have approached things.

I haven't, unfortunately. I'm planning on doing it eventually, but it'll probably be a while. The tl;dr though is a RasPi hosting a local-only wifi network and Mosquitto + ESP8266's everywhere. I was originally going to go for Arudino+NRF24 wireless, but ESP8266 is cheaper, smaller, longer range, simpler and still compatible with the Arduino library.

Thanks for your thoughts Gregory!

We're also in the process now of finding a good storage solution for time series data at my dayjob. We're not storing any server metrics, but more personal user health data and related metrics. So we want the flexibility of being able to write new metrics on your personal timeline without altering a schema. Most reads would be querying single users timeline to fetch their data and deliver through APIs for rendering in clients. Also of course to do analysis on all users timelines, find correlations etc. but those queries are less frequent and not so time critical. I'm starting working on a prototype now with MongoDB. Other DBs that have come up has been InfluxDB, Riak TS, Amazon DynamoDB and probably some others I don't remember. Haven't actually thought that much about Postgres, but thanks for your links to your blog posts I will read up on how Postgres might work. Most other non-time series data would still be in our MySQL setup.

I highly recommend looking at Keogh's work, particularly the iSAX and iSAX2 stuff: http://www.cs.ucr.edu/~eamonn/SAX.htm

While it is used for machine learning, it actually makes a lot of sense to follow a similar approach for more general time series applications.

I'm familiar with SAX, it's remarkably powerful at discerning patterns and identifying similarity between series, good stuff, thanks for the link.

SAX requires different type of storage. You can store SAXified time series in ElasticSearch, or Solr but time-series database doesn't fit for this. Time-series databases should be able to generate SAX representation.

> You can store SAXified time series in ElasticSearch, or Solr but time-series database doesn't fit for this.

That's making some presumptions about the time-series database use case. SAX is convenient for storing and retrieving the data as well as identifying trends or recurring behaviour. What more do you need?

You can't retrieve original time-series data from SAX storage because of normalization. To query time-series data by content (approximate 1-NN, motif discovery, etc) you need inverted index.

The normalized data is the index. It can still be pointing to the raw data. The nice thing is that the index organizes the data in a way that makes it easily (lossless) compressible.

> It can still be pointing to the raw data.

Yep. Each SAX word should be mapped to the list of seriesid:timestamp pairs. This list is often referred as postings list in information retrieval. The resulting data-structure is an inverted index. SAX and iSAX papers describes inverted index variant (really bad one) based on folder structure but one can use convenient IR tools for this.

iSAX2 yields a much better inverted index style model.

The thing is, particularly with time series data, a lot of times it is sufficient to at least start with the summary data in the index.

Reply on news.ycombinator.com