Why Not to Build a Time-Series Database • Hacker News Board

Re: Why Not to Build a Time-Series Database

#51

Post by misframer » Thu, Nov 08, 2018, 3:59 AM UTC

manigandham wrote: post #48

misframer wrote: post #45

Earlier quoted context omitted.

> All the features you mentioned are already part of distributed column-oriented databases. I disagree, but even if that was the case, not all of them perform well. For example, we could've used Cassandra for our use case at my previous employer but the lack of push-down aggregations (at the time, not sure if they're supported now) would've been terrible for our top-K aggregate queries.

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.

Re: Why Not to Build a Time-Series Database

#52

Post by ghc » Thu, Nov 08, 2018, 3:59 AM UTC

manigandham wrote: post #46

coder543 wrote: post #40

Earlier quoted context omitted.

> Others can just do it by scanning a wider time frame or the entire table. "Scanning the entire table" for every request to have the last value recorded is rarely a practical option. > KDB+ supports this with "asof" joins. > [...] > Again I question how common this query is and whether it's really worth limiting yourself to a special TSDB because of it. KDB literally markets itself as a time series database. What's…

Distributed relational column-oriented databases are best at large data volumes and OLAP queries. KDB+ is one of those, even though they call it a TSDB in marketing terminology because of its FinTech customer base. TimescaleDB is not a TSDB, it's an extension to add automatic partitioning to PostgreSQL tables. Timescale helps Postgres get more performance, but it does not give you the full capabilities of a real dist…

> Distributed relational column-oriented databases are the best at large OLAP data volumes and queries. KDB+ is one of those, even though they call it a TSDB in marketing terminology because of its FinTech customer base.

You're mistaken about Kdb's relational features. Kdb was designed as a time series processing engine using arrays (columns). Column storage doesn't have anything to do with whether a database is relational or not, and Kdb wasn't originally any more relational than the language Erlang is.

Re: Why Not to Build a Time-Series Database

#53

Post by qaq » Thu, Nov 08, 2018, 4:00 AM UTC

coder543 wrote: post #44

qaq wrote: post #34

Earlier quoted context omitted.

You do realize there are say ClickHouse clusters that ingest in a few days more than largest timescale cluster can handle as it's max size.

Considering that TimescaleDB has only been available for a relatively short period of time, I would love to see a source for that statement. It sounds like a really fun article to read. Since TimescaleDB is creating a new partition for each chunk of time, it should be able to maintain its ingestion rate consistently for as long as you have storage to store that data. Perhaps it won't keep up with distributed, eventua…

google is you friend google clickhouse vertica etc. the comment about limited analytical power is especially fun. Cloudflare is ingesting 11 million rows per second into CH.

Re: Why Not to Build a Time-Series Database

#54

Post by manigandham » Thu, Nov 08, 2018, 4:00 AM UTC

coder543 wrote: post #39

manigandham wrote: post #35

Earlier quoted context omitted.

Timescale, for all their wonderful marketing, is just an automatic sharding extension for PostgreSQL. You can accomplish the same yourself using native partitioning, or pg_partman, or Citus. Partitions are a basic building block for scaling performance and storage so it helps when you have lots of data, but Postgres w/Timescale does not have column-oriented storage and is still single-node only so it comes nowhere ne…

> Timescale, for all their wonderful marketing, is just an automatic sharding extension for Postgres database. You can accomplish the same yourself using native partitioning, or pg_partman, or Citus or any number of other tools. Put another way... "Postgres, for all their wonderful marketing, is just an automatic data organization system for . You can accomplish the same yourself using open, read, write, or any numbe…

Not really what I was saying at all.

Timescale adds automatic partitioning to Postgres, a single-node rowstore relational database. This will naturally give you better performance for larger data (whether time-series or not).

This will not approach the performance and scalability of a fully distributed relational column-oriented database like Clickhouse or MemSQL, because automatic partitioning is just one of many techniques they use for fast performance. There is nothing a special TSDB, or TSDB extension, can do that these database cannot already do faster, while providing rich SQL and joins.

Re: Why Not to Build a Time-Series Database

#55

Post by bigger_cheese » Thu, Nov 08, 2018, 4:02 AM UTC

marshf wrote: post #16

Time-series data handling/storage seems a mostly solved problem in the mining, oil, and manufacturing industries. Deployed in the field since the 80's, https://www.osisoft.com/about-osisoft/#more-about-pi-system Discourse: Industry user now OSIsoft employee

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

Re: Why Not to Build a Time-Series Database

#56

Post by camel_gopher » Thu, Nov 08, 2018, 4:05 AM UTC

manigandham wrote: post #30

camel_gopher wrote: post #20

Earlier quoted context omitted.

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/

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.

Re: Why Not to Build a Time-Series Database

#57

Post by chaotic-good » Thu, Nov 08, 2018, 4:05 AM UTC

manigandham wrote: post #30

camel_gopher wrote: post #20

Earlier quoted context omitted.

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/

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.

Re: Why Not to Build a Time-Series Database

#58

Post by ghc » Thu, Nov 08, 2018, 4:06 AM UTC

car10s wrote: post #42

ghc wrote: post #14

Earlier quoted context omitted.

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…

Vertica has this functionality and it has been there for years. Fully functional database and you can do time series joins, gap filling with linear interpolation or constant. You can define the intervals at what you want the data points. And you can scale from a few gigs to petabytes of data. https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/...

I agree! Vertica's temporal capabilities are marvelous and the engineers who worked on the planner optimizations for the time extensions are brilliant.

There are of course trade-offs to the approach Vertica takes -- look at StreamBase for a very different take on the problem, another Stonebraker project.

Any of course historians represent yet another take, optimized for point-in time queries that are native and don't need the processing extensions Vertica uses.

Re: Why Not to Build a Time-Series Database

#59

Post by manigandham » Thu, Nov 08, 2018, 4:08 AM UTC

cdoxsey wrote: post #21

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…

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 sc…

I think Clickhouse would do well but I've seen other metrics/observability vendors (like Honeycomb) also build their own systems given the scale and cost factors.

Isn't Datadog on AWS? If you have very specific needs and can build a vertical infrastructure stack then it makes perfect sense to build your own.

Re: Why Not to Build a Time-Series Database

#60

Post by tuukkah » Thu, Nov 08, 2018, 4:09 AM UTC

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 TSDB, and no doubt will iterate the architecture again over time as we hit an even higher magnitude of scale down the line.

> Given our experience in this complex space, I would sincerely recommend you don’t try and do this at home, and if you have the money you should definitely outsource this to the experts who do this as a full time job, whether its Outlyer or another managed TSDB solution out there. As so many things turn out in computing, it’s harder than it looks!