Why Not to Build a Time-Series Database

"Don't do it because it's hard! And you should listen to us because we have the meta-knowledge and experience (now) to know everything there is to know about this topic, plus the bravery to admit in public that we are only human and we make mistakes."

Isn't this the mantra of all of these types of articles?

OK, yes, it usually makes sense. Especially in the case where you are like these guys and experienced enough in some relevant area to know just how difficult it can be. These are perfectly good reasons from technical, business, and project planning perspectives.

Isn't there a TLDR where they mentioned when it does make sense to build your own TSBD? Presumably in some case where you have a team of serious, high-grade experts who know exactly what they are doing; have requirements that cannot be met by any of the other offerings out there; and where the whole thing has been specced out and deemed reasonable?

That's how I read it too. To people who haven't worked with metrics at scale though there is some good information and it's worth reading.

It blows my mind that businesses are willing to outsource metrics. When I worked at Amazon it was trivial to estimate the next quarter's results from the app metrics. Naturally this meant we were subject to trading restrictions.

If a monitoring company ever starts applying Google/Facebook style ethics with regards to exploiting the data their customers give them, they will be in an incredibly powerful position.

Hmmm. I used to be part of a team that handled market data at crazy rates and we took exactly the opposite approach to these guys.

When I see:

"You Can Lose a Few Datapoints Here and There"

I see that these guys are barking the wrong tree.

1. We used single thread per network card. (Yes, we architected clusters/failovers, etc... but not once was it required because of data rates)

2. The server could handle a fully saturated Gibit network at <50% CPU (per core)

3. Data was NEVER thrown away (but we had allowances in our API to let the client reading the data to drop updates and get sub-second aggregates instead -- eg OHLC or summation)

4. Data was stored in basically flat file systems.

5. Our calculation engine was run 'downstream' toward the client ends, or on the client end, away from data collection. If needed (ie. the calcs were expensive to run), these could feed back into the server for long term storage.

This was mid 2000. I'm sure this is not rocket science for modern day timeseries guys.

The premise itself is also quite funny, imo. I think very few people on this planet would think "I need a database so I build one myself". They might do this with the application layer, but most people consider databases black boxes that they interact with through SQL, or maybe not even that. Maybe they just use an abstraction framework in their favorite languages that lets them write objects which have .load() and .save() methods that generate SQL by themselves.

When I'm interviewing a database expert, the one that says:

> "You Can Lose a Few Datapoints Here and There"

is not the one I'm going with...

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.

That's interesting. What are query times like? Let's say for single series to query data for a week at a five-minute interval, how many seconds it would take?

Yes I can confirm data historians like PI are used heavily in my industry (manufacturing).

A solved problem at what scale? A kid with pencil and paper can solve the problem when it's small enough.

Customers using a large-scale analytics database...

Some companies record as much as they can, e.g. "here's all of the user's mouse-movements, their full User Agent, etc." or "here's everywhere the user tapped on the app and which of the notifications we sent them that they responded to, and more!"

The example from the article was "one team at one of our customers decided to dump 30 million metrics on us, send all of their mobile product metrics into Outlyer"

1 mil is only 16K per second.

Transportation companies--truck fleet data, airplanes, etc.

At one employer most customers would opt-in to upload their system logs, and company would analyze to anticipate problems for preventive maintenance.

The result was a tens of terabytes a day Niagara of data.

When I left they were in early stages of Hadoop because ordinary parse/analyze was starting to fall behind.

I use to work at a Fortune 50 retailer on the cloud platform (a lot of tooling around CI/CD for the teams that manage the website). We had a large problem with keeping the metrics pipeline current. A major issue is that be default, Spring Boot publishes about 500 different metrics on a 10 second slice. Allowing every application to pump out that many default metrics, most of which are never used, means that it takes only 334 instances to get one million metrics per minute (1,000,000 / 6 / 500 = 333 1/3). I would guess we had a couple thousand instances in production on a normal day. In a couple weeks, they have Black Friday. Any team that hasn't been able to fix performance problems are given the go ahead to just throw money at it and scale horizontally in obscene ways.

Of course, our metrics were all handled in house. From talking to the teams that handled the metrics pipeline, the vendors were great for smaller companies, but there was no off the shelf solution for a companies that large with that volume. But I did very little with that myself, other than look into the fact that Spring Boot published way too many default metrics. Who needs P50, P70, P75, P80, P85, P90 - P99 on all web requests?! Just set a default that is small and worthwhile and let the developers adjust as needed.

I used to work at Meraki. There, we would, every 5 minutes or so, record byte and packet counts classified by traffic class and remote end of the connection for every single client connected to an SMB internal or customer-facing network.

(Meraki actually did implement its own time-series database, and after I left published a paper describing its design and implementation. https://meraki.cisco.com/lib/pdf/trust/lt-paper.pdf. Good quote on the motivation: "As discussed in Section 2.3.3, customers have a nearly insatiable demand for high-resolution historical data, even though they mostly query data from the recent past."

Machine-generated event telemetry from mobile phones, cars, etc can easily be tens of millions per second in real-world applications. Human-generated event telemetry (e.g. text messaging) peaks at hundreds of thousands per second if you are working on global scales.

There is virtually an unlimited number of applications that could generate 16k events per second (million per minute).

Often per-click stuff ends up with dozens or hundreds of data points from different parts of the code -- heartbeats, feature usage, funnels, experiment entry, etc.

We have customers that generate tens of millions of measurements per second. Lots of low-level systems latencies can be collected at high volume. Also, high volume online services can easily generate this order of magnitude.

16k samples/s is not a lot. There are many Prometheus users with hundreds of thousands of samples/s on a single Prometheus server.

Across their organisations it can be much more, Fastly has reported 2.2M/s (https://promcon.io/2018-munich/slides/monitoring-at-scale-mi...) for example.

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 of readings from the same sensor. Why? Many systems in industrial environments send new readings only "on-change", and assume the underlying data storage architecture will forward fill to in-between times. This is why the practically ancient time series architecture of data historians still dominates in these use cases.

In fact, for many time series functions you actually have to throw away the notion of relational joins to be able to efficiently perform time-preserving joins. Window functions only work in basic use cases with relatively small amounts of data where you're aggregating.

I've had great success using event timestamp ( milliseconds since 1970 ) as a column, with an index on it. And then when you query you can use BETWEEN. If you write your own ORM you can make it automatically calculate the time range according to some defaults - minute, second, etc. Works great.

If you are using spatial data, You can also use two columns like this for longitude and latitude.

There are some unique challenges to storing time series data that are different than those of relational databases. Namely, read/write asymmetry, data safety, data aggregation, and analysis of large data sets.

I wrote in depth about these problems and how different TSDBs solve them here. https://www.irondb.io/2018/08/tsdbs-at-scale-part-one/

Granted its an exceptional case, but the query loads we saw at Datadog were poorly served by off-the-shelf solutions.

Maybe things are different now, but I doubt it. You can spend a fortune to get good performance, or you can deal with slow performance, or you can invest a lot of engineering effort and get both, but there's not a ready-to-use solution that will magically replace an entire engineering team for real scale.

But almost no one ever hits that scale, so maybe it's better to adopt this line as a rule of thumb anyway...

A modern, distributed, relational, column-oriented database will often stress emphatically in the documentation that using timestamps as primary keys is an anti-pattern that's likely to lead to hot tablets: https://cloud.google.com/spanner/docs/schema-design#choosing...

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

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 ended up building our own on top of MySQL.

[0] https://misfra.me/2016/04/09/tsdb-list/

> Time-series is just data with time as a primary component.

Doesn't it have a special feature that data comes in a linearly increasing time dimension.

I think time will change the foundation of a lot of databases, once someone gets it right, and I’m not sure time-series is really it.

We’re currently spending billions trying to build bitemporal public data in Europe, and it’s no easy feat so far.

Basically what we need is to be able to register future data, that don’t come in to play until they are supposed to, as well as keeping a live history that you can spook through to view a data set from any given date, as well as make changes to some past data as if you were there at that date.

You can obviously do so with code, and a lot of the old SAP systems actually support this, but the first DB that handles this well will get to run every single public system.

This works for a small / medium size company but certainly does not scale for bigger companies. There are several problems that you gonna run at scale.

I wouldn't really say that splunk is time-series in the same way they wanted. It's mainly logs indexing, but not the kind of aggregation you'd want from numeric, labelled metrics.

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.

That may be a perfectly good solution if you have a very static infrastructure and narrow use case.

As a thought exercise, for the most trivial solution, you could create a single append only flat file. This may work well for writes, but what happens when you want to read the datapoints for only a single series in time order? This would result in an expensive scan over the whole file. An improvement could be to create a file per series, but this becomes problematic when writing many small datapoints across each different file. The problem worsens in the case of a dynamic containerised infrastructure which produce a unique number of timeseries over very short intervals, which was the catalyst for the development of Prometheus TSDB v2, as the prior version stored a file per timeseries.

As the post states, there is a balance between the read and write pattern - achieving that with a few lines of C for a general purpose case is a difficult task, if not impossible.