pg_background

Overview

Release tag 1.9.2 still ships extension SQL version 1.9.

Version

Build

You can build the DEB packages for pg_background using pig build:

pig build pkg pg_background         # build DEB packages

Install

You can install pg_background directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_background;          # Install for current active PG version

pig ext install -y pg_background -v 18  # PG 18
pig ext install -y pg_background -v 17  # PG 17
pig ext install -y pg_background -v 16  # PG 16
pig ext install -y pg_background -v 15  # PG 15
pig ext install -y pg_background -v 14  # PG 14

dnf install -y pg_background_18       # PG 18
dnf install -y pg_background_17       # PG 17
dnf install -y pg_background_16       # PG 16
dnf install -y pg_background_15       # PG 15
dnf install -y pg_background_14       # PG 14

apt install -y postgresql-18-pg-background   # PG 18
apt install -y postgresql-17-pg-background   # PG 17
apt install -y postgresql-16-pg-background   # PG 16
apt install -y postgresql-15-pg-background   # PG 15
apt install -y postgresql-14-pg-background   # PG 14

Create Extension:

CREATE EXTENSION pg_background;

Usage

Sources: official README, v1.9.2 release

pg_background executes SQL in PostgreSQL background worker processes. The worker runs inside the server and uses its own transaction, which makes it useful for asynchronous maintenance, autonomous side effects, and long-running tasks that should not block the caller.

CREATE EXTENSION pg_background;

SELECT * FROM pg_background_launch_v2(
  'SELECT count(*) FROM large_table',
  65536,
  'count-large-table'
) AS h;

SELECT * FROM pg_background_result_v2(h.pid, h.cookie) AS (count bigint);

Core API

• pg_background_launch_v2(sql, queue_size, label) launches a tracked worker and returns (pid, cookie).
• pg_background_submit_v2(sql, queue_size, label) is fire-and-forget for side-effect SQL.
• pg_background_result_v2(pid, cookie) consumes the worker result set once.
• pg_background_wait_v2(...) and pg_background_wait_v2_timeout(...) wait for completion.
• pg_background_cancel_v2(...) stops execution; pg_background_detach_v2(...) stops tracking but lets work continue.
• pg_background_list_v2(), pg_background_stats_v2(), and pg_background_get_progress_v2(...) expose worker state and progress.

Typical Patterns

Run maintenance without holding the client session open:

SELECT * FROM pg_background_submit_v2(
  'VACUUM (ANALYZE) public.events',
  65536,
  'vacuum-events'
);

Use an autonomous side effect from application SQL:

SELECT * FROM pg_background_submit_v2(
  $$INSERT INTO audit_log(ts, event) VALUES (clock_timestamp(), 'job queued')$$
);

GUCs And Security

• pg_background.max_workers limits concurrent workers per session.
• pg_background.default_queue_size controls the shared-memory queue size.
• pg_background.worker_timeout sets an execution timeout; 0 means no limit.
• The extension creates a dedicated pg_background_worker NOLOGIN role and ships helper functions to grant or revoke execution privileges.

Caveats

• Prefer the V2 API. The older V1 API is still present for compatibility but lacks cookie-based PID reuse protection.
• The v1.9.2 release is a binary-only patch for assert-enabled PostgreSQL builds. The SQL extension version remains 1.9, so there is no new SQL upgrade script or user-facing function delta from 1.9.1.