pgmq is a PostgreSQL extension for persistent application messages, events, and tasks. It supplies a ready-to-use lightweight message queue with an API aligned to AWS SQS, avoiding the implementation work of building queue tables, locking, and failure handling from scratch. section 11.6 section 11.8
Use the pgmq schema functions to:
- Create and manage queues backed by separate PostgreSQL tables. section 11.7
- Send JSONB messages and retrieve them with either immediate deletion or temporary visibility. section 11.7.1 section 11.7.2
- Recover messages when consumers fail by letting visibility timeouts expire. section 11.7.2
- Archive processed messages under a custom retention policy. section 11.7 section 11.7.2
Queue DMV visitors
Create a queue with SELECT pgmq.create('visitors_queue'); then enqueue a visitor with SELECT pgmq.send(queue_name => 'visitors_queue', msg => '{"service":"car_registration","visitor":"Olivia Morgan"}'). Use pgmq.pop('visitors_queue') when an officer is ready; it returns the message and removes it. For recoverable processing, call pgmq.read(queue_name => 'visitors_queue', vt => 120, qty => 1). The selected visitor remains hidden for two minutes, reappears if unprocessed, and can later be archived with pgmq.archive(queue_name => 'visitors_queue', msg_id => 2). section 11.7.1 section 11.7.2
Listing 11.6 Starting Postgres with pgmq on Unix
docker volume create postgres-pgmq-volume
docker run --name postgres-pgmq \
-e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password \
-p 5432:5432 \
-v postgres-pgmq-volume:/var/lib/postgresql/data \
-d ghcr.io/pgmq/pg17-pgmq:v1.5.1
Start a development PostgreSQL container using the pg17-pgmq:v1.5.1 image, then connect with psql and run CREATE EXTENSION pgmq;. section 11.6
