How to Run Queries Asynchronously in PostgreSQL Using pg_background Extension

Modern applications often suffer when heavy database operations block user-facing queries. Large UPDATEs, VACUUM, analytics SELECTs, or bulk inserts can easily slow everything down.

This is where the pg_background extension becomes useful.

pg_background allows you to execute SQL statements in background workers, meaning your main session doesn’t have to wait for the query to finish. It’s essentially a fire-and-forget / async execution mechanism inside PostgreSQL.

This extension will be compatible with the PostgreSQL version starting from 9.5

All examples below were tested on PostgreSQL 17/18.

What is pg_background?

pg_background is a PostgreSQL extension that lets you:

• Launch SQL queries in a background backend
• Continue using your current session immediately
• Optionally fetch results later
• Or detach completely and let the job run silently

Internally, it creates a background worker process for each launched query.

Important to understand:

• This is not PostgreSQL parallel query
• This is not planner-level optimization
• It is session-level asynchronous execution

Typical Use Cases

pg_background is especially helpful for:

• Large UPDATE / DELETE operations
• VACUUM / ANALYZE
• Reindexing
• Batch inserts
• Analytics queries
• Odoo maintenance tasks
• Migration scripts
• Background cleanup jobs

Anywhere you don’t want your main session to wait.

Building and Installing pg_background

Clone the repository:

mkdir pg_background
git clone https://github.com/vibhorkum/pg_background.git --depth=1
cd pg_background/pg_background

Build using PGXS (important to match your PostgreSQL version):

Check the version of pg_config in your system like this

which pg_config

You get a result like this

/usr/lib/postgresql/18/bin/pg_config

Compile the extension using the system installed pg_config

make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install

Then connect to PostgreSQL:

psql -p 5433

Create the extension:

CREATE EXTENSION pg_background;

Verify:

\dx+ pg_background

You get result like this:

  Objects in extension "pg_background"
                   Object description                   
--------------------------------------------------------
 function grant_pg_background_privileges(text,boolean)
 function pg_background_detach(integer)
 function pg_background_launch(text,integer)
 function pg_background_result(integer)
 function revoke_pg_background_privileges(text,boolean)

Core Functions in pg_background extension

1. pg_background_launch()

Starts a SQL command in the background and returns a job PID.

Simple example

SELECT pg_background_launch('SELECT pg_sleep(50);');

Output:

pg_background_launch
-------------------
16208

The returned number is the background worker PID.

Your session is immediately free.

Watching it via pg_stat_activity

SELECT * FROM pg_stat_activity WHERE pid = 16465;

Output shows:

backend_type | pg_background
query        | SELECT pg_sleep(100);
state        | active
wait_event   | PgSleep

This confirms the query is running in a separate backend.

INSERT example

CREATE TABLE bg_test (
    id serial PRIMARY KEY,
    data text,
    created_at timestamp DEFAULT now()
);
SELECT pg_background_launch(
    'INSERT INTO bg_test (data) 
     SELECT ''Record '' || generate_series(1, 1000) 
     RETURNING count(*);'
) AS pid;

Let’s check it’s result by this query

SELECT * FROM pg_background_result(35581) AS (rows_inserted bigint);

You get result like this

 rows_inserted 
---------------
          1000
(1 row)

Perfect for bulk loads without blocking your main session.

2. pg_background_result()

Fetches results from a completed background job.

Because this function returns a record, it must be called properly.

If you run:

SELECT pg_background_launch('vacuum verbose');

You get result like this

pg_background_launch 
----------------------
                36513

Let’s check its result

SELECT pg_background_result(36513);

Result :

INFO:  vacuuming "postgres.information_schema.sql_sizing"
INFO:  finished vacuuming "postgres.information_schema.sql_sizing": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 23 remain, 0 are dead but not yet removable
removable cutoff: 6519, which was 0 XIDs old when operation ended
new relfrozenxid: 6519, which is 5775 XIDs ahead of previous value
new relminmxid: 533, which is 532 MXIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 69.383 MB/s, avg write rate: 0.000 MB/s
buffer usage: 13 hits, 5 reads, 0 dirtied
WAL usage: 1 records, 0 full page images, 311 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_13442"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_13442": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 6519, which was 0 XIDs old when operation ended
new relfrozenxid: 6519, which is 5775 XIDs ahead of previous value
new relminmxid: 533, which is 532 MXIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 40.064 MB/s, avg write rate: 0.000 MB/s
buffer usage: 25 hits, 1 reads, 0 dirtied
WAL usage: 1 records, 0 full page images, 258 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
 result 
--------
 VACUUM
(1 row)

3. pg_background_detach()

Detaches the job completely.

After detaching:

• PostgreSQL forgets the worker
• You cannot fetch results
• The query continues silently

Example:

SELECT pg_background_launch('VACUUM ANALYZE ir_attachment;');

Suppose PID = 17805

Detach:

SELECT pg_background_detach(17805);

This is ideal for maintenance tasks.

4. grant_pg_background_privileges()

By default, only superusers can use pg_background.

Grant access to another role:

SELECT grant_pg_background_privileges('postgres', true);

This internally runs:

• GRANT EXECUTE on pg_background_launch
• GRANT EXECUTE on pg_background_result
• GRANT EXECUTE on pg_background_detach

5. revoke_pg_background_privileges()

Remove access:

SELECT revoke_pg_background_privileges('postgres', true);

This internally runs:

• REVOKE EXECUTE on pg_background_launch
• REVOKE EXECUTE on pg_background_result
• REVOKE EXECUTE on pg_background_detach

Important Behavior Notes

1. Each launch creates a new backend

You can see it clearly in:

SELECT backend_type FROM pg_stat_activity;

Value:

pg_background

2. Results are single-use

Once fetched via pg_background_result, they’re gone.

3. Detached jobs cannot be tracked

After pg_background_detach, you lose control.

4. This is NOT a parallel query

pg_background:

• Runs SQL in another backend

It does NOT:

• Share execution plans
• Use PostgreSQL parallel workers
• Improve query speed automatically

It only avoids blocking.

pg_background is a powerful tool when you need:

• Non-blocking database operations
• Background maintenance
• Async analytics
• Cleaner application flow

It doesn’t replace PostgreSQL optimization techniques, but it gives you control over when and how queries block your sessions.