Database

Vibhor Kumar: pg_background 2.0: esecuzione di SQL in background, ora più pulita, più sicura e pronta per PostgreSQL 19

08 giugno 2026

Sintesi redazionale: Ogni sviluppatore PostgreSQL prima o poi si trova di fronte allo stesso limite architettonico, anche se solitamente tale limite si presenta come una normale richiesta di funzionalità piuttosto che come un problema di progettazione del database. Una transazione dell'applicazione deve completare un'operazione aziendale,. Fonte originale: https://postgr.es/p/9lw

Every PostgreSQL developer eventually reaches the same architectural boundary, although the boundary usually appears as an ordinary product request rather than a database design problem. An application transaction needs to complete one business operation, but the surrounding platform also needs to write an audit record, launch a slow report, refresh a cache, fire a notification, or start some enrichment logic that should not delay the user. The first version of the application usually places that extra work inside the same transaction because that approach is simple and convenient. The problem appears later, when a rollback removes diagnostic information, a slow report increases API latency, or a user request begins carrying the weight of every downstream process that the business has attached to it.PostgreSQL is excellent at transactional consistency because it ensures that related changes succeed or fail together. That behavior is exactly what you want when an order, payment, inventory adjustment, or account update must remain correct. However, there are real-world cases where the triggering transaction and the follow-on work should not share the same fate. An audit record should survive a rollback, a notification should not hold an HTTP request open, and an analytical report should not force a user to watch a spinner while PostgreSQL scans millions of rows.PostgreSQL does not provide Oracle-style autonomous transactions as a built-in feature, so teams often create their own patterns around this gap. Some teams use dblink loopbacks to force work through another database connection, while other teams use LISTEN and NOTIFY with external workers, polling tables, cron jobs, or message queues. These approaches can work, and larger platforms may still need full orchestration layers when the workflow spans many services. However, when the work is fundamentally SQL that should run inside PostgreSQL, the extra infrastructure can feel like a small bridge built to cross a puddle.pg_background addresses this problem by allowing PostgreSQL to launch real background workers that execute SQL in independent server processes. Each worker has its own transaction lifecycle, which means it can commit, fail, or return results independently from the session that launched it. The extension does not require a separate daemon, an external scheduler, or a sidecar service. It uses PostgreSQL machinery to solve a PostgreSQL-shaped problem.Today, pg_background 2.0 makes that capability cleaner, safer, easier to observe, and easier to adopt across modern PostgreSQL environments. This release removes historical API clutter, keeps compatibility aliases for existing users, strengthens security defaults, improves observability, simplifies cancellation and waiting semantics, and adds PostgreSQL 19 beta readiness. The release matters because it is not merely adding functions to an extension. It is turning a useful primitive into a cleaner operational building block for production systems.**The Core Mental Model Is Independent SQL Execution Inside PostgreSQL**The simplest way to understand pg_background is to think about a user-facing transaction that should not carry every piece of downstream work on its back. A support application may need to save a ticket immediately, while a background task classifies the ticket, updates reporting tables, and prepares data for later semantic search. An e-commerce platform may need to confirm an order immediately, while a background task refreshes recommendation inputs and prepares analytical summaries. In both cases, the foreground transaction owns business correctness, while the background worker owns follow-on processing.The pg_background model starts with a launch operation that returns a handle containing a process identifier and a cookie. The process identifier identifies the worker process, while the cookie protects the caller from accidentally interacting with the wrong process if the operating system later reuses a process identifier. That detail matters in production because long-running systems cannot assume that a PID remains globally meaningful forever. The cookie turns the worker handle into a safer reference that applications can store, pass around, and use later when they collect results.``` ````-- Launch SQL inside a PostgreSQL background worker and store the handle in psql variables.-- The returned handle contains both the worker PID and a cookie that protects against PID reuse.SELECT (h).pid AS pid, (h).cookie AS cookieFROM ( SELECT pg_background_launch( 'SELECT count(*) FROM big_table' ) AS h) s\gset-- The foreground session can now continue doing other work while the worker runs independently.-- The result can be collected later by using the PID and cookie together as the worker handle.SELECT *FROM pg_background_result(:pid, :cookie) AS (n bigint);`The worker in this example runs in its own transaction and streams its result back when the caller chooses to collect it. That design gives developers a simple primitive for asynchronous SQL execution without forcing them to introduce another service. The caller can wait for the result, inspect the worker, cancel it, detach from it, or build higher-level patterns on top of it. The important point is that the database now has a native way to say, “run this separately, and let me decide later whether I need the result.”**The Canonical API Drops the Historical **`_v2`** Suffix Without Breaking Existing Code**`_v2`Earlier pg_background releases carried historical naming baggage because the project needed to evolve safely. The original API used unsuffixed names such as `pg_background_launch()`and `pg_background_result()`, but those early functions returned raw process identifiers without cookie protection. Later releases introduced safer cookie-protected handles, but they added `_v2`suffixes to avoid colliding with the original API. That compatibility strategy protected existing users, but it also made the recommended API look like a permanent migration artifact.pg_background 2.0 removes the old v1 API and promotes the clean unsuffixed names as the canonical interface. This change matters because new users should not have to learn the project’s migration history before they can launch a background worker. The old `_v2`names remain available as deprecated aliases, which means existing production code can upgrade first and rename later. That migration path is intentionally calm because database infrastructure should not force teams into unnecessary rewrite weekends.``` ````-- Before pg_background 2.0, applications used the v2 name for the safer cookie-protected API.-- This still works in pg_background 2.0, but it is now a deprecated compatibility alias.SELECT pg_background_launch_v2( 'INSERT INTO audit_log(event_type, event_time) VALUES (''order_retry'', clock_timestamp())');-- In pg_background 2.0, the unsuffixed name is the canonical API.-- This is the preferred form for all new code and documentation.SELECT pg_background_launch( 'INSERT INTO audit_log(event_type, event_time) VALUES (''order_retry'', clock_timestamp())');`The practical impact is straightforward. If your application already uses `_v2`functions, the upgrade does not require immediate code changes because the aliases forward to the same underlying behavior. However, those aliases are deprecated and scheduled for removal in a future major release, so teams should treat the 2.0 cycle as a migration runway. The best approach is to upgrade safely first, then rename functions in normal application maintenance cycles rather than under release pressure.You can also confirm the deprecation message directly from `psql`, which helps teams discover old usage during review. This matters in large environments where SQL functions may be called from application code, migration scripts, operational runbooks, and stored procedures. A visible catalog-level deprecation message gives engineers a practical way to identify what needs to change. It also prevents the upgrade guidance from living only in release notes that nobody reads during a late-night incident.``` ````-- Inspect the deprecated alias so that maintainers can see the migration guidance in psql.-- The function remains callable in 2.0, but the description makes the future removal explicit.\df+ pg_background_launch_v2`**Cancellation and Waiting Now Use One Function Each With Optional Parameters**Earlier versions exposed separate functions for closely related behaviors. If a user wanted to cancel with a grace period, the API used a different function from immediate cancellation. If a user wanted to wait with a timeout, the API used a different function from waiting indefinitely. That design worked, but it made the surface area larger than the behavior justified.pg_background 2.0 consolidates these patterns into single entry points with optional parameters. This change improves readability because the function name now expresses the operation, while the optional parameter expresses the policy. A production runbook becomes easier to understand when operators can see that everything is a cancellation or a wait, rather than having to remember several names that represent small variations of the same operation. The change also makes application code easier to branch around because `pg_background_wait()`now returns a boolean that tells the caller whether the worker stopped before the timeout.``` ````-- In pg_background 1.x, graceful cancellation was a separate function,-- pg_background_cancel_v2_grace. It was REMOVED in 2.0 (not kept as an alias);-- its behavior folded into the grace_ms parameter shown below.SELECT pg_background_cancel_v2_grace(:pid, :cookie, 5000); -- 1.x only; errors in 2.0-- In pg_background 2.0, the same behavior uses the canonical cancel function.-- The grace_ms parameter gives the worker time to exit before stronger termination is applied.SELECT pg_background_cancel(:pid, :cookie, grace_ms => 5000);`The same simplification applies to waiting. A web application may launch a background report and wait briefly to see whether it completes quickly enough to return inline. If the report does not complete within two seconds, the application can return a job handle to the user and allow the result to be fetched later. That pattern is common in reporting platforms where small reports should feel instant, but large reports should not consume the request path indefinitely.``` ````-- In pg_background 1.x, waiting with a timeout was a separate function,-- pg_background_wait_v2_timeout. It was REMOVED in 2.0 (not kept as an alias);-- its behavior folded into the timeout_ms parameter shown below.SELECT pg_background_wait_v2_timeout(:pid, :cookie, 2000); -- 1.x only; errors in 2.0-- In pg_background 2.0, timeout behavior belongs to the canonical wait function.-- The function returns true when the worker finishes and false when the timeout expires.SELECT pg_background_wait(:pid, :cookie, timeout_ms => 2000) AS worker_finished;`There is one behavioral detail that deserves explicit attention during upgrades. In pg_background 2.0, `timeout_ms => 0`means “wait forever,” not “poll once and return immediately.” If an application previously treated a zero timeout as a non-blocking status check, it must change that call to a small positive timeout such as one millisecond. This detail matters because an incorrect assumption here can turn a lightweight polling path into a blocking call that ties up application sessions.```_(testo troncato)_