0

I'm seeking advice on how to optimize my TimescaleDB setup, which handles a large volume of time-series data. I have around 20,000 time-series profiles with a one-year duration, using a quarterly time resolution (4 timestamps per hour). This amounts to approximately 700 million entries. My database is hosted on an Azure PostgreSQL server.

Here are the details of my setup:

Hardware Specifications:

4 vCores 16 GiB memory 512 GB storage Database Structure: I have two tables, one for the load profiles with the columns (id, time, value, sensor_id), and another table with the columns (id, sensor_id). There are two indexes on the load profile table, one on (sensor_id, time), and another on sensor_id.

Sample Query: A typical query I use to aggregate data is:

SELECT AVG(value), time
FROM public.loadprofilepool
WHERE sensor_id IN (
    SELECT id 
    FROM public.sensor_table
    ORDER BY RANDOM()
    LIMIT 500
)
GROUP BY time;

Please note that this is a sample query where the list of sensor_ids is generated on the fly. In a real situation, the list of ids would come from elsewhere.

Data Distribution: For now, there are 24 * 4 * 365 rows (one year duration, quarterly) per sensor and there are 20,000 sensors. In the future, there will also be live sensor data, which data distribution will depend on the specific sensor.

Performance Metrics: When running these queries, the CPU usage does not exceed 20% and memory usage is constant at about 40%.

Given these details, I'm struggling with query speed. Extracting 10 to 1000 profiles and summing them up to generate a timeseries for each timestamp currently takes about 5 to 20 seconds, whereas my target is less than 5 seconds.

My questions are as follows:

  1. Is my current setup the most efficient for handling and querying this volume and type of time-series data? If not, could you suggest alternative methods? I've considered NoSQL databases, cloud storage with Zarr or NetCDF files, but I'm not sure which, if any, would be more suitable.

  2. How can I optimize my current setup to achieve faster query results? Are there specific TimescaleDB or PostgreSQL configurations or optimizations, indexing strategies, or query formulation tactics that would help improve performance?

Thank you in advance for your help. Any suggestions or guidance would be greatly appreciated.

Best regards, Hannes

2
  • Hey! can you confirm what version of Timescaledb you have there?
    – jonatasdp
    Jul 5, 2023 at 15:47
  • Hey. My version is 2.5.1.
    – Hannes
    Jul 6, 2023 at 7:33

1 Answer 1

0

Sample Query: A typical query I use to aggregate data is:

  1. Is it a real query?
  2. What is the need of ORDER BY RANDOM()?
  3. Can you share how you build your queries to simulate what you want?

Timescaledb is a extension that will help you with the time series data partitioning the data and make it accessible through several "chunks".

Every chunk is a table with data from a specific time interval. If the query order by is random, and no where clause limiting the data, it will try to fetch and calculate over all the data and it will be very expensive.

If you're using the timescaledb extension, try to compute the data that you'll query often using the continuous aggregates feature, which will be feeding a materialized view and make results pre-processed and cached.

2
  • 1. In reality, I would have a list of sensor ids (string, max 100 characters). These strings are mapped to integer ids in the sensor_table table. With these integer ids I would query the loadprofiletable. 2. In practice the set of sensors (which can include thousands of sensors) will not be the same so I am simulating the retrieval of different sets of sensors. That's why I am creating the list of ids by randomly sampling the sensor_id table for (integer) ids. 3. So in the query the list of ids would not be generated by random. Note that this part is not the bottleneck in the query time.
    – Hannes
    Jul 6, 2023 at 7:45
  • Got it! thanks for sharing.
    – jonatasdp
    Jul 6, 2023 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.