---
title: "PostgreSQL"
description: "The database Queen runs on: version 15 or newer, no extensions, a direct connection, a pool that fits max_connections, and a cost model counted in commits."
---

> Queen MQ documentation, for AI agents
> Complete self-contained summary of Queen MQ: https://queenmq.com/llms-brief.txt
> Fetch that first when the question is about the product rather than about this page.
> Index of all pages: https://queenmq.com/llms.txt

# PostgreSQL

PostgreSQL is not a dependency of Queen, it is Queen's storage engine. Messages, offsets, the
dedup index, queue configuration and the dead-letter table are ordinary tables, and every queue
semantic runs in stored procedures inside the database. Sizing PostgreSQL is sizing Queen.

## Set it up

PostgreSQL 15 or newer, no extensions. The floor is one unique index declared
`NULLS NOT DISTINCT`, which 14 does not have. The simplest correct setup is a dedicated database
owned by a dedicated role:

```sql
CREATE ROLE queen LOGIN PASSWORD 'change-me';
CREATE DATABASE queen OWNER queen;
```

Point the `PG_*` variables at it. The broker applies `schema.sql` and 23 procedure files at every
boot, from text compiled into the binary, under an advisory lock that serialises replicas. That
needs `CREATE` on the database, ownership of what it creates and the right to issue grants, which
the owner role above has.

Where policy forbids DDL from an application role, `QUEEN_APPLY_SCHEMA=0` starts the broker
straight into serving against a schema you applied yourself, in the same file order, and matching
it to the binary becomes your job.

One grant is worth knowing about on a shared database: the `queen_streams` schema and its two
tables are granted to `PUBLIC`, so every role that can log in can read and write stream state.

## Give it a direct connection

The broker must keep the same backend for the life of a pooled connection, and a pooler in
transaction or statement mode takes that away. Two mechanisms break:

- **Session advisory locks.** The boot-time schema apply holds one across the whole DDL, and the
  maintenance cycle holds another across many autocommitting calls: that lock is how exactly one
  replica sweeps per cycle.
- **Prepared statements.** The hot paths prepare once per connection, and transaction-mode pooling
  invalidates that cache on every checkout, so every push, pop and ack pays a parse and a plan.

**Session mode is the pooling mode that works.** PgBouncer in session mode pins one server
connection per client for the life of that connection, so neither mechanism above is taken away,
and it is a supported way to run. Two things decide whether it earns its place in front of a
broker. It multiplexes nothing here, because the broker's own pool holds its connections open for
the process lifetime: 28 client connections were measured against 28 backends. And a server
connection recycled underneath a held session advisory lock surfaces as
`pg_advisory_unlock reported not-held` in the retention logs. The deployments those numbers came
from point `PG_PORT` at PostgreSQL directly for that reason. Give each broker its own backends.

## Size the pool before you scale out

`DB_POOL_SIZE`, default 160, is the ceiling on concurrent database work for one broker, and the
arithmetic is per cluster:

```text
replicas × DB_POOL_SIZE + superuser_reserved_connections + your own tooling ≤ max_connections
```

Three brokers at the default want 480 connections and a stock PostgreSQL allows 100. That mismatch
answers `500` with `{"error":"pool"}` long before anything looks like a query problem.

`QUEEN_STMT_TIMEOUT_MS`, default 30000, is broker-side. The broker never sets PostgreSQL's own
`statement_timeout`, so if you want a server-side ceiling as well, set it on the broker's role
above the broker's value.

## The server settings that matter

The workload is append-heavy: a segment insert and an allocator-row update per push, a consumer-row
update per pop and ack, and a matching delete rate once retention is on.

- `max_connections`, the setting most likely to be wrong: see the arithmetic above.
- `shared_buffers`, because the hot set is the allocator and consumer rows plus the newest
  segments, tiny and updated constantly.
- `synchronous_commit`, where `on` is the durable choice and the one the published runs used.
- `commit_delay` and `commit_siblings`, so one fsync carries more of the concurrent pushes.
- `max_wal_size` and the checkpoint settings, because a high sustained write rate checkpoints
  constantly at stock values.
- the autovacuum cadence, because the tables updated in place churn far faster than their row
  counts suggest.

The broker sets its own per-table `fillfactor` and autovacuum parameters at every boot. These are
measured, not defaults with a margin. The coordination tables (`log_partitions`, `log_consumers`)
carry `fillfactor` headroom to keep update chains HOT and on-page, threshold-based autovacuum
(`scale_factor = 0`) because their churn rate has nothing to do with their row count, and
`vacuum_truncate = off`, because heap truncation takes an ACCESS EXCLUSIVE lock, reclaims nothing
on a fixed-population table, and freezes every push and pop behind it for seconds. Turning it off
removed an entire class of periodic latency spike seen across every high-rate run.

`log_segments` carries a different set, including matching parameters on its TOAST table, chosen
after stock scale factors were measured re-firing vacuum every 13 seconds at 1M msg/s. All of it is
storage parameters only: no table rewrite, idempotent at every boot. Do not strip them in a tuning
pass.

Two tables are the exception to "measured", and they say so themselves. `queen.kv` and
`queen.log_timers` take the same threshold-based autovacuum and the same `vacuum_truncate = off`, on
the same reasoning, but their `fillfactor = 70` is set **by analogy** and has no dated measurement
behind it. On `queen.log_timers` the analogy is weak by construction, because the claim updates an
indexed column and is therefore never HOT, so the headroom buys page locality rather than HOT chains.
Two more numbers are missing for the same reason: how many timers a second a machine sustains before
autovacuum on that table enters the profile, and how many of the global `autovacuum_max_workers`
(default 3) the pair occupies in steady state. If that budget bites, the first symptom is the log
slowing down with no visible culprit, which is why the dead-tuple ratio from `pg_stat_user_tables` is
the indicator to watch on those two specifically: `vacuum_truncate = off` removes the acute symptom and
leaves only the slow one. The tables these parameters exist for, and why each one is shaped the way it is, are in
[the storage model](/internals/storage-model).

## Size it in commits, not messages

A push becomes one compressed segment row per partition touched. Those rows are not one transaction
each: each dispatch bundles up to `QUEEN_V2_BUNDLE_MAX` ready, disjoint partitions into a single
`log_push_multi_v1` transaction, so one commit and one fsync carry many partitions. Commit rate is
decoupled from partition count, which is what 16,500 commits per second during the
[million-partition run](/benchmarks/cardinality-1m) measures. The question that decides the
hardware is therefore how many messages ride each commit, and a saturated Queen is almost always
commit-bound rather than CPU-bound.

```bash
curl -s http://localhost:6632/metrics/prometheus | grep -E 'queen_fusion_items_per_batch|queen_batch_rtt_milliseconds'
```

The first gauge is messages per commit, the second is how long a commit takes. Client batch size is
the dominant lever on the first one, ahead of every server-side knob, and it is free. The broker
also coalesces concurrent pushes and concurrent acks into shared transactions on its own, and can
do the same for pop claims (`QUEEN_POP_FUSION`, off by default), which puts N claim legs in one
transaction. The claim that fusion shares is in [life of a pop](/internals/life-of-a-pop).

Partition count is the other axis, and it drives background work whether or not traffic exists:
retention, the stats reconciler and the metrics collector all iterate partitions on a fixed
cadence. An idle deployment with 100 partitions and one with 100,000 differ in steady-state
database CPU while both move zero messages.

1. Measure your own message. Payload size and compressibility decide storage.

2. Fix the batch size first, then read `queen_fusion_items_per_batch` under real load.

3. Choose the partition count from the consumer parallelism you need, then price its background
   cost.

4. Load the deployment until `queen_admission_waiting{lane="push"}` goes non-zero while
   `queen_admission_budget` sits at its ceiling. That is the commit ceiling on that disk. Read
   `queen_admission_txn_per_train` at that point for how many transactions each fsync is carrying,
   and `queen_admission_cycle_ms` for the flush interval you are actually getting: those two are
   what `commit_delay` and `commit_siblings` move. `pool_waiting` is not the signal here. The
   arbiter caps admitted write transactions below `DB_POOL_SIZE` minus
   `QUEEN_ADMISSION_POOL_RESERVE` precisely so admitted work never queues on the pool, so a
   non-zero `pool_waiting` means read traffic or an unmetered path, not a saturated write path.
   The arbiter itself is in [flow control](/internals/flow-control).

5. Turn retention on before you go live. An unbounded queue is a full disk on a schedule you did
   not choose.

The measured runs behind all of this, each with the conditions it was taken under, are in
[Benchmarks](/benchmarks).

The database being the whole state also makes backup a `pg_dump` of both schemas, with the
commands and the one artefact no dump captures in
[Operations](/deploy/operations#backup-and-restore).

PostgreSQL 15, a direct connection, a pool that fits `max_connections`, and a commit rate you have
measured rather than assumed.

Source: https://queenmq.com/deploy/postgres/index.mdx