How to Make PostgreSQL Complete Background Tasks Even After a Reboot

Imagine this scenario: you're writing a complex data processing workflow. You need to fetch records, send them to an external API, update the status in the database, and then kick off report generation. If the database "crashes" or the server reboots halfway through the process, a standard PL/pgSQL procedure will simply abort. The result is "dangling" data and a headache: how do you figure out which stage everything stopped at, and how do you safely restart it?

Usually we solve this with workarounds. We set up pg_cron, create tables with job queues, write workers in Python or Go that constantly poll the database. In the worst case, we drag heavy monsters like Temporal or Airflow into the project. But engineers at Microsoft decided all of this was unnecessary complexity and released the pg_durable extension.

Why developers need this

The main idea behind the project is to give PostgreSQL the ability to execute long-running functions that are resilient to failures. In the authors' terminology, this is called "Durable Execution".

If your workflow is defined through pg_durable, the database will save its state after each step. If the server reboots right in the middle of executing a heavy query or API call, the extension will pick up the task from the last successful checkpoint. You no longer need to glue together cron jobs, workers, and status tables.

How it works under the hood

The project is written in Rust using pgrx. Architecturally, it's not just a wrapper but a full-fledged execution environment inside the database. It consists of several layers:

1. SQL DSL: a set of operators for describing the task graph.
2. Background Worker: a background process inside Postgres that manages execution.
3. Duroxide: an orchestration engine (also a Microsoft development) that handles deterministic replay and checkpoints.

Interestingly, the authors chose a "SQL-native" approach. You describe the logic right in the console or migration, using special operators like ~> or |=>.

Key features

Here are three things that really simplify your life.

Fault tolerance without external services

You don't need Redis for queues or separate Temporal instances. Everything lives right in the df.* and duroxide.* tables. Data and control logic are in the same transactional environment. This eliminates the classic distributed systems problem where a task exists in the queue but changes in the database haven't been committed yet.

Parallel execution and merging

Using operators, you can easily "fork" task execution into multiple parallel streams and then wait for them to complete. The README has a clear example: count users, orders, and revenue simultaneously, then consolidate everything into a single reporting step.

Integration with external systems

The extension has a df.http() function. This means you can call external microservices or neural network APIs directly from a long-running process. If the API returns a 500 error, pg_durable can wait and retry without blocking the entire database's operation.

Code example

Here's what creating a simple task directly in SQL looks like:

-- Запускаем процесс: берем 100 необработанных документов и обновляем их статус
SELECT df.start(
    'SELECT id FROM documents WHERE processed = false LIMIT 100' |=> 'batch'
    ~> 'UPDATE documents SET processed = true WHERE id = ANY($batch)'
);

This code will create a Workflow instance that is guaranteed to execute. If the UPDATE fails, the system will know exactly which batch of data it was trying to process.

Where this comes in handy

I see several scenarios where pg_durable will save you a lot of time.

First, AI pipelines. If you need to run thousands of rows through embeddings and save them to pgvector, this is the ideal tool. Text chunking, OpenAI API calls, and upserts to the database are packaged into one reliable pipeline.

Second, large-scale data processing (ETL). Instead of writing monstrous PL/pgSQL procedures that fall over when WAL runs out, you can break the work into small steps with checkpoints.

Third, administration automation. For example, checking table bloat, sending a notification, and waiting for approval — all of this can be described as a Durable Function.

Nuances and limitations

The project is in Preview status. This means it's too early to push it to production, but it's perfect for internal tools.

Important limitations: you need PostgreSQL 17 or 18. If you're on older versions, you'll need to upgrade. Another point is security. By default, all functions in shared_preload_libraries require superuser rights for setup, although the developers have provided a permission system via df.grant_usage() for regular roles.

The system is optimized for SQL. If you need complex business logic with tricky loops in Python or Node.js, it's better to use the Duroxide engine directly from your application code. pg_durable is specifically about keeping computations as close to the data as possible.

Microsoft is actively investing in Postgres (remember the Citus acquisition), and pg_durable is another step toward turning the database into a full-fledged application platform. If you're tired of your background tasks "dropping off" at the most inconvenient moments, or if you're fed up with configuring external orchestrators for simple pipelines, definitely check out this repository. To get started, a simple Docker container or Codespaces will suffice, which are already configured in the Development tab in the repository.