Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Timescale, an open-source time-series SQL database for PostgreSQL

timescale.com

Post reply on HN
Print view View original

81–90 of 102 posts

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#81

Post by gtrubetskoy »

mfreed wrote: post #80
riku_iki wrote: post #79

Earlier quoted context omitted.

It is still not very clear at this point if Tgres approach is sufficient enough, and if timescale adds significant performance advantage over native array based Postgres implementation.

I think the broader point is that Tgres is generally focused on computing regular aggregations. Once you do that aggregation, you lose significant information about the relation between data collected at the same time, which eliminates your ability to ask a variety of questions. For some basic dashboarding/monitoring applications you don't need this, for other applications you absolutely do. So, it's pretty common in…

The not so obvious difference in the Tgres approach, and my blogs might not be doing a great job of explaining it, is that some time in Feb 2017 I significantly revamped the storage approach to what I dubbed "vertical" storage whereby a timeslot stores an array of points in which every array element represents an element of a(nother) series.

So it went from:

    series1, array[val1, val2, val3 ...] --> time direction
    series2, array[val1, val2, val3 ...]
    ...
to

    slot1, array[series1_val1, series2_val1, ...] |
    slot2, array[series1_val2, series2_val2, ...] |
                                                  ^ time dir
With this structure you can write a single row and insert data points for n series (where n is array length) in a single row insert.

Thus, if I have 10,000 series, and my arrays are 1000-long, I can insert a data point for each of the 10K series in only 10 row inserts. This only works if the data points for all series for the same slot arrive at approximately same time, which in a monitoring-like scenario they usually do, but in other situations might not be the case.

The flip side of this approach is that querying the data then becomes less efficient because to read one data point of a series you end up reading an array-length of data points you might not care about for this particular query.

Also, tgres takes the round-robin approach, versus the timed partition approach and that's completely apples and oranges when it comes to performance. The round-robin approach also works only if the data points are evenly spaced (or transformed to be evenly spaced on the fly, which is what tgres Go code does), and again, it's hard to judge whether that's fundamentally "good" or "bad".

I can see how readers of this thread my be looking for which technique is faster, but it's just not that simple, and very much depends on the what the actual requirements are. The round-robin versus timed partition is also not mutually exclusive, you can combine the two, which may or may not be faster, not sure, the devil is in the details.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#82

Post by riku_iki »

mfreed wrote: post #80
riku_iki wrote: post #79

Earlier quoted context omitted.

It is still not very clear at this point if Tgres approach is sufficient enough, and if timescale adds significant performance advantage over native array based Postgres implementation.

I think the broader point is that Tgres is generally focused on computing regular aggregations. Once you do that aggregation, you lose significant information about the relation between data collected at the same time, which eliminates your ability to ask a variety of questions. For some basic dashboarding/monitoring applications you don't need this, for other applications you absolutely do. So, it's pretty common in…

> Regarding native storage, the insert rate really goes down as your table gets large, e.g., Timescale gets 20x higher throughput than PG: https://blog.timescale.com/timescaledb-vs-6a696248104e

I actually looked at this benchmark briefly, but couldn't find what kind of PostgreSQL schema you used there. Did you use similar array based schema as was described by Tgress author in his post? https://grisha.org/blog/2015/09/23/storing-time-series-in-po...

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#83

Post by riku_iki »

gtrubetskoy wrote: post #81
mfreed wrote: post #80

Earlier quoted context omitted.

I think the broader point is that Tgres is generally focused on computing regular aggregations. Once you do that aggregation, you lose significant information about the relation between data collected at the same time, which eliminates your ability to ask a variety of questions. For some basic dashboarding/monitoring applications you don't need this, for other applications you absolutely do. So, it's pretty common in…

The not so obvious difference in the Tgres approach, and my blogs might not be doing a great job of explaining it, is that some time in Feb 2017 I significantly revamped the storage approach to what I dubbed "vertical" storage whereby a timeslot stores an array of points in which every array element represents an element of a(nother) series. So it went from: series1, array[val1, val2, val3 ...] --> time direction ser…

> to read one data point of a series you end up reading an array-length of data points you might not care about for this particular query.

But DBs and FSs operate on pages of data and not individual records, so you will be reading that row anyway, and likely much more.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#84

Post by RobAtticus »

riku_iki wrote: post #82
mfreed wrote: post #80

Earlier quoted context omitted.

I think the broader point is that Tgres is generally focused on computing regular aggregations. Once you do that aggregation, you lose significant information about the relation between data collected at the same time, which eliminates your ability to ask a variety of questions. For some basic dashboarding/monitoring applications you don't need this, for other applications you absolutely do. So, it's pretty common in…

> Regarding native storage, the insert rate really goes down as your table gets large, e.g., Timescale gets 20x higher throughput than PG: https://blog.timescale.com/timescaledb-vs-6a696248104e I actually looked at this benchmark briefly, but couldn't find what kind of PostgreSQL schema you used there. Did you use similar array based schema as was described by Tgress author in his post? https://grisha.org/blog/2015/0…

We used a pretty basic approach for both: 12 columns - timestamp, hostname, 10 CPU metrics.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#85

Post by pauldix »

scrollaway wrote: post #51
mfreed wrote: post #10

Earlier quoted context omitted.

We're talking with them...and you can help: https://github.com/timescale/timescaledb/issues/65

I submitted a request; my company's not huge but I hope it helps. Timescale looks like the most promising replacement to InfluxDB on the market. Influx has been a source of pain, data corruption and other various issues; what a world it would be if we could use timescale! The main blocker for us is Grafana support actually. I know Grafana is working on a Postgres connector; I am quite excited about this.

You might have experienced data corruption in an old version of InfluxDB, but we haven't had any reports of that kind of thing in over two years. There are certainly still things to be improved, which we're doing all the time, but we take data integrity seriously.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#86

Post by shady-lady »

mfreed wrote: post #77
mfreed wrote: post #75

Earlier quoted context omitted.

There's not really any meaningful extra size for Timescale compared to standard Postgres. I mean, there's a little extra information about each chunk (table name, its constraints, triggers, etc), and we cache this information in memory to speed up the query/insert side of things. But it's pretty common for these chunks to be on the order of 100s MB to GB, so this is just noise compared to the underlying data/indexing…

Looking at the tables we used for recent benchmarking comparing Postgres vs. Timescale ( https://blog.timescale.com/timescaledb-vs-6a696248104e ): PG 100M rows: 30.98GB (1 table) TS 100M rows: 30.93GB (1 hypertable, 6 chunks) Same results for the 1B table, just 10x larger (and TS has 10x more chunks).

Wow. impressive. would have thought there would have been more overhead.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#87

Post by scrollaway »

pauldix wrote: post #85
scrollaway wrote: post #51

Earlier quoted context omitted.

I submitted a request; my company's not huge but I hope it helps. Timescale looks like the most promising replacement to InfluxDB on the market. Influx has been a source of pain, data corruption and other various issues; what a world it would be if we could use timescale! The main blocker for us is Grafana support actually. I know Grafana is working on a Postgres connector; I am quite excited about this.

You might have experienced data corruption in an old version of InfluxDB, but we haven't had any reports of that kind of thing in over two years. There are certainly still things to be improved, which we're doing all the time, but we take data integrity seriously.

I shouldn't have said data corruption, I just meant time corruption: https://github.com/influxdata/influxdb/issues/8424

I was never able to fix this.

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#88

Post by rohannair »

daurnimator wrote: post #23

Could you contrast this with the approaches mentioned in the series of blog posts starting here: https://grisha.org/blog/2015/09/23/storing-time-series-in-po... That blog post grew to be tgres http://github.com/tgres/tgres https://grisha.org/blog/2017/03/22/tgres-0-dot-10-dot-0b-tim...

The guy wrote something about TimescaleDB as well: https://grisha.org/blog/2017/07/13/timescaledb/

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#89

Post by orion138 »

Great work! I was curious about a few things:

1) Are you planning on using citus for clustering? Or will you have your own clustering implementation separate from Citus?

2) Can you still use barman, wal-e, etc for backups?

3) What are you guys using to generate docs.timescale.com? :)

4) Do you use any sort of custom on disk format?

5) Do you plan on implementing any sort of delta compression?

6) Is there/do you plan to have support for creating roll up/aggregation tables?

Cheers!

Re: Timescale, an open-source time-series SQL database for PostgreSQL

#90

Post by RobAtticus »

orion138 wrote: post #89

Great work! I was curious about a few things: 1) Are you planning on using citus for clustering? Or will you have your own clustering implementation separate from Citus? 2) Can you still use barman, wal-e, etc for backups? 3) What are you guys using to generate docs.timescale.com? :) 4) Do you use any sort of custom on disk format? 5) Do you plan on implementing any sort of delta compression? 6) Is there/do you plan…

Thanks, great questions!

1) We are currently exploring all options for clustering, though we are likely to try something on our own. No final decisions made yet though.

2) One of the next tutorials we'd like to do is how to setup using Timescale with wal-e for backups (we use this in a hosted service we have). Generally we should work with tools that work with PostgreSQL, we just want to make sure we cover all the caveats.

3) It's a custom solution we've built sort of organically that converts Markdown files (with some custom syntax) into HTML. :)

4) Currently we do not.

5) We have had high level talks about various ways to better compress data including delta compression, but nothing definitive yet. We do find just running on ZFS gives 3-4x compression, so that’s already a nice win if compression a priority.

6) This is definitely on our roadmap but again is also in the early stages.

Thanks!

Post reply on HN