Skip to content

PgmqTransport: pgmq's insert-notify throttle row serializes producers per queue and deadlocks fan-out (proposal: NOTIFY from pgmq_dispatch_v1) #4

Open

Description

@iGrog

Summary

pgmq's insert notifications are implemented by a trigger (pgmq.notify_queue_listeners()). On every inserted row it runs:

UPDATE pgmq.notify_insert_throttle
SET last_notified_at = clock_timestamp()
WHERE queue_name = … AND (throttle_interval_ms = 0 OR clock_timestamp() - last_notified_at >= throttle);

There is one row per queue. Once a transaction has updated it, the row stays locked until that transaction ends, and every other transaction inserting into the same queue blocks on it. Even when the throttle window has not elapsed, the UPDATE must wait for the lock holder in order to re-check the row. As a result:

  1. Producers to one queue are serialized for the remainder of the first producer's transaction.
  2. Deadlocks: two transactions publishing to two queues in opposite order lock the two throttle rows crosswise. pgmq.send_topic() orders queues within one call, but thesis_message_bus.pgmq_dispatch_v1() sends all envelopes of a unit of work in handler order, so the order across transactions is arbitrary.

Observed

  • All deadlock detected errors in our consumer logs (6, then 5 after the other fixes) are in UPDATE pgmq.notify_insert_throttle called from notify_queue_listeners() ← pgmq.send / pgmq.send_topic ← thesis_message_bus.pgmq_dispatch_v1. They are retried successfully, but they cost retries and get more frequent as throughput grows.
  • Sampling pg_stat_activity during a load test: 26 of 36 lock waits were select thesis_message_bus.pgmq_dispatch_v1(…) waiting on transactionid behind this row.

Reproduction (deterministic)

Runs in the thesis/pgmq dev stack: docker compose run --rm php php var/repro-throttle.php

declare(strict_types=1);

require __DIR__ . '/../vendor/autoload.php';

use Amp\Postgres\PostgresConfig;
use Amp\Postgres\PostgresConnectionPool;
use Amp\Postgres\PostgresLink;
use function Amp\async;
use function Amp\delay;

$pg = new PostgresConnectionPool(PostgresConfig::fromString('host=pgmq user=postgres password=postgres'));
$pg->query('create extension if not exists pgmq');

foreach (['repro_a', 'repro_b'] as $queue) {
    $pg->query("select pgmq.drop_queue(queue_name) from pgmq.list_queues() where queue_name = '{$queue}'");
    $pg->query("select pgmq.create('{$queue}')");
    $pg->query("select pgmq.enable_notify_insert('{$queue}', 30)");
}

$send = static fn(PostgresLink $tx, string $queue) => $tx->query("select pgmq.send('{$queue}', '{}'::jsonb)");

// 1. Two producers sending to the SAME queue: the second one waits until the first one commits.
$first = $pg->beginTransaction();
$send($first, 'repro_a');

$startedAt = microtime(true);
$second = async(static function () use ($pg, $send): void {
    $tx = $pg->beginTransaction();
    $send($tx, 'repro_a');
    $tx->commit();
});

delay(1); // the first producer keeps working inside its transaction
$first->commit();
$second->await();
printf("1. second producer waited %.2f s - exactly until the first one committed\n", microtime(true) - $startedAt);

delay(0.1); // let the 30 ms throttle elapse so the next inserts update the rows again

// 2. Two producers sending to TWO queues in opposite order (e.g. two handlers publishing events).
$a = $pg->beginTransaction();
$b = $pg->beginTransaction();
$send($a, 'repro_a');
$send($b, 'repro_b');

$aSendsB = async(static fn() => $send($a, 'repro_b')); // waits for B's throttle row
delay(0.1);
$bSendsA = async(static fn() => $send($b, 'repro_a')); // waits for A's throttle row -> cycle

foreach (['A -> repro_b' => $aSendsB, 'B -> repro_a' => $bSendsA] as $name => $future) {
    try {
        $future->await();
        echo "2. {$name}: ok\n";
    } catch (\Throwable $e) {
        echo "2. {$name}: ", strtok($e->getMessage(), "\n"), "\n";
    }
}

foreach ([$a, $b] as $tx) {
    if ($tx->isActive()) {
        $tx->rollback();
    }
}

foreach (['repro_a', 'repro_b'] as $queue) {
    $pg->query("select pgmq.drop_queue('{$queue}')");
}

Output (pgmq 1.11.1, the image from thesis/pgmq's compose):

1. second producer waited 1.02 s - exactly until the first one committed
2. A -> repro_b: ERROR:  deadlock detected
2. B -> repro_a: ok

Related: no trailing notification inside the throttle window

The throttle notifies on the leading edge only. When several separate transactions insert into one queue within 30 ms, only the first insert notifies. If a consumer wakes up and drains the queue before the later transactions commit, nothing wakes it again: the last messages wait for the poll timer (pollInterval, 5 s by default).

This is exactly what happens when many handlers dispatch to the same queue, each in its own unit of work. Measured with a stress harness: a backlog of 500 plus 300 more messages sent in bursts of 10 single-message transactions, 2 workers, pollInterval 10 s. All 800 arrive, but in every run the last 2–4 messages of the final burst arrive only after the 10 s timer, with both thesis/message-bus and thesis/pgmq's own consumer. With the throttle interval set to 0, all arrive within 5 s.

NOTIFY from pgmq_dispatch_v1() (one per committing transaction and queue, no throttle) removes this gap as well.

Proposal

The trigger is pgmq's design, but thesis/message-bus can avoid it, because every message it sends goes through its own thesis_message_bus.pgmq_dispatch_v1():

  • in pgmq_dispatch_v1(), call pg_notify('pgmq.q_' || queue || '.INSERT', NULL) for every queue a message was written to. For send, that is the address. For publish, it is the same set pgmq.send_topic() routes to (SELECT DISTINCT queue_name FROM pgmq.topic_bindings WHERE routing_key ~ compiled_regex);
  • do not use pgmq's insert trigger: createQueue() disables it instead of enabling it, and the consumer keeps listening on the same channel name.

Why this is better:

  • NOTIFY is transactional (delivered on commit, nothing on rollback) and takes no row lock while the transaction runs.
  • Postgres folds duplicate notifications within one transaction, so a batch of 1000 messages to one queue produces one notification.
  • The consumer-side throttle becomes unnecessary: a consumer wakes once per committed transaction rather than once per row.

Trade-offs to decide:

  • Messages inserted by other means (raw pgmq.send) would be picked up by the poll timer only.
  • Delayed messages still need the timer, as today.
  • At commit, notifying transactions take Postgres' global notify-queue lock briefly. That is cheap at this scale, but it is a global point.

We are happy to send a PR if this direction is acceptable.

Activity

  1. iGrog commented on Oct 7, 2026

    @iGrog
    ContributorAuthor

    Added a related finding to the description: the throttle notifies on the leading edge only. When separate transactions insert into one queue within 30 ms, the last of them get no notification. If a consumer has already drained the queue, they wait for the poll timer. Measured with a stress harness: the last 2–4 messages of a burst of single-message transactions arrive only after pollInterval, with both thesis/message-bus and thesis/pgmq; with a throttle of 0 they arrive at once. NOTIFY from pgmq_dispatch_v1() would close this gap as well.

  2. iGrog commented on Nov 9, 2026

    @iGrog
    ContributorAuthor

    Quick follow-up: we've now hit this deadlock in a second service built on the bus, so it's no longer something you only see under a load test.

    I've implemented the proposal from the description in #9.

    What changes

    • pgmq_dispatch_v1() calls pg_notify() itself, on the same channel the trigger used (pgmq.q_<queue>.INSERT), so listeners stay as they are. For publish it notifies every queue send_topic() routed the message to.
    • Nothing stays locked until the sender commits anymore. Postgres delivers the notification on commit (and never for a rolled-back send) and folds identical notifications within one transaction, so we get the throttle for free.
    • Delayed messages aren't announced: they're invisible until the delay passes anyway, and the poll picks them up.
    • createQueue() undoes an earlier setup without stalling a live queue. It deletes the throttle row first (without it the trigger locks nothing), then drops the trigger with a 2 s lock_timeout. If the queue is busy, the trigger is left for the next setup and a warning is logged. When there's nothing to undo, it only reads the catalog.
    • The consumer no longer touches the trigger on start, which also takes care of PgmqTransport: every consumer start recreates the notify trigger (table lock, blocked producers, deadlocks) #3. It listens on the lower-cased channel name, because Postgres names the channel after the queue table and pgmq lower-cases table names; before, a queue with capital letters was never woken up.

    Trade-offs

    • A message inserted with plain pgmq.send(), bypassing the bus, no longer wakes the consumer and waits for the next poll. If that's a real use case, the trigger could stay as an opt-in.
    • Postgres serializes commits that issued a NOTIFY across the whole cluster (one lock on the notification queue, held through the WAL flush), so the ceiling is roughly one sending transaction per fsync. The old trigger hits the same ceiling. In a pgbench run with 32 clients: on empty transactions ~250 vs ~370 commits/s for the old trigger; with 20 ms of work inside the transaction ~280 vs ~290 commits/s, while p95/p99 commit latency dropped 2.5–4×.

    This replaces the trigger handling from #7, so I'll close that one. Unlike #7, it doesn't depend on a new thesis/pgmq release.

    Tests are in #10, on top of the integration-test setup in #8: two producers on one queue don't wait for each other, crosswise sends to two queues don't deadlock, publish wakes the subscribed consumer only after commit, and createQueue() removes an old trigger without stalling a busy queue. On the current 0.5.x the first two fail with a lock wait and deadlock detected.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions