3 ms·

Any idea why hypertable insert rates were slower? I though hypertables were supposed to _increase_ insert rates?

leros 3y ago
Isn't data inserted into basically a normal Postgres table with hypertable extensions? I don't know the details of Timescale but that sounds like it would incur a cost of a normal Postgres insert, plus potentially extra work at insert time, plus extra work in the background to manage the hypertable.
- rahkiin 3y ago
  Not entirely. A hypertable is a postgres table chunked over time. There is the assumption that most data and queries are time-relevant, but also that older data is less relevant than new data. Indexes are per chunk. So if the query analyzer understands you only touch 2023 it can omit looking at any chunk that is from other years and keep those out of memory. Same with the indexes.
PolarizedPoutin 3y ago
Yeah I'm curious about this too. Been meaning to ask on the Timescale forums. My only guess is that there's some small extra overhead due to hypertable chunking. I know Timescale has a blog post from 2017 claiming a 20x higher insert rate but that's for inserting into a table with an index. The general wisdom for loading huge amounts of data seems to be that you should insert into a table with no indexes then build them later though. So with no index, inserting into a hypertable seems a bit slower. Timescale blog post: https://medium.com/timescale/timescaledb-vs-6a696248104e https://medium.com/timescale/timescaledb-vs-6a696248104e
- h4kor 3y ago
  Timescale hypertables automatically have an index on the timestamp. To make this more comparable you could create the same index on the normal table and test the ingestion rate.
perrygeo 3y ago
Hypertable insert rates are faster and more predictable over time. Each individual insert might incur a small bit of extra overhead, but they scale forever since each temporal chunk is indexed separately vs a regular table where the entire index needs to fit in memory. This is a case where you can't make meaningful inferences from micro-benchmarks (they tested 20k rows, you probably need 200M to start seeing the diff)
- deleted 3y ago
  [deleted]
- PolarizedPoutin 3y ago
  Thanks for the insight! It is true that I started with a micro-benchmark of 20k rows for slower inserts, but I also did some longer benchmarks with ~772 million rows.

Isn't data inserted into basically a normal Postgres table with hypertable extensions? I don't know the details of Timescale but that sounds like it would incur a cost of a normal Postgres insert, plus potentially extra work at insert time, plus extra work in the background to manage the hypertable.

rahkiin 3y ago
Not entirely. A hypertable is a postgres table chunked over time. There is the assumption that most data and queries are time-relevant, but also that older data is less relevant than new data. Indexes are per chunk. So if the query analyzer understands you only touch 2023 it can omit looking at any chunk that is from other years and keep those out of memory. Same with the indexes.

PolarizedPoutin 3y ago

Yeah I'm curious about this too. Been meaning to ask on the Timescale forums. My only guess is that there's some small extra overhead due to hypertable chunking. I know Timescale has a blog post from 2017 claiming a 20x higher insert rate but that's for inserting into a table with an index. The general wisdom for loading huge amounts of data seems to be that you should insert into a table with no indexes then build them later though. So with no index, inserting into a hypertable seems a bit slower. Timescale blog post: https://medium.com/timescale/timescaledb-vs-6a696248104e https://medium.com/timescale/timescaledb-vs-6a696248104e

h4kor 3y ago
Timescale hypertables automatically have an index on the timestamp. To make this more comparable you could create the same index on the normal table and test the ingestion rate.

perrygeo 3y ago

Hypertable insert rates are faster and more predictable over time. Each individual insert might incur a small bit of extra overhead, but they scale forever since each temporal chunk is indexed separately vs a regular table where the entire index needs to fit in memory. This is a case where you can't make meaningful inferences from micro-benchmarks (they tested 20k rows, you probably need 200M to start seeing the diff)

deleted 3y ago
[deleted]
PolarizedPoutin 3y ago
Thanks for the insight! It is true that I started with a micro-benchmark of 20k rows for slower inserts, but I also did some longer benchmarks with ~772 million rows.