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

31–40 of 128 posts

Re: Why Not to Build a Time-Series Database

#31

Post by manigandham »

dub wrote: post #22
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…

A modern, distributed, relational, column-oriented database will often stress emphatically in the documentation that using timestamps as primary keys is an anti-pattern that's likely to lead to hot tablets: https://cloud.google.com/spanner/docs/schema-design#choosing...

Yes, that's why my comment said use metric name and timestamp.

Spanner isn't a column-oriented database, but they all support multiple columns as the primary/sort/shard/distribution key. Use the name as the first column, and timestamp as the last column, for scalable distribution.

Re: Why Not to Build a Time-Series Database

#32

Post by misframer »

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…

It really depends. Capabilities like time-series-specific compression, automatic rollups, complex aggregations and/or ranking, stable storage in S3, clustering, and replication vary a lot and I think that's why we see so many TSDBs out there. I maintain a list of TSDBs[0] and it started as an evaluation of what already available for my previous employer to use. We didn't find one that fit our exact use case, so we ended up building our own on top of MySQL.

[0] https://misfra.me/2016/04/09/tsdb-list/

Re: Why Not to Build a Time-Series Database

#33

Post by ced »

manigandham wrote: post #28
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…

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…

You don't have "missing rows". For time series A you have a time point at 12:01 AM, a datapoint at 12:02 AM, and another datapoint two weeks later at 5:04 PM. For time series B the times are different. You need some notion of whatever state the physical system was in at any given time.

Re: Why Not to Build a Time-Series Database

#34

Post by qaq »

coder543 wrote: post #23
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…

> No special "TSDB" needed. I think that is a large simplification. I recommend this (relatively) short article: https://blog.timescale.com/timescaledb-vs-6a696248104e The first graph is particularly salient.

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.

Re: Why Not to Build a Time-Series Database

#35

Post by manigandham »

coder543 wrote: post #23
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…

> No special "TSDB" needed. I think that is a large simplification. I recommend this (relatively) short article: https://blog.timescale.com/timescaledb-vs-6a696248104e The first graph is particularly salient.

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 near the capabilities of cutting-edge columnstores like Clickhouse, KDB+, MemSQL, Kinetica, etc.

Re: Why Not to Build a Time-Series Database

#36

Post by qaq »

varikin wrote: post #8
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?

I use to work at a Fortune 50 retailer on the cloud platform (a lot of tooling around CI/CD for the teams that manage the website). We had a large problem with keeping the metrics pipeline current. A major issue is that be default, Spring Boot publishes about 500 different metrics on a 10 second slice. Allowing every application to pump out that many default metrics, most of which are never used, means that it takes…

why this whole per minute thing I can insert 1000000 per minute on my mbp using reasonable batching (it's only 16K per second)

Re: Why Not to Build a Time-Series Database

#37

Post by PopeDotNinja »

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.

Does Kafka have timestamps? I didn't see any when I looked, but I was working with an older client version & didn't get far into it.

Re: Why Not to Build a Time-Series Database

#38

Post by manigandham »

ced wrote: post #33
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…

You don't have "missing rows". For time series A you have a time point at 12:01 AM, a datapoint at 12:02 AM, and another datapoint two weeks later at 5:04 PM. For time series B the times are different. You need some notion of whatever state the physical system was in at any given time.

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

Re: Why Not to Build a Time-Series Database

#39

Post by coder543 »

manigandham wrote: post #35
coder543 wrote: post #23

Earlier quoted context omitted.

> No special "TSDB" needed. I think that is a large simplification. I recommend this (relatively) short article: https://blog.timescale.com/timescaledb-vs-6a696248104e The first graph is particularly salient.

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 number of other syscalls."

You're doing the whole "large simplification" thing again. Yes, you can do everything yourself. No, you don't want to do that. Postgres by itself is not great for time-series data. Time series databases are useful, as your reply even showed, except for the part where you seem to assume any software that doesn't do something entirely novel is simply a quick abstraction that you could just whip up yourself.

Column stores have advantages over row stores, but they also have disadvantages. Your statement that it "comes nowhere [close to] the capabilities of cutting-edge column stores [...]" could just as easily be reversed as well.

Re: Why Not to Build a Time-Series Database

#40

Post by coder543 »

manigandham wrote: post #38
ced wrote: post #33

Earlier quoted context omitted.

You don't have "missing rows". For time series A you have a time point at 12:01 AM, a datapoint at 12:02 AM, and another datapoint two weeks later at 5:04 PM. For time series B the times are different. You need some notion of whatever state the physical system was in at any given time.

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 the point you're making again?

I think TimescaleDB lacks an "asof" function for now, but it makes up for it by having the full power of PostgreSQL for other stuff. Regardless, Time Series databases like KDB and TimescaleDB are useful.

EDIT:

it looks like TimescaleDB recommends using

  ORDER BY time DESC LIMIT 1
to get the most recent value for any particular set of sources that you're SELECTing over, which would use indices and be reasonably fast.
Post reply on HN