It’s About Time for Time Series Databases

/former Basho employee.

The important thing is balancing chasing the new hotness vs. always using the tried and true.

Last year I ran into a similar sort of situation at work, the funds to buy Ansible Tower required a battle I wasn't willing to fight. The argument was "We've spent so much money on tools that then get abandoned." We spent nearly a year with me being the only one in the company that could run Ansible playbooks because of this, until I finally found RunDeck. Now, we aren't paying for Rundeck, does that make it more likely to be abandoned? Was the risk in our operations if I suddenly became unavailable worth the saved licensing fees? Well, in this case it worked out ok, but it could have gone very badly.

As far as timeseries goes, there are huge advantages here. We used to use collectd/graphite/carbon, but a year or more ago I switched us over to InfluxDB/telegraf/grafana. It was a little rocky a start, the first version of Influx I used had plenty of problems. At the time Prometheus was probably a better choice, but I couldn't come up with a way to make it's pull-centric design fit into our environment. Then InfluxDB settled down and things have been great since.

We have way better metrics, with a way more usable dashboard, and significantly lower load. Literally, disabling collectd ended the complaints about performance on our dev/stg environments.

It worked out pretty well for Oracle. (SCNR)

As an open source product it seems like TimescaleDB is less risky than Datomic. (Not that the comparison is even necessary -- I don't think they compete in the same category apart from both being non-relational DBs.)

[edit:link fixed, thanks! (and thanks for taking the time to correct my misunderstanding re:TimescaleDB as a relational DB)]

Well, I guess you can stick to DB2.

But try telling people you're using SQLite to store critical data...

The first question is tied closely to the interfaces provided by the database, and those interfaces are usually commensurate with the associated tooling. In the case of time series databases, they include statsd, collectd, and good old JSON. Relational databases utilize SQL as expected. Take the example of migrating from Oracle to Postgres; you shouldn't have to spend an inordinate amount of retooling your applications because the data interfaces are similar.

Data egress is similar; SQL databases generally provide SQL/CSV exports, and most TSDBs (the good ones at least) allow you to get the data out via JSON through a REST interface.

Whether or not the DB you choose is open source, or from a small vendor, you should pick the right tool to solve your problem with data portability in mind. (disclosure, I work for http://irondb.io, and have used Postgres for 18 years)

Played with a lot of databases, sql or no, and I always end up going back to PG

Telegram channel https://t.me/clickhouse_en

[0] - https://en.wikipedia.org/wiki/Curse_of_dimensionality

With TimescaleDB we've focused on single node performance to try and reduce the need for clustering. We've found performance scales very well just by adding more disk space if needed and more cores. So maybe some datasets are not practical for your laptop necessarily but a single instance on Azure/AWS/GCP is workable. No need for a cluster to get started :)

(Read scale out is available today and we are working on write scale out, hopefully later this year)

The "let's store everything" isn't being done for fun; it is rather expensive. For sophisticated sensor analytics though, it is essentially table stakes. There are data models where it is difficult to get reliable insights with less than a 100 trillion records. (Tangent: you can start to see the limit of 64-bit integers on the far horizon, same way it was with 32-bit integers decades ago.)

It's intro computer science, if you have a tree structure and fill it up, you spend a lot of time in the corners of theta notation. Timescale uses tightly integrated partitioning on the time axis to deal with write performance and aging data out. Other popular TSDBs are plays on log structured merge trees etc

I also am using Influx at my company and the experience is not that great, mostly due to it being immature yes. For example, I currently have runaway disk usage and I have no way to know what table is using it. So I have to choose between losing data or continuously increasing storage.

We also had a problem where user queries that return too many columns cause the DB server process to OOM. And then it restarts, so another 20 mins of downtime. Also not cool.

We liked the tagging and rollup features, and automatic retention management, but those first 2 problems really turned us off.

Every time I have to store time series data, I never really need ACID transactions. I definitely don't ever need to do updates or upserts. It's always write-once-read-many. ElasticSearch has always been the obvious choice to me and it has worked extremely well. Information retrieval is incredibly robust and for times that I'm worried about consistency, I use Postgres's JSON capabilities and write first to there. You can have your application sanity-check between the two if you're worried about ElasticSearch not receiving/losing data.

I find it really hard to beat.

1. For some developers, just having a SQL interface to time-series data (while maintaining insert/query performance at scale) is good enough reason to use TimescaleDB. For example, when trying to express complex queries, or when trying to connect to SQL-based visualization tools (e.g., Tableau), or anything else in the PostgreSQL ecosystem.

2 For others, the difference is being able to fully utilize JOINs, adding context to time-series data via a relational table. Other time-series DBs would require you to denormalize your data, which adds unnecessary data bloat (if your metadata doesn't change too often), and data rigidity (when you do need to change your metadata).

To quote a user in our Slack Support forums[1]: "Retroactively evolving the data model is a huge pain, and tagging “everything” is often not viable. Keeping series metadata in a separate table will allow you to do all kinds of slicing and dicing in your queries while keeping it sane to modify the model of those metadata as you gain more knowledge about the product needs. At least that is something we have been successful with"

3. For others, it's some of the other benefits of a relational database: e.g., arbitrary secondary indexes.

One thing to think about with NoSQL systems (including every other time-series db) is that their indexing is much more limited (eg, often no numerics) or you need to be very careful about what you collect, as costs grow with cross-product of cardinality of your label sets (strings). We have heard from multiple people that they explicitly didn't collect labels they actually wanted when using [another time series db], because it would have blown up memory requirements and basically OOM'd DB. (Timescale doesn't have this issue with index sizes in memory, and you can create or drop indexes at any time.) You often hear about this as the "cardinality problem", which TimescaleDB does not have.

4. There's also the benefit of being able to inherit the 20+ years of work of reliability on Postgres.

There was no database that did all of this when we decided to build Timescale. (If one did exist, we would have used it).

You can read more about that story here: https://blog.timescale.com/when-boring-is-awesome-building-a...

And for more on our technical approach: https://blog.timescale.com/time-series-data-why-and-how-to-u...

[1] http://slack-login.timescale.com/

It might be bad for the specific organization pushing their new solutions, but for the software industry as a whole, it seems like the good side of "throw spaghetti against the wall and see what sticks". Many ideas will fail, but the good ones will stick around. So I'd say by all means, let people feel free to innovate. And those of us who consume the innovations just need to remember the mantra of "leading edge, not bleeding edge."

Changes in telemetry production (IoT and others) have fundamentally changed the requirements... millions (and often billions) of data points points per-second are now happening. This is being driven by RSM and IoT. RSM is alluded to in my ACM Queue article: https://queue.acm.org/detail.cfm?id=3178371

And we picked Postgres as our starting point exactly because it wasn't new and shiny but boring and it worked.

More on this here [1] if you're interested.

[1] https://blog.timescale.com/when-boring-is-awesome-building-a...

1. It seems to not be optimized for speed (but it's difficult to say since the license forbids publishing benchmarks).

2. It's not open source.

The solution to larger processing simply seems to be loading the files and batching them through whatever processing we want and storing the output in a proper database - which can then be queried instantly.

For a lot of time series data, this does seem like a pretty decent approach.

You can also use any column-oriented relational database with a time-based partition key and do the same thing.

https://en.wikipedia.org/wiki/Operational_historian

At least in my experience, historians are rarely recommend for complex or ad-hoc queries. Typically you just pull the data (by tags) into another application and do your data processing there. It looks like in timeseriesdb lets you execute complex queries in the database. Historians typically only let you fetch data by tag and you need a metadata (e.g. asset management) framework on top to organize the data (e.g. give me avg temp every 5 minutes by sensor). It looks like with timeseriesdb you can have strings/text as fields within the timeseries table, which removes the need (to some degree) to join the data with a seperate metadata database.

I've also never heard of anybody using these commercial historians for time series data you'd see from non industrial processes (e.g. stock data, price tracking, GPS location of people or moving assets, time between clicks on a website,etc).

All that being said, OSISoft PI and AF have their warts, but OSISoft has been around for a while and PI has been battle tested in various industries (e.g. Oil & Gas, Manufacturing). It's closed source and you have to pay for it, so it's probably not attractive to startups and smaller companies. But it does come with a support organization if you need it and can pay for it. And IME data retrieval from PI is extremely performant!