Thoughts on Time-series Databases

I'm surprised that PI[0] isn't mentioned in this thread. It's been around since the 80s and it's ubiquitous in the utility industry for recording SCADA data.

[0]: https://en.wikipedia.org/wiki/OSIsoft

VoIP capture was one of the functions in the system too. But it had others. It looks like your implementation is more sophisticated, I didn't do any delta-encoding. Just periodic fsyncs. The maximum we tested was up to 1TB/day.

Main index ( [(timestamp,offset_in_data)] was simply synced every second. It just looked like a [uint64|uint32|...]. Then there is an advanced service with different plugins, runs in a separate process, trails the main data stream. Each plugin can then write out its own custom index data to file and is also saving its internal state (so it can recover during a hard crash).

At Stack Exchange our monitoring system bosun (http://bosun.org) can use different time series databases as long as they can be bent into tag key+tag value models. Currently it works best with OpenTSDB, but can also support graphite (and elasticsearch populated by logstash). InfluxDB query support is in a branch, but don't want to merge until we have a devoted Bosun+InfluxDB maintainer since we don't use it at Stack currently.

Based on that experience, plus from conversations at Monitorma the other week here is what I think of the current state of some various TSDBs are. Some of this might just be lies or rumor - so take it at at that:

* OpenTSDB: Requires HBase behind it, so that can be a pain for people. Maintenance on it is sparse, it doesn't seem like the project has a shortage of contributors with the time needed. Stability isn't great (connection errors from time to time, having alerts based on querying OpenTSDB highlights this). Aggregation and downsampling don't behave as expected. For example rate derivatives happen too late in the order of operations - linear interpolation can be strange. Also to query metric with anything many tag combinations over more than a recent interval of time (say a month or more) is basically impossible - OpenTSDB memory blows up and GC dominates. This requires one to create additional metrics that are denormalized for this. This is kind of okay because OpenTSDB is incredibly storage efficient at storing time series data. No support for NaN. OpenTSDB has quite a bit of serious users https://github.com/OpenTSDB/opentsdb/wiki/Companies-using-Op.... It can ingest a lot of metrics at a high rate without issue.

* KairosDB: Not much experience here. From what I gather it is like OpenTSDB but for Cassandra. Someone mentioned that they thought they heard some core devs have gone to work at Influx which might be concerning - but I don't know if that is true. But same issue of having to run Cassandra if you don't already.

* Graphite: Very rich query language, but currently not a key / value model. Also is not very storage efficient so the approach is that data gets rolled up after a certain period of time - generally problematic for forecasting.

* InfluxDB: Looks promising, but I heard from multiple people "Tried influxdb - was cool but all my data corrupted and I couldn't recover it" at Monitorama. The general concern at Montiroma was that they are overestimating their stability currently when it comes to a production environment. Based on some basic testing at Stack, we found it to be much slower and take up a lot more space than OpenTSDB.

In summary there is no great choice today. More of a pick your pain and best fit situation. But I'm really curious what people with actual experience in these technologies can add to the tradeoffs and am hopeful for the future.

By "limit" I mean your code would do it, not Postgres. E.g. your series is 86400 datapoints long (seconds in a day), you would store it as 100 rows of 864-element-long arrays.

On the performance problem, my guess is that you wrote in a bunch of columns and had those in where clauses. I hope the documentation made it clear that you'd be range scanning over data and that you probably wouldn't get desirable performance.

In 0.8 and before, the preferred way to model your data was to create many separate series names. This method is currently giving many users great performance.

As with all databases, how you model your schema has a significant impact on performance.

That being said, it might be that Influx wasn't right for your use case and Postgres is just a better thing to go with.

Also, we're not supporting anything prior to the 0.9 series of releases. 0.8 is deprecated and we'll be pushing everyone to move over to 0.9 as we put out more point releases and fix bugs and ensure that it works for their use case over the coming months.