6 ms·

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 ba

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.

mayhemchaos 6y ago
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. 100% opposite of what a CLI interface should be.
akulkarni 6y ago
Welcome to the TimescaleDB community! In case it is helpful, we wrote a tool to easily migrate from InfluxDB to TimescaleDB: https://docs.timescale.com/latest/tutorials/outflux https://docs.timescale.com/latest/tutorials/outflux
- brokentone 6y ago
  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.
  - akulkarni 6y ago
    One of our designers teamed up with one of our web developers, went away for a week, and came back with this website for it: https://www.outfluxdata.com/ https://www.outfluxdata.com/
    - RedShift1 6y ago
      It's missing some kind of wavy background thing in the hero section.
1996 6y ago
Speaking from my own experience, you may save yourself some future effort by moving directly to clickhouse. Timescale brings its own issues. If your goal is performance, you will be better served by clickhouse.
- RedShift1 6y ago
  Can you elaborate on those issues?
  - 1996 6y ago
    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.
    - cevian 6y ago
      (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. [1] https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://blog.timescale.com/blog/building-columnar-compressio...
samhld 6y ago
(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)`. :).
- RedShift1 6y ago
  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).
  - samhld 6y ago
    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.
- kev009 6y ago
  Blame the customer. Nice solution, architect!
  - samhld 6y ago
    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.
jstrong 6y ago
it's not that hard: `select max from (select max(temperature) from measurement_name group by time(1h)) where max > 10`
- RedShift1 6y ago
  Does not produce the desired result, see elsewhere in this thread for why this doesn't work
  - jstrong 6y ago
    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?
    - RedShift1 6y ago
      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.

mayhemchaos 6y ago

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. 100% opposite of what a CLI interface should be.

akulkarni 6y ago

Welcome to the TimescaleDB community! In case it is helpful, we wrote a tool to easily migrate from InfluxDB to TimescaleDB: https://docs.timescale.com/latest/tutorials/outflux https://docs.timescale.com/latest/tutorials/outflux

brokentone 6y ago
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.
- akulkarni 6y ago
  One of our designers teamed up with one of our web developers, went away for a week, and came back with this website for it: https://www.outfluxdata.com/ https://www.outfluxdata.com/
  - RedShift1 6y ago
    It's missing some kind of wavy background thing in the hero section.

1996 6y ago

Speaking from my own experience, you may save yourself some future effort by moving directly to clickhouse. Timescale brings its own issues. If your goal is performance, you will be better served by clickhouse.

RedShift1 6y ago
Can you elaborate on those issues?
- 1996 6y ago
  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.
  - cevian 6y ago
    (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. [1] https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://blog.timescale.com/blog/building-columnar-compressio...

samhld 6y ago

(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)`. :).

RedShift1 6y ago
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).
- samhld 6y ago
  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.
kev009 6y ago
Blame the customer. Nice solution, architect!
- samhld 6y ago
  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.

jstrong 6y ago

it's not that hard: `select max from (select max(temperature) from measurement_name group by time(1h)) where max > 10`

RedShift1 6y ago
Does not produce the desired result, see elsewhere in this thread for why this doesn't work
- jstrong 6y ago
  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?
  - RedShift1 6y ago
    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.