14 ms·

It’s About Time for Time Series Databases

356 points by okket 9y ago

RegBarclay 9y ago
How is TimescaleDB different from OSIsoft PI?
- apohn 9y ago
  Not just OSISoft PI, but there are many other historians for time series data. https://en.wikipedia.org/wiki/Operational_historian https://en.wikipedia.org/wiki/Operational_historian At least in my experience, historians are rarely recommend for complex or ad-hoc queries. Typically you just pull the data (by tags) into another application and do your data processing there. It looks like in timeseriesdb lets you execute complex queries in the database. Historians typically only let you fetch data by tag and you need a metadata (e.g. asset management) framework on top to organize the data (e.g. give me avg temp every 5 minutes by sensor). It looks like with timeseriesdb you can have strings/text as fields within the timeseries table, which removes the need (to some degree) to join the data with a seperate metadata database. I've also never heard of anybody using these commercial historians for time series data you'd see from non industrial processes (e.g. stock data, price tracking, GPS location of people or moving assets, time between clicks on a website,etc). All that being said, OSISoft PI and AF have their warts, but OSISoft has been around for a while and PI has been battle tested in various industries (e.g. Oil & Gas, Manufacturing). It's closed source and you have to pay for it, so it's probably not attractive to startups and smaller companies. But it does come with a support organization if you need it and can pay for it. And IME data retrieval from PI is extremely performant!
  - cevian 9y ago
    Yeah, to underscore some differences more completely, timescale has: - Full indexing and secondary index support. - Support for transactional semantics - Support for living along side relational data - including foreign keys to the relational data. - Full trigger and constraint support - Support for all of Postgres's native data types including JSON(B) and GIS location data.
    - amelius 9y ago
      Transactional support? I was under the impression that timeseries data is considered immutable.
      - grzm 9y ago
        Being transactional is orthogonal to being immutable. For example, if you want to ensure that a given set of writes is applied atomically, you would issue them in a transaction.
        mfreed 9y ago
        This is especially important if you are also dealing with metadata that you don't want to always denormalize into your time-series tables. If you don't insert it transactionally, you can also lose referential integrity between your time-series and relational (metadata) tables.
      - cevian 9y ago
        That's not always true. There are tons of cases where time-series data gets corrected later. It is true that time-series is INSERT-mostly but UPDATES do happen.
arbesfeld 9y ago
We use Cassandra extensively at https://logrocket.com https://logrocket.com. How does performance compare vs Cassandra when you don't need the semantics or transactions of SQL? I'm surprised the article only provides benchmarks against PostgreSQL.
- RobAtticus 9y ago
  That is currently next in our pipeline for a benchmark blog post. Early results look good on both the read and write side in terms of raw performance, with the benefit of more complex queries being more easily expressable.
  - akulkarni 9y ago
    To add to this: We've found that the benefit of Cassandra's approach is when scaling out to 100s of nodes (which TimescaleDB currently does not support). But we found Cassandra's per node usage not that impressive. We'll have more benchmarks vs Cassandra soon (@robatticus is literally the person working on it), but suffice to say that users have already replaced multi-node Cassandra clusters (e.g., 10 nodes) with a single TimescaleDB instance (optionally adding read-only replicas for hot standbys).
    - aksyn 9y ago
      I recommend you benchmark against ScyllaDB, not Cassandra.
kodablah 9y ago
Sorry to leave the technical detail part real quick. But is anyone else concerned about using a DB solely from a company built specifically around that DB? After Rethink DB (sustainability issue) and Foundation DB (bought and shuttered/hidden) and Riak (admittedly haven't kept up but I saw [0]), I am wary of using any DB that is not built by a large community or is not built as a non-core project from a large tech company. Sorry TimescaleDB, I see you have raised a decent amount of funding, but I have to choose my DBs w/ trepidation these days. 0 - https://www.theregister.co.uk/2017/07/13/will_the_last_person_at_basho_get_the_lights_oh_too_late/ https://www.theregister.co.uk/2017/07/13/will_the_last_perso...
- walterbell 9y ago
  Is the concern mitigated by this being an open-source Postgres extension? One question would be whether they have attracted external OSS contributors.
  - kodablah 9y ago
    Depends on your reliance I suppose. Appears most of TimescaleDB's vendor-specific pieces are in the DDL area, but there are plenty of DML-specific extensions. I'm not saying TimescaleDB might not get to a level of maturity where I'd use it, I personally just can't justify it today. I've found DBs and ops/orchestration engines to be the most painful to move off from if you need to.
    - pas 9y ago
      They also provide the most bang for the feature-complexity buck. (A DB is something you don't want to roll on your own, and you want to use the purpose-built one for your use case. A columnar store for time series, a scatter-gather SSD + random read optimized engine for hot OLTP, a fancy multi level cache/tier BLOB store for photos/files, CockroachDB for geo consistency, etcd for local consistent config, and so on.)
- gaius 9y ago
  If Timescale goes bust you still have all your data in Postgres, which isn’t going anywhere. The risk is much lower.
stdbrouw 9y ago
> nobody wants to have large grain snapshots of data for any dataset that is actually comprised of a continuous stream of data points Except, of course, for those who realize that the precision of a statistic only increases at sqrt(n) and that a biased dataset will remain biased regardless of how much data you have. I'll take a large grain dataset that I can load on my computer and analyze in five minutes over a finer grained dataset where I need to set up a cluster before I can even get started. Enough with the "let's store everything" fetishism already. (Somewhat tangential to the blog post, I realize.)
- teej 9y ago
  The reason we need to store everything is less about needing perfect accuracy of measurement (though I think we do want it) and more about the curse of dimensionality[0]. We want to slice, pivot, and filter datasets more aggressively than ever before which helps drive aggressive data collection. [0] - https://en.wikipedia.org/wiki/Curse_of_dimensionality https://en.wikipedia.org/wiki/Curse_of_dimensionality
  - ryanworl 9y ago
    You can use sampling to both store every dimension of a data point and to not store an unwieldy amount of data.
    - teej 9y ago
      This simply doesn’t work when you have sparsely populated dimensions and/or you don’t know what dimensions are important in advance. Both of these are very common. That’s why you don’t see a higher prevalence of estimated measurement.
      - ryanworl 9y ago
        You’re definitely not wrong, but if you simply can’t operate a system to store everything for some reason, sampling is a lot better than just doing an aggregation on one dimension and throwing everything else away. For anyone reading this who is interest in a practical application with sampled operational data, check out Facebook Scuba. https://research.fb.com/publications/scuba-diving-into-data-at-facebook/ https://research.fb.com/publications/scuba-diving-into-data-...
shangxiao 9y ago
This is a bit easier to grok: http://www.timescale.com/how-it-works http://www.timescale.com/how-it-works (if you're new to this like me)
- bitoneill 9y ago
  https://en.wikipedia.org/wiki/Time_series_database https://en.wikipedia.org/wiki/Time_series_database
YCode 9y ago
> Back in 2014...developers...could use relational databases with SQL interfaces, which are easy to use but they don’t scale well. I'm skeptical.
- g09980 9y ago
  Guessing they meant to write "... for time series aggregation"
Radim 9y ago
Perhaps not as lofty goal as time series, but a good, commercial database for vectors (aka the building representation block of modern machine learning) is long overdue too. A few open source options exist (Spotify's Annoy, Facebook's FAISS, NMSLIB) but these are rather low-level, more hobby projects than enterprise level engines (index management, transactions, sharding…). After building a few document similarity engines for our clients we took up the gauntlet and created ScaleText, https://scaletext.ai https://scaletext.ai. It's still early days but the demand for a sane, scalable, cross-vertical and well-supported NLP technology is encouraging.
tw1010 9y ago
I have a hard time shaking the feeling that database innovation is being forced not by necessity, but by a culture of blind relentless change.
- analognoise 9y ago
  Silicon Valley on reinventing the wheel: "Ours is rounder!"
- codingdave 9y ago
  OK, but is that bad? It might be bad for the specific organization pushing their new solutions, but for the software industry as a whole, it seems like the good side of "throw spaghetti against the wall and see what sticks". Many ideas will fail, but the good ones will stick around. So I'd say by all means, let people feel free to innovate. And those of us who consume the innovations just need to remember the mantra of "leading edge, not bleeding edge."
  - tw1010 9y ago
    I didn't say it was bad (though the tone of my post definitely hinted at it). I agree with you. Following the trail of necessity can easily lead to locally optimal points, away from the global maximum.
- cevian 9y ago
  I have to say that a sentiment similar to this is why we built Timescale as a Postgres extension and not started a new database from scratch. We didn't want a new shiny thing just for kicks but rather a product focused on solving a particular problem.
- postwait 9y ago
  Relentless change is most likely the motivator for these innovations. However, I don't think it is needless innovation or innovation for the same of innovation. Personal opinion that there isn't much innovation in what this article covers, but still it is happening elsewhere. Changes in telemetry production (IoT and others) have fundamentally changed the requirements... millions (and often billions) of data points points per-second are now happening. This is being driven by RSM and IoT. RSM is alluded to in my ACM Queue article: https://queue.acm.org/detail.cfm?id=3178371 https://queue.acm.org/detail.cfm?id=3178371
amyjess 9y ago
At my employer, we've recently (as of the middle of last year) been making a considerable effort to use InfluxDB to track our KPIs. It's working out wonderfully for us, and I'm expecting it'll get used more and more as the year goes on. What really floors me about Influx is how fast it is. A query that used to take hours in Oracle takes seconds in Influx. And the influx query is readable: rolling data up to various intervals produced nightmare queries in Oracle but is short and sweet in Influx. Want to take data gathered every 5 minutes and give a daily average? Yeah, good luck with that in Oracle. It's doable, sure, but I've seen the code. And the speed. Both are ugly. The only problem I have with InfluxDB is it's still a little immature, and the tooling isn't where it should be. This isn't entirely Influx's fault; most third parties aren't aware it exists or don't care. Our reporting team uses Crystal Reports, which can't talk to InfluxDB. So I end up having to write a Python script that runs in cron every night to perform InfluxDB queries for the previous day's data doing all the rolling and average/min/max calculations and then inserting the results into Oracle, just so our reporting team can get to the data. For some KPIs I'm working on right now, we decided to not go through the reporting team, and I'm writing a webapp in Python/Bottle to display the report, and we're probably going to augment that with graphs from Grafana. Grafana is beautiful, by the way.
- scrollaway 9y ago
  I think you're saying a lot more about Oracle than about Influx :) I also am using Influx at my company and the experience is not that great, mostly due to it being immature yes. For example, I currently have runaway disk usage and I have no way to know what table is using it. So I have to choose between losing data or continuously increasing storage.
  - amyjess 9y ago
    One bit of immaturity I just remembered: InfluxDB does a terrible job of telling you when you've fat-fingered a query. You can select from a series that doesn't exist or reference a tag that doesn't exist inside a 'where' clause, and InfluxDB will happily return 0 results instead of giving an error. I've spent some time trying to figure out why I'm not getting data, and it turns out that it was because I typoed the name of the series. I still like Influx though, warts and all.
olympus 9y ago
The article recognizes that several time series databases already exist. They also say, "we aren't trying to compete against kdb+." They explain how they can handle time series data better than NoSQL databases that aren't time series focused. But what are they doing better than the existing time series databases? Surely they must have some advantage or they wouldn't have raised 16.1 million dollars.
- busterarm 9y ago
  Every time I read about some new solution to storing time series data, I always feel like I must be doing something wrong, but I've run into _zero_ problems yet. Every time I have to store time series data, I never really need ACID transactions. I definitely don't ever need to do updates or upserts. It's always write-once-read-many. ElasticSearch has always been the obvious choice to me and it has worked extremely well. Information retrieval is incredibly robust and for times that I'm worried about consistency, I use Postgres's JSON capabilities and write first to there. You can have your application sanity-check between the two if you're worried about ElasticSearch not receiving/losing data. I find it really hard to beat.
  - cevian 9y ago
    We've had some clients try this, find consistency issues between elastic nodes or postgres and elastic and start using TimescaleDB as a way to simplify the stack and application. But obviously YMMV and this is highly dependent on your query needs.
  - pfranz 9y ago
    I had set up ElasticSearch for logging events and was thinking about using it to store metrics as well. It seemed like it ticked all the boxes and would work pretty well when I was messing around with it. I ended up leaving that job and set up something specifically for metrics from scratch. I didn't compare 1 to 1, but this was much faster to query and had much lower requirements (disk space, memory, etc). Both use-cases are using it as a time-series database, and there's no reason ElasticSearch couldn't work for both of those use-cases for many people. When using two different backends (one for events and another for metrics) I meant drawing a line when creating/logging data and two ways to query...which sucks for training. You also would have to maintain/archive two different data stores.
dustingetz 9y ago
How about one made by Rich Hickey — https://www.datomic.com/ https://www.datomic.com/
- witwughu391 9y ago
  is Datomic good for timeseries ? AFAIWA it's good for rare-write often-read applications
- dragandj 9y ago
  Sadly, Datomic lacks two of (arguably) very important features in this space: 1. It seems to not be optimized for speed (but it's difficult to say since the license forbids publishing benchmarks). 2. It's not open source.
  - dustingetz 9y ago
    I empathetically agree with #1 but object to #2. Datomic, on an architectural basis alone, is going to be strictly faster than say an RDBMS, because it can parallelize almost all of the workload. Read-side is entirely elastic; index and storage maintenance does not block the writer, and the single operation that must be serial is the actual ACID write primitive, the conditional-put to DynamoDB. In RDBMS, the ACID writer is competing for resources with all these other operations, because they can't be parallelized without sacrificing consistency.
    - dragandj 9y ago
      We are talking about time-series databases here. I guess they are more optimized for the specific workload than a generic RDBMS.
chrismccabe 9y ago
What is the benefits of timescaledb or influxdb? surely the enterprise pricing is going to be similar to KDB which has been around for years and a very mature product
marknadal 9y ago
We had major success by simply batching incoming writes into ~15 second chunks and writing that as a file to S3 and an index that tracks how the files are split / chunked to make read performance decent. This alone gave us an insanely scalable (load tested against 100GB/day ~100M records/day) for a grand cheap total cost of $10/day for everything, server, disk, and S3. https://youtu.be/x_WqBuEA7s8 https://youtu.be/x_WqBuEA7s8 Works great for timeseries data, super scalable, no devops or managing database servers, simple, and works. I believe the Discord guys also did something similar and had written some good engineering articles on it, give it a Google as well.
- postwait 9y ago
  Storing and retrieving data has never been all that hard. The challenge is having user-interactive performance on complex queries against the data. Comparing and correlating and deriving and integrating and ... (lots of other analysis). For many "scaled" systems, 100M records/minute isn't uncommon... and while that's very likely possible with your design the question of economic feasibility enters. Solving these problems at scale with good economics is the playground of TSDB vendors today.
jnordwick 9y ago
I think I say this about every time, because I see it as the major flaw in almost every approach to this problem. I say this from the perspective of having used a wide range of TS databases, but mostly KDB, Oracle, and prop solutions with Cassandra and Mongo products thrown in. There are two big problems with relational SQL databases: the storage and the query language. The two aren't separate concerns. While projects like this might fix the storage issue, they will never be simple or fast without also changing the query language too. SQL doesn't fit the column/array paradigm well. There are extensions that attempt to close the gap, but they are slow and complex compared to just fixing the query language. Operations like windowed queries and aggregates can be painfully slow because of the mismatch between SQL and the ordered data. I would absolutely love to work on a product that attempts to fix the query issue at the same time as working on the storage and interface issues. You don't need an obscure APL-like language, just something that promotes using the properties inherent in time series to the front. Also, it is a bit weird to say you are not trying to compete with the likes of KDB then go on to say how good you are at its core competencies. As always, eagerly awaiting the TPC-D benchmarks and comparisons to the heavy hitting time series dbs out there, not the easily crushed FOSS products (except for maybe AeroSpike that I wish I had more experience with).
- cobbzilla 9y ago
  PostgreSQL with jsonb columns can be quite powerful, best of both worlds; but yes the syntax for querying within json is a bit arcane.
acomjean 9y ago
I worked at a place that monitored power usage minute by minute across 1000s of locations. We just used MySQL with a time column. Maybe I'm not the target audience but I'm failing to see what this gets me. The problem is they say the data is imuatable and stored sequentially, allthough our data was imutable with devices on the net the data comes in random order when these ineviatably have connection problems. We always aggregated our data into larger time blocks. Storage was cheap and doing comparative analysis across locations and time zones was always our pain point. I think using a Postgres database is wise.
- kev009 9y ago
  Then effectively your insert rate is between 16 and 60 per second, sure, you don't really need a sophisticated partitioning or log structured DB. Native static partitioning would give you a decent speedup without much thought. It's intro computer science, if you have a tree structure and fill it up, you spend a lot of time in the corners of theta notation. Timescale uses tightly integrated partitioning on the time axis to deal with write performance and aging data out. Other popular TSDBs are plays on log structured merge trees etc
  - postwait 9y ago
    Reducing "Other TSDBs" to log-structured-merge trees is misleading. Any large-scale TSDB has something sophisticated underneath and LSM is often just one tiny part of that. I would argue (as most do) that any TSDB "simply used an LSM" it would be doomed at any scale over time.
    - kev009 9y ago
      There was no reduction, it was intended as a pointer to one data structure some TSDBs are using underneath. I would bold and highlight the "etc" present there for you if the markup allowed it. I hope a reader would become interested in what an LSM tree is (and perhaps as importantly isn't)
augustl 9y ago
Isn't it a bit weird to launch a "never delete anything" store today and not mention anything about European GDPR requirements? How would you go about deleting a users data upon request?
- manigandham 9y ago
  You delete it. It's still a database. What's being done here is just smart partition management as an extension to Postgres, similar to CitusDB. You can also use any column-oriented relational database with a time-based partition key and do the same thing.
lima 9y ago
ClickHouse (the analytics DMBS by Yandex), while not explicitly designed as such, is a fantastic time series database. There's even a special backend, the GraphiteMergeTree, which does staggered downsampling, something most TSDBs aren't able to. It's the most promising development in this space I've seen in a long time. https://clickhouse.yandex/ https://clickhouse.yandex/ https://clickhouse.yandex/docs/en/table_engines/graphitemergetree.html https://clickhouse.yandex/docs/en/table_engines/graphitemerg...
- betaby 9y ago
  Direct link how to use as a graphite whisper replacement https://github.com/yandex/graphouse https://github.com/yandex/graphouse Telegram channel https://t.me/clickhouse_en https://t.me/clickhouse_en
- nawgszy 9y ago
  Completely unrelated question, but how on earth is 'clickhouse.yandex' a valid web address?
  - betaby 9y ago
    The same way https://domains.google https://domains.google is, you pay - you get your own TLD
- manigandham 9y ago
  I'd also recommend Druid, MemSQL, SnappyData, MapD and other column-oriented databases. Any of them can partition on a time column with full SQL and extremely fast aggregations and high compression that come from columnar storage.
  - cevian 9y ago
    Hi, you've posted this notion that partitioning a column store by time would yield the same result as TimescaleDB a few times, so thought we'd jump in and clear things up. We fully agree that column stores have their place, particularly if you have a massive number of metrics, and all you care are roll-ups on single column axes. There are some major differences between TimescaleDB and column stores. Namely, TimescaleDB supports a lot of features that column stores in general do not. - Secondary Indexes. - Transactional semantics. - Can operate on data sets greater than available memory (doesn't have to be all in-memory unlike memSQL and some others). Time-series data is voluminous. - A whole bunch of specialized time-based optimizations that optimize query plans when working with time-based indexes and data. - Constraints - Including foreign keys. - Triggers - Joins with relational data. - Full SQL - allowing you to use complex queries and window functions - Compatible with data tools that use SQL - which gets you gets you the richest ecosystem of tools in the data world - The full gamut of Postgres datatypes including JSON/B and GIS location data - 20+ years of reliability, tested backups, live streaming replication, etc. - Geospatial support through best-in-class PostGIS And of course, we're only getting started :)
cbcoutinho 9y ago
We use a proprietary database system that uses a 'flat-file' format (no idea what that means) and is primarily time series based due to the fact that we're logging sensor data. Since it's primarily a backend, you can't access it outside of their proprietary gui. It's also accessible as a linked server via SQL Server, but this is slow as hell for non-trivial queries. We use it within a power plant setting where we heavily prioritize db-writes, which this software is apparently very good in, and db-reads are less of a focus. I'm not sure if moving to another db system would be beneficial, but I would be very grateful if accessiblity could be much less of a hack
- BillinghamJ 9y ago
  We have a similar setup for our telematics data (insurance company). It’s stored in files which contain DEFLATE’d BSON. Similarly, we have a custom GUI for viewing it, but do also have some terminal tools which can be used. The solution to larger processing simply seems to be loading the files and batching them through whatever processing we want and storing the output in a proper database - which can then be queried instantly. For a lot of time series data, this does seem like a pretty decent approach.
dx034 9y ago
What I often miss with these kinds of databases is compression capabilities. I currently use my own delta-of-delta encoding for time series data (stored in postgres) as that gave me much better compression than any of the time series databases I tested. It's not that storage isn't available, it's easy to store terrabytes of data. But really fast storage is still expensive and rare. Being able to compress time series data to 3-5% of the raw value allows keeping most in memory, speeding up reading writing and analysis. InfluxDB is quite good at that but unfortunately, they struggle storing many small datasets.
anonu 9y ago
I almost thought this was going to be an ad for KX as part of the subject is their slogan... Glad to see open source tools in this space gaining traction.

356 points by okket 9y ago

RegBarclay 9y ago

How is TimescaleDB different from OSIsoft PI?

apohn 9y ago
Not just OSISoft PI, but there are many other historians for time series data. https://en.wikipedia.org/wiki/Operational_historian https://en.wikipedia.org/wiki/Operational_historian At least in my experience, historians are rarely recommend for complex or ad-hoc queries. Typically you just pull the data (by tags) into another application and do your data processing there. It looks like in timeseriesdb lets you execute complex queries in the database. Historians typically only let you fetch data by tag and you need a metadata (e.g. asset management) framework on top to organize the data (e.g. give me avg temp every 5 minutes by sensor). It looks like with timeseriesdb you can have strings/text as fields within the timeseries table, which removes the need (to some degree) to join the data with a seperate metadata database. I've also never heard of anybody using these commercial historians for time series data you'd see from non industrial processes (e.g. stock data, price tracking, GPS location of people or moving assets, time between clicks on a website,etc). All that being said, OSISoft PI and AF have their warts, but OSISoft has been around for a while and PI has been battle tested in various industries (e.g. Oil & Gas, Manufacturing). It's closed source and you have to pay for it, so it's probably not attractive to startups and smaller companies. But it does come with a support organization if you need it and can pay for it. And IME data retrieval from PI is extremely performant!
- cevian 9y ago
  Yeah, to underscore some differences more completely, timescale has: - Full indexing and secondary index support. - Support for transactional semantics - Support for living along side relational data - including foreign keys to the relational data. - Full trigger and constraint support - Support for all of Postgres's native data types including JSON(B) and GIS location data.
  - amelius 9y ago
    Transactional support? I was under the impression that timeseries data is considered immutable.
    - grzm 9y ago
      Being transactional is orthogonal to being immutable. For example, if you want to ensure that a given set of writes is applied atomically, you would issue them in a transaction.
      - mfreed 9y ago
        This is especially important if you are also dealing with metadata that you don't want to always denormalize into your time-series tables. If you don't insert it transactionally, you can also lose referential integrity between your time-series and relational (metadata) tables.
    - cevian 9y ago
      That's not always true. There are tons of cases where time-series data gets corrected later. It is true that time-series is INSERT-mostly but UPDATES do happen.

arbesfeld 9y ago

We use Cassandra extensively at https://logrocket.com https://logrocket.com. How does performance compare vs Cassandra when you don't need the semantics or transactions of SQL? I'm surprised the article only provides benchmarks against PostgreSQL.

RobAtticus 9y ago
That is currently next in our pipeline for a benchmark blog post. Early results look good on both the read and write side in terms of raw performance, with the benefit of more complex queries being more easily expressable.
- akulkarni 9y ago
  To add to this: We've found that the benefit of Cassandra's approach is when scaling out to 100s of nodes (which TimescaleDB currently does not support). But we found Cassandra's per node usage not that impressive. We'll have more benchmarks vs Cassandra soon (@robatticus is literally the person working on it), but suffice to say that users have already replaced multi-node Cassandra clusters (e.g., 10 nodes) with a single TimescaleDB instance (optionally adding read-only replicas for hot standbys).
  - aksyn 9y ago
    I recommend you benchmark against ScyllaDB, not Cassandra.

kodablah 9y ago

Sorry to leave the technical detail part real quick. But is anyone else concerned about using a DB solely from a company built specifically around that DB? After Rethink DB (sustainability issue) and Foundation DB (bought and shuttered/hidden) and Riak (admittedly haven't kept up but I saw [0]), I am wary of using any DB that is not built by a large community or is not built as a non-core project from a large tech company. Sorry TimescaleDB, I see you have raised a decent amount of funding, but I have to choose my DBs w/ trepidation these days. 0 - https://www.theregister.co.uk/2017/07/13/will_the_last_person_at_basho_get_the_lights_oh_too_late/ https://www.theregister.co.uk/2017/07/13/will_the_last_perso...

walterbell 9y ago
Is the concern mitigated by this being an open-source Postgres extension? One question would be whether they have attracted external OSS contributors.
- kodablah 9y ago
  Depends on your reliance I suppose. Appears most of TimescaleDB's vendor-specific pieces are in the DDL area, but there are plenty of DML-specific extensions. I'm not saying TimescaleDB might not get to a level of maturity where I'd use it, I personally just can't justify it today. I've found DBs and ops/orchestration engines to be the most painful to move off from if you need to.
  - pas 9y ago
    They also provide the most bang for the feature-complexity buck. (A DB is something you don't want to roll on your own, and you want to use the purpose-built one for your use case. A columnar store for time series, a scatter-gather SSD + random read optimized engine for hot OLTP, a fancy multi level cache/tier BLOB store for photos/files, CockroachDB for geo consistency, etcd for local consistent config, and so on.)
gaius 9y ago
If Timescale goes bust you still have all your data in Postgres, which isn’t going anywhere. The risk is much lower.

stdbrouw 9y ago

> nobody wants to have large grain snapshots of data for any dataset that is actually comprised of a continuous stream of data points Except, of course, for those who realize that the precision of a statistic only increases at sqrt(n) and that a biased dataset will remain biased regardless of how much data you have. I'll take a large grain dataset that I can load on my computer and analyze in five minutes over a finer grained dataset where I need to set up a cluster before I can even get started. Enough with the "let's store everything" fetishism already. (Somewhat tangential to the blog post, I realize.)

teej 9y ago
The reason we need to store everything is less about needing perfect accuracy of measurement (though I think we do want it) and more about the curse of dimensionality[0]. We want to slice, pivot, and filter datasets more aggressively than ever before which helps drive aggressive data collection. [0] - https://en.wikipedia.org/wiki/Curse_of_dimensionality https://en.wikipedia.org/wiki/Curse_of_dimensionality
- ryanworl 9y ago
  You can use sampling to both store every dimension of a data point and to not store an unwieldy amount of data.
  - teej 9y ago
    This simply doesn’t work when you have sparsely populated dimensions and/or you don’t know what dimensions are important in advance. Both of these are very common. That’s why you don’t see a higher prevalence of estimated measurement.
    - ryanworl 9y ago
      You’re definitely not wrong, but if you simply can’t operate a system to store everything for some reason, sampling is a lot better than just doing an aggregation on one dimension and throwing everything else away. For anyone reading this who is interest in a practical application with sampled operational data, check out Facebook Scuba. https://research.fb.com/publications/scuba-diving-into-data-at-facebook/ https://research.fb.com/publications/scuba-diving-into-data-...

shangxiao 9y ago

This is a bit easier to grok: http://www.timescale.com/how-it-works http://www.timescale.com/how-it-works (if you're new to this like me)

bitoneill 9y ago
https://en.wikipedia.org/wiki/Time_series_database https://en.wikipedia.org/wiki/Time_series_database

YCode 9y ago

> Back in 2014...developers...could use relational databases with SQL interfaces, which are easy to use but they don’t scale well. I'm skeptical.

g09980 9y ago
Guessing they meant to write "... for time series aggregation"

Radim 9y ago

Perhaps not as lofty goal as time series, but a good, commercial database for vectors (aka the building representation block of modern machine learning) is long overdue too. A few open source options exist (Spotify's Annoy, Facebook's FAISS, NMSLIB) but these are rather low-level, more hobby projects than enterprise level engines (index management, transactions, sharding…). After building a few document similarity engines for our clients we took up the gauntlet and created ScaleText, https://scaletext.ai https://scaletext.ai. It's still early days but the demand for a sane, scalable, cross-vertical and well-supported NLP technology is encouraging.

tw1010 9y ago

I have a hard time shaking the feeling that database innovation is being forced not by necessity, but by a culture of blind relentless change.

analognoise 9y ago
Silicon Valley on reinventing the wheel: "Ours is rounder!"
codingdave 9y ago
OK, but is that bad? It might be bad for the specific organization pushing their new solutions, but for the software industry as a whole, it seems like the good side of "throw spaghetti against the wall and see what sticks". Many ideas will fail, but the good ones will stick around. So I'd say by all means, let people feel free to innovate. And those of us who consume the innovations just need to remember the mantra of "leading edge, not bleeding edge."
- tw1010 9y ago
  I didn't say it was bad (though the tone of my post definitely hinted at it). I agree with you. Following the trail of necessity can easily lead to locally optimal points, away from the global maximum.
cevian 9y ago
I have to say that a sentiment similar to this is why we built Timescale as a Postgres extension and not started a new database from scratch. We didn't want a new shiny thing just for kicks but rather a product focused on solving a particular problem.
postwait 9y ago
Relentless change is most likely the motivator for these innovations. However, I don't think it is needless innovation or innovation for the same of innovation. Personal opinion that there isn't much innovation in what this article covers, but still it is happening elsewhere. Changes in telemetry production (IoT and others) have fundamentally changed the requirements... millions (and often billions) of data points points per-second are now happening. This is being driven by RSM and IoT. RSM is alluded to in my ACM Queue article: https://queue.acm.org/detail.cfm?id=3178371 https://queue.acm.org/detail.cfm?id=3178371

amyjess 9y ago

At my employer, we've recently (as of the middle of last year) been making a considerable effort to use InfluxDB to track our KPIs. It's working out wonderfully for us, and I'm expecting it'll get used more and more as the year goes on. What really floors me about Influx is how fast it is. A query that used to take hours in Oracle takes seconds in Influx. And the influx query is readable: rolling data up to various intervals produced nightmare queries in Oracle but is short and sweet in Influx. Want to take data gathered every 5 minutes and give a daily average? Yeah, good luck with that in Oracle. It's doable, sure, but I've seen the code. And the speed. Both are ugly. The only problem I have with InfluxDB is it's still a little immature, and the tooling isn't where it should be. This isn't entirely Influx's fault; most third parties aren't aware it exists or don't care. Our reporting team uses Crystal Reports, which can't talk to InfluxDB. So I end up having to write a Python script that runs in cron every night to perform InfluxDB queries for the previous day's data doing all the rolling and average/min/max calculations and then inserting the results into Oracle, just so our reporting team can get to the data. For some KPIs I'm working on right now, we decided to not go through the reporting team, and I'm writing a webapp in Python/Bottle to display the report, and we're probably going to augment that with graphs from Grafana. Grafana is beautiful, by the way.

scrollaway 9y ago
I think you're saying a lot more about Oracle than about Influx :) I also am using Influx at my company and the experience is not that great, mostly due to it being immature yes. For example, I currently have runaway disk usage and I have no way to know what table is using it. So I have to choose between losing data or continuously increasing storage.
- amyjess 9y ago
  One bit of immaturity I just remembered: InfluxDB does a terrible job of telling you when you've fat-fingered a query. You can select from a series that doesn't exist or reference a tag that doesn't exist inside a 'where' clause, and InfluxDB will happily return 0 results instead of giving an error. I've spent some time trying to figure out why I'm not getting data, and it turns out that it was because I typoed the name of the series. I still like Influx though, warts and all.

olympus 9y ago

The article recognizes that several time series databases already exist. They also say, "we aren't trying to compete against kdb+." They explain how they can handle time series data better than NoSQL databases that aren't time series focused. But what are they doing better than the existing time series databases? Surely they must have some advantage or they wouldn't have raised 16.1 million dollars.

busterarm 9y ago
Every time I read about some new solution to storing time series data, I always feel like I must be doing something wrong, but I've run into _zero_ problems yet. Every time I have to store time series data, I never really need ACID transactions. I definitely don't ever need to do updates or upserts. It's always write-once-read-many. ElasticSearch has always been the obvious choice to me and it has worked extremely well. Information retrieval is incredibly robust and for times that I'm worried about consistency, I use Postgres's JSON capabilities and write first to there. You can have your application sanity-check between the two if you're worried about ElasticSearch not receiving/losing data. I find it really hard to beat.
- cevian 9y ago
  We've had some clients try this, find consistency issues between elastic nodes or postgres and elastic and start using TimescaleDB as a way to simplify the stack and application. But obviously YMMV and this is highly dependent on your query needs.
- pfranz 9y ago
  I had set up ElasticSearch for logging events and was thinking about using it to store metrics as well. It seemed like it ticked all the boxes and would work pretty well when I was messing around with it. I ended up leaving that job and set up something specifically for metrics from scratch. I didn't compare 1 to 1, but this was much faster to query and had much lower requirements (disk space, memory, etc). Both use-cases are using it as a time-series database, and there's no reason ElasticSearch couldn't work for both of those use-cases for many people. When using two different backends (one for events and another for metrics) I meant drawing a line when creating/logging data and two ways to query...which sucks for training. You also would have to maintain/archive two different data stores.

dustingetz 9y ago

How about one made by Rich Hickey — https://www.datomic.com/ https://www.datomic.com/

witwughu391 9y ago
is Datomic good for timeseries ? AFAIWA it's good for rare-write often-read applications
dragandj 9y ago
Sadly, Datomic lacks two of (arguably) very important features in this space: 1. It seems to not be optimized for speed (but it's difficult to say since the license forbids publishing benchmarks). 2. It's not open source.
- dustingetz 9y ago
  I empathetically agree with #1 but object to #2. Datomic, on an architectural basis alone, is going to be strictly faster than say an RDBMS, because it can parallelize almost all of the workload. Read-side is entirely elastic; index and storage maintenance does not block the writer, and the single operation that must be serial is the actual ACID write primitive, the conditional-put to DynamoDB. In RDBMS, the ACID writer is competing for resources with all these other operations, because they can't be parallelized without sacrificing consistency.
  - dragandj 9y ago
    We are talking about time-series databases here. I guess they are more optimized for the specific workload than a generic RDBMS.

chrismccabe 9y ago

What is the benefits of timescaledb or influxdb? surely the enterprise pricing is going to be similar to KDB which has been around for years and a very mature product

marknadal 9y ago

We had major success by simply batching incoming writes into ~15 second chunks and writing that as a file to S3 and an index that tracks how the files are split / chunked to make read performance decent. This alone gave us an insanely scalable (load tested against 100GB/day ~100M records/day) for a grand cheap total cost of $10/day for everything, server, disk, and S3. https://youtu.be/x_WqBuEA7s8 https://youtu.be/x_WqBuEA7s8 Works great for timeseries data, super scalable, no devops or managing database servers, simple, and works. I believe the Discord guys also did something similar and had written some good engineering articles on it, give it a Google as well.

postwait 9y ago
Storing and retrieving data has never been all that hard. The challenge is having user-interactive performance on complex queries against the data. Comparing and correlating and deriving and integrating and ... (lots of other analysis). For many "scaled" systems, 100M records/minute isn't uncommon... and while that's very likely possible with your design the question of economic feasibility enters. Solving these problems at scale with good economics is the playground of TSDB vendors today.

jnordwick 9y ago

I think I say this about every time, because I see it as the major flaw in almost every approach to this problem. I say this from the perspective of having used a wide range of TS databases, but mostly KDB, Oracle, and prop solutions with Cassandra and Mongo products thrown in. There are two big problems with relational SQL databases: the storage and the query language. The two aren't separate concerns. While projects like this might fix the storage issue, they will never be simple or fast without also changing the query language too. SQL doesn't fit the column/array paradigm well. There are extensions that attempt to close the gap, but they are slow and complex compared to just fixing the query language. Operations like windowed queries and aggregates can be painfully slow because of the mismatch between SQL and the ordered data. I would absolutely love to work on a product that attempts to fix the query issue at the same time as working on the storage and interface issues. You don't need an obscure APL-like language, just something that promotes using the properties inherent in time series to the front. Also, it is a bit weird to say you are not trying to compete with the likes of KDB then go on to say how good you are at its core competencies. As always, eagerly awaiting the TPC-D benchmarks and comparisons to the heavy hitting time series dbs out there, not the easily crushed FOSS products (except for maybe AeroSpike that I wish I had more experience with).

cobbzilla 9y ago
PostgreSQL with jsonb columns can be quite powerful, best of both worlds; but yes the syntax for querying within json is a bit arcane.

acomjean 9y ago

I worked at a place that monitored power usage minute by minute across 1000s of locations. We just used MySQL with a time column. Maybe I'm not the target audience but I'm failing to see what this gets me. The problem is they say the data is imuatable and stored sequentially, allthough our data was imutable with devices on the net the data comes in random order when these ineviatably have connection problems. We always aggregated our data into larger time blocks. Storage was cheap and doing comparative analysis across locations and time zones was always our pain point. I think using a Postgres database is wise.

kev009 9y ago
Then effectively your insert rate is between 16 and 60 per second, sure, you don't really need a sophisticated partitioning or log structured DB. Native static partitioning would give you a decent speedup without much thought. It's intro computer science, if you have a tree structure and fill it up, you spend a lot of time in the corners of theta notation. Timescale uses tightly integrated partitioning on the time axis to deal with write performance and aging data out. Other popular TSDBs are plays on log structured merge trees etc
- postwait 9y ago
  Reducing "Other TSDBs" to log-structured-merge trees is misleading. Any large-scale TSDB has something sophisticated underneath and LSM is often just one tiny part of that. I would argue (as most do) that any TSDB "simply used an LSM" it would be doomed at any scale over time.
  - kev009 9y ago
    There was no reduction, it was intended as a pointer to one data structure some TSDBs are using underneath. I would bold and highlight the "etc" present there for you if the markup allowed it. I hope a reader would become interested in what an LSM tree is (and perhaps as importantly isn't)

augustl 9y ago

Isn't it a bit weird to launch a "never delete anything" store today and not mention anything about European GDPR requirements? How would you go about deleting a users data upon request?

manigandham 9y ago
You delete it. It's still a database. What's being done here is just smart partition management as an extension to Postgres, similar to CitusDB. You can also use any column-oriented relational database with a time-based partition key and do the same thing.

lima 9y ago

ClickHouse (the analytics DMBS by Yandex), while not explicitly designed as such, is a fantastic time series database. There's even a special backend, the GraphiteMergeTree, which does staggered downsampling, something most TSDBs aren't able to. It's the most promising development in this space I've seen in a long time. https://clickhouse.yandex/ https://clickhouse.yandex/ https://clickhouse.yandex/docs/en/table_engines/graphitemergetree.html https://clickhouse.yandex/docs/en/table_engines/graphitemerg...

betaby 9y ago
Direct link how to use as a graphite whisper replacement https://github.com/yandex/graphouse https://github.com/yandex/graphouse Telegram channel https://t.me/clickhouse_en https://t.me/clickhouse_en
nawgszy 9y ago
Completely unrelated question, but how on earth is 'clickhouse.yandex' a valid web address?
- betaby 9y ago
  The same way https://domains.google https://domains.google is, you pay - you get your own TLD
manigandham 9y ago
I'd also recommend Druid, MemSQL, SnappyData, MapD and other column-oriented databases. Any of them can partition on a time column with full SQL and extremely fast aggregations and high compression that come from columnar storage.
- cevian 9y ago
  Hi, you've posted this notion that partitioning a column store by time would yield the same result as TimescaleDB a few times, so thought we'd jump in and clear things up. We fully agree that column stores have their place, particularly if you have a massive number of metrics, and all you care are roll-ups on single column axes. There are some major differences between TimescaleDB and column stores. Namely, TimescaleDB supports a lot of features that column stores in general do not. - Secondary Indexes. - Transactional semantics. - Can operate on data sets greater than available memory (doesn't have to be all in-memory unlike memSQL and some others). Time-series data is voluminous. - A whole bunch of specialized time-based optimizations that optimize query plans when working with time-based indexes and data. - Constraints - Including foreign keys. - Triggers - Joins with relational data. - Full SQL - allowing you to use complex queries and window functions - Compatible with data tools that use SQL - which gets you gets you the richest ecosystem of tools in the data world - The full gamut of Postgres datatypes including JSON/B and GIS location data - 20+ years of reliability, tested backups, live streaming replication, etc. - Geospatial support through best-in-class PostGIS And of course, we're only getting started :)

cbcoutinho 9y ago

We use a proprietary database system that uses a 'flat-file' format (no idea what that means) and is primarily time series based due to the fact that we're logging sensor data. Since it's primarily a backend, you can't access it outside of their proprietary gui. It's also accessible as a linked server via SQL Server, but this is slow as hell for non-trivial queries. We use it within a power plant setting where we heavily prioritize db-writes, which this software is apparently very good in, and db-reads are less of a focus. I'm not sure if moving to another db system would be beneficial, but I would be very grateful if accessiblity could be much less of a hack

BillinghamJ 9y ago
We have a similar setup for our telematics data (insurance company). It’s stored in files which contain DEFLATE’d BSON. Similarly, we have a custom GUI for viewing it, but do also have some terminal tools which can be used. The solution to larger processing simply seems to be loading the files and batching them through whatever processing we want and storing the output in a proper database - which can then be queried instantly. For a lot of time series data, this does seem like a pretty decent approach.

dx034 9y ago

What I often miss with these kinds of databases is compression capabilities. I currently use my own delta-of-delta encoding for time series data (stored in postgres) as that gave me much better compression than any of the time series databases I tested. It's not that storage isn't available, it's easy to store terrabytes of data. But really fast storage is still expensive and rare. Being able to compress time series data to 3-5% of the raw value allows keeping most in memory, speeding up reading writing and analysis. InfluxDB is quite good at that but unfortunately, they struggle storing many small datasets.

anonu 9y ago

I almost thought this was going to be an ad for KX as part of the subject is their slogan... Glad to see open source tools in this space gaining traction.