# Duroxide PostgreSQL Provider
A PostgreSQL-based provider implementation for [Duroxide](https://github.com/microsoft/duroxide), a durable task orchestration framework for Rust.
> **Looking for durable functions directly in SQL?** That's [pg_durable](https://github.com/microsoft/pg_durable) — a PostgreSQL extension built on top of duroxide + duroxide-pg. duroxide-pg itself is the lower-level Rust building block: use it when you want to author your own workflows in Rust against a Postgres-backed store.
> **Note:** See [CHANGELOG.md](CHANGELOG.md) for version history and breaking changes.
## Installation
Add to your `Cargo.toml`:
```toml
[dependencies]
duroxide-pg = "0.2"
```
## Usage
```rust
use duroxide_pg::PostgresProvider;
use duroxide::Worker;
#[tokio::main]
async fn main() -> anyhow::Result<()> {
// Create a provider with the database URL
let provider = PostgresProvider::new("postgres://user:password@localhost:5432/mydb").await?;
// Use with a Duroxide worker
let worker = Worker::new(provider);
// ... register orchestrations and activities, then run
Ok(())
}
```
### Custom Schema
To isolate data in a specific PostgreSQL schema:
```rust
let provider = PostgresProvider::new_with_schema(
"postgres://user:password@localhost:5432/mydb",
Some("my_schema"),
).await?;
```
### Microsoft Entra ID Authentication (Azure Database for PostgreSQL)
Connect to **Azure Database for PostgreSQL Flexible Server** using a Microsoft
Entra ID (Azure AD) access token instead of a static password. The provider
acquires the initial token at construction time and a background task
refreshes it before expiry, swapping the new token into the pool via
`sqlx::Pool::set_connect_options`.
```rust,no_run
use duroxide_pg::{EntraAuthOptions, PostgresProvider};
# async fn example() -> anyhow::Result<()> {
let provider = PostgresProvider::new_with_entra(
"myserver.postgres.database.azure.com",
5432,
"mydb",
"my-entra-principal@contoso.onmicrosoft.com",
EntraAuthOptions::new(),
)
.await?;
# Ok(()) }
```
For multi-tenant deployments use the schema variant:
```rust,no_run
use duroxide_pg::{EntraAuthOptions, PostgresProvider};
# async fn example() -> anyhow::Result<()> {
let provider = PostgresProvider::new_with_schema_and_entra(
"myserver.postgres.database.azure.com",
5432,
"mydb",
"my-entra-principal@contoso.onmicrosoft.com",
Some("tenant_a"),
EntraAuthOptions::new(),
)
.await?;
# Ok(()) }
```
#### Identity sources
By default the provider chains `[WorkloadIdentityCredential (only when AKS
federated env vars are present), ManagedIdentityCredential,
DeveloperToolsCredential]` — so the same code works for:
- **AKS**: Workload Identity federation (preferred when
`AZURE_FEDERATED_TOKEN_FILE` and friends are set).
- **Production**: User-assigned or system-assigned managed identity,
App Service / Container Apps managed identity.
- **Local dev**: `az login` (Azure CLI) or `azd auth login`.
#### Required Azure setup
1. Configure an Entra admin on the Flexible Server (`az postgres flexible-server ad-admin set`).
2. Create a Postgres role for the principal:
```sql
SELECT pgaadauth_create_principal('my-app-managed-identity', false, false);
```
3. Grant the role the privileges your application needs (`GRANT ... ON DATABASE
...`, `GRANT USAGE ON SCHEMA ...`, etc.).
#### Sovereign clouds
The default audience is the public-cloud value
`https://ossrdbms-aad.database.windows.net/.default`. Override for sovereign
clouds:
```rust,no_run
use std::time::Duration;
use duroxide_pg::EntraAuthOptions;
let options = EntraAuthOptions::new()
.audience("https://ossrdbms-aad.database.usgovcloudapi.net/.default")
.refresh_interval(Duration::from_secs(15 * 60));
```
#### Notes
- All Entra connections are pinned to `PgSslMode::VerifyFull`. There is no
fallback to weaker TLS modes.
- Brief auth-failure windows during token rotation surface as **retryable**
`ProviderError`s (SQLSTATE `28000` / `28P01`) so the runtime retries
transparently.
- See the [Entra ID technical reference](https://github.com/microsoft/duroxide-pg/blob/main/.paw/work/entra-auth-support/Docs.md)
for the design rationale (refresh scheduling, troubleshooting, dependency
choices).
#### Testing
Two test layers cover the Entra integration:
1. **Local pipeline tests** (`cargo test --lib entra_pipeline`) — exercise the
full token → connect-options → pool → migrations flow against a local
PostgreSQL by injecting a fake `TokenSource` (no Azure dependency). They
automatically skip if `DATABASE_URL` is not set.
2. **Live Entra smoke test** (`tests/entra_live_test.rs`, `#[ignore]`) — opt
in by setting `DUROXIDE_PG_ENTRA_LIVE_TEST=1` plus
`DUROXIDE_PG_ENTRA_TEST_HOST`, `DUROXIDE_PG_ENTRA_TEST_DB`, and
`DUROXIDE_PG_ENTRA_TEST_USER`. Run with
`cargo test --test entra_live_test -- --ignored`. Credentials are picked
up from the ambient `azure_identity` chain.
**First-time setup.** A pair of helper scripts provisions an
ephemeral Azure Database for PostgreSQL Flexible Server (Burstable
B1ms tier, ~$13/month if left running — remember to tear it down):
```bash
az login
./scripts/provision_entra_test_pg.sh # creates RG + server + firewall + Entra admin
# (script prints the env-var block to copy into your shell)
cargo test --test entra_live_test -- --ignored --nocapture
./scripts/teardown_entra_test_pg.sh # deletes the resource group
```
The scripts are idempotent and use the currently `az login`'d user as
the Entra admin / test principal. Override naming with
`DUROXIDE_PG_ENTRA_TEST_PREFIX`, `DUROXIDE_PG_ENTRA_TEST_LOCATION`,
`DUROXIDE_PG_ENTRA_TEST_RG`, or `DUROXIDE_PG_ENTRA_TEST_SERVER` env
vars (see the script headers for details).
## Configuration
| Environment Variable | Description | Default |
|---------------------|-------------|---------|
| `DUROXIDE_PG_POOL_MAX` | Maximum connection pool size | `10` |
### Optional long-polling
Existing constructors use short polling. To reduce idle queries and wake workers
when work arrives, explicitly enable long-polling:
```rust,no_run
use duroxide_pg::{PostgresProvider, ProviderConfig};
# async fn example(database_url: &str) -> anyhow::Result<()> {
let mut config = ProviderConfig::url(database_url);
config.schema_name = Some("my_app".to_string());
config.long_poll.enabled = true;
let provider = PostgresProvider::new_with_config(config).await?;
# Ok(())
# }
```
The same setting works with `ProviderConfig::entra(...)`. Long-polling reserves
one connection **within** the provider's pool for PostgreSQL `LISTEN`, so the
pool must allow at least two connections. It keeps another connection warm for
fetches rather than leaving the first dispatcher to open one. Leave it disabled
for client-only providers, especially one-connection backend clients. It requires a
session-preserving database connection; transaction-pooling proxies are not a
substitute for a dedicated LISTEN session.
`LongPollConfig` also provides `notifier_poll_interval` (default 60 seconds)
and `timer_grace_period` (default 100 milliseconds). The provider schedules
future work and orchestration, activity, and session lease expiry, rescans after
a timer fires, and periodically wakes fetches to recover missed notifications.
Claims and renewals announce their lease deadlines without immediately waking
fetches. An expired claim can therefore be recovered even if its owner crashes.
Notifications are hints; queue predicates and locks remain authoritative.
Timer scans run in a `READ ONLY` transaction with 200 ms server-side lock and
statement timeouts. PostgreSQL cancels blocked scans, and the rolled-back
connection returns to the pool rather than leaving a blocked server backend
behind. Scan errors retry after about one second.
A one-second client watchdog keeps fetches checking for work if a scan stalls
beyond those server limits. It does not cancel the scan or start another one;
the same in-flight scan is retained across listener reconnects. Reconnecting
also has an independent one-second polling fallback. Successful reconnection
resubscribes and rechecks work. If it adopts a scan started before the new
subscription, it follows that result with a fresh scan for both queues so
deadlines created during the reconnect gap are not missed.
An enabled fetch waits for work until its `poll_timeout`; a zero timeout still
performs a single fetch. The timeout bounds notification waiting, not an
in-flight database operation. Dropping the provider or closing its pool stops
the notification/timer task; connection cleanup has the network-failure
limitation described below.
The first notification wakes a fetch immediately. Further notifications that
find no eligible work, and timer catch-up scans, are coalesced to a 100 ms
cadence to bound query load. This can add up to 100 ms after an unsuccessful
wake; the overall polling deadline is not extended.
Migration `0030` installs **disabled** queue notification triggers. Constructing
a provider with both `MigrationPolicy::ApplyAll` and `long_poll.enabled = true`
activates them for that schema. `VerifyOnly` workers require that activation
first and perform no DDL themselves. See [Migration Policy](docs/migration-policy.md).
Once activated, inserts, visibility changes, lease changes, released locks, and
consumption of claimed orchestration messages emit notifications. Acknowledgment
notifications are delivered after the transaction releases the instance lock, so other
workers recheck events that arrived during the turn. Delayed abandonment also
announces the immediate instance-lock release, so other visible messages need
not wait for the abandoned message's delay. This includes writes from
short-polling producers. Turning off a provider's listener does not deactivate
the schema's triggers. Transactions writing those queues or session leases cannot use
`PREPARE TRANSACTION`, because PostgreSQL does
not support preparing transactions that emit notifications. Schemas that have
not opted in retain their existing transaction behavior.
PostgreSQL serializes transactions that emit notifications through a
server-wide lock held until commit completes. Slow WAL flushes or synchronous
replication waits can therefore delay other notification writers, even in
other schemas or databases on that server. This cost includes **every successful
activity and orchestration claim**, renewals that change their lease deadlines,
and session-lease writes. Empty fetches do not notify. A normal completed
activity has three notifying commits (enqueue, claim, completion acknowledgment);
an orchestration turn has two (claim and acknowledgment). Each lease-renewal
transaction also joins this serialization when it emits a hint. Multiple hints
in one transaction still acquire the commit lock only once.
These are schema-wide costs, including short-polling callers of an activated
schema. Benchmark concurrent writers with representative commit latency before
activation; idle-query savings alone do not establish loaded throughput.
Detected connection failures reconnect automatically, but silent network
partitions are not actively probed. Until the transport reports a failure,
work relies on the polling and refresh fallbacks instead of prompt
notifications. SQLx listener cleanup can also wait indefinitely on a wedged
socket, so provider drop does not guarantee bounded connection cleanup and
`pool.close()` has no hard shutdown deadline in that situation.
Recreate providers if their queue tables are dropped and recreated: notification
channels identify the original tables. Runtime session-capacity decisions are
also made once per fetch; a slot that started polling without session capacity
may need another fetch after its poll timeout before it can claim session work.
This feature does **not** convert a `duroxide-pg-opt` database into a
`duroxide-pg` database. Their migration histories remain incompatible.
#### Reproducing long-poll performance
The ignored benchmarks in [tests/long_poll_tests.rs](tests/long_poll_tests.rs)
compare short and long polling without changing the runtime's lease durations.
Use a disposable, optimized PostgreSQL server with assertions disabled:
```sh
DUROXIDE_PG_POOL_MAX=10 cargo nextest run --release --test long_poll_tests \
--run-ignored only --nocapture \
-E 'test(workflow_latency_and_throughput_with_long_polling)'
```
Use otherwise idle, controlled hosts for comparisons. Concurrent workloads can
invalidate both throughput comparisons and wait-time measurements.
The workflow driver uses two orchestration slots, two worker slots, a 100 ms
minimum poll interval, and a 30 s long-poll timeout. It measures 20 sparse
one-activity workflows with a 200 ms idle gap, then 20 concurrent clients running
one- and five-sequential-activity workflows for 10 seconds, with three loaded
repetitions per mode. Activities return immediately. A completed warm-up workflow
is excluded. Latency includes enqueue and the client's completion-status polling;
throughput includes draining in-flight workflows after submissions stop.
Each `BENCH` JSON line records the mode, pool limit, concurrency, duration,
PostgreSQL version, commit settings (`fsync`, `full_page_writes`,
`synchronous_commit`, and `synchronous_standby_names`), and replication state.
Loaded runs sample notification-commit lock waits and `SyncRep` waits every
10 ms. The reported backend-seconds are estimates integrated across the
provider's connections, not individual-query latency measurements, and can
exceed wall-clock time. The notification measurement uses PostgreSQL's shared
`pg_database` object-0 heavyweight lock, not the `NotifyQueue` LWLock.
Keep the settings and source revision alongside results; the driver does not
relax durability.
The separate `idle_query_load_is_reduced_with_long_polling` benchmark requires
`pg_stat_statements` and measures 65 seconds per mode. Run it alone in an
otherwise idle disposable database: it counts database-wide top-level statements
except its statistics queries, including timer-scan transaction control.
The `pg-stress` binary continues to use short polling.
For commit-contention measurements, use a disposable primary with a physical
synchronous standby. The higher-concurrency profile uses 16 orchestration
slots, 16 worker slots, 64 clients, and a warmed pool; it also measures
concurrent orchestration, activity, and session renewals with 60-second leases.
Both workflow and renewal runs use three 10-second repetitions per mode.
The renewal workload deliberately saturates renewal calls rather than using
the runtime's normal renewal intervals.
The benchmark connection needs `pg_read_all_stats` (or superuser privileges) to
read other sessions' wait events and the standby's `pg_stat_replication.state`
and `sync_state`. Ask a database administrator to grant that role to the
benchmark role in the disposable environment; the benchmark does not grant
itself privileges. Without it, replication columns can be masked even when a
healthy standby exists.
For a reproducible 10 ms apply-latency experiment, configure the standby's
connection with `application_name=pr41_bench_standby` and set
`recovery_min_apply_delay = '10ms'` there. On the primary, keep `fsync` and
`full_page_writes` enabled and use:
```conf
synchronous_standby_names = 'FIRST 1 (pr41_bench_standby)'
synchronous_commit = 'remote_apply'
```
Once that standby is streaming, run against the primary:
```sh
DUROXIDE_PG_POOL_MAX=100 cargo nextest run --release --test long_poll_tests \
--run-ignored only --nocapture \
-E 'test(notification_contention_with_synchronous_standby)'
```
The test rejects missing synchronous replication and insufficient pool capacity.
It requires observed notification and replication waits in the enabled runs;
disabled-schema controls must have no notification-lock waits. This experiment
models a delayed synchronous apply acknowledgment, not a particular cloud
service's replication topology.
## Features
- Automatic schema migration on startup
- Optional notification-driven long-polling for orchestration and activity work
- Connection pooling via sqlx
- Custom schema support for multi-tenant isolation
- Full implementation of the Duroxide `Provider` and `ProviderAdmin` traits
- Poison message detection with attempt count tracking
- Lock renewal for long-running orchestrations and activities
- KV store — durable per-instance key-value state for orchestration coordination
- Orchestration stats introspection via `Client::get_orchestration_stats()`
- Microsoft Entra ID authentication for Azure Database for PostgreSQL (managed identity, Workload Identity, az CLI)
## Latest Release (0.2.0)
- Requires Duroxide `0.2.x`. If your application depends on both crates, upgrade them together.
- **Upgrading from 0.1.38 or earlier requires downtime.** Pause retention, [stop and drain all provider and direct SQL callers](migrations/README.md#upgrading-safely), and let one new `ApplyAll` process finish migrations `0027` through `0030` before resuming traffic.
- Age-based retention can now prune old continue-as-new generations without deleting pending continuations. The new `prune_executions_batch` SQL function lets maintenance jobs scan a page of executions at a time.
- Deferred activities no longer jump ahead of work that was already ready, for both session and non-session workers.
- [Opt-in long polling](#optional-long-polling) wakes idle workers and orchestration dispatchers using PostgreSQL `LISTEN`/`NOTIFY`. Short polling remains the default.
See [CHANGELOG.md](CHANGELOG.md) for full version history.
> **Releases are published by Microsoft's internal OSS infrastructure.** See
> [RELEASE_POLICY.md](RELEASE_POLICY.md) for details.
## Previous Release (0.1.38)
- Fixes an orchestration acknowledge that could commit after another worker had taken the instance over. The consumed messages stayed queued and were processed again, so one queue message or external event could run an activity twice (migration `0026`).
- Fixes deadlocks between orchestration lock renewal and fetch, acknowledge or abandon, and stops renewal from reviving an expired lease or shortening a live one (migration `0026`).
- Retries transient renewal errors, and finds leases by lock token through a new unique index instead of a table scan.
- Adds a deadlock test suite that runs in every `cargo test`, and a TLA+ model of the lease procedures under worker clock skew (`docs/tla/orchestration-leases`).
## Support
Use GitHub Issues for bug reports and feature requests. Do not report security vulnerabilities through public GitHub issues; follow the instructions in [SECURITY.md](SECURITY.md) instead.
## Code of Conduct
This project has adopted the [Microsoft Open Source Code of Conduct](https://opensource.microsoft.com/codeofconduct/). For more information, see the [Code of Conduct FAQ](https://opensource.microsoft.com/codeofconduct/faq/) or contact [opencode@microsoft.com](mailto:opencode@microsoft.com) with questions or comments.
## Security
Microsoft takes the security of our software products and services seriously. Please do not report security vulnerabilities through public GitHub issues. See [SECURITY.md](SECURITY.md) for security reporting instructions.
## Privacy and Telemetry
duroxide-pg does not send telemetry to Microsoft. Applications may configure their own database, logging, or metrics collection; those signals are controlled by the application owner.
## Trademarks
This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow [Microsoft's Trademark & Brand Guidelines](https://www.microsoft.com/en-us/legal/intellectualproperty/trademarks/usage/general). Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos is subject to those third-party policies.
## License
MIT License - see [LICENSE](LICENSE) for details.