pg_background
Run SQL queries in the background
Repository
vibhorkum/pg_background
https://github.com/vibhorkum/pg_background
Source
pg_background-1.8.tar.gz
pg_background-1.8.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pg_background | 1.8 | TIME | GPL-3.0 | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 1100 | pg_background | No | Yes | No | Yes | No | Yes | - |
| Related | pg_cron pg_task pg_later pgq timescaledb timescaledb_toolkit timeseries periods |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | MIXED | 1.8 | 1817161514 | pg_background | - |
| RPM | PGDG | 1.8 | 1817161514 | pg_background_$v | - |
| DEB | PIGSTY | 1.8 | 1817161514 | postgresql-$v-pg-background | - |
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:
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
Execute arbitrary SQL commands in background worker processes within PostgreSQL. Unlike dblink (which creates a separate connection), pg_background workers run inside the database server in independent transactions.
Use Cases:
- Background maintenance (VACUUM, ANALYZE, REINDEX)
- Asynchronous audit logging
- Long-running ETL pipelines
- Independent notification delivery
- Parallel query patterns
Quick Start (V2 API)
CREATE EXTENSION pg_background;
-- Launch a background job
SELECT * FROM pg_background_launch_v2(
'SELECT count(*) FROM large_table'
) AS handle;
-- pid | cookie
-- -------+-------------------
-- 12345 | 1234567890123456
-- Retrieve results (one-time consumption)
SELECT * FROM pg_background_result_v2(12345, 1234567890123456) AS (count BIGINT);
-- Fire-and-forget (no result needed)
SELECT * FROM pg_background_submit_v2(
'INSERT INTO audit_log (ts, event) VALUES (now(), ''system_check'')'
) AS handle;
V2 API Reference
| Function | Returns | Description |
|---|---|---|
pg_background_launch_v2(sql, queue_size) | pg_background_handle | Launch worker, return cookie-protected handle |
pg_background_submit_v2(sql, queue_size) | pg_background_handle | Fire-and-forget (no result consumption) |
pg_background_result_v2(pid, cookie) | SETOF record | Retrieve results (one-time consumption) |
pg_background_detach_v2(pid, cookie) | void | Stop tracking worker (worker continues) |
pg_background_cancel_v2(pid, cookie) | void | Request cancellation |
pg_background_cancel_v2_grace(pid, cookie, grace_ms) | void | Cancel with grace period |
pg_background_wait_v2(pid, cookie) | void | Block until worker completes |
pg_background_wait_v2_timeout(pid, cookie, timeout_ms) | bool | Wait with timeout |
pg_background_list_v2() | SETOF record | List known workers in current session |
pg_background_stats_v2() | pg_background_stats | Session statistics (v1.8+) |
pg_background_progress(pct, msg) | void | Report progress from worker (v1.8+) |
pg_background_get_progress_v2(pid, cookie) | pg_background_progress | Get worker progress (v1.8+) |
Cancel vs Detach
| Operation | Stops Execution | Removes Tracking |
|---|---|---|
cancel_v2() | Yes (best-effort) | No |
detach_v2() | No | Yes |
- Use
cancel_v2()to stop work (terminate execution, prevent commit) - Use
detach_v2()to stop tracking (free bookkeeping while worker continues)
Worker Lifecycle
-- Cancel a running job
SELECT pg_background_cancel_v2(pid, cookie);
-- Wait for completion
SELECT pg_background_wait_v2(pid, cookie);
-- Wait with timeout (returns true if completed)
SELECT pg_background_wait_v2_timeout(pid, cookie, 5000);
-- List active workers
SELECT * FROM pg_background_list_v2() AS (
pid int4, cookie int8, launched_at timestamptz,
user_id oid, queue_size int4, state text,
sql_preview text, last_error text, consumed bool
);
Worker states: running, stopped, canceled, error
Progress Reporting (v1.8+)
-- From within worker SQL
SELECT pg_background_progress(50, 'Halfway done');
-- From launcher (check progress)
SELECT * FROM pg_background_get_progress_v2(pid, cookie);
GUC Settings (v1.8+)
| Parameter | Default | Description |
|---|---|---|
pg_background.max_workers | 16 | Max concurrent workers per session |
pg_background.default_queue_size | 65536 | Default shared memory queue size |
pg_background.worker_timeout | 0 | Worker execution timeout (0 = no limit) |
V1 API (Legacy)
The v1 API is retained for backward compatibility but lacks cookie-based PID reuse protection:
SELECT pg_background_launch('VACUUM VERBOSE my_table') AS pid \gset
SELECT * FROM pg_background_result(:pid) AS (result TEXT);
SELECT pg_background_detach(:pid);
The V2 API is recommended for production use due to cookie-based PID reuse protection.
Security Model
- Extension is installed by superusers, with no PUBLIC grants by default
- A dedicated
pg_background_workerNOLOGIN role is created - Helper functions manage privileges:
grant_pg_background_privileges(role, include_v1) - Workers execute as the launching user (not superuser)
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.