Connection poolers

A connection pooler such as PgBouncer, Supavisor or Neon’s pooler sits between clients and Postgres and shares a few server sessions among many clients. For Alcuin, connect directly or through a pooler in session mode where you can. Keep transaction mode for your application.

Session mode and transaction mode

Mode	A client keeps one server session	Works like a direct connection
Session	For as long as it stays connected	Yes
Transaction	Only until the current transaction ends	No

In transaction mode, each statement outside a transaction can run in a different server session. Inside BEGIN … COMMIT, a query tab keeps one session until the transaction ends.

What breaks in transaction mode

Anything that lives in the server session, rather than in one transaction, can be lost between statements:

• SET: a setting made in one statement may be gone in the next. This includes the settings Alcuin makes per session: read-only on a locked connection, and the Stop queries after limit. Alcuin still refuses the writes it recognizes on a read-only connection before sending them, but the server’s own guard may not hold. Use SET LOCAL inside a transaction instead.
• Prepared statements made with PREPARE: the next statement may reach a session that never saw it, and fail with an error such as prepared statement "s1" does not exist.
• LISTEN and NOTIFY: a listener’s session is not yours for long.
• Session advisory locks: pg_advisory_lock holds the lock in a session you may never reach again. Use pg_advisory_xact_lock inside a transaction.
• Temporary tables and WITH HOLD cursors that outlive a transaction.

Run migrations and structure changes on a direct connection, too: migration tools often rely on the session.

What Alcuin changes behind a pooler

Alcuin treats a connection as pooled when:

• the port is 6432, PgBouncer’s usual port,
• the port is 6543 on a Supabase host (….pooler.supabase.com or db.<project-ref>.supabase.co), Supabase’s transaction pooler,
• the host is a Neon host with -pooler after the endpoint ID,
• the server session changes between two statements when Alcuin connects, or
• a result’s cursor fails the way a transaction pooler makes it fail.

Behind a pooler, Alcuin cannot keep a result open on the server between pages. It reads each result in one go, up to 100,000 rows or 32 MB, and Read the rest is unavailable. Its tip says why: Read the rest is not available for this result: behind a connection pooler, rows are read in one go, up to the same cap. Add a LIMIT, or connect directly, to page through more. Results and memory has the caps.

The list of other databases on the server carries Listed by the server; your pooler may only route some of these.

Which port to use

Provider	Direct	Session mode	Transaction mode
Supabase	db.<project-ref>.supabase.co, port 5432	Shared pooler (….pooler.supabase.com), port 5432	Shared pooler, port 6543
Neon	The endpoint host, port 5432	Not offered	The -pooler endpoint host, port 5432
PgBouncer you run	The Postgres server, usually port 5432	Depends on its pool_mode	Depends on its pool_mode; usually port 6432

For Alcuin, use the Direct column where you can, and Supabase’s session mode on a network without IPv6. On port 5432 Supabase’s shared pooler runs in session mode, so Alcuin pages through results there as it does on a direct connection. Cloud providers has the full recipes.

Connections that drop

Poolers often close idle client connections. When that happens, Alcuin shows Connection lost. Reconnecting… and reconnects; see ALC-1002: Connection lost. If it happens often, look at the pooler’s idle timeouts, such as PgBouncer’s client_idle_timeout.

A pooler that runs out of server connections makes connects wait or fail. The error comes from the pooler, not from Postgres, so check the pooler’s own limits as well as max_connections.