Why Not to Build a Time-Series Database

'metric_name text' is actually a tag-value list. Many TSDB's allows you to match data by tag. Each tag should be represented by a column in your example.

Single table design will be prone to high read/write amplification due to data alignment. Usually, you need to read many series at once so your query will turn into full table scan. Or it will read a lot of unneeded data which happened to be located near the data you need. Writes will be slow since your key starts with metrics name. Imagine that you have 1M series and each series gets new data point every second. In your scema it will result in 1M random writes.

Cardinality of the table will go through the roof, BTW. Every data point will add the key. Good luck dealing with this.

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.

You are talking about regular relational databases. I'm talking about distributed column-oriented databases. Big difference.

You can store tags and other data in JSON/ARRAY columns. The primary key is used for automatically sharding and sorting.

Groups of rows are sorted, split into columns, compressed, and stored as partitions with metadata. This means you can 'scan' the entire table in milliseconds using metadata and then only open the partitions, and the columns inside, that you actually need for your query. There are no random writes either, it's all constant sequential I/O with optional background optimization. And because of compression, storing the same key millions of times has no real overhead.

As stated several times before, we deal with this everyday on trillion row tables inserting 100s of billions of rows daily. Queries run in seconds. We do just fine.

As I was reading through the post I kept wondering why they weren't using some warehousing technique for older data - either dump it to S3 or better yet, Google BigQuery, which is amazingly fast at that scale. They only did it after doing lots of fire-fighting and per-tenant clusters.

Clickhouse would also be a good option for doing aggregating queries that TSDBs are mostly used for.

One of my wishlist items in the data space is a Managed Clickhouse offering. :-)

As the blog author, great to see the discussion and feedback, so appreciate it!

Without going through comments one by one, the main ones about this being a solved problem or there's already solutions out there that do this, I would just say those comments remind me exactly of the type of conversations I had years ago with my team. We all thought it would be much easier or thought there would be something off the shelf that could do everything, and after several years of fire fighting, the reality was the problem looks much simpler than it really is, by a long mile.

Now that we've been doing this for a few years, and spoken directly with creators of many other TSDBs, we take a very skeptical view of all claims made about any database. They all sound amazing when you first read about them, maybe even work great in testing, till you hit scale and then you find all the limitations. If there was a perfect TSDB out there, everyone would be using it and there wouldn't be a new one announced on a weekly basis!

I think the one comment on query loads being different sums things up - I've no doubt all the other options thrown out there work well for data historians, but for monitoring tools with loads of concurrent users, loading dashboards with 10's or 100's of queries each, and alerting systems polling every few seconds in parallel, the query load can get very high quickly, and making those fast while still writing metrics in at scale, is a hard problem and I don't think any individual TSDB has really solved that properly, which is why we ended up building our own distributed architecture ourselves.