4 ms·
"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 an
by manigandham 8y ago
"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 scale easily with full SQL and joins. You can keep your other business data there too, along with JSON, geospatial, window functions, and all the other rich analytical queries available with relational databases.
We have trillion row tables that work great. No special "TSDB" needed.
- ghc 8y agoWhile 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 of readings from the same sensor. Why? Many systems in industrial environments send new readings only "on-change", and assume the underlying data storage architecture will forward fill to in-between times. This is why the practically ancient time series architecture of data historians still dominates in these use cases. In fact, for many time series functions you actually have to throw away the notion of relational joins to be able to efficiently perform time-preserving joins. Window functions only work in basic use cases with relatively small amounts of data where you're aggregating.
- deleted 8y ago[deleted]
- manigandham 8y agoI'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" as of a certain time, which doesn't seem to be a common query but is totally possible. KDB+ has "asof" joins and others can handle it with window functions using last_value(). We run queries on a table containing 2.7+ trillion rows of data that has no set pattern and infinite cardinality, and results return within seconds. Window functions and joins work without issue. Have you actually tried using a columnstore?
- ced 8y agoYou 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.
- manigandham 8y agoYes, 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.
- coder543 8y ago> 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.
- manigandham 8y agoDistributed 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 distributed column-oriented system. If you must use PostgreSQL though then it's a good compromise. The query you posted does not match the discussion about the last value at a specific instant in time, only the last value ever recorded in the table for that key.
- car10s 8y agoVertica 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/AnalyzingData/TimeSeries/UsingTimeSeriesAnalytics.htm%3FTocPath%3DAnalyzing%2520Data%7CTime%2520Series%2520Analytics%7C_____0 https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/...
- ghc 8y agoI 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.
- timeseries2018 8y agoI've had great success using event timestamp ( milliseconds since 1970 ) as a column, with an index on it. And then when you query you can use BETWEEN. If you write your own ORM you can make it automatically calculate the time range according to some defaults - minute, second, etc. Works great. If you are using spatial data, You can also use two columns like this for longitude and latitude.
- camel_gopher 8y agoThere 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/ https://www.irondb.io/2018/08/tsdbs-at-scale-part-one/
- manigandham 8y agoAll 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.
- camel_gopher 8y agoSure they can handle them, just not in an economically viable fashion.
- manigandham 8y agoCompared 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?
- chaotic-good 8y ago90% compression on time series is not viable unless you have some very specific dataset.
- manigandham 8y ago
- cdoxsey 8y agoGranted 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 scale. But almost no one ever hits that scale, so maybe it's better to adopt this line as a rule of thumb anyway...
- manigandham 8y agoI 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.
- cdoxsey 8y agoYeah 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.
- dub 8y agoA 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_a_primary_key_to_prevent_hotspots https://cloud.google.com/spanner/docs/schema-design#choosing...
- manigandham 8y agoYes, 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.
- anon12345690 8y agothe first section on that page shows what the op was saying, to use the name column first and timestamp last
- coder543 8y ago> No special "TSDB" needed. I think that is a large simplification. I recommend this (relatively) short article: https://blog.timescale.com/timescaledb-vs-6a696248104e https://blog.timescale.com/timescaledb-vs-6a696248104e The first graph is particularly salient.
- qaq 8y agoYou 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.
- coder543 8y agoConsidering 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, eventually consistent databases, but such databases generally have very limited analytical power, and if you're using them for anything but time series data, that whole "eventual consistency" thing requires a lot of careful thought.
- manigandham 8y agoTimescale is an extension to add automatic partitioning to PostgreSQL to give you some scalability and performance benefits. It is nowhere near the performance potential of a real distributed column-oriented database, which are strongly consistent, have rich SQL support, and even support transactions.
- qaq 8y agogoogle 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.
- manigandham 8y ago
- misframer 8y agoIt 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/ https://misfra.me/2016/04/09/tsdb-list/
- manigandham 8y agoAll the features you mentioned are already part of distributed column-oriented databases. S3 storage is orthogonal and unrelated to time-series data. It's usually not included in shared-nothing local storage architecture of databases but you can definitely mount S3 storage in a variety of ways. Many options on your list are not TSDBs, like Aerospike, Elasticsearch, Cassandra, Kudu, GridGain/Ignite. EventQL and Riak are obsolete. Apache Apex is a stream processing framework. Many of the others are just extensions to Prometheus built-in mini-storage or offer time-series indexing on top of existing databases.
- misframer 8y ago> 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.
- manigandham 8y agoWhat 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, Azure Table Storage, etc. They can sometimes handle time-series queries with good data modeling because of fast prefix scans but the lack of a real query language makes them a bad choice for analytics scenarios.
- dominotw 8y ago> 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.
- manigandham 8y agoI 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.
- dominotw 8y agoI think, its different because its dimension where usefulness of data goes does as the data ages. TimescaleDB for example, does optimisations ( they call it chunks or something) based on this fact.
- manigandham 8y agoTimescale is just an automatic partitioning extension for Postgres. You can also do it with the native partitioning feature, or pg_partman, or Citus, or other tools. Partitioning the table is the optimization, so that you skip over data when querying and manage it in smaller parts, but Timescale doesn't do anything about older data and neither do most databases.
- anarazel 8y ago> Timescale is just an automatic partitioning extension for Postgres. That, and a good PR operation.
- eksemplar 8y agoI 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 through to view a data set from any given date, as well as make changes to some past data as if you were there at that date. You can obviously do so with code, and a lot of the old SAP systems actually support this, but the first DB that handles this well will get to run every single public system.
- ironman1478 8y agoThe 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)
- StreamBright 8y agoThis 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.
- manigandham 8y agoThat's true for pretty much any technology.