ListenBrainz moves to TimescaleDB • Hacker News Board

Re: ListenBrainz moves to TimescaleDB

#81

Post by enordstr » Wed, Jul 22, 2020, 9:16 PM UTC

jarym wrote: post #60

mfreed wrote: post #19

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

Have you been following ZHeap and do you think Timescale will benefit from a storage engine like that (less write amplification)?

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.

Re: ListenBrainz moves to TimescaleDB

#82

Post by sgt » Wed, Jul 22, 2020, 9:43 PM UTC

samhld wrote: post #54

sgt wrote: post #43

Earlier quoted context omitted.

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.

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.

Re: ListenBrainz moves to TimescaleDB

#83

Post by jstrong » Wed, Jul 22, 2020, 11:27 PM UTC

120bits wrote: post #42

Interesting read and thanks for sharing. 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…

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.

Re: ListenBrainz moves to TimescaleDB

#84

Post by jstrong » Wed, Jul 22, 2020, 11:29 PM UTC

RedShift1 wrote: post #18

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.

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

Re: ListenBrainz moves to TimescaleDB

#85

Post by jstrong » Wed, Jul 22, 2020, 11:34 PM UTC

willvarfar wrote: post #61

samhld wrote: post #55

Earlier quoted context omitted.

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

Question for influx solution architect: How do you delete points from a specific measurement in a specific retention policy?

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.

Re: ListenBrainz moves to TimescaleDB

#86

Post by RedShift1 » Thu, Jul 23, 2020, 1:12 AM UTC

jstrong wrote: post #84

RedShift1 wrote: post #18

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.

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

Does not produce the desired result, see elsewhere in this thread for why this doesn't work

Re: ListenBrainz moves to TimescaleDB

#87

Post by jstrong » Thu, Jul 23, 2020, 1:48 AM UTC

RedShift1 wrote: post #86

jstrong wrote: post #84

Earlier quoted context omitted.

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

Does not produce the desired result, see elsewhere in this thread for why this doesn't work

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?

Re: ListenBrainz moves to TimescaleDB

#88

Post by jstrong » Thu, Jul 23, 2020, 1:53 AM UTC

nwmcsween wrote: post #75

1996 wrote: post #38

Earlier quoted context omitted.

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.

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'!

Re: ListenBrainz moves to TimescaleDB

#89

Post by willvarfar » Thu, Jul 23, 2020, 4:37 AM UTC

jstrong wrote: post #85

willvarfar wrote: post #61

Earlier quoted context omitted.

Question for influx solution architect: How do you delete points from a specific measurement in a specific retention policy?

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.

You didn’t specify the retention policy...?

Re: ListenBrainz moves to TimescaleDB

#90

Post by RedShift1 » Thu, Jul 23, 2020, 8:51 AM UTC

jstrong wrote: post #87

RedShift1 wrote: post #86

Earlier quoted context omitted.

Does not produce the desired result, see elsewhere in this thread for why this doesn't work

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…

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.