DOCSEARCHbyAlgolia
Submit Docs

PgQue Documentation

repository·main·Indexed 23 days ago

https://github.com/nikolays/pgque

PgQue is a high-performance, zero-bloat event queue implemented in pure PL/pgSQL for PostgreSQL. It provides a Kafka-like shared event log model within Postgres, designed for managed environments where C extensions are prohibited. The project includes a comprehensive benchmark harness to measure throughput, end-to-end delivery latency, subconsumer scaling, and performance under blocked xmin horizons compared to SKIP LOCKED queues.

Tokens
72.3K
Snippets
125
Records
379
Agent score
81%

What's inside PgQue

  1. Overview of PgQue

    main

    PgQue is a zero-bloat, pure SQL/PL/pgSQL event queue designed for PostgreSQL 14+. Unlike traditional task queues that use SKIP LOCKED with UPDATE/DELETE (which causes dead tuple bloat and VACUUM pressure), PgQue uses snapshot-based batching and TRUNCATE-based table rotation.

    Key characteristics:

    • Zero Bloat: Designed to avoid dead tuples and performance decay under sustained load.
    • Managed Postgres Ready: Works on RDS, Aurora, Cloud SQL, Supabase, Neon, etc., because it requires no C extensions or external daemons.
    • Event Stream Model: Functions more like a shared event log (similar to Kafka) than a task queue. It supports native fan-out where every registered consumer receives every event via independent cursors.
    • ACID Compliant: Leverages full Postgres durability, transactions, and WAL.

  2. What is pgque and how does it relate to PgQ?

    main

    pgque is a productization of PgQ, not a reimplementation. It uses the proven PL/pgSQL logic from PgQ but repackages it to be more accessible and modern.

    Key differences from PgQ:

    • Single-file install: No make or CREATE EXTENSION required.
    • Schema: Uses the pgque schema to allow coexistence with the original PgQ.
    • Modernization: Optimized for PostgreSQL 14+.
    • Daemon replacement: Uses pg_cron instead of the pgqd daemon.
    • Enhanced API: Adds modern patterns like send/receive/ack/nack, DLQ, and delayed delivery.
    • Observability: Includes built-in metrics views, health diagnostics, and OTel integration.
    • SDKs: Provides native client libraries for Python, Go, Node.js, and Ruby.

  3. Available PgQue Client Libraries

    main

    PgQue provides four first-party client libraries that act as thin wrappers over the pgque.* SQL primitives. These clients are designed to provide idiomatic interfaces for their respective languages while maintaining feature parity across the ecosystem.

    Supported languages and packages:

    • Python: pgque-py
    • Go: pgque-go
    • TypeScript: pgque
    • Ruby: (First-party client available)

    All clients support core capabilities including connection management, sending jobs (send, send_batch), receiving jobs (receive), acknowledging/nack-ing jobs (ack, nack), and ticker management (ticker, force_next_tick).

  4. Understand the benchmark directory structure

    main

    The benchmark harness is organized into several functional directories:

    • install/: Contains installation scripts for various queue systems (e.g., install_pgq.sh, install_river.sh, install_awa.sh).
    • runners/: Orchestration scripts like run_r7.sh, run_r10.sh, and clean_reinstall.sh.
    • consumers/: SQL or Python implementations of consumers for different systems (e.g., consumer_pgque.sql, consumer_awa.py).
    • producers/: SQL or Python implementations of producers (e.g., producer_pgque.sql, producer_awa.py).
    • tooling/: Samplers and utility scripts for observability (e.g., bloat_sampler.py, idle_in_tx.sh).
    • charts/: Python scripts for analyzing and visualizing benchmark data (e.g., r10_throughput_chart.py).
    • gifs/: Scripts to generate animated GIFs of benchmark results.

  5. How to induce a 'death spiral' using idle_in_tx.py

    main

    To simulate the PostgreSQL queue death spiral (where a long-running transaction prevents vacuuming and causes table bloat), use the idle_in_tx.py script.

    This script opens a REPEATABLE READ transaction and holds the xmin horizon indefinitely. To stop the transaction and allow vacuuming to resume, kill the process with SIGTERM.

  6. Determine when NOT to use pgque

    main

    pgque is not suitable for all workloads. Avoid using it if:

    • Sub-10ms latency is required: pgque's tick-based architecture typically results in 1-2s latency (though this can be reduced to ~100ms with LISTEN/NOTIFY wakeups). For sub-10ms requirements, consider graphile-worker.
    • Sustained throughput exceeds 100k jobs/sec: While highly efficient, if you need massive sustained throughput, a dedicated broker like Kafka or RedPanda may be necessary.
    • Complex multi-step workflows are needed: If you require branching logic, retries with escalation, or stateful orchestration (e.g., "Step A -> Step B -> if fail, Step C"), use a workflow engine like Temporal, Restate, or Absurd instead of a queue.
    • You are locked into a specific ecosystem: If your stack is exclusively Go or Elixir, specialized tools like River or Oban might offer better developer experience (DX).

  7. Understand the PgQue tick-based delivery model

    main

    A common point of confusion is why pgque.receive returns zero rows immediately after a send.

    PgQue is tick-based, not row-claiming. Producers append events, but consumers do not see individual rows; they see batches. A batch is the set of events between two ticks. Until a tick occurs, there is no batch boundary, and pgque.receive will return nothing.

    In production, a scheduler (like pg_cron) drives ticks continuously (defaulting to 10 times per second). For testing or manual intervention, you can use pgque.force_next_tick and pgque.ticker() to manually advance the queue state.

  8. Manage Cooperative Consumer State Transitions

    main

    Cooperative consumers move through specific roles in the pgque.subscription table. Understanding these transitions is critical for managing the lifecycle of your workers.

    Roles

    • normal: An ordinary fan-out consumer.
    • coop_main: The logical consumer group cursor row.
    • coop_member: A subconsumer row that can own an active cooperative batch.

    State Transitions

    • normal $\rightarrow$ coop_main: Occurs when the first subconsumer is registered for a logical consumer. This will fail if the normal consumer currently has an active batch.
    • coop_main $\rightarrow$ normal: Occurs when the last coop_member is unregistered. This is only allowed if no active member batches remain, or if they have been safely nacked with batch_handling = 1.
    • coop_member $\rightarrow$ deleted: Occurs when a subconsumer is unregistered after passing active-batch safety checks.

  9. Understand the Latency Trade-off in PgQue

    main

    PgQue prioritizes stability and zero-bloat over sub-millisecond dispatch. It uses a ticking mechanism to batch events, which introduces end-to-end delivery latency.

    The Three Latencies

    1. Producer latency: The time for send / insert_event. This is individually fast.
    2. Subscriber latency: The time for next_batch over a pre-built batch. This is individually fast.
    3. End-to-end delivery: The time from send until the event is visible to a consumer.

    Tuning Latency

    By default, PgQue ticks 10 times per second (every 100 ms). The average end-to-end delivery is roughly half the tick period (median ~52 ms).

    To reduce latency, you can tune the tick period using pgque.set_tick_period_ms(ms). Accepted values must be exact divisors of 1000 ms (e.g., 50ms for 20 ticks/sec).

    Note: If your application requires single-digit-millisecond dispatch, PgQue may not be the appropriate tool.

  10. Understand PgQue's Zero-Bloat architecture

    main

    Unlike SKIP LOCKED queues that rely on DELETE and VACUUM (which can fail to reclaim space if a long-running transaction holds the xmin horizon), PgQue avoids row deletions on its hot path.

    How Rotation works

    • PgQue uses table inheritance (INHERITS) with three rotating child tables.
    • Instead of deleting rows, the ticker advances to the next child table and uses TRUNCATE on the oldest one.
    • TRUNCATE drops the entire table storage at once, leaving zero dead tuples for VACUUM to handle.

    Operational Risk: Consumer Lag

    Rotation can only TRUNCATE a child table once every consumer has read past it. If a consumer stops or becomes chronically slow, it 'pins' the lowest tick, preventing rotation and causing the event tables to grow. Monitor consumer lag to prevent storage growth.

  11. Prevent disk exhaustion by detecting stuck consumers

    main

    The Critical Risk: Stuck Consumers

    PgQue reclaims space by rotating event tables (using TRUNCATE). Rotation is gated by the slowest consumer: if a consumer is stuck (crashed, deadlocked, or too slow), it pins the lowest tick, and rotation is blocked indefinitely. This causes event tables to grow without bound.

    How to detect a stuck consumer

    Watch for a consumer where:

    1. last_seen keeps growing (the consumer is inactive).
    2. last_tick is frozen (not advancing).
    3. The queue's last_tick_id is advancing.
    4. pending_events is climbing.

    How to resolve

    If a consumer is confirmed dead and will not return, you must unsubscribe it to allow rotation to proceed:

    select pgque.unsubscribe('orders', 'dead_consumer');

    Alternatively, to tear down a queue and all its consumers:

    select pgque.drop_queue('orders', true);

  12. Important caveats and transaction rules

    main

    ack() return value

    client.ack(batchId) returns Promise<number>.

    • Returns 1 if the batch was active and successfully finished.
    • Returns 0 if the batch was not found or already finished (stale/double ack). A 0 result is not a SQL error; the promise resolves normally. Use this to detect double-acks.

    Transactions and Snapshots

    PgQue is snapshot-based. send $\rightarrow$ ticker $\rightarrow$ receive must each run in its own committed transaction.

    • pg.Pool#query handles this by making every call an implicit transaction.
    • Pitfall: For transactional enqueue, use client.rawPool to call BEGIN, pgque.send, and COMMIT on a checked-out client.
    • Do not mix pgque.send and pgque.receive in the same shared transaction. Similarly, do not mix pgque.maint_retry_events and pgque.ticker in one transaction.