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:

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:

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.

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 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.

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.

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.

The measured runs behind all of this, each with the conditions it was taken under, are in 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.

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