Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why Not to Build a Time-Series Database

outlyer.com

Post reply on HN
Print view View original

61–70 of 128 posts

Re: Why Not to Build a Time-Series Database

#61

Post by manigandham »

misframer wrote: post #51
manigandham wrote: post #48

Earlier quoted context omitted.

What features do you disagree about? Cassandra is not a distributed relational column-oriented database, so yes, it will be bad at OLAP queries. Cassandra is a "wide-column" or "column-family" database, which is unfortunately confusing industry jargon but better referred to as an advanced/nested key-value store. It comes from the original Dynamo whitepaper, along with similar systems like HBase, BigTable, DynamoDB, A…

I understand. Can you give an example of a “modern distributed relational column-oriented database”? Two capabilities that are important in my work are roll-ups (reducing resolution of data) and fast bulk deletes of old data.

Clickhouse, MemSQL, Redshift, MapD, Kinetica, etc.

If you just want rollups and don't care about every row, then look at Druid (or imply.io for a startup making it easier).

All these systems can delete old data very quick as they just delete entire compressed partition files.

Re: Why Not to Build a Time-Series Database

#62

Post by misframer »

manigandham wrote: post #61
misframer wrote: post #51

Earlier quoted context omitted.

I understand. Can you give an example of a “modern distributed relational column-oriented database”? Two capabilities that are important in my work are roll-ups (reducing resolution of data) and fast bulk deletes of old data.

Clickhouse, MemSQL, Redshift, MapD, Kinetica, etc. If you just want rollups and don't care about every row, then look at Druid (or imply.io for a startup making it easier). All these systems can delete old data very quick as they just delete entire compressed partition files.

Some of those we’ve looked at before and decided not to go with because of unknown observability, high operational requirements, or cost. But yeah, no real problems with data models or queries.

I think Druid has come the closest to the most ideal system for the requirements I’ve had to deal with, but haven’t used it yet.

Thanks, by the way! This helps a lot.

Re: Why Not to Build a Time-Series Database

#63

Post by manigandham »

camel_gopher wrote: post #56
manigandham wrote: post #30

Earlier quoted context omitted.

All modern columnstores can handle vast ingest rates and query speeds. It's all down to sharding, zone maps and sparse indexing, fast algorithms that operate on compressed data, and storage throughput. These are well-solved problems at this point. Your blog post doesn't mention a single columnstore database though. KDB+, Clickhouse, MemSQL, or any of the GPU-powered variations will happily beat any TSDB out there.

Sure they can handle them, just not in an economically viable fashion.

Compared to what? Economically viable is very vague and relative. Columnar storage can easily reach 90% compression levels, is faster to read, and vectorized processing beats per-row/record iteration, so there's a reason it's the best for OLAP currently.

Why not benchmark IronDB against Clickhouse and post the results?

Re: Why Not to Build a Time-Series Database

#64

Post by manigandham »

chaotic-good wrote: post #57
manigandham wrote: post #30

Earlier quoted context omitted.

All modern columnstores can handle vast ingest rates and query speeds. It's all down to sharding, zone maps and sparse indexing, fast algorithms that operate on compressed data, and storage throughput. These are well-solved problems at this point. Your blog post doesn't mention a single columnstore database though. KDB+, Clickhouse, MemSQL, or any of the GPU-powered variations will happily beat any TSDB out there.

They can't handle high cardinality. Imagine having millions of columns in the column-oriented database (70% of those columns are updated every second). Imagine that you have to add new columns all the time. The main misconception about TSDB's is that it's just a data with timestamp. TSDB's has multi-dimentional data model, time is only one of the dimensions.

You don't need to add new columns.

   CREATE TABLE metrics (metric_name text, ts timestamp, properties json, key(metric_name, ts))
OLAP queries with SQL are very good at handling whatever dimensions you want.

Re: Why Not to Build a Time-Series Database

#65

Post by dominotw »

manigandham wrote: post #11

"time-series database" is some of the most overhyped nonsense since noSQL. Time-series is just data with time as a primary component. It comes in all shapes and volume, but if you have a lot of data and are running heavy OLAP queries than we already have an entire class of capable databases. Use any modern distributed relational column-oriented database, set primary key to metric id + timestamp, and you'll be able to…

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

Re: Why Not to Build a Time-Series Database

#66

Post by manigandham »

dominotw wrote: post #65
manigandham wrote: post #11

"time-series database" is some of the most overhyped nonsense since noSQL. Time-series is just data with time as a primary component. It comes in all shapes and volume, but if you have a lot of data and are running heavy OLAP queries than we already have an entire class of capable databases. Use any modern distributed relational column-oriented database, set primary key to metric id + timestamp, and you'll be able to…

> 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 don't see why that's so special. Other data can have incrementing IDs or some other value. In fact all data can be considered to have a timestamp, at the very minimum being when it was inserted into the database, so it's a rather vague definition overall.

Re: Why Not to Build a Time-Series Database

#67

Post by peatfreak »

tuukkah wrote: post #60

TLDR: "Why Not to Build a Time-Series Database? Because we're building one and you should pay us." > Hopefully our story will make you think twice before trying to build your own TSDB in house using open-source solutions, or if you’re really crazy, building a TSDB from scratch. Building and maintaining a TSDB is a full time job, and we have dedicated expert engineers who are constantly improving and maintaing our TSD…

"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?

Re: Why Not to Build a Time-Series Database

#68

Post by peatfreak »

LukeShu wrote: post #18
djrobstep wrote: post #13

Earlier quoted context omitted.

Folks need to resist the inclination to just gather maximum data for the hell of it. If you're pumping out a million metrics per minute, almost none of those are ever going to actually be used to generate meaningful insight.

I used to work at a startup that made physical robots. The robot generated several GBs of data every time it turned on. You're correct, most of that data wasn't looked at most of the time. But every now and then, someone would say "Hey, I saw a robot do something funny the other day, what the hell happened?" And having all that data usually made it possible to figure out what happened. To me, "maximum data for the he…

> To me, "maximum data for the hell of it" isn't about generating insight by looking at trends, it's about generating insight during incident analysis.

Agree 100%.

Re: Why Not to Build a Time-Series Database

#69

Post by gianm »

manigandham wrote: post #61
misframer wrote: post #51

Earlier quoted context omitted.

I understand. Can you give an example of a “modern distributed relational column-oriented database”? Two capabilities that are important in my work are roll-ups (reducing resolution of data) and fast bulk deletes of old data.

Clickhouse, MemSQL, Redshift, MapD, Kinetica, etc. If you just want rollups and don't care about every row, then look at Druid (or imply.io for a startup making it easier). All these systems can delete old data very quick as they just delete entire compressed partition files.

Fwiw, more recent versions of Druid have a no-rollup mode that does ingestion row-for-row. It ended up being useful for cases where you _do_ care about every row, maybe because you want to retrieve individual rows or maybe because you don't want to define your rollups at ingestion time. And in that mode, Druid behaves like the other DBs you mention.

(I am a Druid committer.)

Re: Why Not to Build a Time-Series Database

#70

Post by manigandham »

ghc wrote: post #47
manigandham wrote: post #28

Earlier quoted context omitted.

I'm not sure where the complexity is that you claim, nor what it has to do with data historians? So what if there are missing rows? This doesn't affect the database and any aggregations will work fine. Databases don't "fill-in" data, but you can definitely write whatever SQL you need to create averages and buckets to smooth out results. From reading your website, it seems you're talking about the "last value recorded…

Relational databases represent a column/row-oriented architecture. Data historians are a specialized, non-relational, time -oriented architecture. Using time as a key in a relational index implies that only ordering is important, but that is not the case. Distance between points in time is extremely important because time operates on a continuous 1d line and data points are represented at varying distances between ea…

Most data/operational historians are separate programs on top of databases, so is that what you're actually talking about? Your papers seem to suggest that.

Stonebraker is talking about OLTP vs OLAP. I agree that they are very different scenarios.

Post reply on HN