CODEWITHSUNDEEP
postgresqltime-seriestimescaledbhypertable
Hitanshu
Hitanshu

Reputation: 11

How to increase performance of TimescaleDB?

I have installed Postgresql 12 with TimescaleDB.

I have two tables in database

  1. Sensor_Data
  2. Sensor_Data_TS (Timescale Hypertable). Both the tables have same columns, same data and indexing is done on sensor_id and time in both the tables.

Now when queries are executed to insert or query data from these two tables to check performance difference, query time is coming almost same. I have inserted around 500 Million records to check the performance difference but sometimes Sensor_Data table is returning data faster than hypertable.

Queries run to create hypertable.

CREATE TABLE Sensor_Data_TS (LIKE Sensor_Data INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);

SELECT create_hypertable('Sensor_Data_TS', 'time', chunk_time_interval => 86400000);

CREATE INDEX ON Sensor_Data_TS (sensor_id, time DESC);

Used "timescaledb-tune" command to configure properties for TimescaleDB.

I want to know if i am doing any mistake in configuring the hypertable ? How can I achieve fast performance with timescaleDB as they claim on their website ?

Upvotes: 1

Views: 2686

Answers (1)

jonatasdp
jonatasdp

Reputation: 1412

What is the insert rate that you achieved?

How many sensor_ids are you emulating?

I'm doing similar tests right now and using tsbs and probably it can give you some insights about it without needing to recreate all the scenarios.

If you query the data you'll see a huge difference because the query planner will have faster parallel interaction over several chunks.

You can also compress your data and segment the compression by sensor_id which will make it really fast to query the data by time and sensor_id.

I'd start trying to also run with different chunk intervals (reduce it) as 20M records per chunk is also a very huge amount of data.

Upvotes: 1

Related Questions