PostgreSQL State Adapter

Store chains, jobs and workers in PostgreSQL with pg, postgres.js, Bun SQL, Drizzle, Kysely, Prisma, Knex or MikroORM.

@queuert/postgres supports horizontal scaling with database-level locking (FOR UPDATE SKIP LOCKED), writeable CTEs for atomic batch operations, and all Queuert features including concurrent multi-worker deployments that share one database; see Horizontal Scaling.

Clients

Install

Terminal

npm install @queuert/postgres

Requires PostgreSQL 14 or later, which migrateToLatest() checks. The same package includes the PostgreSQL notify adapter (LISTEN/NOTIFY).

Setup

Each tab shows the example's PgStateProvider and the lines that create the adapter:

src/provider.ts

import type { PgStateProvider } from "@queuert/postgres";
import type { Pool, PoolClient } from "pg";
import { type TxHooks, withTxHooks } from "queuert";

export type PgPoolContext = { poolClient: PoolClient; txHooks: TxHooks };

export const createPgPoolStateProvider = ({
  pool,
  prepareStatements = true,
}: {
  pool: Pool;
  /**
   * When true (default), queries that arrive with an `id` are sent as named
   * prepared statements (`name = id`), letting pg cache the parsed plan per
   * connection. The adapter folds template variants into `id`, so it uniquely
   * identifies the resolved SQL. Set to `false` for transaction-mode poolers
   * (PgBouncer, Supavisor) where named statements break across pooled sessions.
   */
  prepareStatements?: boolean;
}): PgStateProvider<PgPoolContext> => {
  const exec = async (
    client: PoolClient,
    id: string | undefined,
    sql: string,
    params: unknown[],
  ): Promise<unknown[]> => {
    if (id !== undefined && prepareStatements) {
      const result = await client.query({ name: id, text: sql, values: params });
      return result.rows;
    }
    const result = await client.query(sql, params);
    return result.rows;
  };

  return {
    withTransaction: async (cb) =>
      withTxHooks(async (txHooks) => {
        const poolClient = await pool.connect();
        try {
          await poolClient.query("BEGIN ISOLATION LEVEL READ COMMITTED");
          const result = await cb({ poolClient, txHooks });
          await poolClient.query("COMMIT");
          return result;
        } catch (error) {
          await poolClient.query("ROLLBACK").catch(() => {});
          throw error;
        } finally {
          poolClient.release();
        }
      }),
    isTransaction: (txCtx) => {
      const status = txCtx.poolClient.getTransactionStatus();
      return status === "T" || status === "E";
    },
    onTransactionCommit: (txCtx, callback) => {
      txCtx.txHooks.onCommit(callback);
    },
    onTransactionRollback: (txCtx, callback) => {
      txCtx.txHooks.onRollback(callback);
    },
    classifyError: ({ error }) => {
      const driverError = error as { code?: unknown; message?: unknown } | undefined;
      if (driverError?.code !== "42P01") return undefined;
      const tableName = /relation "([^"]+)" does not exist/.exec(String(driverError.message))?.[1];
      return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
    },
    executeSql: async ({ txCtx, id, sql, params }) => {
      if (txCtx) return exec(txCtx.poolClient, id, sql, params);
      const poolClient = await pool.connect();
      try {
        return await exec(poolClient, id, sql, params);
      } finally {
        poolClient.release();
      }
    },
  };
};

src/index.ts

const stateProvider = createPgPoolStateProvider({ pool: db });
await using stateAdapter = createPgStateAdapter({
  stateProvider,
});
await stateAdapter.migrateToLatest();

Configuration

createPgStateAdapter

  • stateProvider required
    The PgStateProvider for your client; see State provider.
  • schema "public"
    PostgreSQL schema that holds all objects.
  • tablePrefix "queuert_"
    Table name prefix for namespace isolation.
  • jobIdType "uuid"
    Type of the job id columns.
  • Type of the worker id columns.
  • The most blocker chains a single job may declare. Declaring more throws BlockerLimitExceededError; see Job Blockers.
  • countCap 10_000
    The per-status count at which countChainsByTypeName and countJobsByTypeName (and the dashboard's type counts) stop counting and report hasMore. A higher cap means a longer index scan per count.
  • generateJobId crypto.randomUUID
    Called for every job created without a caller-supplied id.
  • validateJobId accept all
    Runs on every caller-supplied and generated ID before writing, throwing InvalidJobIdError on failure.
  • generateWorkerId crypto.randomUUID
    Called once for each worker runner created without a workerId; must return a new id on every call.
  • validateWorkerId accept all
    Runs on the configured or generated worker id; createWorkerRunner throws InvalidWorkerIdError on failure.

TypeScript

const stateAdapter = createPgStateAdapter({
  stateProvider,
  maxBlockersPerJob: 500,
  countCap: 50_000,
});

maxBlockersPerJob and countCap must be positive integers; anything else throws when the adapter is created. The resolved values are readable on stateAdapter.config.

State provider

A PgStateProvider bridges your database client with the adapter: the adapter runs all its SQL and transactions through it. Each Setup tab is a complete provider for its driver or ORM; to use another client, implement the same members:

PgStateProvider

  • Manages connection acquisition and transaction lifecycle. The callback receives a transaction context representing an active transaction. A provider that carries txHooks in its context creates them here with withTxHooks, around the transaction, so they flush after COMMIT. It states READ COMMITTED explicitly (BEGIN ISOLATION LEVEL READ COMMITTED, or the client's isolation-level option), so a higher default_transaction_isolation cannot apply; see Isolation level.
  • Reports whether a context is inside an open transaction, from the driver's own state and without a query. Mutating client methods reject a context that fails it, so passing a pool or the root ORM handle instead of the transaction throws instead of autocommitting each statement.
  • Runs a callback after the context's transaction commits, and awaits it before the call that opened the transaction settles. The client and the worker register every side effect (notify events, observability events) here. A provider over a client with transaction events, such as MikroORM, runs it from those events; any other provider carries txHooks in its transaction context and registers the callback on them. See Tx Hooks.
  • Runs a callback synchronously when the context's transaction, or the savepoint the context belongs to, rolls back; the commit callbacks registered there never run. Implemented the same way as onTransactionCommit.
  • Executes SQL statements. When a transaction context is provided, uses that connection; when omitted, acquires and releases its own connection. readOnly marks a statement that only reads; a provider may route it to a read replica or a separate reader pool.
  • Returns { kind: "undefined_table", tableName } for a missing-table error (SQLSTATE 42P01; most drivers expose it as code), with tableName read from the relation "<table>" does not exist message, and undefined for any other error, or when it cannot read the table name. The adapter uses it to turn a write that hit a dropped write fence into SchemaFencedError.
  • close optional
    Only needed when the provider owns resources beyond the client or pool you pass in. Must be idempotent.

Testing your provider

@queuert/postgres/conformance checks a provider you write. pgStateProviderConformanceCases are every case a state adapter must pass, the cases that hold two transactions open at once, checks of the schema write fence, and checks that classifyError names a missing table. createPgStateProviderConformanceContext prepares the context they run against: it takes a createStateProvider factory and a withNonTransactionContext that runs a callback with a context outside any transaction, and migrates the schema once. An optional withSavepoint runs a callback in a savepoint of a transaction, opened the way your application opens one, and passes it the savepoint's context; when you give it, the cases check that onTransactionCommit and onTransactionRollback follow savepoints. Each case then builds its own adapter over a new provider from your factory, with the tables cleared, and closes both when it ends. The suite drives no test runner: your test file owns the database in its own hooks and registers one test per case, as each example does:

src/provider.spec.ts

describe("postgresql/state-pg provider conformance", () => {
  let pg: AcquiredPostgres;
  let pool: Pool;
  let context: PgStateProviderConformanceContext;

  beforeAll(async () => {
    pg = await acquirePostgres("postgres:18", import.meta.url);
    pool = new Pool({ connectionString: pg.connectionString, idleTimeoutMillis: 0 });
    context = await createPgStateProviderConformanceContext({
      createStateProvider: async () => createPgPoolStateProvider({ pool }),
      withNonTransactionContext: async (fn) => {
        const poolClient = await pool.connect();
        try {
          return await fn({ poolClient });
        } finally {
          poolClient.release();
        }
      },
    });
  }, 60_000);

  afterAll(async () => {
    await pool.end();
    await pg[Symbol.asyncDispose]();
  });

  for (const conformanceCase of pgStateProviderConformanceCases) {
    test(conformanceCase.title, async () => {
      await conformanceCase.run(context);
    });
  }
});

The full API is in the conformance reference.

Isolation level

Queuert supports only READ COMMITTED, PostgreSQL's default isolation level. This covers every transaction Queuert writes in, including the ones you pass to complete and to client methods. Its conditional and multi-statement writes, such as unblockJobs locking the blocked jobs and then recomputing their blockers, rely on each statement taking a fresh snapshot after it waits on a row lock. Every provider above opens its transactions with the level stated, so a server or role whose default_transaction_isolation is higher cannot change it; open the transactions you pass to Queuert the same way.

Write fence

Every insert, update or delete of a job, a blocker or a worker references an empty {prefix}schema_fence_v1 table, the write fence of the adapter's schema version. A breaking migration drops the fence tables of older versions, so code older than the schema fails each write with SchemaFencedError and changes nothing, instead of corrupting state. Reads still work, and older workers report the error through workerError and keep retrying. Stop them once you have deployed the newer version. Processes on 0.17 reference no fence, so stop them before the first breaking migration.

Schema

The adapter creates its schema via migrateToLatest(). All objects live under a configurable PostgreSQL schema (default: public) with a table name prefix (default: queuert_, shown as {prefix}) for namespace isolation.

A database migrated by 0.17 is carried onto the schema by adding the {prefix}schema_fence_v1 table and recording 001_schema_v1 next to the 0.17 migration records, without touching job or worker rows, so 0.17 processes keep running while migrateToLatest() runs, and a 0.17 process that restarts afterwards finds no migration left to apply. A 0.16 database is refused: upgrade it with @queuert/postgres 0.17 first.

The migrator behind migrateToLatest() is exported for your own tables too; see Application Migrations.

job — Stores all job state.

{prefix}job

  • idpkjobIdType (default: uuid)Job identifier
  • type_nametextJob type identifier
  • chain_idsame as idThe head job's id — every job in a chain points to the head
  • chain_indexintegerPosition in chain (0 for the head, incrementing for continuations)
  • continued_to_idsame as idThe next job's id in the chain
  • inputjsonbJob input data
  • outputjsonbCompletion output (null until completed)
  • statustextblocked, pending, running or completed (CHECK-constrained)
  • created_attimestamptzWhen the job was created
  • scheduled_attimestamptzEarliest time the job can be acquired
  • completed_attimestamptzWhen the job completed (null until completed)
  • completed_bysame as worker.idWorker ID that completed the job (null for workerless)
  • attemptintegerNumber of processing attempts (starts at 0)
  • last_attempt_attimestamptzWhen the last failed attempt ended
  • last_attempt_errorjsonbError from the last failed or reclaimed attempt
  • attempt_attimestamptzWhen the current attempt started (null when idle)
  • attempt_bysame as worker.idWorker ID holding the current attempt
  • attempt_tokenuuidToken of the current attempt
  • trace_contexttextW3C traceparent for this job
  • chain_statushead rows onlytextrunning or completed (CHECK-constrained)
  • chain_completed_athead rows onlytimestamptzWhen the chain completed (null until completed)
  • chain_deduplication_keyhead rows onlytextKey for chain deduplication
  • chain_trace_contexthead rows onlytextW3C traceparent for the chain

job_blocker — Tracks dependencies between jobs and chains. Each blocker slot is unique.

{prefix}job_blocker

  • job_idpksame as idThe blocked job
  • blocked_by_chain_idpksame as idHead job ID of the blocker chain
  • indexpkintegerPosition in the blockers array
  • trace_contexttextW3C traceparent

worker — Holds one row per worker, each with a lease.

{prefix}worker

  • started_attimestamptzWhen the worker started
  • lease_untiltimestamptzLease expiry — the worker is live while it is in the future
  • tokenuuidIdentifies this registration, new on every insert
  • concurrencyintegerMost attempts the worker runs at once
  • idpkworkerIdType (default: uuid)Worker ID, the worker runner's workerId
  • type_namestext[]Job types the worker processes

migration — Tracks applied schema migrations.

{prefix}migration

  • nametextMigration identifier (e.g., 001_schema_v1)
  • applied_attimestamptzWhen the migration was applied

migration_lock — Holds a single-row lease that gives migrateToLatest() cross-process mutual exclusion.

{prefix}migration_lock

  • idintegerAlways 1 (single-row constraint)
  • locked_bytextOwner id of the current migration run
  • locked_untiltimestamptzLease expiry (heartbeat-extended)

schema_fence_v<N> — Always empty; one per schema version N whose code may write. Every insert or update of a job, blocker or worker references its version's table, and a breaking migration drops the older ones.

{prefix}schema_fence_v<N>

  • idpkintegerNever written

Indexes

{prefix}job

  • chain_deduplication_idxDeduplication lookup(chain_deduplication_key, created_at DESC) WHERE chain_deduplication_key IS NOT NULL AND chain_index = 0
  • chain_index_idxChain position uniquenessUNIQUE (chain_id, chain_index) WHERE chain_index > 0
  • job_idxAll jobs by type and time(type_name, created_at)
  • job_pending_idxJob acquisition and pending job listing(type_name, scheduled_at) WHERE status = 'pending'
  • job_blocked_idxBlocked job listing(type_name, scheduled_at) WHERE status = 'blocked'
  • job_running_idxRunning job listing(type_name, attempt_at) WHERE status = 'running'
  • job_attempt_by_idxWorker job listing and running job counts(attempt_by, attempt_at) WHERE status = 'running'
  • job_completed_idxCompleted job listing(type_name, completed_at) WHERE status = 'completed'
  • chain_idxChains by type and time(type_name, created_at) WHERE chain_index = 0
  • chain_running_idxRunning chain listing(type_name, created_at) WHERE chain_index = 0 AND chain_status = 'running'
  • chain_completed_idxCompleted chain listing(type_name, chain_completed_at) WHERE chain_index = 0 AND chain_status = 'completed'

{prefix}job_blocker

  • job_blocker_chain_idxBlocker resolution(blocked_by_chain_id)

{prefix}worker

  • worker_idxWorker listing(started_at)

Vacuum tuning

The schema configures aggressive autovacuum and storage settings on the job tables:

storage parameters

  • fillfactor = 75 job
    Reserves free space per heap page for HOT updates. job_blocker skips it: blockers are inserted and deleted without intermediate updates.
  • fillfactor = 50 worker
    Leaves room for each lease renewal to be a HOT update on the same page.
  • autovacuum_vacuum_threshold / autovacuum_analyze_threshold = 5000, scale_factor = 0 job job_blocker
    Pin autovacuum to a fixed dead-tuple budget.
  • autovacuum_vacuum_cost_delay = 0 job job_blocker
    Removes I/O throttling.

Examples

@queuert/postgres

The API reference for every export is in @queuert/postgres.

esc

Search guides, integrations and every exported symbol.

↑ ↓ to navigate↵ to open