Is there a way to lower the priority of compression job in order to give more time for insert queries ?
Could you provide a little more clarification on your current setup? For instance:
chunk_time_intervalof the hypertable
- ingest rate (rows/min is probably a good place to start)
- TimescaleDB version
- current compression policy settings? (
select * from timescaledb_information.jobsand
select * from timescaledb_information.job_statsfor the job in question)
Chunk time interval is 7days
i have 24000 rows/minutes mainly on 2 tables
last compression time was 15minutes and 13minutes.
i am running timescaledb-postgresql-14_2.5.0
for compression policies, i use default one: Compression Policy [10xx]
actually the only “issue” is my application is buffering insert queries more than usual during this compression time.
So i was wondering if there is a way to slow down the compression job.
chunk_time_interval is 7 days, then in theory compression should only take resources once every 7 days or so. Compression happens per chunk, and the chunk isn’t compressed until the
range_end timestamp of that chunk is older than
compress_after=>... in the policy. Even if
compress_after is set to
10 minutes and the policy runs every minute (hypothetical example), it’s still going to take ~7 days before the compression policy would actually trigger and start compressing the chunk.
Assuming that’s when you see this resource issue impact inserts, it probably is just a size/work issue. At 24,000 rows/minute, your chunks have ~242 million rows and are certainly 10s of GBs in size I’d assume. Depending on the resources available to the Postgres server and your compression settings (
compress_orderby), it does take time to do the compression on hundreds of millions of rows.
If the buffering is an issue during these weekly compression jobs, one option is to reduce the
chunk_time_interval to a few days (maybe 2 or 3 days), which would reduce the size of each chunk and improve the overall compression speed.
A few things on reducing the
- When you change
set_chunk_time_interval- the change only takes effect starting with the next created chunk. So, it won’t be an instant change.
- I’d encourage you to upgrade to 2.7.0+ ASAP. There are some really nice improvements in there, once of which is better handling of
now()for chunk exclusion during planning. If you lower your chunk time, you’ll end up with more chunks, so having this awesome improvement will be a huge help for time-based queries.
Let me know if this helps or if you have other questions. Actually seeing the compression policy settings (including
orderby) would probably be helpful for any next steps.
Thanks for all details.
I’ll try to fine tune those parameters.