Policy_compression job priority

TimescaleDB and PostgreSQL
1

Hi,
Is there a way to lower the priority of compression job in order to give more time for insert queries ?

2

Hey @rems,

Could you provide a little more clarification on your current setup? For instance:

  • chunk_time_interval of the hypertable
  • ingest rate (rows/min is probably a good place to start)
  • TimescaleDB version
  • current compression policy settings? (select * from timescaledb_information.jobs and select * from timescaledb_information.job_stats for the job in question)

Thanks!

3

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.

Regards

4

If the 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_segmentby and 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 chunk_time_interval:

  • When you change chunk_time_interval with 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 segmentby and orderby) would probably be helpful for any next steps.

5

Thanks for all details.
I’ll try to fine tune those parameters.