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

81–90 of 128 posts

Re: Why Not to Build a Time-Series Database

#81

Post by manigandham »

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…

I never said relational is related to column-storage.

kdb+ has SQL semantics and relational queries, but it's a combination of the q language integrated into a database so sure, it's a superset of a relational database. Perhaps we disagree on what relational means.

Re: Why Not to Build a Time-Series Database

#82

Post by hardwaresofton »

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…

I appreciate your links to further reading, and I'm trying to read the Aurora paper right now but after reading the abstract and the intro (I'm in progress right now), I can't find a case that is uniquely fit/perfect for data historians... I know this is already asking a lot, but would you mind giving me one go-to- use case that really made you think "this is what purpose-built data historian-style databases are good for?".

Every issue mentioned in the abstract/intro (which are meant to motivate the paper) seems like it can be solved as an add-on to existing application databases (albeit with their most recent developments/capabilities in mind). The very description of HADP vs DAHP systems seems silly, because it's just a question of write load, and that's fundamentally only solved with batching and efficient IO, or if you give up durability, it doesn't seem inherent to the data model. There's also assertions like:

> Moreover, performance is typically poor because middleware must poll for data values that triggers and alerters depend on

But like, postgres though, you're free to define a better/more efficient LISTEN/SUBSCRIBE based trigger mechanism, for example, you can highly optimized code right in the DB... Thinking of some of the cases called out in the paper here's what I think in my head:

- Change tracking vs only-current-value -> just record changes/events, as far as tables getting super big, partitioning helps this (timescaledb does this)

- Backfilling @ request time -> an postgres extension could do this

- Alerting -> postgres does have customizable functions/procedures as well as LISTEN/SUBSCRIBE. The paper is right (?) about TRIGGERs not scaling then this might be the most reasonable point.

- Approximate query answering is possible with postgres with stuff like HyperLogLog, but the paper is certainly right in that it is not implemented by default.

Maybe I'm mistaking the extensibility of postgres for the redundancy of the paradigm, akin to thinking something like "lisp is multi-paradigm so why would I use Haskell for it's enhanced inference/safety".

I'm still reading the paper so maybe by the end of it it will dawn on me.

Re: Why Not to Build a Time-Series Database

#83

Post by ironman1478 »

eksemplar wrote: post #77
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…

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

The PI data archive (it's the database part of the pi system) actually has a lot of these features, including the future data stuff (probably the biggest feature in the past few years). It's made by OSIsoft which is a big player in time series databases for industrial settings. (fyi I work on this product)

Re: Why Not to Build a Time-Series Database

#84

Post by StreamBright »

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…

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.

Re: Why Not to Build a Time-Series Database

#86

Post by brylie »

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

Earlier quoted context omitted.

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.

We have a lot of data stored in Postgres JSON fields at my work. Around three months ago, we were trying to optimize some queries by adding sub-key indexing to the JSON field. We tried multiple times, but Postgres seemed to keep using sequential scan on the records, rather than the JSON index. So, we just decided to normalize the data and use proper foreign key fields for query performance.

Re: Why Not to Build a Time-Series Database

#87

Post by pritambarhate »

bra-ket wrote: post #27

We used a combination of Kafka + Hbase+ Phoenix ( http://phoenix.apache.org/ ) for similar purpose. It takes some effort to setup initial Hbase cluster but once you do it manually once and automate with Ansible /systemd it's pretty robust in operation. All our development was around query engine using plain JDBC/SQL to talk to Hbase via Phoenix. Scaling is as simple as adding a node in the cluster.

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?

Re: Why Not to Build a Time-Series Database

#88

Post by viraptor »

Daneel_ wrote: post #85

I’m surprised no one has brought up Splunk in here (that I could see at a cursory glance). They manage to do time-series storage on a pretty large scale (over 5PB/day for their largest customer).

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.

Re: Why Not to Build a Time-Series Database

#89

Post by cheriot »

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

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

Re: Why Not to Build a Time-Series Database

#90

Post by viraptor »

User23 wrote: post #74
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…

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

> It blows my mind that businesses are willing to outsource metrics.

It makes sense at various scales compared to hiring, training, maintaining infrastructure, handling incidents, etc. related to your own metrics solution.

Post reply on HN