# Database Backends

pg-boss runs on stock single-node PostgreSQL by default, but it also supports several
PostgreSQL-compatible backends. Those are distributed SQL engines like CockroachDB, YugabyteDB
and Citus, plus the embedded WASM build [PGlite](https://pglite.dev). You select one with the `backend` option,
which applies all the compatibility behavior that backend needs.

## Backend profiles

`backend` is the **only** option you set. It selects the database pg-boss is running against and
turns on the right combination of internal compatibility behavior for it:

```typescript
import PgBoss from 'pg-boss'

const boss = new PgBoss({
  connectionString: 'postgresql://root@localhost:26257/pgboss',
  backend: 'cockroachdb'
})
```

Each backend has a *kind*: `standard` (stock PostgreSQL), `distributed` (clustered
Postgres-compatible engines), or `embedded` (in-process PostgreSQL):

| `backend` | Kind | What it enables |
|-----------|------|-----------------|
| `postgres` *(default)* | standard | *(none, full PostgreSQL)* |
| `cockroachdb` | distributed | Lock-free fetch, split-statement writes, single shared table, immediate constraints, lock-free schema setup, plain indexes (+ numeric coercion), default-only column adds, no LISTEN/NOTIFY |
| `yugabytedb` | distributed | Lock-free schema setup + single shared table |
| `citus` | distributed | *(none, coordinator-local tables behave like plain PostgreSQL)* |
| `pglite` | embedded | *(none, full PostgreSQL; see [PGlite](#pglite-embedded))* |

`backend` is the only option you set. pg-boss derives everything above from it, so a deployment
can't end up with an inconsistent combination. The rest of this page explains each behavior (and
names the internal flag it maps to, for anyone reading the source). Databases without a profile
(Aurora DSQL, Spanner) are **not yet supported**.

## Database compatibility

The matrix shows which PostgreSQL features each backend supports (✅). Where a feature isn't
available (❌), pg-boss automatically switches to the compatible alternative. See the
[compatibility flags](#compatibility-flags) below.

| Database | Status | `backend` | SKIP LOCKED | Multi-mutation CTEs | Table partitioning | Deferrable constraints | Advisory locks | Covering indexes | Writes to a column it just added | LISTEN/NOTIFY |
|----------|--------|-----------|:---:|:---:|:---:|:---:|:---:|:---:|:---:|:---:|
| PostgreSQL | Tested | `postgres` | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ |
| CockroachDB | Tested | `cockroachdb` | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ | ❌ |
| YugabyteDB | Partial¹ | `yugabytedb` | ✅ | ✅ | ❌ | ✅ | ❌ | ✅ | ✅ | ✅³ |
| Citus | Tested² | `citus` | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ |
| PGlite | Tested | `pglite` | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅ | ✅⁴ |

¹ YugabyteDB runs the standard fetch path; non-partitioned queueing works, but partitioned queues,
multi-master startup, and live migrations fail ([#21833](https://github.com/yugabyte/yugabyte-db/issues/21833)). See below.

² In standard mode (coordinator-local table) Citus supports these like plain PostgreSQL. A
deliberately sharded job table would lose `SKIP LOCKED` compatibility and need the atomic-`UPDATE`
fetch instead. See [Citus](#tested-citus-compatible-in-standard-mode).

³ YugabyteDB supports cluster-wide LISTEN/NOTIFY as an early-access feature that is **off by
default**, so enable the `ysql_yb_enable_listen_notify` flag on TServers and Masters. When it's off,
leave `useListenNotify` disabled and avoid the queue `notify` option; pg-boss delivers via polling.

⁴ PGlite is embedded single-connection PostgreSQL, so LISTEN/NOTIFY works entirely in-process. The
`fromPglite` adapter wires it up automatically, so `useListenNotify` works with no extra setup.

Bun's `Bun.SQL` client has no row of its own: it is a different *driver* against PostgreSQL, not a
different database, so it runs on the `postgres` profile with every feature above, LISTEN/NOTIFY
included, on Bun 1.4.0 and later. See [Bun](#bun-driver).

## Compatibility flags

Here's what each behavior does differently from stock PostgreSQL, and the internal flag it maps to
in the source.

| Capability | Effect | Trade-off | Flag |
|------------|--------|-----------|------|
| Lock-free fetch | Fetch jobs with an atomic `UPDATE ... RETURNING` (plus a `state < 'active'` recheck) instead of `SELECT FOR UPDATE SKIP LOCKED`. | Under high contention some workers get empty results instead of skipping to unlocked rows. | `noSkipLocked` |
| Split-statement writes | Run `complete`, `fail`, and supervisor expiry as split statements inside a transaction rather than a single multi-mutation CTE. | A few extra round-trips per command; negligible for normal workloads. | `noMultiMutationCte` |
| Single shared table | Create the job table without `PARTITION BY LIST`. | Per-queue partitioning (`partition: true`) is unavailable; all jobs share one table. | `noTablePartitioning` |
| Immediate constraints | Omit `DEFERRABLE INITIALLY DEFERRED` on foreign keys. | Constraints check immediately rather than at commit (no effect on normal operation). | `noDeferrableConstraints` |
| Lock-free schema setup | Disable `pg_advisory_xact_lock` (used to coordinate schema creation/migration). | Concurrent instances may occasionally do redundant maintenance, which is a performance concern rather than a correctness one. | `noAdvisoryLocks` |
| Plain indexes | Omit the `INCLUDE` clause on covering indexes. | Slightly less efficient index-only scans during fetch; minimal for most workloads. | `noCoveringIndexes` |
| Default-only column adds | Leave out the `UPDATE` that seeds a column the same migration added, for engines that refuse to write a column in the transaction that added it. | Rows carry the column's default until something rewrites them: after v41, a schedule holding an RRULE reads as `cron` from `getSchedules()` until the next scheduling pass relabels it. | `noAddColumnBackfill` |
| No transactional heartbeat | Refuse a [`transactional`](./api/workers.md#work-name-options-handler) worker on a queue configuring `heartbeatSeconds`, for engines that will not let a transaction write a row another session wrote after it began. | The two cannot be combined on that engine; `expireInSeconds` is the liveness bound for a transactional worker there. | `noTransactionalHeartbeat` |

Lock-free fetch, split-statement writes, and the transactional-heartbeat refusal are **runtime**
behaviors; the other five are **schema** choices applied at install/migration time. CockroachDB needs
all eight; other distributed engines need only a subset (see below). One further CockroachDB
adjustment is keyed on `backend === 'cockroachdb'` directly rather than on a flag: coercing
text-encoded integers back to numbers (see below).

### Why fetch and mutation strategy are tracked separately

`noSkipLocked` and `noMultiMutationCte` address two unrelated limitations that happen to coincide on
CockroachDB but are independent in principle:

- **`noSkipLocked`** is about the *fetch* path. By default pg-boss claims jobs with `SELECT FOR
  UPDATE SKIP LOCKED`. In some distributed engines that has problems:
  1. **Performance degradation under concurrency.** With 500 workers, mean latency for `SELECT FOR
     UPDATE SKIP LOCKED` can reach 311ms or higher ([CockroachDB #97135](https://github.com/cockroachdb/cockroach/issues/97135)).
  2. **Unexpected row skipping.** `SKIP LOCKED` can skip unlocked rows, causing workers to miss
     available work ([CockroachDB #121917](https://github.com/cockroachdb/cockroach/issues/121917)).

  With `noSkipLocked`, pg-boss instead claims jobs with an atomic `UPDATE ... RETURNING` and a
  `state < 'active'` recheck:

  ```sql
  WITH next AS (
    SELECT id FROM jobs
    WHERE name = $name
      AND state < 'active'
      AND start_after <= now()
    ORDER BY priority DESC, created_on, id
    LIMIT $batchSize
  )
  UPDATE jobs j SET
    state = 'active',
    started_on = now(),
    retry_count = CASE WHEN started_on IS NOT NULL THEN retry_count + 1 ELSE retry_count END
  FROM next
  WHERE j.id = next.id
    AND j.state < 'active'  -- recheck for concurrent safety
  RETURNING j.*
  ```

  See [Andrew Werner's article on distributed work queues](https://dev.to/ajwerner/quick-and-easy-exactly-once-distributed-work-queues-using-serializable-transactions-jdp)
  for the pattern. The trade-off: under high contention multiple workers' CTEs may select the same
  candidate rows, all attempt the `UPDATE`, one wins, and the rest receive empty results and poll
  again. That is acceptable when processing time >> fetch time (typical for job queues).

- **`noMultiMutationCte`** is about the *write* path. pg-boss's `complete`, `fail`, and supervisor
  expiry normally run as a single CTE that mutates more than one table at once (e.g. completing a
  job and unblocking its flow dependents). CockroachDB rejects this with `multiple mutations of the
  same table "job" are not supported`. With `noMultiMutationCte`, those operations run as separate
  statements inside one transaction instead, so a job can't be lost between them.

Only `SKIP LOCKED` is replaced in the fetch path. Other operations still use ordinary
`SELECT ... FOR UPDATE` (without `SKIP LOCKED`), which distributed engines support fine.

A third CockroachDB quirk is handled separately: it returns integer columns (`INT8`) as text.
pg-boss `Number()`-coerces the numeric job and queue fields on read **whenever `backend ===
'cockroachdb'`**, not via a compatibility flag. It's a wire-format property of the database, so it's
keyed on the backend identity rather than piggybacking on a behavioral flag. (Stock Postgres returns
these as `int4` numbers, so no coercion is needed there.)

### Transaction isolation

For optimal correctness with `noSkipLocked`, SERIALIZABLE isolation ensures no two workers claim
the same job, which is the recommended level for distributed work queues. With READ COMMITTED (PostgreSQL or
YugabyteDB defaults), the `state < 'active'` recheck in the `UPDATE` still prevents duplicate claims.

## Per-database notes

Testing status, setup, and caveats for each supported backend.

### Tested: PostgreSQL

PostgreSQL is the primary supported database with full feature support. Use standard mode, with no
special options needed.

### Tested: CockroachDB

CockroachDB needs every compatibility behavior; the `cockroachdb` profile turns them all on:

```typescript
const boss = new PgBoss({
  connectionString: 'postgresql://root@localhost:26257/pgboss',
  backend: 'cockroachdb'
})
```

Internally that enables atomic-`UPDATE` fetch (`noSkipLocked`), split-statement mutations
(`noMultiMutationCte`), a non-partitioned table (`noTablePartitioning`), non-deferrable constraints
(`noDeferrableConstraints`), no advisory locks (`noAdvisoryLocks`), no covering-index `INCLUDE`
(`noCoveringIndexes`), and numeric coercion on read. See
[compatibility flags](#compatibility-flags).

**Transactional workers on CockroachDB** work, with two differences from PostgreSQL:

- A queue configuring `heartbeatSeconds` cannot carry one (`noTransactionalHeartbeat`). The
  heartbeat refreshes `heartbeat_on` on the claimed row from a pooled connection, by design, so the
  job stays visibly `active` while the handler runs. Under serializable isolation that write lands
  above the handler transaction's timestamp and the completion pg-boss runs inside the transaction
  is then refused, so `work()` rejects the combination rather than shipping a worker that fails
  every batch. Drop `heartbeatSeconds` and let `expireInSeconds` bound the job, or drop
  `transactional` and settle with `complete({ db })`. A heartbeat that arrives after `work()` has
  checked the queue, from `heartbeatSeconds` on the job itself or on the queue (`updateQueue()`, or a
  `createQueue()` that `work()` ran ahead of), gets past that check; each batch carrying one then
  fails before its transaction opens, with an error naming where it came from rather than the
  queue's configuration.
- When something takes the claim away mid-handler and the handler then settles the job itself
  through `tx`, that settle comes back as a raw `40001` retry error rather than `affected: 0`. The
  transaction rolls back either way, so the handler's writes never land beside a job that is about
  to run again; only the error the job records differs from the message PostgreSQL produces.

**Because `cockroachdb` disables table partitioning:**
- Queue-level partitioning (`partition: true` on `createQueue`) is not supported
- All jobs are stored in a single table instead of per-queue partitions

CockroachDB uses a [different partitioning model](https://www.cockroachlabs.com/docs/stable/partitioning)
that requires partition columns in the PRIMARY KEY and partitions defined inline at table creation.

In `noMultiMutationCte` mode the supervisor's expiry (`failJobsByTimeout` / `failJobsByHeartbeat`)
uses CockroachDB-safe split statements instead of the multi-mutation `failJobs()` CTE (which
CockroachDB rejects), so timed-out and heartbeat-timed-out jobs are correctly moved to
`retry`/`failed` rather than stranded in `active`. Separately, numeric job and queue fields (including `heartbeatSeconds`) are coerced back to
numbers on read, because CockroachDB returns integers as text. That one is keyed on
`backend === 'cockroachdb'` rather than on a flag.

**Verified on CockroachDB** (full-suite run, PostgreSQL-only tests skipped): job send/fetch,
complete, fail (by id), retry of an explicitly failed job (including exponential backoff and
`retryDelayMax`), maintenance expiration of timed-out jobs (`expireInSeconds`), heartbeat config +
heartbeat-timeout (`heartbeatSeconds`), queue policies (`short`/`singleton`/`stately` with
`partition: false`), throttle/debounce, deferral, cancellation, flow dependency
blocking/unblocking, including a blocking parent that fails and retries, and schema migration to
latest (with rollback on error).

#### Testing the runtime toggles

`noSkipLocked` and `noMultiMutationCte` are pure runtime behaviors (no schema impact) that work on
plain PostgreSQL, so the project exercises them two ways:

- **`npm run test:distributed`** runs the **entire** test suite on Postgres with
  `DISTRIBUTED=true`, which makes `test/testHelper.ts`'s `getConfig()` set the internal
  `__test__distributed` hook for every test (forcing `noSkipLocked` + `noMultiMutationCte` on top of
  the plain-Postgres schema, since the flags are not publicly configurable). This is the primary
  safety net: any new test is automatically exercised against the distributed code paths, fast and
  reliably, without paying CockroachDB's slow per-test DDL. It runs as its own CI job.
- **`npm run test:cockroachdb`** runs `test/distributedDatabaseTest.ts` against a real CockroachDB
  cluster (`DB_TYPE=cockroachdb`, which also enables the compatibility flags above). A focused smoke
  test confirming actual CockroachDB compatibility; runs on every push/PR.
- **`npm run test:cockroachdb:full`** runs the **entire** suite against a real CockroachDB cluster
  (`--no-file-parallelism`). This is the compatibility-matrix / regression signal. It is not part of
  CI, so run it before a release or after touching SQL. A fresh schema installs in well under a
  second (every table's indexes are declared inside its `CREATE TABLE`; see `inlineTableIndexes`),
  so what remains is CockroachDB's latency on the test bodies themselves. The per-test timeout is
  raised automatically under CockroachDB (see `vitest.config.ts`).
- **`npm run test:cockroachdb:single`** starts a single-node, in-memory CockroachDB
  (`docker-compose.cockroach-single.yaml`) and runs the same full suite against it, about twice as
  fast as the three-node cluster. Use it for local iteration; the three-node cluster is still the one
  that exercises replication. The two use the same port, so stop one before starting the other.

For tests that depend on PostgreSQL-only features (table partitioning, covering indexes, or an exact
PostgreSQL schema/migration shape), `test/testHelper.ts` exports `itPostgresOnly` /
`describePostgresOnly`, which skip under CockroachDB. For data-driven tests that loop over
`partition: true`/`false`, narrow the cases with `helper.isCockroachDb`.

`test/distributedDatabaseTest.ts` holds the invariants the general suite cannot express
(concurrent-fetch deduplication, `failDistributed`/`completeDistributed` composition inside a caller
transaction, and the compatibility-flag construction paths). Those cases force distributed runtime
via `__test__distributed` (or select `backend: 'cockroachdb'` for the schema-construction case), so
they run in every mode.

### Partially compatible: YugabyteDB

YugabyteDB is a PostgreSQL-compatible distributed database (reports as PostgreSQL 15). pg-boss has
been tested against a single-node YugabyteDB (`docker compose -f docker-compose.yugabyte.yaml up -d`,
`npm run test:yugabytedb:full`). Basic queueing works, but there is a significant caveat.

**Use `backend: 'yugabytedb'`.** It enables `noAdvisoryLocks` + `noTablePartitioning` and keeps the
standard fetch mode. YugabyteDB does **not** need `noSkipLocked` or `noMultiMutationCte` (it has
neither CockroachDB's `SKIP LOCKED` issues nor the multi-mutation CTE restriction). With this
backend, standard (non-partitioned) queueing works: send / fetch / complete, retries, job
expiration, flows, and queue policies (`short` / `singleton` / `stately`).

```typescript
const boss = new PgBoss({
  connectionString: 'postgresql://localhost:5433/pgboss',
  backend: 'yugabytedb'
})
```

**Why `noTablePartitioning` is required.** pg-boss creates a per-queue partition with DDL (`CREATE
TABLE … PARTITION OF …`) inside the same transaction that inserts the queue row. Two YugabyteDB
behaviors make this fail:

1. It cannot transparently retry a multi-statement transaction sent over the *simple* query
   protocol on a conflict ([yugabyte-db#21833](https://github.com/yugabyte/yugabyte-db/issues/21833))
   and pg-boss wraps these operations in a `BEGIN; … ; COMMIT;` text block, so the conflict surfaces
   as `current transaction is expired or aborted`.
2. DDL is **not rolled back transactionally**, so the partition table survives the aborted
   transaction, and a retry then fails with `relation "…" already exists`.

`noTablePartitioning` keeps all jobs in one table and skips the per-queue DDL, sidestepping both.
Advisory locks are a [Tech Preview](https://github.com/yugabyte/yugabyte-db/issues/3642) on
YugabyteDB, hence `noAdvisoryLocks`.

**Still not reliable on YugabyteDB:** partitioned queues (`partition: true`), multi-master
concurrent startup, and live schema migrations between pg-boss versions all involve DDL under
contention. A fresh install (`createSchema`) is fine; upgrading an existing deployment is not.

For the partitioned-queue case specifically, this is **not** just a query-protocol problem and
cannot be fixed by parameterizing the call. Running `create_queue` as a real client-side transaction,
or as a single parameterized autocommit statement, was tested and still fails: once the partition
DDL hits a conflict YugabyteDB reports `query layer retry isn't possible … some data was already
sent to the user` and aborts, because it cannot transparently retry a transaction that performs DDL.
Avoiding the partition DDL (`noTablePartitioning`) is the only thing that works.

### Tested: Citus (compatible in standard mode)

Citus is a PostgreSQL extension that can shard tables across nodes. pg-boss never calls
`create_distributed_table()`, so its tables stay **local to the coordinator** and behave like plain
PostgreSQL. The **full pg-boss test suite passes** against a single-node Citus coordinator
(`citusdata/citus`, PostgreSQL 18 + Citus 14) with the `citus` extension loaded and **no special
flags**. Partitioning, queue policies, flows, heartbeat, multi-master, and migrations all work.

```typescript
const boss = new PgBoss({
  connectionString: 'postgresql://localhost:5434/pgboss',
  backend: 'citus' // standard mode, no flags
})
```

Run it yourself: `docker compose -f docker-compose.citus.yaml up -d` then `npm run test:citus:full`.

**The one caveat is opt-in.** If you deliberately shard the pg-boss job table with
`create_distributed_table()`, `SELECT FOR UPDATE SKIP LOCKED`
[only works for single-shard queries](https://docs.citusdata.com/en/stable/develop/reference_workarounds.html)
so a *distributed* job table would need the CockroachDB-style atomic-`UPDATE` fetch, which the
`citus` profile does not enable. There is no profile for sharded Citus today; a **reference table**
(replicated to all nodes) or the default coordinator-local table both work with `backend: 'citus'`.

### PGlite (embedded)

[PGlite](https://pglite.dev) is a complete PostgreSQL build packaged as a WASM library that runs
embedded in your Node.js (or browser) process, with no separate database server. Because PGlite is real
PostgreSQL, pg-boss runs against it with **no compatibility flags**: declarative partitioning,
deferrable constraints, advisory locks, covering indexes, `SELECT FOR UPDATE SKIP LOCKED`, and the
multi-statement migration DDL all work. It is **not** a distributed backend. It is embedded
single-connection PostgreSQL, reached through the `@electric-sql/pglite` client rather than the `pg`
connection pool, via the `fromPglite` adapter.

#### Usage

Install PGlite alongside pg-boss:

```bash
npm install @electric-sql/pglite
```

Construct a PGlite instance, wrap it with `fromPglite`, and select the `pglite` backend profile:

```ts
import { PGlite } from '@electric-sql/pglite'
import PgBoss, { fromPglite } from 'pg-boss'

const pglite = new PGlite('idb://my-app')   // or new PGlite() for in-memory

const boss = new PgBoss({
  backend: 'pglite',
  db: fromPglite(pglite)
})

await boss.start()

await boss.createQueue('email')
await boss.send('email', { to: 'user@example.com' })

const [job] = await boss.fetch('email')
// ... do work ...
await boss.complete('email', job.id)
```

#### Lifecycle is yours to manage

Unlike the default `pg`-pool connection, pg-boss does **not** open or close the PGlite instance. You own it. Construct it before `boss.start()` and close it after `boss.stop()`:

```ts
await boss.stop()
await pglite.close()
```

This mirrors the [ORM transaction adapters](api/adapters.md): pg-boss only calls `executeSql` on the
object you provide.

#### Single-connection considerations

PGlite serializes everything through one connection. pg-boss's background loops (maintenance,
scheduling, monitoring) and your workers all share that single connection, so queries are processed
one at a time. This is fine functionally, since PGlite queues requests internally, but you should keep
concurrency modest:

- There is no benefit to large `batchSize` or many concurrent workers; they cannot run in parallel.
- For embedded / local-first / testing workloads (PGlite's sweet spot) this is rarely a constraint.
- For high-throughput multi-worker queues, use a server-based PostgreSQL instead.

#### Persistence

PGlite supports in-memory, IndexedDB (browser), and filesystem persistence. See the
[PGlite docs](https://pglite.dev/docs/filesystems). pg-boss treats all of them identically; the job
schema and data persist wherever the PGlite instance stores its data directory.

### Bun (driver)

[Bun](https://bun.sh) ships its own PostgreSQL client, `Bun.SQL`. It talks to an ordinary PostgreSQL
server, so this is not a backend profile. The default `postgres` profile applies and every feature
works. It is a different *driver*, reached through an adapter instead of the `pg` connection pool.

You do not need it to run pg-boss on Bun: the bundled `pg` driver works under the Bun runtime, and a
connection string is the simpler choice. Reach for an adapter when you want one client, and one
pool, shared between pg-boss and the rest of your Bun application.

| What you want | Use |
|---|---|
| pg-boss to run on your Bun client | `fromBunSql(sql)` as the constructor `db` |
| To enqueue inside a Drizzle transaction on `bun-sql` | [`fromDrizzle(tx, sql)`](api/adapters.md#drizzle) as the per-call `db` |

#### Running pg-boss on Bun.SQL

```ts
import { SQL } from 'bun'
import PgBoss, { fromBunSql } from 'pg-boss'

const sql = new SQL(process.env.DATABASE_URL)

const boss = new PgBoss({ db: fromBunSql(sql) })

await boss.start()

await boss.createQueue('email')
await boss.send('email', { to: 'user@example.com' })
```

As with PGlite, the client is yours: pg-boss never calls `end()` on it.

#### Enqueueing inside a transaction

The `db` option on `send()`, `insert()`, `complete()` and friends takes anything implementing
`executeSql`, so job writes can join a transaction you already own. On Drizzle over `bun-sql` that
is the [Drizzle adapter](api/adapters.md#drizzle), unchanged from any other driver:

```ts
import { drizzle } from 'drizzle-orm/bun-sql'
import { sql } from 'drizzle-orm'
import { fromDrizzle } from 'pg-boss'

await db.transaction(async (tx) => {
  await tx.insert(orders).values({ item: 'widget', qty: 1 })

  await boss.send('order-processing', { item: 'widget' }, { db: fromDrizzle(tx, sql) })
})
```

Without Drizzle, wrap a `sql.begin()` transaction the same way `fromBunSql` wraps the client.

#### What the adapters absorb

Three `Bun.SQL` behaviours differ from `pg` in ways pg-boss would otherwise trip over. The adapters
handle them; nothing is asked of you. They are listed because the first explains the shape of the
queries you will see in `pg_stat_statements`, and because the first two are upstream bugs that may
be fixed out from under this note.

- **Array parameters.** Bun cannot encode a JS array as a PostgreSQL array parameter; it stringifies
  it, and every `= ANY($n::uuid[])` fails with `malformed array literal`
  ([oven-sh/bun#18775](https://github.com/oven-sh/bun/issues/18775)). Both adapters expand an
  array-cast parameter into `ARRAY[$2,$3]` of scalar parameters, so bind count varies with the
  number of ids in a `complete()` or `cancel()` call.
- **Parameter type inference.** Bun infers a bind parameter's type from the cast in front of it, so
  `$n::json` re-encodes an already-serialized payload
  ([oven-sh/bun#28819](https://github.com/oven-sh/bun/issues/28819)). pg-boss routes those casts
  through `::text::json` for every driver, so this one is handled in the SQL rather than an adapter.
- **Error codes.** Bun reports every server error as `ERR_POSTGRES_SERVER_ERROR` and puts the
  SQLSTATE in `errno`, where `pg` puts it in `code`. `fromBunSql` moves it onto `code`, which is
  where pg-boss reads it to tell a lost fetch race from a real failure.

Transaction handling differs too, though it is invisible from the outside: pg-boss installs its
schema, migrates, and runs maintenance as `BEGIN; ... COMMIT;` scripts, and Bun refuses those on a
pooled connection (`Only use sql.begin, sql.reserved or max: 1`). `fromBunSql` reserves a connection
for the duration of one.

#### LISTEN/NOTIFY

`useListenNotify` works on **Bun 1.4.0 and later**, which is where `sql.listen()` arrived
([oven-sh/bun#32089](https://github.com/oven-sh/bun/pull/32089)). Bun reconnects and re-subscribes on
its own, and reports each subscribe through the callback pg-boss uses to close the gap, so a
notification missed while the listener was down is recovered by the next fetch.

On an earlier Bun the adapter exposes no listener at all, and pg-boss emits a
`listen_notify_unavailable` warning and delivers by polling, which is the default and never more than a
latency difference.

### Not supported: Aurora DSQL

There is no `backend` profile for Aurora DSQL, so pg-boss cannot currently be configured to run
against it. The notes below are for contributors evaluating a future profile.

[Amazon Aurora DSQL](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility.html)
is a serverless distributed SQL database with PostgreSQL compatibility. However, it uses
[optimistic concurrency control (OCC)](https://aws.amazon.com/blogs/database/concurrency-control-in-amazon-aurora-dsql/)
instead of traditional pessimistic locking, which fundamentally changes how locking works.

**Feature support:**
- Table partitioning: ❌ [Not supported](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-postgresql-compatibility-migration-guide.html), since Aurora DSQL auto-manages distribution
- Foreign key constraints: ❌ Not supported
- Deferrable constraints: ❌ N/A (no FK constraints)
- Advisory locks: ❌ Not supported (OCC model)
- Covering indexes (INCLUDE): ✅ Supported
- Synchronous CREATE INDEX: ❌ Only [`CREATE INDEX ASYNC`](https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-create-index-async.html) supported

**Key concerns:**
1. **OCC vs pessimistic locking**: `SELECT FOR UPDATE` in Aurora DSQL doesn't lock rows. It flags
   them for conflict detection at commit time. `SKIP LOCKED` is not meaningful in this model.
2. **Async-only index creation**: pg-boss uses synchronous `CREATE INDEX` during schema migrations.
   Aurora DSQL only supports `CREATE INDEX ASYNC`, which may cause migration failures.
3. **Retry logic required**: OCC returns serialization errors on conflict instead of blocking.
   Applications must implement retry logic.

**A profile would need** the CockroachDB-style behavior: `noSkipLocked` (OCC doesn't support
`SKIP LOCKED` semantics), `noMultiMutationCte`, `noTablePartitioning`, `noDeferrableConstraints`, and
`noAdvisoryLocks`. Compatibility is **uncertain** regardless, because of async-only index
creation and OCC behavior. If you experiment with Aurora DSQL, please report your findings.

### Not supported: Spanner

There is no `backend` profile for Spanner, so it cannot currently be configured. **Spanner (with
PGAdapter)**: Google Cloud Spanner provides PostgreSQL wire compatibility via
[PGAdapter](https://cloud.google.com/spanner/docs/pgadapter). A profile would likely need
`noSkipLocked`, `noMultiMutationCte`, `noTablePartitioning`, `noDeferrableConstraints`, and
`noAdvisoryLocks` like CockroachDB, but Spanner's more limited PostgreSQL support may pose other
incompatibilities. If you experiment with Spanner, please report your findings.

## Scaling beyond a single table

For very high-throughput workloads (thousands of jobs per second), `noSkipLocked` alone may not be
sufficient. At scale, contention on the job table becomes a bottleneck regardless of the fetch
strategy.

### Application-level sharding

A more scalable approach is to shard work at the application level using `singletonKey`:

```typescript
// Each worker claims a partition (e.g., via consistent hashing or assignment)
const workerId = process.env.WORKER_ID // 0, 1, 2, ...
const totalWorkers = parseInt(process.env.TOTAL_WORKERS)

// Send jobs with partition assignment
await boss.send('my-queue', jobData, {
  singletonKey: `partition-${jobId % totalWorkers}`
})

// Each worker only processes its partition
await boss.work('my-queue', {
  singletonKey: `partition-${workerId}`
}, handler)
```

### Hash-based distribution

For CockroachDB specifically, consider [hash-sharded indexes](https://www.cockroachlabs.com/docs/stable/hash-sharded-indexes)
to distribute write load across ranges. This helps avoid hotspots when inserting time-ordered jobs.

### When to use alternative systems

**Use pg-boss** (database-backed queue) when:
- Throughput is under ~10,000 jobs/second (PostgreSQL handles this comfortably)
- Processing time >> fetch time (typical for background jobs)
- Transactional consistency with your data is required
- You want to minimize infrastructure complexity

**Consider dedicated message queues** (Kafka, Redis Streams) when:
- Sustained throughput exceeds ~50,000 jobs/second
- Job processing times are sub-millisecond
- Fire-and-forget semantics are acceptable

**Throughput reference points:**
- PostgreSQL job queues: 7-30k jobs/sec ([benchmarks](https://gist.github.com/chanks/7585810), [Tembo MQ](https://legacy.tembo.io/blog/mq-stack-benchmarking/))
- Kafka: 1-2M messages/sec ([LinkedIn](https://engineering.linkedin.com/kafka/benchmarking-apache-kafka-2-million-writes-second-three-cheap-machines), [Honeycomb](https://developer.confluent.io/learn-more/podcasts/handling-2-million-apache-kafka-messages-per-second-at-honeycomb/))
- Redis Streams: 1-7M messages/sec ([benchmarks](https://goatreview.com/building-a-high-performance-message-queue-with-redis-streams/))

## Known limitations and race conditions

These apply when running with `noSkipLocked` (the atomic-UPDATE fetch path).

### Cache staleness

pg-boss caches queue metadata (including active singleton keys) with a configurable refresh interval
(`queueCacheIntervalSeconds`, default 60s). Under high concurrency:

- Two workers may both see stale cache showing no active singletons
- Both attempt to claim jobs with the same singleton key
- The `state < 'active'` recheck prevents duplicate claims, but one worker receives empty results

This is a performance issue, not a correctness issue. No job is processed twice.

### Serialization errors (CockroachDB)

CockroachDB uses SERIALIZABLE isolation by default. Under high contention, transactions may fail with
serialization errors and need to retry. pg-boss's fetch operation treats all errors as "no jobs
available" and returns empty results; the worker retries on the next poll cycle.

### Empty results under contention

With `noSkipLocked`, when multiple workers fetch concurrently:

1. All workers' CTEs may select the same candidate jobs (no row locking)
2. All workers attempt the `UPDATE`
3. One succeeds, the others fail the `state < 'active'` recheck
4. Failed workers receive empty results

This is the documented trade-off. For job queues where processing time >> fetch time, this is
acceptable, since workers simply poll again.

### Compatibility notes

- All pg-boss features (priorities, groups, singletons, retries, etc.) work on every backend.
- The atomic-`UPDATE` fetch (`noSkipLocked`) offers no benefit on stock PostgreSQL. Under contention
  workers receive empty results instead of efficiently skipping to unlocked rows, which is why it is
  only enabled for backends that need it, never on `backend: 'postgres'`.