Chunk Compression Scheduling & Automation

The engineering problem this guide solves is narrow but consequential: how to compress aging chunks on a deterministic schedule without ever stalling ingestion, corrupting a continuous aggregate refresh, or leaving reclaimable disk locked in bloat. TimescaleDB ships native columnar compression and a background job to drive it, but the default add_compression_policy call is only the starting point. Production IoT platforms need the policy to fire in the right order relative to rollups and retention, to survive lock contention on hot hypertables, and to actually return bytes to the operating system after the row-store-to-columnar rewrite. This page is part of the broader data retention and compression lifecycle and assumes you already run TimescaleDB in production with hypertables in place.

Raw chunking alone does not guarantee storage efficiency or predictable latency. A chunk written today is optimal for ingestion — a heap that accepts out-of-order inserts cheaply — but wasteful for the analytical scans that dominate reads a week later. The scheduling layer described here moves each chunk across that boundary at the moment it stops being write-hot, and does so idempotently so a redeploy or a crashed worker never leaves the hypertable in a half-compressed state.

# Prerequisites

Compression scheduling depends on the hypertable being compression-enabled, on background workers being available to run the job, and on the segmentation columns being chosen before the first chunk compresses. Confirm each of the following before registering a policy.

• TimescaleDB 2.10 or newer on PostgreSQL 14+ (the add_compression_policy signature, compress_after, and the timescaledb_information.chunks columns used below assume this baseline).
• The target hypertable exists and its chunk_time_interval sizing matches ingestion cadence — over-fragmented chunks multiply the per-chunk compression overhead and inflate catalog size.
• timescaledb.max_background_workers is high enough to cover the compression job alongside every refresh and retention job that runs concurrently; a starved pool is the most common cause of a policy that “exists but never runs”.
• max_worker_processes is raised in tandem so background jobs never starve application connections.
• maintenance_work_mem is sized for the compression pass — the rewrite batches rows into columnar segments in memory before flushing.
• The connecting role owns the hypertable, since the policy job executes as the hypertable owner.
• You have chosen segmentby and orderby columns from your query patterns — these are structural and expensive to change once chunks are compressed.

Verify the version and worker headroom in a single pass:

-- Confirm the extension version and that background workers are actually available.
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';
SHOW timescaledb.max_background_workers;
SELECT count(*) AS running_workers
FROM pg_stat_activity
WHERE backend_type = 'TimescaleDB Background Worker Scheduler';

# Step-by-Step Implementation

The steps below map directly to the flowchart above: you enable compression on the hypertable, register the policy that evaluates the compress_after threshold, drive lock-aware compression for any out-of-band passes, then reclaim space with vacuum.

# 1. Enable compression on the hypertable

Compression is a table-level property. Set the columnar compression models parameters — segmentby and orderby — before any policy runs, because they define the physical layout every future chunk inherits. Guard the ALTER so a redeploy against an already-configured table is a no-op:

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relname = 'sensor_readings'
      AND n.nspname = 'public'
      AND c.reloptions::text LIKE '%timescaledb.compress=true%'
  ) THEN
    EXECUTE format(
      'ALTER TABLE sensor_readings SET (
        timescaledb.compress,
        timescaledb.compress_segmentby = ''device_id, metric_type'',
        timescaledb.compress_orderby = ''time DESC''
      );'
    );
  END IF;
END $$;

The choice of segmentation and ordering columns directly dictates compression ratios and query pruning efficiency. Misconfigured parameters fragment storage, degrade scan performance, or stall refreshes. The rule: segmentby columns should match your most common equality filter predicates (for example device_id), and orderby should match the time column direction used by your range queries. On multi-tenant deployments where the tenant key is high-cardinality, review the chunk indexing patterns for high-cardinality tags before settling the segmentby set.

# 2. Register the compression policy idempotently

The native scheduler evaluates each chunk’s time range against compress_after and compresses any chunk that has aged past it. Treat policy creation as declarative infrastructure — if_not_exists => TRUE makes redeploys converge rather than error:

-- Idempotent policy creation: compress chunks once they age past 30 days.
SELECT add_compression_policy(
    'sensor_readings',
    compress_after => INTERVAL '30 days',
    if_not_exists  => TRUE
);

Align compress_after with your retention boundary so the policy never spends I/O compressing chunks that a retention policy is about to drop. If raw data lives 90 days and you compress at 30, chunks spend two-thirds of their life compressed; if you compress at 85 you save almost nothing. Size the gap from the point where writes to a chunk effectively stop.

# 3. Evaluate candidates dynamically (optional)

The built-in scheduler is sufficient for steady-state workloads. When you need dynamic thresholds — compressing earlier under disk pressure, or gating on continuous aggregate progress — query timescaledb_information.chunks directly and drive compression from your own orchestration:

import psycopg
from psycopg.rows import dict_row

def evaluate_compression_candidates(conn_str: str, table_name: str, threshold_days: int = 30) -> list:
    """Return uncompressed chunks older than the threshold, oldest first."""
    with psycopg.connect(conn_str, row_factory=dict_row) as conn:
        with conn.cursor() as cur:
            # Build the interval from a real parameter; %s is not substituted inside a
            # quoted SQL literal such as INTERVAL '%s days'.
            cur.execute("""
                SELECT chunk_schema, chunk_name, range_start, range_end, is_compressed
                FROM timescaledb_information.chunks
                WHERE hypertable_name = %s
                  AND is_compressed = false
                  AND range_end < (NOW() - make_interval(days => %s))
                ORDER BY range_end ASC
                LIMIT 10;
            """, (table_name, threshold_days))
            return cur.fetchall()

# 4. Compress with lock awareness

Compression acquires an ACCESS EXCLUSIVE lock on each chunk during its transition. In high-write environments an unmanaged pass can block ingestion or trigger cascading statement timeouts. For any out-of-band compression — backfills, catch-up after an outage, disk-pressure sweeps — wrap compress_chunk in exponential backoff so transient lock conflicts retry instead of crashing the pipeline:

import time
import logging
import psycopg

logger = logging.getLogger(__name__)

def safe_compress_chunk(conn_str: str, chunk: str, max_retries: int = 3, base_backoff: float = 2.0) -> bool:
    """`chunk` must be the schema-qualified chunk name,
    e.g. '_timescaledb_internal._hyper_1_2_chunk'."""
    for attempt in range(max_retries):
        try:
            with psycopg.connect(conn_str) as conn:
                with conn.cursor() as cur:
                    cur.execute("SELECT compress_chunk(%s::regclass);", (chunk,))
                conn.commit()
            logger.info("Successfully compressed chunk: %s", chunk)
            return True
        except psycopg.OperationalError as e:
            if "lock" in str(e).lower() and attempt < max_retries - 1:
                delay = base_backoff * (2 ** attempt)
                logger.warning("Lock contention on %s. Retrying in %.1fs...", chunk, delay)
                time.sleep(delay)
                continue
            logger.error("Failed to compress %s: %s", chunk, e)
            raise
    return False

Feed safe_compress_chunk the schema-qualified names from evaluate_compression_candidates, iterating oldest-first so a rolling window of at most one chunk holds a lock at any moment.

# 5. Reclaim space with vacuum

Compression rewrites a chunk’s row store into compressed columnar batches, but the freed heap pages return to the operating system only after VACUUM runs. Relying on default autovacuum thresholds leaves bloat in heavily updated compressed chunks. VACUUM cannot run inside a function or transaction block, and per-relation vacuum timestamps live in pg_stat_all_tables — not the chunks view — so generate the statements and execute them from a client (in psql, end the query with \gexec):

-- Generate VACUUM statements for compressed chunks not vacuumed in 7 days; run them
-- outside a transaction (in psql, end the query with \gexec to execute the results).
SELECT format('VACUUM (VERBOSE, ANALYZE) %I.%I;', c.chunk_schema, c.chunk_name)
FROM timescaledb_information.chunks c
JOIN pg_stat_all_tables s
  ON s.schemaname = c.chunk_schema AND s.relname = c.chunk_name
WHERE c.is_compressed = true
  AND (GREATEST(s.last_vacuum, s.last_autovacuum) IS NULL
       OR GREATEST(s.last_vacuum, s.last_autovacuum) < NOW() - INTERVAL '7 days');

# Configuration Parameters Reference

Estimate the storage a policy will hold using a measured compression ratio $r$ (compressed size ÷ raw size). For a hypertable holding $H_{\text{drop}}$ days total and compressing after $H_{\text{hot}}$ days, at $B_{\text{day}}$ raw bytes ingested per day:

$$S_{\text{total}} = \underbrace{H_{\text{hot}} \cdot B_{\text{day}}}_{\text{raw chunks}} \;+\; \underbrace{(H_{\text{drop}} - H_{\text{hot}}) \cdot B_{\text{day}} \cdot r}_{\text{compressed chunks}}$$

For a fleet writing 40 GB/day, compress_after => 30 days, a 365-day horizon, and a measured $r = 0.08$, raw chunks hold ~1.2 TB while compressed chunks hold $(365-30)\times 40 \times 0.08 \approx 1.07$ TB — against ~14.6 TB the same window would cost uncompressed.

# Integration with Adjacent Features

Compression scheduling never runs in isolation; it is one stage of a sequenced lifecycle.

• Continuous aggregates. A continuous aggregate refresh policy reads both compressed and uncompressed chunks during incremental materialization. Compress a chunk before its buckets are finalized and a refresh whose window reaches back into it can fail or skip ranges. Schedule compression to run after the refresh policy design and scheduling window has closed over that chunk, or add a compress_after buffer that exceeds your materialization lag. When refreshes and compression share the scheduler, review how the asynchronous execution queue serializes them so neither starves.
• Retention. Compression and TTL policy enforcement both key off a chunk’s time range. Order matters: a chunk should be downsampled and compressed before it becomes eligible to drop. Setting compress_after well below drop_after guarantees that ordering.
• Partitioning. On multi-tenant hypertables, space partitioning for multi-tenant IoT keeps per-tenant chunks small enough that each compression pass is short and holds its ACCESS EXCLUSIVE lock briefly.

# Performance Validation

After a policy has run, confirm chunks are actually compressing and the job is healthy. Compression status and per-chunk savings come from the chunks view:

-- Compression coverage and before/after bytes per hypertable.
SELECT
    hypertable_name,
    count(*) FILTER (WHERE is_compressed)       AS compressed_chunks,
    count(*) FILTER (WHERE NOT is_compressed)   AS uncompressed_chunks,
    pg_size_pretty(sum(before_compression_total_bytes)) AS raw_size,
    pg_size_pretty(sum(after_compression_total_bytes))  AS compressed_size
FROM timescaledb_information.chunks c
LEFT JOIN chunk_compression_stats('sensor_readings') s
  ON s.chunk_name = c.chunk_name
WHERE hypertable_name = 'sensor_readings'
GROUP BY hypertable_name;

Confirm the background job is succeeding and measure its runtime against the schedule:

SELECT
    j.job_id,
    js.last_run_status,
    js.last_successful_finish,
    js.last_run_duration,
    js.total_failures
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats js USING (job_id)
WHERE j.proc_name = 'policy_compression'
  AND j.hypertable_name = 'sensor_readings';

If last_run_duration approaches schedule_interval, runs risk overlapping — lengthen the interval or narrow the backlog by lowering compress_after so fewer chunks queue per pass.

# Troubleshooting

ERROR: chunk "_hyper_1_2_chunk" is not compressed when running retention or a manual decompress. The policy has not reached that chunk yet, or it was decompressed for an update. Check is_compressed in timescaledb_information.chunks and confirm the job’s last_run_status is Success.

Policy is registered but chunks never compress. The worker pool is exhausted. Raise timescaledb.max_background_workers (and max_worker_processes above it), restart, and re-check the running_workers query from the prerequisites. A total_failures that climbs while last_run_status is not Success confirms the diagnosis.

ERROR: tuple concurrently updated or lock timeouts during compression. Ingestion or an UPDATE is touching the chunk as the policy tries to seal it. Move the compression window later so it only targets chunks past their write-hot phase, or drive out-of-band passes through safe_compress_chunk with backoff.

ERROR: cannot update/delete rows from chunk ... compression is enabled. A late or corrective write is hitting an already-compressed chunk. Decompress the specific chunk with decompress_chunk, apply the write, and let the policy recompress it — do not disable compression on the whole hypertable.

Disk usage does not drop after compression reports success. Freed heap pages have not been returned to the OS. Run the vacuum generator from Step 5; for chunks with heavy prior churn a plain VACUUM will not shrink the file — schedule off-peak VACUUM (FULL) on those specific chunks only.

Frequently Asked Questions

# Related & Navigation

← Back to Data Retention & Compression Lifecycle Automation

• TTL Policy Mapping & Enforcement — the retention stage that runs after compression in the lifecycle.
• Columnar Compression Models for High-Frequency Telemetry — how segmentby/orderby shape the on-disk format.
• Refresh Policy Design & Scheduling — sequence compression after the refresh window closes.
• Space Partitioning for Multi-Tenant IoT — keep per-tenant chunks small so each compression pass holds its lock briefly.
• Calculating Optimal chunk_interval for IoT Sensor Data — right-size chunks before enabling any compression policy.