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:

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

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:

config/initializers/pgbus.rb

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

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.

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.

config/initializers/pgbus.rb

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

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.

config/initializers/pgbus.rb

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.

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.

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.

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.

config/initializers/pgbus.rb

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

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.

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.