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