Message queues in Postgres Pro: ditching external brokers for true transactional reliability

In the age of distributed systems — where every component must be not just fast, but predictable — the reliability of data exchange becomes mission‑critical. Picture this: a user clicks “Generate Report”, and instantly a dozen processes must fall into sync — from creating the document to emailing it. But what if the mail server is temporarily down? Or the task processor crashes mid‑operation?

That’s exactly where message queues step in: they turn a chaotic storm of requests into a controlled stream, ensuring no task goes missing along the way.

The story behind creating built‑in queues for PostgreSQL started with a familiar pain: external brokers like RabbitMQ or Kafka — while powerful — introduce complexity. They need dedicated servers, clusters, monitoring, backups… the whole zoo. In enterprise environments with thousands of deployments, every additional component increases operational risk and administrative load.

So the question naturally arises: why bolt on a separate broker when the queue can live inside the database itself? Not only does this save time, it eliminates consistency headaches: if a transaction rolls back, the message rolls back with it — no extra code, no magic glue.

Two approaches to queues: log‑based vs AMQP/JMS

Distributed systems have converged on two main models for message processing:

1. Log‑based queues (e.g., Kafka). These operate as an append‑only event log. Data is written strictly sequentially, and consumers read it in the same order. Great for data synchronization between microservices or for database replication. But their biggest strength — linearity — becomes a weakness when flexibility is needed. You can’t easily pick messages by priority or filter them on the fly.
2. AMQP/JMS brokers (e.g., RabbitMQ). These let you manage message lifecycles: priorities, filters, error handling. You can emulate asynchronous RPC and reliably retry failed operations. Their weakness is not the protocol — it’s the architecture: no external broker can guarantee full transactional consistency with a database.

The problems with external brokers: transactions & complexity

Using Kafka or RabbitMQ in enterprise setups often leads to subtle, but very real problems:

• Data mismatch. Imagine this: the application sends a message to RabbitMQ and starts a DB transaction. The message is already in the broker, but the DB transaction fails on commit. Result? Inconsistency: the task executes, but its data isn’t saved. You can fix this with two‑phase commits (2PC), but that adds another coordinator, slows everything down, and complicates the architecture.
• Operational overhead. External brokers need their own installation, tuning, monitoring, backups. They add new failure points (e.g., network splits between the app and the broker). Support becomes harder — especially when different teams manage the DB and the broker.

Postgres Pro Enterprise Queues: transactional, native, automated

The new pgpro_queue extension is a direct response to developers tired of juggling consistency issues. By integrating queues inside the database, it removes the need for external components entirely.

Messages are stored in regular tables, replicated via standard PostgreSQL mechanisms, and take part in the same transactions as your application logic.

Installation & setup

Setting up pgpro_queue is pleasantly straightforward:

1. Enable in shared_preload_libraries (postgresql.conf):

shared_preload_libraries = 'pgpro_queue'

1. Create the extension:

CREATE EXTENSION pgpro_queue;

1. Initialize internal objects:

SELECT queue_initialize();

This creates a dedicated schema pgpro_queue_data, ensuring smooth pg_dump and replication.

Key features & how they work

1. Retry‑on‑rollback (automatic retries). This is pgpro_queue’s secret sauce. If a transaction that processed a message rolls back (for example, because an external service was unavailable) the message isn’t lost. It automatically returns to the queue.

• Configuration. Set retry policy per queue using CREATE_QUEUE with q_retries and q_retrydelay.
• Important. To enable retries, you must set your database name in postgresql.conf:

pgpro_queue.database_with_managed_retry = 'your_database_name'

Without it, messages are deleted on rollback.

1. Filtering & Priorities. pgpro_queue lets you shape the message stream:

• Priorities. When inserting a message via INSERT_MESSAGE, you can set q_msg_priority. The lower the value, the higher the priority. Higher-priority messages are processed first.
• Filtering. READ_MESSAGE and READ_MESSAGE_XML accept q_msg_hfilter an q_msg_pfilter parameters, letting you fetch messages based on the contents of their headers or properties.

1. JSON & XML support. Separate APIs simplify integration with external systems:

• JSON: INSERT_MESSAGE, READ_MESSAGE;
• XML: INSERT_MESSAGE_XML и READ_MESSAGE_XML;
• Universal: READ_MESSAGE_ANY.

1. Delayed execution. Use q_msg_enable_time to defer when a message becomes available.

Under the hood

pgpro_queue is built on a simple principle: messages live in normal PostgreSQL tables. This enables WAL loggingб crash‑safe recovery and streaming replication. Every message is written to WAL, ensuring recovery even after power loss. The crown jewel is the retry‑on‑rollback mechanism tightly integrated with PostgreSQL’s transaction engine. If processing fails, the message is transparently returned for another attempt — no custom logic needed.

Another strength is its synergy with the built‑in Postgres Pro Enterprise scheduler. This internal cron‑like system can launch periodic jobs that themselves generate sub‑tasks and push them into queues. Example: generating thousands of regional reports. The scheduler creates regional tasks, which enqueue messages considering local time zones.

External brokers like Kafka are indeed robust, but only in isolation. In real systems, the weak points are the integration layers: networks, configs, and transaction boundaries. With pgpro_queue, queues live in the same universe as your data. Messages survive crashes thanks to WAL and replication. Admins no longer need separate dashboards, everything is managed through the familiar PostgreSQL toolkit.

Important technical notes

To use pgpro_queue effectively, keep in mind:

• Isolation level. Supported only in transactions with READ COMMITTED.
• Prepared transactions (2PC). If a transaction with READ_MESSAGE() is prepared via PREPARE TRANSACTION, the message remains locked until COMMIT PREPARED or ROLLBACK PREPARED. Note: ROLLBACK PREPARED unlocks the message but does not trigger retries.

Roadmap: what's next for built‑in queues

Upcoming features include:

1. Pub/Sub subscription system. Similar to RabbitMQ Exchanges: producers will publish to topics, and all subscribers get a copy. This enables full event‑driven architectures.
2. Callback notifications. The database will be able to send an HTTP callback when a message arrives — no more polling loops.
3. Dead Letter Queue (DLQ). Planned for later releases: a safe place for poisoned messages.

Conclusion

For enterprise systems, the main benefit is predictability. Every message is processed within database transactions, eliminating inconsistencies. Infrastructure becomes simpler: instead of a jungle of services, you get one cohesive environment where queues, business logic, and scheduling are all first‑class citizens.

This approach is ideal for domains where errors are costly—banking, healthcare, government registries. If your application depends on atomic operations and low operational overhead, PostgreSQL’s built‑in queues aren’t just convenient — they’re essential. They don’t just deliver messages — they deliver order to the chaos of distributed systems.