Operating with PgBouncer

How to use Cyclone with a PgBouncer connection pooler.

PgBouncer Modes

PgBouncer supports three pooling modes:

Mode	Description	Cyclone compatibility
Session	One server connection per client session	Full compatibility
Transaction	PgBouncer holds the server connection only during a transaction	Partial: LISTEN/NOTIFY broken
Statement	PgBouncer holds the server connection only during a statement	Not supported

Transaction Pooling Caveats

In transaction-pooling mode, PgBouncer gives the client a different server connection between transactions. This breaks these operations:

• LISTEN/NOTIFY: Cyclone holds one LISTEN on the cyclone_available channel. The queue is in the notification payload. That signal wakes workers immediately on enqueue. Cyclone also holds LISTEN on cyclone_run_done for Wait. LISTEN is a session-level operation. The LISTEN is lost when the connection returns to the pool.
• Advisory locks: cyclone.Migrate uses one advisory lock to serialize migrations. Call migrations on a direct connection or a session-mode connection. Do not use transaction pooling for that call.
• Prepared statements: the PgBouncer prepared-statement mode and the pgx query mode must be compatible. Make them compatible before you use transaction pooling.

LISTEN/NOTIFY Implications

Worker clients must maintain a persistent LISTEN connection for the full Client.Start lifetime. This is a session-level operation. It is not compatible with transaction pooling.

Two solutions:

1. Use WithNotify(false). This disables runtime notifications. Workers then poll only. Workers poll on their configured interval. Migrations still must use a separate direct connection or a session-mode connection.

2. Use a separate direct connection for the LISTEN worker. Point workers at a non-pooled Postgres connection. Point enqueue-only clients at the PgBouncer transaction pool.

Worker Clients vs Insert-Only Clients

The typical PgBouncer setup for Cyclone:

Insert-only clients (those that only call Enqueue, Submit, Cancel, GetRun) do not use LISTEN. They can use transaction-mode PgBouncer when its prepared-statement settings are compatible with pgx. Do not call Migrate through that pool.

Using WithNotify(false) with Transaction-Mode PgBouncer

// Worker client via PgBouncer in transaction mode + no notifications:
workerPool, _ := pgxpool.New(ctx, os.Getenv("PGBOUNCER_URL"))

client, _ := cyclone.NewClient(workerPool, mux,
    cyclone.WithWorkers(8),
    cyclone.WithNotify(false), // fall back to polling; safe with transaction pooler
)

With WithNotify(false), this client's workers poll on each tick. They do not wake immediately on NOTIFY. Cyclone disables only the listener. Publication continues. Wakeups still fire. Other processes that LISTEN can wake promptly. The poll interval is this client's latency floor. Workers pick up work within one poll interval of it becoming available. The delay is not sub-millisecond.

Recommended Connection Layout

Pool sizes:

For 8 workers with default txSlots=8:

• Worker pool direct: minimum MaxConns = txSlots + 6 = 8 + 6 = 14
• PgBouncer transaction pool: a default client also validates at 10 connections. For a producer-only client, set WithWorkers(1) and WithTxSlots(1). The minimum is then 7.

Pool Size Examples

// Worker pool (direct to Postgres or session-mode PgBouncer)
workerConfig, _ := pgxpool.ParseConfig(os.Getenv("DIRECT_DATABASE_URL"))
workerConfig.MaxConns = 18 // 8 txSlots + 6 reserve + buffer
workerPool, _ := pgxpool.NewWithConfig(ctx, workerConfig)

workerClient, _ := cyclone.NewClient(workerPool, mux,
    cyclone.WithWorkers(8),
    cyclone.WithNotify(false), // safe with transaction-mode pooler if needed
)

// Application pool (transaction-mode PgBouncer OK)
appConfig, _ := pgxpool.ParseConfig(os.Getenv("PGBOUNCER_URL"))
appConfig.MaxConns = 7
appPool, _ := pgxpool.NewWithConfig(ctx, appConfig)

// Enqueue-only client using the app pool (no Start() call)
enqueueClient, _ := cyclone.NewClient(appPool, nil,
    cyclone.WithWorkers(1), cyclone.WithTxSlots(1)) // never call Start

Troubleshooting Notification Delays

Symptom: workers are slow to pick up new jobs (latency greater than the poll interval).

Cause: LISTEN/NOTIFY does not work. Notifications are lost, or the workers do not receive them.

Diagnosis:

1. Check if PgBouncer is in transaction mode.
2. Check if WithNotify(false) is set.
3. Check LISTEN connections with SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Client' AND application_name LIKE 'cyclone%'.

Fix: set WithNotify(false) and use polling. Or route worker connections directly to Postgres. That path bypasses PgBouncer for the LISTEN connection.

Related

• Queue Sizing
• Engine Design