simplebroker-pg

Postgres backend plugin for SimpleBroker.

This package is intentionally separate from simplebroker itself. SimpleBroker remains SQLite-first. This package adds a Postgres backend through the public backend plugin hook.

Requirements

• Python 3.11+
• PostgreSQL
• A dedicated schema for SimpleBroker tables

Standard, GIL-enabled CPython 3.15 is supported, with native PostgreSQL CI coverage on Linux. This qualification does not cover free-threaded Python 3.15t or JIT-specific behavior.

public is intentionally rejected.

Exact-target broadcast requires backend API v5: SimpleBroker 5.6.1 or newer and simplebroker-pg 3.3.1 or newer. Default selection intersects requested names with existing queues. Python create_missing=True instead inserts into the complete requested set, intentionally recreating a queue deleted before the broadcast lock is acquired. Selection and insertion occur in one PostgreSQL transaction.

SimpleBroker 7.1.0 and simplebroker-pg 3.6.0 are the first coordinated backend API v6 set, which adds terminal activity-waiter close. Package dependency floors are minimums; the exact runtime handshake remains authoritative for every installed pair.

SimpleBroker 7.3.0 and simplebroker-pg 3.8.0 are the first coordinated backend API v7 set. It adds the required monotone durable high-water advance used by persistence restore. Package dependency floors remain minimums; the exact runtime handshake remains authoritative for every installed pair.

SimpleBroker 8.0.0 and simplebroker-pg 4.0.0 are the first coordinated backend API v8 set. It adds bounded public-ID selection order and SQL storage schema v6. Package dependency floors remain minimums; the exact runtime handshake remains authoritative for every installed pair.

SimpleBroker 8.1.0 and simplebroker-pg 4.1.0 are the first coordinated backend API v9 set. It adds the atomic write-time keep_newest pending window. PostgreSQL locks the high-water metadata row and then takes a transaction- scoped SHARE ROW EXCLUSIVE lock on messages before insert and trim. A large first trim therefore stalls row mutations on unrelated queues; measured 100k/220k displaced rows took about 425/1000 ms on PostgreSQL 18. Cost is linear in displaced rows and has no bounded-time guarantee.

Persistent Queues for one process-session target share one bounded connection pool. Each operation borrows a checkout and returns it when the operation finishes. A transaction or suspended iterator retains its checkout until it commits, rolls back, or closes; an idle thread-local core consumes no pool slot. Normal Queue operations and BrokerSession.connection() manage this automatically. Applications do not check physical connections in or out and do not need cleanup_connections() or BrokerSession.recycle_thread() to free a completed PostgreSQL operation's pool slot. The default pool maximum is 3 and checkout timeout is 30 seconds. The activity listener uses one separate connection, so one active process-session target uses at most four PostgreSQL connections by default. Size a deployment from its database-wide connection budget and maximum broker process count, with capacity reserved for other clients. Checkout exhaustion is reported to the caller. Separate checkouts allow unrelated threads to make progress independently, but PostgreSQL row and advisory locks can still serialize conflicting transactions.

With the default bound, a fourth simultaneous command operation waits until a slot returns and raises SimpleBroker OperationalError after the checkout timeout if none does. Close or exhaust iterators promptly: a suspended iterator intentionally keeps its transaction and checkout until settlement.

Use Queue.sidecar() or the broker connection's sidecar() context for caller-owned tables. Sidecars from a persistent Queue or BrokerSession share that process session's bounded pool. Keep transaction=True blocks short and always exit them: an open sidecar transaction retains one checkout until commit or rollback. Enough retained transactions can exhaust the pool, making later operations on that session wait and, if no slot returns, fail at the checkout timeout. Non-transactional sidecar statements return their checkouts automatically. An ephemeral Queue owns a separate runner for its sidecar session and is outside another session's three-checkout ceiling.

Timestamp resynchronization uses a guarded compare-and-advance update. If a concurrent allocator publishes a higher durable last_ts after repair begins, the repair preserves that winner and refreshes its local cache from the surviving value; it never moves PostgreSQL high-water backward.

Core Compatibility

This first-party extension declares a minimum supported SimpleBroker core version and its backend API version independently. SimpleBroker checks the exact API handshake when it resolves the plugin. An incompatible pair fails at backend resolution with upgrade-or-pin guidance instead of running against an unknown interface. Core and extension package version numbers do not match. The backend API version is separate from the PostgreSQL storage schema version and is not stored in the database. A breaking private-seam change requires a backend API version bump.

The package dependency is an install-time minimum; the runtime handshake is the authoritative interface check. Install the extension through the core release's pg extra. See the backend authoring guide for the handshake boundary.

Installation

# Fresh install through SimpleBroker's convenience extra
pipx install "simplebroker[pg]"

# Add to an existing pipx-installed simplebroker (recommended)
pipx inject simplebroker simplebroker-pg

# Or install through the convenience extra in a project
uv add "simplebroker[pg]"

# Or install the extension directly with uv
uv add simplebroker-pg

# Or install the extension directly with pip
pip install simplebroker-pg

simplebroker[pg] still installs this package as a separate distribution. Postgres support is not built into the default simplebroker install.

Python Usage

from simplebroker import Queue
from simplebroker_pg import PostgresRunner

runner = PostgresRunner(
    "postgresql://postgres@127.0.0.1:54329/simplebroker_test",
    schema="simplebroker_app",
)

queue = Queue("jobs", runner=runner, persistent=True)
try:
    queue.write("hello")
    print(queue.read())
finally:
    queue.close()
    runner.close()

Connection Pressure Inspection

Callers that have narrowed a Queue to PostgreSQL can inspect server-wide connection pressure through the public package-root helper:

from simplebroker_pg import get_connection_stats

if queue.backend_name == "postgres":
    stats = get_connection_stats(queue)

The returned dictionary has exactly numbackends, max_connections, superuser_reserved_connections, and reserved_connections integer fields. reserved_connections is zero on PostgreSQL versions where that setting does not exist.

numbackends is sum(pg_stat_database.numbackends) across the server. Stock catalog permissions let an ordinary role observe other established roles and databases without pg_monitor, pg_read_all_stats, a grant, or an installed function. It is intentionally conservative: autovacuum and other database-attached workers can be included even when they do not consume a max_connections client slot. The snapshot is not a reservation, so callers must retain a safety margin and tolerate concurrent change.

The helper executes one read-only statement through the Queue's normal operation checkout, core lock, and retry path. It does not use sidecar or create database objects. A target-resolved persistent Queue borrows from its process-session pool; an ephemeral Queue may open one connection for the operation. Malformed result data raises ValueError; database and permission failures retain SimpleBroker's database exception types.

Connection Owners and Usage

Bind an opaque accounting label in each dedicated process before creating any PostgreSQL resources. Multiple processes may share a label.

from simplebroker_pg import bind_connection_owner, get_connection_usage

bind_connection_owner("worker-group-A")  # Before pools, listeners, or inspection.
# Create a PostgreSQL Queue using the target examples above.
usage = get_connection_usage(queue)
print(usage["total"], usage["by_owner"].get("worker-group-A", 0))

ConnectionUsage is an exported TypedDict with total: int and by_owner: dict[str, int]; returned dictionaries are fresh. One materialized activity query supplies both counts through the Queue's existing probe. Total includes all database-attached backends on the server, including idle clients, the observer, unrelated clients and workers. Only visible client backends with a valid owner marker receive owner credit. Other roles' backend types may be hidden from ordinary observers. No privileges are granted; an omitted owner proves neither absence nor death.

Labels match [!-~]{1,54}: non-space printable ASCII, case-sensitive, without secrets. The extension sets application_name to sb-owner: plus the label, overriding DSN/ambient names. Same-label binding is idempotent; conflicting or late binding raises RuntimeError. Failed resource creation also seals binding. Closure does not unbind, and fork/spawn children bind explicitly. Even a usage read can start resources, so bind before reading.

Pooled connections (including later growth), shared notification listeners and direct first-party connections are covered without changing pool or session identity. Caller-built PostgresRunner is covered; caller-built driver connections and third-party runners are not. Arbitrary caller SQL can change the observed application name. Markers are cooperative, not authority. Inside a caller-owned transaction, PostgreSQL's activity-cache semantics apply; the helper does not commit, roll back, refresh the cache or open a separate monitoring connection. Multiplexing proxies may not preserve this accounting model. Usage is observation, not a permit. Budgets, reservations and launch decisions stay in the application.

See the Python guide and SB-API-14/15.

Multi-Queue Activity Waiters

Postgres supports simplebroker.create_activity_waiter_for_queues(...) with one process-local shared LISTEN/NOTIFY listener per DSN and schema. The waiter wakes when any watched queue receives activity, ignores unrelated queue notifications, and returns the same ActivityWaiter | None shape as the core API.

Wakeups are hints. After wait(timeout) returns True, callers should still drain queues through normal SimpleBroker reads or moves. Close the multi-queue waiter explicitly when the watcher lifecycle ends. Its first close() is terminal before cleanup; every later call is a no-op, including when the first call raised. The waiter owns registrations, not the runner or shared listener, and does not expose shutdown().

CLI Usage

Create .broker.toml in the project root, or use the configured BROKER_PROJECT_CONFIG_PATH / BROKER_PROJECT_CONFIG_NAME location:

version = 1
backend = "postgres"
target = "postgresql://postgres@127.0.0.1:54329/simplebroker_test"

[backend_options]
schema = "simplebroker_app"

Then use the normal CLI from any child directory with project scope enabled:

broker init
broker write jobs hello
broker read jobs

You can also run entirely from environment variables without a project config:

BROKER_BACKEND=postgres \
BROKER_BACKEND_TARGET='postgresql://postgres@127.0.0.1:54329/simplebroker_test' \
BROKER_BACKEND_SCHEMA='simplebroker_app' \
BROKER_BACKEND_PASSWORD='postgres' \
broker init

Notes:

• In env-only backend configuration, BROKER_BACKEND_TARGET overrides the host/port/user/database parts.
• BROKER_BACKEND_HOST, BROKER_BACKEND_PORT, BROKER_BACKEND_USER, BROKER_BACKEND_PASSWORD, and BROKER_BACKEND_DATABASE are only used when there is no target from project config or env.
• When project TOML provides the target or schema, the project file wins. BROKER_BACKEND_PASSWORD can still be supplied from env and is never written to project TOML.
• The Postgres database must already exist. broker init creates the managed schema/tables inside that database; it does not create the database itself.
• Missing backend/plugin errors are distinct from target/auth errors. Invalid schema names, bad passwords, malformed targets, and missing databases are reported as validation or connection failures, not as "backend not available" errors.