Fun fact: TimescaleDB exists because we were using InfluxDB + Postgres for a previous IoT project and also found it unworkable (developer experience, query language, reliability, scalability and performance, operations, etc).
We first built TimescaleDB as "Postgres for time-series" for our own needs and then decided to open-source it for others. :-)
Timescale engineer here. I'm betting we'll see a nice win; we tend to see write-mostly workloads the UNDO shouldn't be too expensive, and the smaller tuple sizes should be nice. We've built Timescale to be compatible with custom storage engines, so it should work as a drop-in, though of course until we've tested it we won't be sure.
Another Timescale engineer here. As previously pointed out, zheap should work as a drop-in in TimescaleDB. In fact, I just tried it and it works. However, it currently requires an unmerged PR to work properly: https://github.com/timescale/timescaledb/pull/2082, as well as further testing.
Not too long ago, I was asked to work on some analytics project and it required time-series data. I'm not a rockstar programmer and don't really know much about trends. So, I ended up googling and stumble upon InfluxDB. It felt like that right choice and I started playing with it. As the time passed, I realized that it might be a good software and I'm sure people love InfluxDB, but it wasn't the right choice for me. I didn't really like the docs, maybe its good now. And I had the same feeling about query syntax, it felt weird.
I moved to TimescaleDB and never looked back. I have it production for almost 2 months now. 20 tables and over 100Million writes/week. One of things I really liked was staging, I don't use docker and or anything fancy. I have bash script that and it runs on centos box and all timescale extension and postgres database are packaged together.
I was impressed by the timescale compression feature. I wasn't using it earlier because I had to be careful about what columns I need to segmentby. I would love to see some more features but I'm sure timescaledb team is already on it.
to each his own - I find influxdb somewhat flaky but the best part about it is not having to write the atrocious sql queries I would need to to get the same kind of windowed aggregations. `group by time(1h)` and so on is pretty handy.
Thanks. I love developer comments -- we've all written things like "this is hacky and ugly" in our code :)
# Note: influxdb-python first replaces each backslash in the username with two backslashes
# and because in influx queries, we have to escape each backslash, overall each backslash
# must be replaced by 4 backslashes. Yes, this is hacky and ugly.
I wrote that comment 3 years ago! It definitely took me a week or two worth of work to figure out what was happening there. The problem was exacerbated by the fact that the influx cli client then had different behaviour compared to the python client. Not sure if that has been fixed, but this isn't code that I'm really proud of. :D
Excited to find out about the project. I regularly use MusicBrainz but didn't know about the sister projects.
I'll definitely be creating a ListenBrainz account. As a long time last.fm user I occasionally worry about the future of the platform. (There have been long stretches of time where it seems to have been in maintenance mode). You seem to support bulk importing last.fm data right?
Yep, we do support bulk import of last.fm data [0].
We also have a Spotify importer that automatically imports stuff from Spotify, if you use Spotify, I would definitely recommend setting that up.
We're a really small team (all volunteers), so we don't move with as much urgency as I'd like to, but we've been making slow but steady progress over the years.
If you find any rough edges, or have any feedback, I'd be happy to hear, my email is in the HN profile. :)
I'm in the process of moving from InfluxDB to TimescaleDB myself and can't wait to get rid of the hoops I have to jump through to get InfluxDB to answer some basic questions, mostly stemming from the fact that InfluxQL doesn't support boolean expressions. Something like 'SELECT MAX(temperature) > 10 FROM...' doesn't work.
I'm personally working on migrating a DB I've had for tracking some economic time-series data from Influx to TimescaleDB, and I have to say Outflux is the most savage tool name ever.
Agreed -- every time I tried to query data from the command line I could not get anything to work. I would usually find a piece of working python code and adapt that to run the query instead.
The issue I have with clickhouse is the codebase, it's an absolute behemoth and seemingly embeds musl libc? It also uses a huge amount of SIMD intrinsics for everything when SWAR or really nothing from my view looking in would have been better (memcpy, etc).
caveat: I'm not familiar with the clickhouse codebase. however, usually "embedding" musl libc is about portability - it allows you to build an entirely static binary that can run on practically any box. is this different?
secondly, I don't get where you're coming from faulting a columnar database for using SIMD! why is memcpy better? it comes across as, 'they worked too hard making it fast'!
It would be too long. To quickly summarize, from the pain of backups (unless you setup a WAL replica, the load may take your database down), the large size of the data on disk (timescale does offer some compression now, but it's still too much), the low performance of large queries, the memory requirements - it's death by a thousand papercuts!
Don't get me wrong, timescale is a great way to get started with time series - just like sqlite is a great way to get started with databases if all you know is nosql.
However, it quickly brings its own challenges - and the new license is the cherry on the cake: it is locking you down to your own infrastructure unless you want to pay for timescale own SAAS offering (and then prey they do not alter the condition of the deal too much later)
It is just not worth it, unless you have a very small problem, or you can afford to have people concentrating on timescale maintenance - and in this case, you would be getting better bang for bucks by having these people work on clickhouse.
I'm speaking only from my own experience. I have relatively large servers dedicated to time series (about 100T of disk space, between 128 and 256 Gb of RAM). They were going to be retired for even bigger servers. Instead, we experimented with clickhouse on one of the recently decommissioned servers. We could not believe the benchmarks! Moving to clickhouse has improved the performance on about every metric. Yes, it required some minor SQL rewrites, about 1 day of work total, but unless your hardware is free and your queries are set in stone, clickhouse makes more sense.
Hi - I just want to clarify some mistake / misrepresentation about our Timescale License (TSL):
1. The TSL is not a new license, have had it in place since late 2018. What we recently announced is that multi-node TimescaleDB will be available for free under the TSL (free, source available), while (for example) clustered InfluxDB is purely proprietary (paid, closed source).
2. Our TSL license prevents offering TimescaleDB-as-a-service, it absolutely does NOT prevent you from running/offering a SaaS service or from utilizing cloud services/infra (you say "it is locking you down to your own infrastructure unless you want to pay for timescale own SAAS offering"). Specifically, Timescale offers a pure Apache-2 version and a "Timescale License" (TSL) Community version. For the TSL version, what it primarily restricts is the cloud providers like AWS and Azure from offering TimescaleDB-as-a-service (e.g., TimescaleDB Community on AWS RDS). Many thousands of companies use our community version for free to build SaaS services running on their own AWS instances.
> InfluxDB is purely proprietary (paid, closed source).
And clickhouse is not. I just suggest skipping the timescaledb step to someone migrating from influx, and going straight to clickhouse.
> For the TSL version, what it primarily restricts is the cloud providers like AWS and Azure from offering TimescaleDB-as-a-service (e.g., TimescaleDB Community on AWS RDS)
If there is some kind of emergency and I need to have the database on the cloud, this is a serious restriction. It limits my choices and constrains my actions.
> Many thousands of companies use our community version for free to build SaaS services running on their own AWS instances.
We have our servers, so it wasn't an issue. It was more of a long term concern, a chilling effect: what else may be restricted in the future?
Again, I think timescaledb has a wonderful place. It will certainly become the entry level database for timeseries.
> If there is some kind of emergency and I need to have
> the database on the cloud, this is a serious restriction.
> It limits my choices and constrains my actions.
> if the license change to allow me to deploy to a cluster
> of AWS servers (just in case we ditch our own hardawre),
You can deploy TimescaleDB on AWS servers (the TSL certainly allows it). Most of our users do. They don't run their own hardware.
You can even use our Apache-2 k8s helm charts [1] to immediately spin up a cluster of replicated TimescaleDB nodes with automated leader-election/failover and continuous incremental backup. The helm charts have first-class support for AWS EKS.
What the TSL prevents is _Amazon_ offering TimescaleDB as a paid DBaaS service. To my knowledge, none of the major cloud vendors offer Clickhouse as a first-class paid service, so that's somewhat a moot point. I guess theoretically Amazon could launch Clickhouse-as-a-service, but that theoretical possibility doesn't help you in your emergency.
Apart from license issues, Clickhouse is just really impressive: it’s a minor pain operationally, but in our tests it left all the Postgres-based timeseries solutions in the dust for real-time analytics without rollup tables.
> Clickhouse is just really impressive: it’s a minor pain operationally, but in our tests it left all the Postgres-based timeseries solutions in the dust for real-time analytics without rollup tables
There are many things I'm willing to tolerate with that level of performance!
Timescale has added their own layer of compression and columnar layouts to the Postgres row storage. That will get you to around 70% of the performance of using a dedicated column-oriented data warehouse, with the rest depending on how complex and selective your queries are.
It won't match the pure scan and computation speed of Clickhouse but the continuous aggregation feature is the recommended approach for querying large datasets (similar to Clickhouse table engines like AggregatingMergeTree).
(TimescaleDB engineer here) Some of the comments here sound technically off.
We've never seen a backup take down a machine. The backups we use are the same as Postgres which are used by millions of companies without a problem (and can be streaming incremental backups like pgBackrest, WAL-E, etc. or whole-database backups like pg_dump). As with any DB you do have to size and configure your database correctly (which these days isn't hard).
We've never seen anybody claim that ClickHouse offers significantly better compression than we do overall. Obviously compression depends heavily on data distribution and I'm sure you could make up a dataset where clickhouse does better (just as you could where TimescaleDB does better). But on real distributions we don't see this at all, we do pretty advanced columnar compression on a per-datatype basis [1], and see median space reduction of 95% from compression across users.
Large queries is a weird claim to make since Postgres has more different types of indexes than Clickhouse and has support for multiple indexes. If you are processing all of your data for all your queries then yes, click house sequential scans may be better. But that's less common, and also where TimescaleDB continuous aggregates come in.
We've seen customers successfully use our single-node version with 100s of billions of rows so claiming that we are just for small use-cases is simply untrue, and especially with the launch of multi-node TimescaleDB.
I understand people may have different preferences and experiences, but some of these felt a bit off to me.
On servers with a very high CPU load, backup done without using continuous streaming to a second server and done from this second server, something (I have stopped using timescale so I can't tell you what did) during backup caused a peak in load and IO, impacting read and write performance of the primary server, causing a cascading failure of the processes due to timeouts, eventually taking the server down due to swap issues and OOM triggering a reboot.
So we stopped doing backups. Actually, that's how we started using clickhouse: for cold storage, as the files in /var/lib/clickhouse used far less storage space and issues. Eventually the same data was sent both to timescaledb and clickhouse, in a poor's man backup. Finally, timescaledb was removed.
> As with any DB you do have to size and configure your database correctly (which these days isn't hard).
Thanks for supposing we didn't try. We did not end up with 256Gb of RAM per server for no reason.
All I'm saying is that Timescale totally has a place, but not beyond a certain scale and complexity.
> We've never seen anybody claim that ClickHouse offers significantly better compression than we do overall
Altiny does, so do a few others. mandigandham above says that you are now at 70% of what clickhouse does. I'm not saying you're not improving. It was just one of the too many issues we had to fight.
Also, you have only recently introduced compression - good, but I'm not aware if you already offer something like DateTime Codec(DoubleDelta, LZ4), or the choice of compression algorithms. LZ4 can be slow, so there is a choice between various alternatives.
For example, T64 calculates the max and min values for the encoded range, and then strips the higher bits by transposing a 64-bit matrix. Sometimes it makes sense. zStd is slower than T64 but needs to scan less data, which makes up for it. Sometimes it makes more sense.
Large databases need more flexibility.
> If you are processing all of your data for all your queries then yes, click house sequential scans may be better
I confirm, it is better.
And for some workloads, continuous aggregates make no sense.
> We've seen customers successfully use our single-node version with 100s of billions of rows so claiming that we are just for small use-cases is simply untrue, and especially with the launch of multi-node TimescaleDB
I have about 50Tb of data per server. What is below 1Tb I call "small use cases".
> I understand people may have different preferences and experiences, but some of these felt a bit off to me.
When I was trying to use timescaledb and reported weird issues, I had the same return: my use case and bug report felt "off" to the person I reported them to.
Maybe it is why they weren't addressed - or maybe much later, when reported by more clients?
Personally, I have no horse in the game. If you become better than clickhouse for my workload, and if the license change to allow me to deploy to a cluster of AWS servers (just in case we ditch our own hardawre), I will consider timescale again in the future.
For now, I'm watching it evolve, and slowly address the outstanding issues, like disk usage, and performance. By your own admission and benchmarks, you are now at 70% of what clickhouse does - in my experience, the actual difference is much higher.
But I sincerely hope you succeed and catch up, as more software diversity is always better.
- I believe the Altinity Benchmarks [0] are from 2018, on TimescaleDB 0.12.1. TimescaleDB has gotten much better since then (now on version 1.7.2), and most notably, offers native compression now (it did not then).
- I believe manigandham's 70% comment is more of an offhand estimate and not a concrete benchmark. But perhaps he can weigh in. :-)
- Re: compression algorithms, TimescaleDB now employs several best-in-class algorithms, including delta-delta, gorilla, Simple-8b RLE. Much more than just LZ4. [1]
Overall, I don't think anyone has done a real storage comparison between TimescaleDB and Clickhouse since we launched native compression. It's on our todo list, but we also welcome external benchmarks. But based on what we've found versus similar systems, I suspect our storage usage would be really similar.
(InfluxData solution architect here)
Boolean is supported. You query it in the WHERE clause.
Try `SELECT MAX(temperature) FROM ... WHERE temperature > 10`. That said, I'm not sure why you'd run a query like that in InfluxQL as it's the same as `SELECT max(temperature)`. :).
It's not the same thing. 'SELECT MAX(temperature) > 10 FROM ...' gets you all the datapoints, with a value of true or false. Moving it to the WHERE clause only gets you the datapoints where temperature is > 10. Yes, you can fill with 0 after the GROUP BY, but if all datapoints are less than 10, you get nothing back from the database. That's confusing: were all datapoints less than 10, or was there nothing registered in the time window I'm querying? Impossible to tell the difference. Not to mention some user interfaces just bailing: no data. I need to show a chart with a timeline of this condition being true or false.
Plus my actual use case is even more complex, not only do I need something like MAX(temperature) > 10, I need (MAX(temperature)) > 10 && (MAX(temperature) - MAX(dewpoint)) > 4.5).
Yep you're right -- oversight on my part. To be clear, the "not sure why you'd run a query like that" was referring to doing it the InfluxQL way (which is not the same thing)...where your results would end up being the same controlling for time range.
not a solution architect but, just delete by a very specific timestamp. this is not possible if you are writing at coarse time precisions, so don't do that.
`delete from meas_name where time='2020-07-22T15:40:58.375506762Z'` - just tried that and it deleted one row.
Huh? I don't mean to come off as blaming anyone for anything. Trying to offer a work around for their problem with InfluxQL. They usually exist. They may not be good enough for some but offering them doesn't hurt. In this case, I certainly misunderstood the need though.
ah, I see what you wanted now (`max(col) > 10...` -> [true, false, true, ..]). seems like it would be pretty easy to use numerical transformations for the same thing, depending on how you were going to be using it: `select max(col) - 10 from meas group by time(1h)` will give you negative results wherever you would have "false" in the query you wanted to do. primarily I want the timeseries/metrics database I use to be FAST over featureful, it's a specialized tool, and of course you can always just pull the data out and do whatever you want with it in code. which is to say, maybe you would be better off with something else, but that isn't necessarily a knock on influx, you know?
Depends on the use case. If you only need to do some basic stuff like give me room temperature for that room between t1 and t2, or average server usage for all servers, fine. But any more business or analytical queries like "timeline of when was this true or false", "histogram of temperatures", "Power consumption this month", "runtime in hours per shift (where each shift is an arbitrary division in time)" are left in the cold with seemingly little progress made to fit these use cases. So for a system that claims real-time visibility it has suprisingly little analytical and time-based capabilities.
(TimescaleDB co-founder). Thanks for the kind words! I feel especially proud about the first point "openness" - this is something we strive for both technically and culturally.
For example, we have a pretty active Slack channel[0] where you can ask us anything. We've probably given away $$$$ of free support over the years ;-)
We too started off with influx but it wasn't a good fit mainly due to use having issues with high cardinality. I don't know if this is still the case with current implementations, but what it boils down to is if your data is searchable by a "user_id" really look elsewhere. That might be an oversimplification but that's the gist of it.
I was fully ready to just roll my own partitioned table and gave TimescaleDB a shot. It worked well. There was a bug we ran into, but it was an existing one documented on github and was addressed pretty quickly.
I still like influx, and would use it again but beware of the cardinality issues.
timescale is a postgres extension. 'postgres as a platform' is an interesting world to live in.
postgres built-in RBAC is clunky or people would be relying on it, but an ecosystem of postgres plugins could include cleaner or smaller versions of this feature.
Even things like complex migrations (github's gh-ost, for example) could exist as DB plugins.
The following time series databases are popular right now:
* ClickHouse (this is a general-purpose OLAP database, but it is easy to adapt it to time series workloads)
* InfluxDB
* TimescaleDB
* M3DB
* Cortex
* VictoriaMetrics
The last three of these TSDBs support PromQL query language - the most practical query language for typical time series queries [1]. So I'd recommend starting from learning PromQL and then evaluating time series databases from the list above.
If you are familiar with Postgres and/or SQL, then you may want to start with TimescaleDB. It's just Postgres for time-series. Full SQL, so it's possible to be productive instantly.
In some cases it is difficult to define the table columns up front. Instead, a few tables: Object, Property, Time and Value (example below) are defined which make it possible to create new items on the fly. This works reasonably well up to a few billion records in the value table. However it does end up taking a lot of space (covering indexes/requisite memory are required for performance). It would be great to see a Postgres compatible solution that solves this problem in a more optimal way than a stock RDMS.
Object
objectId
objectName
other...
Property
propertyId
objectId FK
propertyName
other...
> If you ever write bad data to a measurement in InfluxDB, there is no way to change it
Correct me if I'm wrong, but I'm fairly certain you can just write data with the same timestamp again and it gets updated. Deleting is also easily possible.
ListenBrainz dev here. We wanted stuff like the ability to do stuff like "DELETE FROM measurement where field = blah" and that support didn't exist last time we looked. [0]
Another thing that's not mentioned in the post but was a pain point for us was that it's not easy to query for fields with "null" values [1].
I figure a lot of our pain might be because we're not as good at Influx as we are at PostgreSQL. We've been running MusicBrainz[2] for ~18 years on PostgreSQL, that knowledge will hopefully transfer over a little with Timescale.
Ok, we had an issue deleting data from a series. The delete just hung and never completed. I assumed this was not really possible on Influx. Perhaps this is an edge case.
(Solution architect at InfluxData here) Out of curiosity...what was the need for this DELETE? Deleting (not dropping) being somewhat of a "second class citizen" was a design choice to make room for more pressing time series needs. In my experience, `DELETE`ing is rarely necessary.
We work a lot with forecast data and sometimes mistakes happen and a forecast gets written to the wrong time series or for the wrong period. In these cases we do not want to drop the complete time series but only delete the erroneous parts.
While it is indeed a rare occurence, it is also very slow.
I often use metrics for giving reports via Grafana. They are usually 99% correct which is good enough for many cases. The benefit of doing so is that you already need and have nice dashboard for devs to follow what is going on with the system real time, and its just grafana account distant from customer. So we do this on several big gov systems. Sometimes customers complain that there is a slight difference between real state and what metrics show but its not a big deal and rarelly happens (when you have millions of things, is it really important to know 100% precise value in majority of contexts?)
Recently my colegue was testing some script and added some huge numbers on metric that is used for one of those reports. We had to delete those tests as customer complained that now his total invoice number jumped to trillions.
It was actually a bunch of data that was inserted coming from an IoT device that was invalid. So just deleting that time period and limiting to a tag would have been sufficient.
I'd love to hear more about how your data ingestion works. I'm thinking of implementing TimescaleDB myself, but in my initial read of the docs, the focus seemed to be managing the database, not getting data into the database...
That's not really helpful. Let's assume you have a distributed system; you probably don't want all of your system components connecting directly to TimescaleDB. You also probably want to have some layer that implements queuing and handles back pressure if it can't insert into the database at the rate that events are coming in. You may want to batch insert data.
I'd assume that most anyone building a system like this at scale has to solve these problems; does everyone roll their own?
This issue is related to slightly confusing _warnings_ that the software prints out, it doesn't effect the _correctness_ of the backups.
The warnings are produced by COPY TO, which is used by pg_dump, since COPY TO doesn't copy chunks. It is not an issue for pg_dump, since it also do COPY TO on each chunk table.
Timescale engineer here - was part of discussion about this warning. We need to do another round and see how to remove this confusion.
We first built TimescaleDB as "Postgres for time-series" for our own needs and then decided to open-source it for others. :-)