Benchmark shows that plain postgres is significantly faster

I read the TimescaleDoc " Why Use TimescaleDB over relational databases?" (https://docs.timescale.com/timescaledb/latest/overview/how-does-it-compare/timescaledb-vs-postgres/#much-higher-ingest-rates). Here is shown in an image that inserting data works much faster with the help of timescale.

But in my selfmade performance test, I get the opposite as result:

The data is inerserted in groups of 10000. The table table_timescale contains the hypertable. The inserted timestamps are contiguous and not random. This detail helps timescale a lot. But yet it does not reach the plain postgres speed.

Why do I have such different results in comparison to the documentation?

@Malte,

Thanks for reaching out. Without more detail about your set and data, it’s nearly impossible to say why you’re seeing these results.

I can say that I’ve just been spending the last few weeks testing the newest Timescale and Postgres (both partitioned and unpartitioned) with upwards of 3 billion rows and Timescale has never been slower than Postgres.

It’s also interesting to me that the shapes of your data are exactly parallel which doesn’t seem correct to me either. If TimescaleDB is setup correctly and partitioned correctly, I’d expect that trend to be more consistent based on hundreds of benchmarks I’ve run over the last two years.

Are you using a specific tool or something you’ve written yourself?

Thanks for your answer. I will try to give you the details.

For my performance test i am using python. I insert the data with the function psycopg2.extras.execute_values() and 10000 datapoints per call. The time needed to execute the function is written down. Each datapoint contains a timestamp, increasing 1 second per datapoint. When tim is at the end of 2021, it starts again at the beginnning of the year 2021. In the test I have inserted 100,000,000 datapoints into both tables.

My Virtual Machine has 16GB Memory and runs Windows 10.


And this is what the data looks like in the database:

test_data=# select * from table_timescale order by id asc limit 10;
id | tagid | tim | v
----±------±--------------------±—
0 | 68 | 2021-01-01 00:00:00 | 2
1 | 189 | 2021-01-01 00:00:01 | 7
2 | 227 | 2021-01-01 00:00:02 | 8
3 | 40 | 2021-01-01 00:00:03 | 3
4 | 84 | 2021-01-01 00:00:04 | 8
5 | 52 | 2021-01-01 00:00:05 | 6
6 | 124 | 2021-01-01 00:00:06 | 10
7 | 234 | 2021-01-01 00:00:07 | 10
8 | 197 | 2021-01-01 00:00:08 | 10
9 | 4 | 2021-01-01 00:00:09 | 8
(10 Zeilen)

test_data=# SELECT * from timescaledb_information.dimensions;
hypertable_schema | hypertable_name | dimension_number | column_name | column_type | dimension_type | time_interval | integer_interval | integer_now_func | num_partitions
-------------------±----------------±-----------------±------------±----------------------------±---------------±--------------±-----------------±-----------------±---------------
master | table_timescale | 1 | tim | timestamp without time zone | Time | 2 days | | |
(1 Zeile)


I set up timescale with timescale_tune and accapted the default settings. Additionally I reduced the chunk size from 7 days to 1 day.

If you need further information please let me know. Thanks for helping me :slight_smile:

1 Like

A few possible ideas with given your additional detail.

First, the Windows filesystem might impact a partitioned workload in a way we are unaware of. We don’t have any current benchmark experience there. It’s plausible that there is some interplay with the storage and the number of chunks. Again, all of our benchmarks are with a Linux distro (often Ubuntu), so maybe there’s a performance issue with partitions and Windows??

Second, you’re probably experiencing degredation with TimescaleDB is that you’re using a single worker based on your program description. Every benchmark we publish for testing/pushing ingest speed as high as possible use multiple workers because the smaller chunks generally allow just the right portions of indexes to be in memory while inserts are happening. At one thread, you’re probably incurring some planning penalty, although a 10% impact still feels a bit steep to me (which might point back to the Windows component)

Finally, I wouldn’t lower the chunk_time_interval to 1 day. We recently published a blog post on some considerations when setting this, and TBH, having 1 day chunks for 100 million rows feels pretty small here unless there’s another compelling reason to set it differently. A week or a month probably makes more sense for your testing.

1 Like

Third: Setting is back to default :slight_smile:

Second: Why do you think that I am using a single worker?
postgresql.conf:

  • timescaledb.max_background_workers = 8
  • max_parallel_workers = 4
  • max_worker_processes = 15

Hey @Malte,

Not PostgreSQL worker. Your Python script, as described, makes one connection to ingest 10,000 rows at a time. If you’re going for speed, multiple connections, all trying to push data as fast as possible, is the only way to achieve that.

1 Like

Wouldn’t it be expected that a single insert on TimescaleDB is slower than on vanilla PostgreSQL, because extra work has to be performed? If yes, then indeed, a series of inserts performed serially would see that result as well. The graphs look like that, too.
In order to see a performance improvement from inserting via TimescaleDB I would assume that we need to produce contention, which will slow down vanilla PostgreSQL, but TimescaleDB will profit from using its chunks model. Contention is what happens in the real world, when potentially millions of IoT devices send sensor data every second or so.
If you don’t have contention, because you have only a few clients, but they are heavy users, then you are probably better off with vanilla PostgreSQL in terms of insertion speed. It may be different for querying your data, because that could produce contention even with just a handful of queries.

1 Like