Diagnosing Lock Contention During Compression

When TimescaleDB compresses a chunk it takes an ACCESS EXCLUSIVE lock on that chunk for the duration of the rewrite, and if an ingestion INSERT or a late UPDATE touches the same chunk during that window every one of those writes blocks behind the compression backend until it commits. This page shows how to catch the resulting blocking chain in pg_locks and pg_stat_activity, confirm that the compress_chunk job is the head of the chain, measure how long writers have been stalled, and resolve the contention without disabling compression.

# Input Profiling: What to Capture First

Lock contention during compression is transient by nature — it lasts only as long as the chunk rewrite — so the diagnosis depends on catching the system mid-stall rather than reconstructing it afterward. Before writing any queries, know which three catalog surfaces carry the evidence and what each one contributes:

• pg_stat_activity — one row per backend. The columns that matter are wait_event_type, wait_event, state, query, query_start, and backend_type. A writer parked behind compression shows state = 'active' with wait_event_type = 'Lock'; the compression worker itself shows backend_type = 'background worker' running a compress_chunk call.
• pg_locks — one row per lock request, held or waiting. The granted boolean separates holders from waiters, and relation, mode, pid, and locktype identify exactly what is locked and how strongly. Compression’s rewrite takes mode = 'AccessExclusiveLock'.
• pg_stat_activity.pid joined back to the chunk — the locked relation OID resolves to a _hyper_*_chunk name via regclass, which you match against timescaledb_information.chunks to prove the contention is on a chunk currently being compressed by the compression scheduling automation.

Take a first, coarse reading to confirm contention actually exists. If the count comes back zero repeatedly during your compression window, the stalls are elsewhere and the rest of this page does not apply:

-- How many backends are currently waiting on a lock, and for how long?
SELECT
    pid,
    backend_type,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS waited_for,
    left(query, 60)      AS query_head
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

Any row here is a backend that has been denied a lock and parked. The waited_for interval is your severity signal: a few hundred milliseconds is the normal cost of a small chunk rewrite, while tens of seconds means a large chunk or a long-running writer is holding the chain open.

# Diagnosis: Building the Blocking Chain

Counting waiters tells you contention exists; it does not tell you who is at the head of the chain. The canonical technique is to self-join pg_locks against itself — a waiting lock request matched to the granted lock request that conflicts with it on the same lockable object — and then decorate both ends with pg_stat_activity. PostgreSQL exposes this directly through pg_blocking_pids(), which is simpler and handles multi-lock chains correctly:

-- Blocked-vs-blocking chain, with the compression backend surfaced as the blocker.
SELECT
    waiter.pid                          AS waiting_pid,
    waiter.wait_event_type,
    now() - waiter.query_start          AS waiter_waited,
    left(waiter.query, 50)              AS waiting_query,
    blocker.pid                         AS blocking_pid,
    blocker.backend_type                AS blocking_backend,
    now() - blocker.query_start         AS blocker_running,
    left(blocker.query, 50)             AS blocking_query
FROM pg_stat_activity AS waiter
CROSS JOIN LATERAL unnest(pg_blocking_pids(waiter.pid)) AS blocking(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = blocking.pid
WHERE waiter.wait_event_type = 'Lock'
ORDER BY blocker_running DESC;

Read the result top-down. The blocking_pid that appears repeatedly — once per waiter — is the head of the chain. When blocking_backend = 'background worker' and blocking_query contains compress_chunk, you have confirmed compression is the cause. The blocker_running interval is how long that backend has held its lock; the waiter_waited interval is how long each writer has been starved.

To tie the chain to a specific chunk and prove it is mid-compression, resolve the locked relation and cross-check the chunk catalog:

-- Which chunk is under an ACCESS EXCLUSIVE lock, and is it a live compression?
SELECT
    l.pid,
    l.mode,
    l.granted,
    c.relation::regclass          AS locked_object,
    ch.hypertable_name,
    ch.is_compressed
FROM pg_locks AS l
JOIN pg_locks AS c
      ON c.pid = l.pid AND c.locktype = 'relation'
LEFT JOIN timescaledb_information.chunks AS ch
      ON ch.chunk_name = split_part(c.relation::regclass::text, '.', 2)
WHERE l.mode = 'AccessExclusiveLock'
  AND l.granted = true
ORDER BY l.pid;

If locked_object resolves to a _hyper_*_chunk relation and is_compressed is still false at the moment you sample, the ACCESS EXCLUSIVE lock belongs to an in-flight compress_chunk that has not yet committed the columnar rewrite.

# Worked Example

A telemetry platform ingests continuously into a device_metrics hypertable and schedules compression nightly at 02:00. At 02:14 an on-call alert fires: ingestion lag is climbing and the write queue is backing up. Sampling pg_stat_activity returns four Lock waiters, all pointed at PID 24815:

The chunk-resolution query shows PID 24815 holding AccessExclusiveLock on _hyper_5_142_chunk, is_compressed = false, with blocker_running = 00:00:47. The picture is now unambiguous: compression started rewriting chunk 142 at 02:13:27, and every write routed to that chunk — three streaming inserts plus one late correction UPDATE — has been queued behind it for the ~45 seconds the rewrite has taken so far.

Why is a 45-second rewrite happening at all? Chunk 142 turns out to be the current chunk. The nightly job used compress_after => INTERVAL '1 hour', so it selected a chunk whose time range still overlaps live ingestion. Compression should target chunks old enough that no writer still addresses them. The fix is to widen the window so compression never races ingestion:

-- Move the compression boundary well clear of the active write range.
SELECT remove_compression_policy('device_metrics');
SELECT add_compression_policy(
    'device_metrics',
    compress_after => INTERVAL '2 days',
    if_not_exists  => TRUE
);

With compress_after at two days, the job only ever locks chunks whose time range closed long ago, so the ACCESS EXCLUSIVE lock lands on a chunk that no INSERT and no correcting UPDATE is still targeting. The blocking chain disappears not because compression got faster but because it stopped colliding with live writers.

# Edge Cases & When to Deviate

The nightly-job example is the common case, but several variants change the diagnosis or the fix:

• Late UPDATE/DELETE on an already-compressed chunk. A correction that arrives after a chunk is compressed forces TimescaleDB to decompress the affected chunk first, and that decompress_chunk takes its own ACCESS EXCLUSIVE lock. Widening compress_after does not help here because the chunk is already cold; the durable fix is to keep late-arriving corrections out of compressed ranges, which is what the patterns in handling out-of-order data insertion in TimescaleDB are designed to do.
• lock_timeout as a pressure valve. Setting lock_timeout on the ingestion role (for example SET lock_timeout = '2s') caps how long a writer will wait before erroring out instead of stalling indefinitely. The tradeoff is real: a low timeout converts a stall into a wave of failed inserts your ETL must retry, while a high one lets the queue grow. Use it as a guardrail against runaway chains, not as a substitute for scheduling compression clear of writes.
• decompress_chunk contention from ad-hoc backfills. A manual decompress_chunk run to backfill history competes for the same ACCESS EXCLUSIVE lock as the compression policy and can itself become the head of a chain. Run backfills in a maintenance window and never concurrently with the compression job.
• Autovacuum interplay. Autovacuum on a chunk takes a SHARE UPDATE EXCLUSIVE lock, which does not conflict with normal writes but does conflict with the ACCESS EXCLUSIVE request from compression. An autovacuum that starts just before the compression job can delay the job’s lock acquisition, lengthening the window in which writers pile up. If blocker_running is large but the compression query is itself waiting, check for an autovacuum worker on the same chunk.
• Statement-level vs chunk-level scope. Compression locks one chunk at a time, so contention is always chunk-scoped. If you see waiters spread across many chunks simultaneously, suspect a policy that dispatches multiple compress_chunk calls in parallel rather than a single long rewrite.

# Verification

After changing the schedule window, adding a backoff, or setting lock_timeout, confirm the fix by proving that no ACCESS EXCLUSIVE waits recur during the next compression run. Sample the waiter view repeatedly across the compression window:

-- Should return zero rows throughout the compression window after the fix.
SELECT
    a.pid,
    now() - a.query_start AS waited_for,
    left(a.query, 60)     AS waiting_query
FROM pg_stat_activity AS a
WHERE a.wait_event_type = 'Lock'
  AND a.query ILIKE '%device_metrics%';

For a longer-horizon confirmation that leans on TimescaleDB’s own bookkeeping rather than a live snapshot, check that the compression job now succeeds without overrunning its window and that no job is repeatedly retrying:

-- Compression job history: last run status and duration should be stable.
SELECT
    j.job_id,
    j.proc_name,
    js.last_run_status,
    js.last_successful_finish,
    js.total_failures,
    js.last_run_duration
FROM timescaledb_information.jobs AS j
JOIN timescaledb_information.job_stats AS js USING (job_id)
WHERE j.proc_name = 'policy_compression'
ORDER BY js.last_successful_finish DESC;

A healthy result shows last_run_status = 'Success', a last_run_duration that fits comfortably inside the scheduled interval, and a total_failures count that stops climbing. If waiters reappear, widen compress_after further or move the job to a quieter window; if total_failures rises while the live snapshot stays clean, the job is erroring for a reason unrelated to locking and belongs in a job-diagnostics investigation rather than this one.

# Related

• Chunk Compression Scheduling & Automation — configuring compress_after and the policy window so compression stays clear of live writes
• Handling Out-of-Order Data Insertion in TimescaleDB — keeping late UPDATE/DELETE corrections out of compressed chunk ranges
• Monitoring, Observability & Alerting for TimescaleDB Automation — the broader view catalog and alerting surface these lock queries feed into

← Back to Background Worker & Lock Monitoring