Schema and procedure catalogue

Everything here lives in the PostgreSQL schema queen, created by server/sql/schema.sql. That file plus exactly 33 files under server/sql/procedures/ (001–033) are embedded in the binary with include_str! and applied at every boot in lexical order, one statement per transaction, under session advisory lock 778120010. All of it is idempotent (CREATE OR REPLACE, IF NOT EXISTS, ADD COLUMN IF NOT EXISTS), so re-apply is a no-op, and QUEEN_APPLY_SCHEMA=0 skips the step entirely, which lets a privileged role pre-apply the DDL so the broker can run as a low-privilege user.

The apply only creates and adds. There are no teardown files and no migration blocks: a deployment starts from an empty database, and schema.sql’s CREATE TABLEs declare the shape directly. Where a later release needed a column on a table an earlier one already declared, the file spells the step out as ADD COLUMN IF NOT EXISTS beside the CREATE, so a database on the same line picks it up at the next boot. That is not an upgrade path: pointing this build at a database written by a different schema is still unsupported, and a new version means a fresh database. See Retired objects for the history.

The engine needs CREATE SCHEMA and no extensions, with one floor worth naming: the cgm_identity_uk index below uses NULLS NOT DISTINCT, which needs PostgreSQL 15 or newer.

The file map: 001_log_schema and 002_streams_schema are the DDL, 003_log_push through 007_log_streams are the engine’s write, read, ack, maintenance and streaming paths, 008/009 are the streams registry calls, 010_log_admin and 011_log_stats are the log-native admin and stats surface, and 012–023 (minus 020) are the shared management plane: configure, queue deletion, consumer groups, status, messages, traces, stats readers, metrics, Prometheus. 020_log_partition_counters is a special case: it only attaches triggers, and its position after both 001 (the table) and 019 (the trigger functions) is the point of it. 024–027 are the KV and timer surfaces, applied on every boot like the rest: 024 and 025 carry their tables and their request-path procedures, 026 the sweeper’s two slow phases, and 027 the read-only quota poll every broker runs whether or not it sweeps. 028–031 are the maintenance and tenancy surfaces the later releases added: 028 the retained-bytes slow lane, 029 the durable per-task lease table that elects one replica per maintenance cycle, 030 the whole database footprint of the ephemeral queue class (its declarations and quota, never its messages), and 031 the tenant purge. The ordering is not cosmetic anywhere: SQL-language function bodies resolve tables at creation time, so the DDL files must land before every procedure body that names them.

That last rule is why 024 and 025 are last rather than early. 005_log_ack applies before them and calls both of their apply procedures, which works only because the transaction wire’s body is plpgsql and resolves names at run time. In the other direction it is a free mechanical guard: nothing applied before 024 can name those tables from a LANGUAGE sql body without failing the boot, which is what keeps log_partition_dead_v1 in 006 from ever growing a veto leg on them, and with it the per-partition scan it fronts.

Queue identity: queen.queues (schema.sql)

One row per queue, and the single queue identity in the system: queen.log_partitions.queue_id references queues(id) ON DELETE CASCADE directly, so every partition→config join is by id. There is no separate log-engine queue table and no (tenant_id, name) bridge join between configuration and data, which makes the cross-tenant name-join hazard class structurally unrepresentable (the retention work list joins by id).

Identity is (tenant_id, name), enforced by the unique index queues_tenant_name_uk. The columns the engine reads directly:

The rest of the row is the queue configuration /configure writes: retry limit and delay, DLQ flags, delayed_processing, window_buffer, the retention policy columns, encryption, max_wait_time_seconds, max_queue_size, min_pop_wait_time. See Queue options.

Why lease_time defaults to 60 and why there is no storage column are both explained in the storage model; the storage: "segments" wire literal is hard-coded in 012_configure, 010_log_admin and 011_log_stats.

A queue row is created by the first push (003_log_push provisioning), by /configure (012_configure), by the transaction wire (005_log_ack), by a streams sink (007_log_streams), and by a wildcard pop or subscription registration on a queue that does not exist yet (004_log_pop). That last one is a deliberate widening: the pop writes a durable queue-scoped consumer_groups_metadata row, whose FK needs the queue row, and subscribe-before-first-push is legitimate, so the FK must not turn an early subscriber into an error. The flip side: a consumer typo now creates a queue row, where it previously left only an orphan watermark.

Queue deletion is one call: queen.delete_queue_v1 (013_analytics) resolves the id, deletes the queue’s log_txns and log_dlq rows explicitly (both FK-less by design, the cascade cannot reach them), then deletes the queues row and lets the FK cascade take log_partitions → log_segments/log_consumers, consumer_watermarks, the queue-scoped consumer_groups_metadata rows, queue_lag_metrics and stats. Previously this was two non-atomic steps (the SQL delete plus a broker-side log-table teardown).

Log tables

queen.log_partitions (001_log_schema)

One row per partition, and the per-partition write serializer; the columns (the last_offset allocator and the two retention watermarks) are documented in the storage model.

PRIMARY KEY (id) and UNIQUE (queue_id, name); queue_id references queen.queues(id), cascading. Index: idx_log_partitions_queue_write (queue_id, last_write_at), the wildcard candidate scan’s hot filter. Storage parameters: fillfactor = 70, autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 500, autovacuum_vacuum_cost_delay = 0, vacuum_truncate = off.

The row is deleted only by the cleanup phase, and only once the partition is empty and both its timestamps are older than PARTITION_CLEANUP_DAYS. Deleting it cascades to log_segments (none, by definition) and log_consumers, so the group cursors go with it; a later push recreates the partition under a new id, allocating from last_offset = -1 again. See Retention internals.

queen.log_segments (001_log_schema)

The messages, packed as length-prefixed zstd-compressed frame blobs; the columns are documented in the storage model.

PRIMARY KEY (partition_id, base_offset) and no secondary indexes: the PK is also the pop path. blob is SET STORAGE EXTERNAL. Autovacuum: scale factor 0.1, insert scale factor 0.3, cost limit 4000, cost delay 0, with the same values mirrored onto the TOAST table.

queen.log_txns (001_log_schema)

The hash sidecar, written on every push whether deduplication is on or off; the columns, and the obligation the missing foreign key puts on the two partition deleters, are documented in the storage model.

PRIMARY KEY (partition_id, base_offset), no secondary indexes, and deliberately no foreign key on partition_id. Autovacuum: scale factor 0.1, insert scale factor 0.3, cost delay 0.

queen.log_consumers (001_log_schema)

Coordination state per (partition, consumer_group): the cursor, the lease and the retry budget. The columns are documented in the storage model.

PRIMARY KEY (partition_id, consumer_group); partition_id references log_partitions(id), cascading. Storage parameters: fillfactor = 50, autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 500, autovacuum_vacuum_cost_delay = 0, vacuum_truncate = off.

queen.log_dlq (005_log_ack)

Dead-lettered frames, as snapshots.

Index: idx_log_dlq_partition_failed_at (partition_id, failed_at DESC).

Nothing in the retention cycle purges this table. Because these rows have no foreign key either, a partition that still holds one is never reclaimed by the cleanup phase: dropping the partition would leave the snapshots unreachable rather than clean them up, since every DLQ reader joins through log_partitions. (delete_queue_v1 does clear them, explicitly, as part of deleting the queue.)

Shared tables the log engine uses

These are created by schema.sql (and 019_worker_metrics for the metrics tables) and are not engine-specific. Since the queue-identity merge they key on queen.queues(id) wherever they used to carry a queue name, so a deleted queue cascades its shared rows away.

Procedures, file by file

Every function below is in the schema queen. Where a signature ends in p_tenant UUID DEFAULT '00000000-...0001', the parameter was added last with a default so an unscoped caller lands on the default tenant, and the older signature is explicitly dropped on re-apply so no ambiguous overload survives.

001_log_schema.sql (tables)

The four log_* tables above (log_dlq lives in 005_log_ack), plus queen.hotlist_repairs and one helper.

queen.hotlist_repairs is not a log table: it is the durable announcement of a deliberate cursor move, which is the one class of pendingness the windowed reseed cannot see (nothing was written). PRIMARY KEY (tenant_id, queue_name, consumer_group), one nullable partition_name (NULL = the whole queue), a repair_at timestamp indexed for the readers and a reason. A seek and a consumer-group delete write it in their own transaction; every broker reads it on the reconcile pass and repairs the rings named there; rows older than an hour are pruned inline by the writer. Two repairs naming different partitions widen to NULL rather than one being lost. It is a publication channel, not a general lost-notification repair: an entry cleared in error, a stranded claim or a stale lease park are still the full walk’s job.

003_log_push.sql (push)

log_push_one_v1 is also called by the transaction wire and the streams cycle, so there is exactly one place in the system where an offset is assigned.

004_log_pop.sql (pop)

log_pop_v1 is DROP-and-CREATE rather than CREATE OR REPLACE, because its OUT row type is part of the signature and OR REPLACE refuses return-type changes, which would brick the boot re-apply.

005_log_ack.sql (ack, lease renewal, DLQ, transactions)

Also creates queen.log_dlq (above).

get_dlq_messages_v1 used to be defined here too; it is not any more. 010_log_admin applies after this file and redefines the same signature, so the copy here was overwritten at every boot and never ran. 010_log_admin owns it now.

006_log_maintenance.sql (retention steps)

This file also adds idx_retention_history_executed_at (executed_at DESC), the index the retention analytics readers scan.

007_log_streams.sql (streaming)

010_log_admin.sql (admin and observability)

Ported from the retired seg-era consumer-group, observability and traces files. The rows-engine legs those redefinitions once carried are gone. There is one engine, so every body here runs unconditionally, and the join to queen.queues on p.queue_id is itself the queue-existence guard (it supplies namespace, task, priority and tenant scoping in the same touch).

Three procedures from the retired observability file were deliberately not ported, because they have no Rust call site: get_queue_messages_v1, has_pending_messages and get_prometheus_metrics_v1.

011_log_stats.sql (stats and analytics)

020_log_partition_counters.sql (triggers)

Defines no functions; attaches trg_partitions_created_counter and trg_partitions_deleted_counter to queen.log_partitions. It is a separate file for two reasons. It must run after both 001_log_schema (the table) and 019_worker_metrics (the trigger functions), so a fresh database gets the attachment on its very first boot. And the attachment is conditional on the trigger being absent: DROP TRIGGER takes ACCESS EXCLUSIVE and CREATE TRIGGER takes SHARE ROW EXCLUSIVE on the engine’s hottest table, so re-running the pair every boot would stall every push and pop behind each rolling restart. The trade-off is that changing a trigger definition needs a manual drop; changing the function body still takes effect on restart, since CREATE OR REPLACE FUNCTION rebinds it without touching the table.

Advisory locks

The one global lock order

There are six lock spaces, not two, and a proof written over a subset of them is not a proof. The inventory below is the complete one, read off every FOR UPDATE, every UPDATE and every advisory lock in the engine’s procedure files.

Leaf tables, which are insert-only or have a single writer, sit below all of them: log_segments, log_txns, log_dlq, retention_history, kv_usage, kv_quota.

The declared total order is:

queen.kv → queen.log_timers → advisory → queen.queues
         → queen.log_partitions → queen.log_consumers → leaves

And the rule that makes it a proof fits on one line:

No actor may ACQUIRE a lock on queen.kv or queen.log_timers after acquiring a lock on queen.queues, queen.log_partitions or queen.log_consumers.

Acquire is the load-bearing word, and the distinction it draws is what makes the rule falsifiable rather than merely severe. Writing again to a row this same transaction already holds is not an acquisition: it adds no edge to the wait-for graph. The precedent is in the house already, in the multi-push pre-lock, which re-locks rows it holds on purpose. Without the distinction the rule reads as a ban on the timer fire, whose last step deletes exactly the rows it locked in its first step, and the first person to apply it literally rewrites the one procedure that is correct.

Each actor that takes more than one row in a space also takes them in a declared order within that space: (namespace, key) ascending for queen.kv and (queue, timer_key) ascending for queen.log_timers, both with an explicit COLLATE "C"; name ascending for provisioning inserts; id ascending for queen.log_partitions; (partition_id, consumer_group) ascending for queen.log_consumers.

Those two conditions together are the whole argument, and both are needed: the first orders the spaces, the second orders the rows inside a space, which is where the first says nothing. Given both, the wait-for graph is acyclic by resource ordering.

Three actors never appear in that graph at all, whatever they visit: the pop, the sweeper’s timer claim and the sweeper’s KV prune all take their rows with SKIP LOCKED, so they never wait, and a vertex that never waits cannot be an edge.

log_partition_cleanup_step_v1 is the one maintenance step that holds many partition locks at once, so it is bound like any other multi-partition writer: it takes them ascending by id in a single statement, with SKIP LOCKED so it steps over a partition a pusher is holding instead of queueing in front of the write path. The ack path takes only consumer locks and never a partition lock; the per-partition retention steps take exactly one partition lock each.

The residue, stated honestly

No cycle survives the rule, but two costs do, and both are accepted rather than absent.

A fire holds timer rows while it waits for a partition lock, because T → P is the direction the order prescribes. If a cancel for one of those rows arrives in that window it waits. It does not fail, it is not lost, and it does not deadlock: the chain ends when the fire commits. The user’s cancel returns late, and what it returns then depends on whether the fire got there first.

The transaction wire holds a queen.kv row from its first step until its own commit, which is the outermost space held for the longest time in the system. It can spend that time waiting on a partition held by a fusion flush, and the flush never asks for a KV row, so the chain terminates. A chain is not a cycle, but the holding time is real and it is the price of putting the KV step first. It is first because the common case is a failed precondition, and failing there costs one insert and one raise, before a single partition lock has been taken, instead of an entire bundle written and thrown away.