A hands-on walkthrough. With psql access to a Postgres 14+ instance and ten minutes, you can follow every step end to end. You will type SQL, see what comes back, and build an intuition for how PgQue moves messages.
By the end, you will have a working orders queue with a processor consumer, a retry flow, a dead letter queue, and health checks.
Prerequisites: Postgres 14 or newer, a database to install into, and psql (the tutorial uses \i and \gset, which are psql meta-commands). pg_cron is recommended for production but not required here — this tutorial drives the ticker manually so it works anywhere.
Each step shows the exact SQL and the expected output. Your msg_id / ev_id / pending_events / ev_new numbers will differ from the examples — every pgque.force_next_tick call skips the event sequence forward by about 1000, so exact numeric output depends on when you call it. Treat those numbers as illustrative. When transaction boundaries matter (and they matter — PgQue is snapshot-based), the text calls that out.
You can run every snippet in psql with --no-psqlrc and PAGER=cat if you want reproducible output. From the cloned repo, so \i sql/pgque.sql resolves:
cd /path/to/pgque
PAGER=cat psql --no-psqlrc -d mydbFor vocabulary — "batch", "tick", "rotation" — see the concepts glossary.
PgQue is a single SQL file. Install it inside a transaction so a failure leaves no half-built schema behind:
begin;
\i sql/pgque.sql
commit;Verify the install by asking for the version:
select pgque.version(); version
----------------
[[your version]]
The install creates the pgque schema, three roles (pgque_reader, pgque_writer, pgque_admin), and every function you will call in the rest of this tutorial. The roles are siblings: pgque_writer produces (send, send_batch); pgque_reader consumes (subscribe, receive, ack, nack); pgque_admin is a member of both.
Skip the next snippet if you are following this tutorial as the install owner — the rest of the tutorial works without any extra grants. The block below is the typical app-role setup you would do in production, where app_orders / app_webhook / metrics are app-role names you create yourself:
-- Produce + consume:
grant pgque_reader, pgque_writer to app_orders;
-- Pure producer:
grant pgque_writer to app_webhook;
-- Pure consumer / dashboard / metrics:
grant pgque_reader to metrics;See reference.md — Roles and grants for the full table and rationale.
A queue is a named, shared event log. A consumer is a named cursor into that log. Any number of producers can write to the same queue concurrently, and any number of consumers can subscribe — each sees every event through its own cursor, independently (fan-out by default). You can create as many queues as you want in the same database; this tutorial uses one.
select pgque.create_queue('orders');
select pgque.subscribe('orders', 'processor'); create_queue
--------------
1
subscribe
-----------
1
create_queue returns 1 when it created the queue (and 0 if it already existed — the call is idempotent). subscribe is the modern alias for register_consumer.
Send one event to the queue. The jsonb overload validates and canonicalizes the payload:
select pgque.send('orders', '{"order_id": 42, "total": 99.95}'::jsonb); send
------
1
That is the event id (ev_id) — unique within the queue, monotonically increasing within a rotation window.
pgque.send also accepts a raw text payload — useful for protobuf, msgpack, or XML that you encode yourself. Untyped string literals like '{"x":1}' without the ::jsonb cast resolve to the text overload. This tutorial stays on jsonb for clarity; see the reference for the full overload rules and the NUL-byte caveat for binary payloads.
Now try to pull that event back out:
select * from pgque.receive('orders', 'processor', 100); msg_id | batch_id | type | payload | retry_count | created_at | ...
--------+----------+------+---------+-------------+------------+-----
(0 rows)
Zero rows. This surprises every first-time user. Here is why.
PgQue is tick-based, not row-claiming. Producers append events to the queue, but consumers do not see rows directly — they see batches. A batch is the set of events between two ticks. Until a tick happens, there is no batch boundary, so pgque.receive has nothing to return.
In normal operation, a scheduler (pg_cron or an external loop) drives ticks continuously. With the default pg_cron path, PgQue ticks every 100 ms (10 ticks/sec) inside a single 1-second cron slot. In this tutorial you have not started a scheduler, so no tick has run yet.
See the concepts glossary for the full definitions of event, batch, tick, and consumer.
For demos and tests, PgQue provides pgque.force_next_tick to bypass the tick thresholds for one queue. It does not create the tick by itself — you still have to call pgque.ticker() afterwards to produce the tick:
-- separate transactions (psql autocommit). Do not wrap in begin/commit:
-- ticker() must see the prior send committed before it can include it in a batch.
select pgque.force_next_tick('orders');
select pgque.ticker(); -- separate transaction force_next_tick
------------
1
ticker
--------
1
force_next_tick returns the current tick id (the queue was seeded with tick 1 by create_queue). ticker() returns the number of queues it processed.
Each statement above runs in its own transaction — required, not stylistic. PgQue is snapshot-based: the ticker captures a snapshot, and
receiveonly returns events whosesendcommitted before it. Wrappingsend+force_next_tick+ticker+receivein onebegin/commitreturns zero rows. See the snapshot rule.
Now try receiving again:
select * from pgque.receive('orders', 'processor', 100); msg_id | batch_id | type | payload | retry_count | created_at
--------+----------+---------+------------------------------------+-------------+----------------------
1 | 1 | default | {"total": 99.95, "order_id": 42} | | 2026-04-17 10:00:00+00
(1 row)
The event is back. retry_count is null because this is the first delivery attempt. The batch_id is the important value for the next step.
In production, pg_cron calls pgque.ticker_loop() once per second, and that procedure calls pgque.ticker() every tick_period_ms ms (100 ms by default). An external worker loop can call pgque.ticker() at whatever cadence you choose. force_next_tick exists for the situation here: advancing the queue without waiting on the ticker's lag threshold.
A batch stays assigned to a consumer until the consumer calls ack. Until then, the same batch is returned every time you call receive — the consumer has not moved forward.
Capture the batch_id from step 5 and ack it. In psql you can use \gset:
select batch_id from pgque.receive('orders', 'processor', 100) limit 1 \gset
select pgque.ack(:batch_id); ack
-----
1
Or, if you already saw batch_id = 1 in the output, call it directly:
select pgque.ack(1);ack is the modern alias for PgQ's finish_batch. It finalizes the batch and advances the consumer's cursor past it.
Call receive once more to confirm there is nothing left:
select * from pgque.receive('orders', 'processor', 100);(0 rows)
What you just did, in PgQ terms. The modern receive/ack pair wraps PgQ's canonical consumer loop:
batch_id = next_batch(queue, consumer) -- NULL → sleep and retry
events = get_batch_events(batch_id)
process(events) -- event_retry per event on failure
finish_batch(batch_id)
commit
pgque.receive = next_batch + get_batch_events. pgque.ack = finish_batch. pgque.nack = event_retry (with DLQ routing when retry_count >= max_retries). Both surfaces ship; the primitives are available for advanced use. See the reference or the concepts glossary.
Every row pgque.receive returns is a pgque.message composite: msg_id (PgQ's ev_id), batch_id, type, payload (text — cast to jsonb for JSON access), retry_count (NULL on first delivery), created_at, and four free-form extra1..4 text columns.
Consumers sometimes fail. nack handles that: the message is scheduled for redelivery after a delay you choose. Before demoing it, lower the retry ceiling so you can drive a message to the DLQ in step 8:
select pgque.set_queue_config('orders', 'max_retries', '2');The parameter is max_retries, not queue_max_retries — set_queue_config prepends queue_ for you.
Send another event, tick, and receive (each select is a separate transaction — required, see snapshot rule):
-- send / force_next_tick / ticker / receive are four separate transactions in psql
-- autocommit. Do not wrap them in begin/commit — the snapshot rule still applies.
select pgque.send('orders', '{"order_id": 43, "total": 10.00}'::jsonb);
select pgque.force_next_tick('orders');
select pgque.ticker(); -- separate transaction
select * from pgque.receive('orders', 'processor', 100); -- separate transaction msg_id | batch_id | type | payload | retry_count | ...
--------+----------+---------+------------------------------------+-------------+----
2 | 2 | default | {"total": 10.00, "order_id": 43} | |
(1 row)
Now pretend the handler failed. nack takes the full pgque.message row, so the natural pattern is a do block that receives and nacks in one place.
Both nack and ack are needed on the same batch. They are not alternatives: nack per-event schedules a retry (or routes to the DLQ if retry_count >= max_retries); ack per-batch finalizes the batch and advances the consumer cursor. Without the ack, the consumer never moves past the batch and the same events are redelivered forever.
do $$
declare
v_msg pgque.message;
begin
select * into v_msg from pgque.receive('orders', 'processor', 1) limit 1;
perform pgque.nack(v_msg.batch_id, v_msg, '0 seconds'::interval, 'simulated failure');
perform pgque.ack(v_msg.batch_id);
end $$;The event is now in PgQ's retry queue. Moving it back into the main event stream is a separate maintenance step: pgque.maint_retry_events(). After that, the next tick makes it visible again:
-- four separate transactions (psql autocommit). Do not wrap in begin/commit.
select pgque.maint_retry_events();
select pgque.force_next_tick('orders');
select pgque.ticker(); -- separate transaction
select * from pgque.receive('orders', 'processor', 100); -- separate transactionIn production, pgque.start() schedules maint_retry_events on its own cadence — you never call it by hand. See pgque.maint() and the surrounding Lifecycle entries in the reference.
msg_id | batch_id | type | payload | retry_count | ...
--------+----------+---------+------------------------------------+-------------+----
2 | 3 | default | {"total": 10.00, "order_id": 43} | 1 |
(1 row)
Same msg_id = 2, new batch_id = 3, retry_count = 1 — that's the redelivery.
Keep nacking. You set max_retries = 2 in step 7, and the message was just redelivered with retry_count = 1. On the next nack it becomes retry_count = 2; one more nack after that, and nack sees retry_count >= max_retries and routes the message to pgque.dead_letter instead of the retry queue.
Two more nack cycles. Run the following block twice — the nack do block followed by maint_retry_events + tick:
do $$
declare
v_msg pgque.message;
begin
select * into v_msg from pgque.receive('orders', 'processor', 1) limit 1;
perform pgque.nack(v_msg.batch_id, v_msg, '0 seconds'::interval, 'still failing');
perform pgque.ack(v_msg.batch_id);
end $$;
-- the do-block above is one transaction; the three statements below are
-- three more, in psql autocommit. Do not wrap them in begin/commit.
select pgque.maint_retry_events();
select pgque.force_next_tick('orders');
select pgque.ticker(); -- separate transactionThe second iteration sees retry_count = 2 and routes to the DLQ instead of the retry queue. After it runs, receive returns nothing — the event has moved to pgque.dead_letter.
Inspect the DLQ:
select dl_id, dl_reason, ev_id, ev_retry, ev_data
from pgque.dlq_inspect('orders'); dl_id | dl_reason | ev_id | ev_retry | ev_data
-------+---------------+-------+----------+----------------------------------
1 | still failing | 2 | 2 | {"total": 10.00, "order_id": 43}
(1 row)
From here, two moves. After you have fixed the upstream bug, put the event back on the queue:
select pgque.dlq_replay(1);The event re-enters the main queue with a fresh event id and will be delivered on the next tick. The DLQ row is removed.
To empty the DLQ, use dlq_purge — it deletes rows older than the interval you pass ('0 seconds' clears everything for that queue; the default is '30 days'):
select pgque.dlq_purge('orders', '0 seconds'::interval);Three functions read out queue and consumer health.
select queue_name, ticker_lag, ev_per_sec, ev_new, last_tick_id
from pgque.get_queue_info('orders'); queue_name | ticker_lag | ev_per_sec | ev_new | last_tick_id
------------+-----------------+------------+--------+--------------
orders | 00:00:03.412 | 0.12 | 0 | 7
ticker_lag is the wall time since the last tick. If this grows without bound, the ticker is not running.
select queue_name, consumer_name, lag, last_seen, pending_events
from pgque.get_consumer_info('orders', 'processor'); queue_name | consumer_name | lag | last_seen | pending_events
------------+---------------+--------------+--------------+----------------
orders | processor | 00:00:02.11 | 00:00:01.50 | 0
lag is the age of the consumer's last finished batch — high means the consumer is falling behind. last_seen is the elapsed time since the consumer last processed a batch — high means the consumer has stopped calling receive. pending_events is the count waiting in the current table for the next tick. For a healthy system, lag and last_seen both stay low and ticker_lag stays under a few seconds.
select * from pgque.status(); component | status | detail
------------+-------------+----------------------------------
postgresql | info | PostgreSQL 17.2 on ...
pgque | info | [[your version]]
pg_cron | unavailable | pg_cron not installed -- call ...
queues | info | 1 queues configured
consumers | info | 1 active subscriptions
status() is the one-stop health check. If pg_cron is installed and pgque.start() has been run, you will see ticker and maintenance rows with scheduled status and the cron job id.
You have been driving the ticker by hand. In production you want a scheduler driving it. The recommended default is pg_cron — pre-installed or one-command available on every major managed Postgres provider (RDS, Aurora, Cloud SQL, AlloyDB, Supabase, Neon). For self-managed Postgres, follow the pg_cron setup guide.
With pg_cron available in the same database as PgQue:
select pgque.start();That one call schedules four cron jobs: pgque_ticker every second, pgque_retry_events every thirty seconds (moves nack'd events back into the main stream), pgque_maint every thirty seconds (rotation step 1 and vacuum), and pgque_rotate_step2 every ten seconds (rotation step 2). Check them with select * from pgque.status(); or select * from cron.job;.
Sub-second ticking, by default. pg_cron's minimum schedule is 1 second, but PgQue's pgque_ticker job calls CALL pgque.ticker_loop(), a procedure that re-invokes pgque.ticker() every tick_period_ms ms inside that one slot, committing between iterations. The default is 100 ms (10 ticks/sec), so end-to-end delivery typically lands within ~50 ms median.
Tune the rate at runtime — no need to re-run start(), the change applies on the next pg_cron slot (≤1 s):
select pgque.set_tick_period_ms(50); -- 20 ticks/sec
select pgque.set_tick_period_ms(1000); -- 1 tick/sec (the original pgqd cadence)Why a procedure with commit between iterations: each pgque.ticker() call has to run in its own transaction (it records a pg_snapshot to mark the batch boundary, and the snapshot must be committed before the next tick records its own). Without per-iteration commits, all the ticks in the 1-second slot would share one snapshot and the held xmin would block PgQ's metadata rotation.
WAL budget: inactive queues are cheap. The 100 ms default is a check cadence; with no events, most checks return NULL and PgQue backs off toward ticker_idle_period (default 1 minute). The larger WAL estimates apply only to queues that materialize ticks continuously. A forced-tick PG18 measurement isolated about 280 bytes of WAL per materialized tick per queue; if a queue materializes continuously, that projects to roughly 240 MiB/day at 10 materialized ticks/sec, or 24 MiB/day at 1 materialized tick/sec. For small projects, WAL-constrained systems, or slow logical-replication subscribers, pgque.set_tick_period_ms(1000) is a reasonable starting point if ~500 ms median delivery latency is acceptable. See tick-frequency.md for details.
pg_cron in a different database. pg_cron runs jobs in one designated database (cron.database_name, typically postgres). If your PgQue schema lives in a different database, use the cross-database pattern to call pgque.ticker_loop(), pgque.maint_retry_events(), and pgque.maint() across databases. Todo: a future release will detect this and emit the correct cron.schedule_in_database calls from pgque.start() automatically.
pg_cron log hygiene. pg_cron logs every job execution to cron.job_run_details. PgQue's four scheduled jobs together add roughly 5,000 rows per hour, with no built-in purge — the table grows forever otherwise.
Worth knowing: PgQue's sub-second ticker does not make this worse. The internal loop runs inside a single 1-second pg_cron slot, so the per-second cron.job_run_details row count is the same whether tick_period_ms is 1000 or 1.
Recommended: keep pg_cron logging enabled if you want run history for other jobs, and purge only PgQue's high-volume job records:
select cron.schedule(
'pgque_purge_cron_log',
'0 * * * *',
$$
delete from cron.job_run_details d
using cron.job j
where d.jobid = j.jobid
and j.jobname in (
'pgque_ticker',
'pgque_retry_events',
'pgque_maint',
'pgque_rotate_step2',
'pgque_purge_cron_log'
)
and d.end_time < now() - interval '1 day'
$$
);If you do not need successful-run history for any pg_cron job on the instance, you can disable it globally instead:
alter system set cron.log_run = off;
-- requires a Postgres restart; errors from failed jobs still land in
-- the Postgres server log via cron.log_min_messages (default WARNING)Todo: a future pgque.start() will warn about this overhead and offer to schedule the purge job.
Without pg_cron at all, call pgque.ticker() and pgque.maint() from your application or an external scheduler (system cron, systemd, a worker loop) on the cadence you want. tick_period_ms is only consulted by pgque.ticker_loop() — outside the pg_cron path, your driver chooses the rate.