pg_background
pg_background : Run SQL queries in the background
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 1110 | pg_background | pg_background | 2.0.2 | TIME | GPL-3.0 | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d-r | No | Yes | No | Yes | yes | no |
| Relationships | |
|---|---|
| See Also | pg_cron pg_task pg_later pgq timescaledb timescaledb_toolkit timeseries periods |
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PGDG | 2.0.2 | 18 17 16 15 14 | pg_background | - |
| RPM | PGDG | 2.0.2 | 18 17 16 15 14 | pg_background_$v | - |
| DEB | PGDG | 2.0.2 | 18 17 16 15 14 | postgresql-$v-pg-background | - |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
el8.aarch64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
el9.x86_64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
el9.aarch64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
el10.x86_64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
el10.aarch64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
d12.x86_64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
d12.aarch64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
d13.x86_64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
d13.aarch64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
u22.x86_64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
u22.aarch64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
u24.x86_64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
u24.aarch64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
u26.x86_64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
u26.aarch64 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 | PGDG 2.0.2 |
Source
pig build pkg pg_background; # build rpm/debInstall
Make sure PGDG repo available:
pig repo add pgdg -u # add pgdg repo and update cacheInstall this extension with pig:
pig install pg_background; # install via package name, for the active PG version
pig install pg_background -v 18; # install for PG 18
pig install pg_background -v 17; # install for PG 17
pig install pg_background -v 16; # install for PG 16
pig install pg_background -v 15; # install for PG 15
pig install pg_background -v 14; # install for PG 14Create this extension with:
CREATE EXTENSION pg_background;Usage
Sources: official README, v2.0 release notes, migration guide.
pg_background executes SQL inside PostgreSQL background worker processes. Workers run independent transactions inside the server, which is useful for asynchronous maintenance, autonomous side effects, bounded long-running tasks, and progress-tracked jobs.
Version 2.0 makes the unsuffixed API canonical. The old _v2 names remain deprecated aliases through the 2.x line, but new code should use names such as pg_background_launch, pg_background_result, and pg_background_run.
One-Shot Execution
Use pg_background_run when the SQL has side effects and you only need execution metadata:
CREATE EXTENSION pg_background;
SELECT completed, has_error, sqlstate, error_message,
row_count, command_tag, elapsed_ms, timed_out
FROM pg_background_run(
'INSERT INTO audit_log(ts, who) VALUES (clock_timestamp(), current_user)',
queue_size := 0,
timeout_ms := 30000,
label := 'audit-login'
);Launch And Fetch Results
Use the launch/result pattern when the background SQL returns rows:
SELECT * FROM pg_background_launch(
'SELECT count(*) FROM large_table',
queue_size := 65536,
label := 'count-large-table'
) AS h;
SELECT * FROM pg_background_result(h.pid, h.cookie) AS (count bigint);Results can be consumed once. Keep both pid and cookie; the cookie protects later calls from PID reuse.
Fire And Forget
For side effects where no result rows need to be consumed:
SELECT * FROM pg_background_submit(
$$VACUUM (ANALYZE) public.events$$,
queue_size := 65536,
label := 'vacuum-events'
);Core API
pg_background_launch(sql, queue_size, label)launches a worker and returnspg_background_handle(pid, cookie).pg_background_submit(sql, queue_size, label)launches fire-and-forget work and returns a handle.pg_background_result(pid, cookie)consumes result rows once.pg_background_result_info(pid, cookie)returns completion and row-count metadata without consuming rows.pg_background_error_info(pid, cookie)returns structured SQL error details.pg_background_wait(pid, cookie, timeout_ms DEFAULT 0)waits for completion;timeout_ms <= 0blocks indefinitely.pg_background_cancel(pid, cookie, grace_ms DEFAULT 0)requests cooperative cancellation.pg_background_detach(pid, cookie)stops tracking a worker while letting it continue.pg_background_outcome(pid, cookie)returns a combined status snapshot without raising on missing state.pg_background_listandpg_background_activityare monitoring views;pg_background_stats()returns session counters.
Convenience helpers include pg_background_run_query, pg_background_drain, pg_background_wait_any, pg_background_cancel_by_label, and pg_background_purge.
Progress Reporting
Report progress from inside the worker SQL, then poll it from the launcher:
SELECT * FROM pg_background_launch($$
SELECT pg_background_report_progress(0, 'starting');
SELECT pg_sleep(1);
SELECT pg_background_report_progress(100, 'done');
$$) AS h;
SELECT * FROM pg_background_get_progress(h.pid, h.cookie);pg_background_report_progress is the 2.0 name; the earlier pg_background_progress name was hard-renamed.
GUCs And Loading
pg_background does not require shared_preload_libraries. Preloading is optional and mainly useful when you want its GUCs available before the extension is first loaded in a session.
SET pg_background.max_workers = 10;
SET pg_background.default_queue_size = '256KB';
SET pg_background.worker_timeout = '5min';pg_background.max_workersdefaults to16.pg_background.default_queue_sizedefaults to65536bytes.pg_background.worker_timeoutdefaults to0, meaning no execution timeout.
Caveats
- Pigsty packages
pg_background2.0 for PostgreSQL 14-18; upstream 2.0 also validates PostgreSQL 19 beta. - Upgrades from pre-1.8 installs must first reach the 1.8/1.10 release line before updating to 2.0.
- The original v1 PID-only API was removed. Unsuffixed names now have cookie-protected semantics and return/use
(pid, cookie). pg_background_cancel_v2_graceandpg_background_wait_v2_timeoutare folded intopg_background_cancel(..., grace_ms)andpg_background_wait(..., timeout_ms).pg_background_status_v2was removed; usepg_background_outcome(pid, cookie).