Operations

# Performance & tuning

Tune autovacuum for the high-churn queue tables, size archive retention, and watch the health metrics.

## Autovacuum tuning

Queue tables churn far faster than the Postgres defaults expect.

PGMQ queue tables see heavy insert/delete churn — a message is inserted, read (an UPDATE), and archived (a DELETE) in seconds. PostgreSQL's default autovacuum is too conservative for that, so dead tuples accumulate and indexes bloat. pgbus applies aggressive per-table tuning automatically: new queues get it at creation, the install migration tunes the default queue, and `pgbus:update` detects untuned tables.

`db:schema:load` drops `ALTER TABLE` settings, so re-apply after a schema load with the generator:

```shell
rails generate pgbus:tune_autovacuum                  # generate the migration
rails generate pgbus:tune_autovacuum --database=pgbus # for a separate database
```

> **Warning:** A long-running transaction pins the MVCC horizon and stops vacuum from cleaning any dead tuple created while it's open — the most common cause of queue-table bloat. Watch `pgbus_oldest_transaction_age_seconds`.

## Archive retention

Archive tables grow unbounded without it.

Archived (successfully-processed) messages accumulate in the `a_<queue>` tables. The dispatcher compacts them hourly; size the window to your volume:

```ruby
Pgbus.configure do |config|
  config.archive_retention = 3.days # default 7.days; nil disables cleanup
end
```

> **Tip:** High-volume queues (>100 msg/s) want a shorter window (1–3 days); audit-sensitive queues may need 30+. SSE stream retention is separate — see [Real-time streams](https://pgbus.zoolutions.llc/docs/streams).

## Job bursts: raise threads and the pool together

Under a job spike, the DB connection pool is the ceiling — not the thread count.

When a queue floods, the instinct is to add worker threads. But a job holds a database connection only for the brief `read_batch` + `archive` round-trip — not for the job body — so a worker's throughput is capped by its **connection pool**, not its thread count. Adding threads past the pool size just makes them queue on connection checkout: latency climbs, throughput doesn't.

So size the two **together**. Raising `threads` alone plateaus at the pool size; raising both scales throughput roughly linearly (measured 8× from 2→16 when the pool matches). The connection pool auto-tunes from the thread count by default, so in practice you raise `threads` and let `pool_size` follow — but if you pin `pool_size`, keep it ≥ `threads`.

```ruby
Pgbus.configure do |config|
  config.worker "default", threads: 16   # more concurrency…
  config.pool_size = 20                  # …needs the connections to back it
end
```

> **Note:** This is the **static headroom** answer, and it's usually the right one: idle pool slots are lazy — they cost nothing until a burst uses them. pgbus deliberately does not autoscale the job pool, because elastic threads on a fixed connection pool can't push past the connection ceiling (measured with `rake bench:job_burst`). Watch `pgbus_worker_pool_utilization` — sustained near 1 means raise both.

## Streams pool autoscaling

Let the SSE streams pool grow into spare connections under a burst, and shrink back when it's over.

The dedicated streams pool (used for durable-broadcast publish and the dispatcher's replay reads) is normally a fixed size — `streams_pool_size` (default 5). Under a genuine burst of SSE clients that pool can saturate, and a saturated pool **serialises** replay reads (it doesn't error — the checkout just waits), so broadcasts fan out more slowly. For steady load, the right fix is simply a larger `streams_pool_size`. For **bursty** load, opt into autoscaling: a periodic maintenance check grows the pool into a fair share of live Postgres connection headroom while it's saturated and shrinks it back to `streams_pool_size` when the burst passes.

```ruby
Pgbus.configure do |config|
  config.streams_pool_autoscale          = true   # opt-in; default false
  config.streams_pool_size               = 5       # baseline + shrink floor
  config.streams_pool_max                = 12      # optional hard per-process cap
  config.streams_pool_autoscale_interval = 300     # check cadence, seconds (default 5 min)
end
```

There is **no connection-count target to tune**. Every threshold derives from live `max_connections`: each check reads `pg_stat_activity`, counts how many pgbus stream processes share the database, and grows only into its own fair share of the free connections. `streams_pool_max` is an optional hard ceiling — leave it `nil` and the dynamic fair share is the cap.

> **Note:** It adds **no extra database connections** and no extra thread: the check is a lightweight `pg_stat_activity` query that runs on an existing idle connection every `streams_pool_autoscale_interval` seconds — like pghero's periodic stats capture. In a web process serving SSE it rides the streamer's idle LISTEN connection; a background worker that only *publishes* broadcasts triggers the same throttled check from the publish path (on the job pool), so pure-publisher processes autoscale too. One grow (or shrink) step per check; a sustained burst converges over a few checks.

> **Tip:** It self-protects: if the database runs critically low on free connections, every process immediately shrinks its streams pool back to the baseline — protecting the database wins over keeping a busy pool full.

> **Warning:** Autoscaling needs to see each process's own connections, so it must connect to Postgres **directly** — a transaction-pooling PgBouncer strips the `application_name` it uses to count peers, so it falls back to assuming it's the only process (still connection-safe, just less precise). It's also a no-op on the shared-ActiveRecord connection path. When in doubt, a larger static `streams_pool_size` is always the simpler choice.

## Fan-out throughput: raise the writer threads

When broadcasts fan out to many SSE clients, scale the writer pool statically.

With `streams_writer_threads > 0`, durable broadcast socket writes move off the dispatcher into a pool of writer threads (each connection pinned to one worker so its frames stay ordered). If you fan out to a large fleet of connections, more writer threads flush them in parallel — throughput scales roughly linearly with the count.

```ruby
Pgbus.configure do |config|
  config.streams_writer_threads = 8   # more parallel socket writes
end
```

> **Note:** This is a **static** knob, and that's deliberate — pgbus does not autoscale the writer pool. A slow or congested client can't stall the fleet: the fan-out write deadline (`streams_fanout_write_deadline_ms`, default 250 ms) evicts it, and it reconnects and replays from the durable archive. So the writer pool scales fan-out throughput; it doesn't need to grow to absorb slow clients. Size `streams_writer_threads` for your peak fan-out fleet (measured with `bench:one[writer_burst_bench]`).

## The metrics that tell you when to tune

The dashboard's Queue Health panel and the Prometheus gauges surface the signals that predict trouble before it becomes an incident:

| Metric | Watch for |
| --- | --- |
| `pgbus_table_dead_tuples` | Dead tuples climbing — vacuum isn't keeping up. |
| `pgbus_table_bloat_ratio` | Dead / (dead + live) rising toward 1. |
| `pgbus_table_last_vacuum_age_seconds` | Long gaps since the last vacuum. |
| `pgbus_oldest_transaction_age_seconds` | A long transaction pinning the MVCC horizon. |
| `pgbus_worker_pool_utilization` | Busy / capacity near 1 — you need more threads. |

> **Note:** This page covers running-system tuning. The gem's benchmark harness and allocation budgets — for contributors optimizing hot paths — live in the repo's `docs/performance.md`.