Notes on avoiding subtransactions in River

A few weeks ago I was reading Incident.io’s post on Postgres queue degradation and I realized that we’d never talked about subtransactions with respect to River. Well, we have some good news on that front: River is subtransaction-free!

Incident.io’s post was a good reminder of why it matters: a seemingly harmless savepoint not only makes a job queue slower, and in the worst case puts it into a death spiral that leads to a production incident.

They explain the problem at length, but this particular idiosyncrasy of Postgres is complicated enough that it took me about five readings before it clicked, so I figured I’d put together a quick recap. There’s plenty of exhaustive versions of this found elsewhere so I’m going to make mine short. In fact, let’s stick to point form.

Tuple headers and MultiXact

• In Postgres, transactions are tracked using internal headers xmin and xmax. These help determine each row’s visibility for other transactions and VACUUM.

• Rows also store flags representing locks held by transactions in another header called t_infomask. A FOR UPDATE puts its transaction ID in xmax and sets HEAP_XMAX_LOCK_ONLY.

BEGIN; -- transaction ID 1000

SELECT * FROM job
WHERE id = 42
FOR UPDATE;

• This elegant design breaks down when multiple transactions need a lock on one row because xmax holds only one ID. To handle this, Postgres has a specialized structure called MultiXact that tracks the transactions and their locks. xmax becomes an indirection to MultiXact, and the row’s flags include HEAP_XMAX_IS_MULTI.

• Use of MultiXact requires coordination. Processes take an LWLock (lightweight lock) to read or modify it.

Savepoints

• SAVEPOINT starts a subtransaction from inside the BEGIN block of an outer transaction.

• Savepoints may create a MultiXact entry representing a separate lock from their parent transaction. At first, this might strike you as odd, but there’s a reason for it: if the savepoint is rolled back, Postgres must retain enough information to return the outer transaction to its initial state.

• A savepoint will only create a MultiXact entry if necessary (i.e., if there’s a new kind of lock), but due to the nature of how a Postgres job queue works, it probably will:

BEGIN; -- transaction ID 1000

-- Parent transaction acquires the job.
SELECT * FROM job
WHERE id = 42
FOR UPDATE SKIP LOCKED;

SAVEPOINT subtx1; -- transaction ID 1001

UPDATE job
SET next_check_at = now() + interval '1 hour'
WHERE id = 42;

--
-- MultiXact now holds:
--     XID 1000 → FOR UPDATE
--     XID 1001 → UPDATE (or NO KEY UPDATE)
--

RELEASE SAVEPOINT subtx1;

COMMIT;

Skip locked

• SKIP LOCKED is one of the key innovations that keep an in-database queue fast. As a client locks jobs to process, instead of conflicting with other consumers, it can detect locked rows quickly and move on:

SELECT *
FROM job
WHERE ready_at <= now()
ORDER BY ready_at, id
LIMIT $batch
FOR UPDATE SKIP LOCKED;

• Locks are detected using a row’s xmax and flags (e.g. t_infomask & HEAP_XMAX_LOCK_ONLY != 0).

• If xmax is a MultiXact ID, there’s a layer of indirection as Postgres inspects the member list.

• Worse yet, each consumer needs to synchronize on the structure. Instead of a granular row-level lock, clients will often need a coarse-grained LWLock:MultiXact*. They contend with each other.

• By comparison, if no subtransactions are involved, SKIP LOCKED needs only a lock-free examination of each row’s t_infomask. Considerably faster on all counts.

How River stays fast

Databases like Postgres are somewhat dangerous beasts because while they invite you to treat them like black boxes and do go a long way in letting you treat them like one, there’s a point where keeping them performant requires jamming a tire iron into the thing, levering the top off, and peering in to get a better understanding of what’s happening inside. Postgres can make an excellent job queue, but the details are important.

That’s where River comes in. It already builds in a number of mitigations to avoid degenerate conditions or compensate for them when they do occur:

• All Pro and non-Pro database operations have been audited to remove subtransactions. With a plain Insert, a top-level transaction is started to ensure consistency, but transactional variants like InsertTx avoid savepoints.

  Most of that is accomplished by rewriting an internal helper that starts a transaction to avoid doing so in case one’s already open:

  func WithTxV[TExec riverdriver.Executor, T any](
          ctx context.Context,
          exec TExec,
          innerFunc func(context.Context, riverdriver.ExecutorTx) (T, error),
  ) (T, error) {
          var defaultRes T
  
          // Reuse an existing transaction without opening a subtransaction.
          if tx, ok := any(exec).(riverdriver.ExecutorTx); ok {
                  res, err := innerFunc(ctx, tx)
                  if err != nil {
                          return defaultRes, err
                  }
                  return res, nil
          }
  
          tx, err := exec.Begin(ctx)
          if err != nil {
                  return defaultRes, fmt.Errorf("error beginning transaction: %w", err)
          }
  
          ...
  }

• Jobs are always acquired in batches by a single producer per client and distributed to workers. This means fewer consumers contend with each other where locks are held.

• Transactions are never held open while jobs are being worked. Jobs are locked in one transaction, processed outside it, and marked with their final states in another.

Our theory of mind is that putting your job queue in Postgres should make your life easier and your application simpler. As cryptically fascinating as MultiXact is to the technically curious, understanding it shouldn’t be a necessity for running a database. River handles job acquisition, management of transaction lifespans, and careful use (or non-use) of savepoints to help keep your attention focused on what you’re supposed to be doing.