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

91–100 of 128 posts

Re: Why Not to Build a Time-Series Database

#91

Post by cmroanirgo »

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…

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

Re: Why Not to Build a Time-Series Database

#92

Post by bbrazil »

statictype wrote: post #2

Nice article. >its not uncommon for some of our customers to send us millions of metrics every minute What kind of customers/services generate millions of points a minute?

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.

Re: Why Not to Build a Time-Series Database

#93

Post by erikb »

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…

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.

Re: Why Not to Build a Time-Series Database

#94

Post by davidjc1 »

OldHand2018 wrote: post #10

It's pretty ridiculous that "Time-Series Database" has come to mean ingesting massive amounts of streaming data. They've been around a long time and have many use cases. They're a great way to store data efficiently, accessing specific data if you know the time range you are looking for is very fast and simple, and you can roll your own in a few dozen lines of C if that's what you want to do. If that's all you need,…

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.

Re: Why Not to Build a Time-Series Database

#95

Post by CoolGuySteve »

cmroanirgo wrote: post #91
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…

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

Yeah, it's still pretty much the same just at 10 or 40 gbit now.

Hardware capture almost never drops and timestamps with GPS sync.

You can then take those capture files and manipulate them however you want into normalized market data.

Market data has the notable feature of being segmented by trading day, so the combination of symbol-venue-date is an appropriately small unit of data to run aggregations of any kind over or to distribute over a cluster.

So for market data at least, there's not much to "rolling your own" time series DB in Python or what-have-you.

Prcessing that firehouse in real time for trading is a different matter though and how you build that depends heavily on your latency requirements.

Re: Why Not to Build a Time-Series Database

#96

Post by cdoxsey »

manigandham wrote: post #59
cdoxsey wrote: post #21

Earlier quoted context omitted.

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.

Yeah AWS, though mostly just EC2.

I think the challenge is that there are multiple competing needs which are in tension. Data isn't uniform, a large write load that's almost never queried, recent data is accessed way more often than older data, flexible tagging means (org,metric) queries produce potentially millions of points (imagine disk usage across every node for every disk), but indexing tags can be very costly, and its difficult to predict what someone is going to want to query.

I agree that hyper-focus on those needs can distort the picture though. You don't actually have to solve them most of the time, and a relatively poorly optimized solution goes a lot further than people realize. Simply adding caching, for example, solves almost all these issues.

Anyway I mostly agreed with your opening comment.

Re: Why Not to Build a Time-Series Database

#97

Post by geocar »

chaotic-good wrote: post #72
manigandham wrote: post #64

Earlier quoted context omitted.

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.

'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 yo…

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

For the life of me, I can't figure out why this would be a good idea. I feel like I must not understand what you're saying:

If I've got a million disks that I want to draw usage graphs for, why I would put each one in a separate column?

What's the business use-case you're imagining?

> Usually, you need to read many series at once so your query will turn into full table scan.

Why do I need a full table scan if I'm going to draw some graphs?

I've got something like 4000 pixels across my screen; I could supersample by 100x and still be pulling down less data than the average nodejs/webpack app.

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

No that's definitely not what manigandham is suggesting. One million disks each reporting their usage means a million rows in two columns (disk name/sym, and volume) would be written (relatively) linearly.

Re: Why Not to Build a Time-Series Database

#98

Post by geocar »

ghc wrote: post #52
manigandham wrote: post #46

Earlier quoted context omitted.

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

> Kdb wasn't originally any more relational than the language Erlang is.

To wit: SQL wasn't originally any more relational[1] than the language Erlang is.

[1]: Codd, E. F. A Relational Model of Data for Large Shared Data Banks -- Communications of the ACM, Vol. 13, No. 6, June 1970, pp. 377-387

I'm not sure what value such a statement brings the world.

Re: Why Not to Build a Time-Series Database

#99

Post by geocar »

coder543 wrote: post #40
manigandham wrote: post #38

Earlier quoted context omitted.

Yes, I understand this as the "last value recorded" concept in my comment. KDB+ supports this with "asof" joins. Others can just do it by scanning a wider time frame or the entire table. Null gaps in a columnstore can be skipped over basically instantaneously and usually are just zone map/index lookups. Again I question how common this query is and whether it's really worth limiting yourself to a special TSDB because…

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

> KDB literally markets itself as a time series database

kdb+ also markets itself as OLAP/OLTP as well.

A lot of these guys market themselves as a "time series database" because kdb+ do and they want to be compared with kdb+ by people who haven't used kdb+ (but might be considering it).

Re: Why Not to Build a Time-Series Database

#100

Post by geocar »

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…

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

Post reply on HN