Welcome to the forums @Rmwin32!
Most policies donât provide a specific parameter for setting the schedule interval when they are created and default to running once a day. One outlier is creating refresh policies for Continuous Aggregates which provide the schedule_interval option on creation.
However, all policies create jobs that you can view and modify parameters for using the informational views and alter_job function.
In your case there are two things to consider. First, how frequently the compression job runs (schedule_interval), and second how far back in time the chunkâs range_end date needs to be before the chunk will be compressed (the compress_after option of the policy).
In your case, you could set the schedule_interval for the compression job to run every hour (or 4 hours, however âquicklyâ you want the chunk to attempt to compress after a new chunk has been created), but leave the compress_after set to 1-day. With this setup, the job will run once an hour and look for any uncompressed chunks with a range_end timestamp that is older than now()-'1 day'::interval.
Basic Steps (adjust for your job details)
1. Find the âjob_idâ of the Compression Job
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_compression' AND hypertable_name='{replace with hypertable}';
2. Alter the job to run once an hour
SELECT alter_job({replace_with_job_id},schedule_interval=>'1 hour'::interval);
If you want to change configuration thatâs specific to this job, look at the config field in the timescaledb_information.jobs view and add that JSON to the alter_job request.
Compression considerations
Iâd be remiss if I didnât briefly remind you that compressing chunks as quickly as possible has two ramifications that you should consider given what you know of your application and query patterns.
First, you cannot UPDATE or DELETE data in a compressed chunk. If your application ever sends older data that would modify those chunks, youâll have to decompress them first, at least as of Feb. 2022 (itâs a limitation we continue to explore solutions to!) If thatâs not how your application works, youâre clear here.
Second, realize that when you compress a chunk, the indexes will not be the same as on uncompressed chunks because the data is transformed into columnar data. Therefore, itâs particularly good for querying specific columns of data (like aggregating a value over a long period of time) rather than something like select * from... over compressed chunks. If you request all of the columns, TimescaleDB has to recompose all of the individual columns back into full rows which takes more work and will not perform as well.
All of that is just to give context and explain some of the nuance of how and when itâs best to compress your chunks, considering your application insert and query patterns.
Let us now if you have other questions!
