3 ms·

I think the broader point is that Tgres is generally focused on computing regular aggregations. Once you do that aggregation, you lose significant information

by mfreed 9y ago

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 Timescale to store raw data in one hypertable (with a shorter data retention policy), and aggregations in a separate table (with a longer data retention). 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 https://blog.timescale.com/timescaledb-vs-6a696248104e I don't see a reason this wouldn't apply to Tgres' use of native storage as well...but once you do aggregations (say, per minute), your tables are just much smaller (only 525K minutes / year), so it perhaps matters less.

gtrubetskoy 9y ago
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.
- riku_iki 9y ago
  > 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.
  - gtrubetskoy 9y ago
    > 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. Yes, when dealing with database performance understanding this goes with the territory. The "game" here is to organize data in such way that the stuff you read inadvertently is something that you will need eventually (as in in a few microseconds). This is where things like CLUSTER and BRIN indexes become important, and this is also why partitioning is a win.
riku_iki 9y ago
> 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 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-postgresql-efficiently/ https://grisha.org/blog/2015/09/23/storing-time-series-in-po...
- RobAtticus 9y ago
  We used a pretty basic approach for both: 12 columns - timestamp, hostname, 10 CPU metrics.

by mfreed 9y ago

gtrubetskoy 9y ago

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.

riku_iki 9y ago
> 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.
- gtrubetskoy 9y ago
  > 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. Yes, when dealing with database performance understanding this goes with the territory. The "game" here is to organize data in such way that the stuff you read inadvertently is something that you will need eventually (as in in a few microseconds). This is where things like CLUSTER and BRIN indexes become important, and this is also why partitioning is a win.

riku_iki 9y ago

> 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 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-postgresql-efficiently/ https://grisha.org/blog/2015/09/23/storing-time-series-in-po...

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