Sizing max_background_workers for Many Aggregates

When a TimescaleDB instance hosts dozens of continuous aggregates alongside compression and retention policies, every one of those policies is a scheduled job competing for a single, instance-wide pool of background workers — and if the pool is smaller than the number of jobs that fire at the same instant, the surplus jobs simply queue and their next_start slips later and later until refreshes fall behind real time. This page turns pool sizing into arithmetic: profile how many jobs you run and when they collide, convert the peak concurrency into a defensible timescaledb.max_background_workers value with headroom, reconcile it against PostgreSQL’s max_worker_processes ceiling, and verify afterward that nothing is stuck waiting.

Why an Undersized Pool Silently Queues Jobs

TimescaleDB runs a single background-worker scheduler per database, and that scheduler dispatches every automated policy — continuous aggregate refresh, compression, retention, reorder, and any user-defined action — by borrowing a worker from one shared pool. The pool size is fixed by timescaledb.max_background_workers, a global setting whose default (16 in recent releases) was chosen for a handful of hypertables, not for a fleet of aggregates. Each job that is actively running occupies exactly one worker for its full duration. When more jobs become due than there are free workers, the scheduler does not fail them; it holds them until a worker frees up, then dispatches the backlog. Individually those delays are invisible, but on a synchronized burst — every daily policy waking at midnight, say — the tail of the queue can start minutes or hours after its scheduled time, which is exactly when a refresh policy begins reporting stale data. The related asynchronous execution and queue management guide covers how that queue behaves under load; here the goal is to make the pool large enough — or the demand spread out enough — that the queue stays empty.

Input Profiling: Count the Jobs and Find the Collisions

Sizing starts with an honest inventory. Everything the scheduler drives lives in timescaledb_information.jobs, one row per policy, with the proc_name that identifies its type and the schedule_interval that controls how often it fires.

Refresh jobs — one policy_refresh_continuous_aggregate per continuous aggregate that has a refresh policy attached
Compression jobs — one policy_compression per hypertable with an active compression policy
Retention jobs — one policy_retention per hypertable with a drop_after policy
Schedule intervals — the cadence of each job; short intervals (5–30 min) recur far more often than daily maintenance, so they dominate collision math
User-defined actions — any custom add_job procedures also draw from the same pool

Count them by type, and note the spread of intervals:

sql

-- Inventory the scheduled jobs that draw on the shared worker pool.
SELECT
    proc_name,                          -- policy_refresh_continuous_aggregate, policy_compression, policy_retention, ...
    count(*)               AS job_count,
    min(schedule_interval) AS fastest_interval,
    max(schedule_interval) AS slowest_interval
FROM timescaledb_information.jobs
WHERE scheduled
GROUP BY proc_name
ORDER BY job_count DESC;

The number that actually sizes the pool is not the total job count — it is the peak concurrency: the largest set of jobs whose next_start timestamps land in the same scheduler dispatch window. Jobs collide whenever their schedules align, and daily jobs almost always align at midnight while short-interval refreshes align every time their period divides the hour. To estimate the worst case, project each job’s firing times forward over a full day and bucket them:

sql

-- Project every scheduled job's firing times across the next 24 hours,
-- then find the 10-second window where the most jobs come due together.
WITH fires AS (
    SELECT
        job_id,
        generate_series(
            next_start,
            next_start + INTERVAL '24 hours',
            schedule_interval
        ) AS fire_at
    FROM timescaledb_information.jobs
    WHERE scheduled
      AND next_start       IS NOT NULL
      AND schedule_interval IS NOT NULL
      AND schedule_interval  > INTERVAL '0'
)
SELECT
    time_bucket(INTERVAL '10 seconds', fire_at) AS window_start,
    count(*)                                    AS concurrent_jobs
FROM fires
GROUP BY 1
ORDER BY concurrent_jobs DESC
LIMIT 10;

The concurrent_jobs value in the top row is your peak, $P_{peak}$ . The bucket width models how tightly the scheduler groups near-simultaneous jobs; ten seconds is a reasonable dispatch window, but widen it if your jobs are slow enough that a worker stays busy across several ticks.

The Sizing Calculation

The required pool is the peak concurrency plus the workers the scheduler consumes for itself plus a safety margin:

W_{bg} = P_{peak} + S_{db} + H

Here $P_{peak}$ is the peak concurrent jobs from the projection above, $S_{db}$ is the number of databases whose TimescaleDB scheduler is active — each scheduler process holds one worker from the pool — and $H$ is headroom for manually invoked work such as run_job, ad-hoc refresh_continuous_aggregate calls, and momentary overruns when a long job has not released its worker before the next tick. A proportional headroom scales with the burst:

H = \max\left(2,\ \lceil 0.15 \cdot P_{peak} \rceil\right)

That value sets timescaledb.max_background_workers. But the TimescaleDB pool is itself carved out of PostgreSQL’s instance-wide worker budget, so the second half of the calculation reconciles it with max_worker_processes (written $M_{wp}$ below), which must cover the background pool and everything else PostgreSQL spawns:

M_{wp} \geq W_{bg} + W_{parallel} + W_{repl} + 1

where $W_{parallel}$ is max_parallel_workers (parallel query execution), $W_{repl}$ is the workers consumed by streaming replication and WAL senders, and the $+1$ covers the TimescaleDB background-worker launcher, a single per-instance process distinct from the per-database schedulers. If max_worker_processes is too low, raising timescaledb.max_background_workers alone accomplishes nothing — the schedulers still cannot start the workers they are permitted to request. Read the current values before changing anything:

sql

SHOW timescaledb.max_background_workers;  -- the pool the scheduler dispatches from
SHOW max_worker_processes;                -- the PostgreSQL-wide ceiling above it
SHOW max_parallel_workers;                -- parallel query workers competing for that ceiling

A small psycopg v3 helper turns the projection straight into a recommendation, which is convenient before onboarding a new tier of aggregates:

python

import math
import psycopg
from psycopg.rows import dict_row

PEAK_SQL = """
WITH fires AS (
    SELECT generate_series(
        next_start, next_start + INTERVAL '24 hours', schedule_interval
    ) AS fire_at
    FROM timescaledb_information.jobs
    WHERE scheduled AND next_start IS NOT NULL
      AND schedule_interval IS NOT NULL AND schedule_interval > INTERVAL '0'
)
SELECT count(*) AS concurrent_jobs
FROM fires
GROUP BY time_bucket(INTERVAL '10 seconds', fire_at)
ORDER BY concurrent_jobs DESC
LIMIT 1;
"""


def recommend_pool(conn_str: str, databases: int = 1) -> dict:
    """Derive a pool recommendation from the projected peak concurrency."""
    with psycopg.connect(conn_str, row_factory=dict_row) as conn:
        peak = conn.execute(PEAK_SQL).fetchone()["concurrent_jobs"]

    headroom = max(2, math.ceil(0.15 * peak))
    bg_workers = peak + databases + headroom
    return {
        "peak_concurrent_jobs": peak,
        "timescaledb.max_background_workers": bg_workers,
        # + max_parallel_workers (8) + replication (3) + launcher (1); tune to your instance.
        "max_worker_processes": bg_workers + 8 + 3 + 1,
    }

Worked Example

Take an instance with 40 continuous aggregates, each refreshed every 30 minutes by its own refresh policy, plus 10 compression policies and 10 retention policies, both running daily — 60 scheduled jobs in one database. They were provisioned in a single migration, so their next_start values are aligned: all 40 refreshes fire together on the half-hour, and at midnight the 20 daily maintenance jobs fire alongside them. The projection returns a peak of 60 concurrent jobs at the midnight bucket. With one scheduler:

H = \max(2,\ \lceil 0.15 \cdot 60 \rceil) = 9

W_{bg} = 60 + 1 + 9 = 70

Rounded to a clean number, set timescaledb.max_background_workers = 72. Reconciling the ceiling with 8 parallel workers, 3 replication workers, and the launcher:

M_{wp} \geq 72 + 8 + 3 + 1 = 84

so max_worker_processes = 96 leaves comfortable slack. The setting is applied in postgresql.conf (or via ALTER SYSTEM) and takes effect on the next restart:

ini

# postgresql.conf — both require a restart, not a reload.
max_worker_processes = 96
timescaledb.max_background_workers = 72

Seventy-plus resident background processes is a heavy standing cost for a burst that lasts seconds, which is the cue to consider the cheaper alternative below. If instead you stagger the 40 refreshes across their 30-minute window and offset the daily jobs by a few minutes each, the projection’s peak collapses to roughly 12. Then $W_{bg} = 12 + 1 + 2 = 15$ , so the default timescaledb.max_background_workers = 16 already suffices and max_worker_processes = 32 covers the ceiling — no oversized pool, no wasted resident processes.

Edge Cases & When to Deviate

The pool is instance-wide, not per-hypertable or per-database. Every database, every hypertable, and every aggregate on the instance draws from the same timescaledb.max_background_workers. A busy analytics database can starve a co-located ingestion database, so size against the whole instance’s combined peak, and remember to add one worker to $S_{db}$ for each additional database whose scheduler runs.
Changing the pool requires a restart. Both timescaledb.max_background_workers and max_worker_processes are startup parameters; SELECT pg_reload_conf() will not apply them, and the change only lands after a full PostgreSQL restart. Plan a maintenance window, because until the restart the new value is inert.
Prefer staggering to enlarging the pool. Lowering $P_{peak}$ is almost always cheaper than raising $W_{bg}$ : spreading initial_start/next_start across each job’s interval flattens the burst, needs no restart, and keeps the resident process count low. Use alter_job(job_id, next_start => ...) to phase-shift a subset of policies; the refresh policy design and scheduling guide covers choosing those offsets so no two cohorts land on the same tick.
Long-running jobs hold their worker. If a compression or refresh job routinely runs longer than the scheduler’s dispatch tick, it occupies its worker across several ticks and effectively raises concurrency beyond the instantaneous projection. Widen the time_bucket window in the peak query, or subtract those slow jobs’ duration from your headroom, before trusting the number.
Don’t over-provision blindly. Each background worker is a real process with its own memory footprint; a pool far larger than the sustained peak wastes RAM and can amplify lock contention during compression rather than relieve it. Size to the real peak plus proportional headroom, not to the total job count.

Verification

After the restart, confirm the new values are live and then prove the queue stays empty. The definitive signal that the pool is undersized is a scheduled job whose next_start has already fallen into the past — the scheduler wanted to run it but had no free worker, so the timestamp is slipping:

sql

-- Any scheduled job whose next_start is already overdue is being starved.
SELECT
    j.job_id,
    j.proc_name,
    j.schedule_interval,
    j.next_start,
    now() - j.next_start   AS overdue_by,
    s.last_run_status,
    s.last_run_duration
FROM timescaledb_information.jobs      j
JOIN timescaledb_information.job_stats s USING (job_id)
WHERE j.scheduled
  AND j.next_start < now()
ORDER BY overdue_by DESC;

An empty result set means the pool is keeping up: every job started on or before its scheduled time. If rows persist here after the change, either the peak was underestimated or a slow job is holding its worker — recompute the projection with a wider bucket. You can also watch the pool utilization directly, which should stay below the ceiling you configured:

sql

-- Live count of TimescaleDB background workers against the configured pool.
SELECT
    count(*)                                              AS active_bgw,
    current_setting('timescaledb.max_background_workers') AS pool_size
FROM pg_stat_activity
WHERE backend_type = 'background worker'
  AND application_name LIKE 'TimescaleDB Background Worker%';

If active_bgw regularly presses against pool_size while overdue jobs accumulate, the pool is the bottleneck; if it stays well below and jobs still slip, look instead at lock contention or long job durations rather than the worker count.

Background Worker & Lock Monitoring — the parent guide on observing the scheduler and its workers
Diagnosing Lock Contention During Compression — when slow jobs, not worker count, are the real bottleneck
Asynchronous Execution & Queue Management — how the scheduler queues and dispatches due jobs
Refresh Policy Design & Scheduling — staggering schedules to flatten the concurrency peak
Monitoring, Observability & Alerting for TimescaleDB Automation — the wider program these worker checks belong to

← Back to Background Worker & Lock Monitoring

Sizing max_background_workers for Many Aggregates

# Why an Undersized Pool Silently Queues Jobs

# Input Profiling: Count the Jobs and Find the Collisions

# The Sizing Calculation

# Worked Example

# Edge Cases & When to Deviate

# Verification

# Related