Query Performance Comparison: Uncompressed vs. Compressed Data in TimescaleDB

TimescaleDB and PostgreSQL
,
1

Hello Team,

I’ve been running some performance tests and encountered an interesting issue: reading from uncompressed data takes longer than reading from compressed data.

Summary

I’ve been testing query performance across different time ranges on both compressed and uncompressed data to understand any differences in response times. Below are the details of the queries I ran:

  1. Queries Executed:
  • Queries for data retrieval for analysis.
  1. Time Durations Tested:
  • I repeated each query for different time ranges:
    • 8 hours
    • 1 month
    • 3 months
    • 6 months
    • 1 year
  1. Observation:
  • For all time ranges tested, I consistently observed that response times for uncompressed data were notably longer than those for compressed data.

Questions

  1. Is this expected behaviour, or could there be an optimization I’m missing for uncompressed data in TimescaleDB?
  2. Are there specific configurations that could help improve query performance on uncompressed data?
  3. Has anyone else experienced similar issues, and if so, how did you address them?

I would greatly appreciate any insights or suggestions you may have for improving performance on uncompressed data.

Thank you in advance for your help!

1 Like
2

Hey! Yes, this is a knowing scenario.

Can you share the query plan of your scenario that is slower? How much slower? Remember that compression is a CPU/IO trade off. So, if databases generally are IO bound, if you have free CPU, it will use less IO but more CPU, making it faster.

Another detail is that segmentby can be used as an index of your data. You may need to have a composite index using your time, <segmentby_column>.

1 Like
3

Thank you very much @jonatasdp for your quick response.

1 Like