Goal: Validate PgBouncer Mode Compatibility with GitLab Rails We need to determine whether the GitLab Rails application can safely operate with PgBouncer in transaction‑mode while retaining its default prepared‑statement behavior, or if session‑mode is required. The decision hinges on the interaction between Rails’ prepared statements and PgBouncer’s pooling strategy. Key Constraints GitLab’s database.yml defaults to prepared_statements: true. Transaction‑mode PgBouncer does not preserve prepared statements unless prepared_statements: false is set. Session‑mode preserves prepared statements but ties each client to a single backend connection, increasing PostgreSQL connection usage. Unresolved Decision Which PgBouncer mode should be adopted for a typical GitLab deployment that prioritizes scalability without compromising statement execution correctness? Can transaction‑mode be used if the Rails prepared_statements flag is disabled, and what impact does this have on query performance and caching? Does session‑mode reliably prevent “prepared statement does not exist” errors after a GitLab upgrade, and how does it affect PostgreSQL connection counts under load? What monitoring or verification steps are required to confirm that the chosen mode remains compatible across future GitLab releases?

GitLab Rails & PgBouncer: Transaction vs Session Mode

Goal: Validate PgBouncer Mode Compatibility with GitLab Rails

We need to determine whether the GitLab Rails application can safely operate with PgBouncer in transaction‑mode while retaining its default prepared‑statement behavior, or if session‑mode is required. The decision hinges on the interaction between Rails’ prepared statements and PgBouncer’s pooling strategy.

Key Constraints

GitLab’s database.yml defaults to prepared_statements: true.
Transaction‑mode PgBouncer does not preserve prepared statements unless prepared_statements: false is set.
Session‑mode preserves prepared statements but ties each client to a single backend connection, increasing PostgreSQL connection usage.

Unresolved Decision

Which PgBouncer mode should be adopted for a typical GitLab deployment that prioritizes scalability without compromising statement execution correctness?

Can transaction‑mode be used if the Rails prepared_statements flag is disabled, and what impact does this have on query performance and caching?
Does session‑mode reliably prevent “prepared statement does not exist” errors after a GitLab upgrade, and how does it affect PostgreSQL connection counts under load?
What monitoring or verification steps are required to confirm that the chosen mode remains compatible across future GitLab releases?

Answer

Transaction‑mode PgBouncer can be used with GitLab Rails only if you disable Rails‑side prepared statements (prepared_statements: false). If you keep the default (prepared_statements: true), transaction‑mode will cause “prepared statement does not exist” errors and you must use session‑mode instead.

Confirmed facts

GitLab’s database.yml defaults to prepared_statements: true.
In transaction‑mode, PgBouncer does not retain server‑side prepared statements across transaction boundaries.
When Rails prepares a statement, it expects the same backend connection to reuse it; transaction‑mode breaks that expectation.
Setting prepared_statements: false makes Rails send queries via the simple query protocol, which works with any PgBouncer pooling mode.
Disabling prepared statements adds a small SQL parsing/planning overhead per query, but PostgreSQL still caches query plans internally, so the impact is usually modest for typical GitLab workloads.
Session‑mode PgBouncer preserves server‑side prepared statements because each client holds a dedicated Postgres backend, at the cost of higher PostgreSQL connection usage (roughly one backend per Rails connection).

Likely explanation

The error occurs because Rails caches a prepared statement on the first execution and later tries to execute it again, assuming the same backend connection. Transaction‑mode PgBouncer may assign a different backend for the second transaction, so the prepared statement no longer exists on that connection, leading to the error. Disabling the cache removes the reliance on server‑side prepared statements, eliminating the mismatch.

Steps to enable transaction‑mode

Edit /etc/gitlab/gitlab.rb (or the equivalent database.yml if you manage it directly) and set:
```
gitlab_rails['db_prepared_statements'] = false
```
Reconfigure GitLab: sudo gitlab-ctl reconfigure (or restart the Rails service if you use a manual setup).
Verify that PgBouncer is running in transaction mode (check SHOW CONFIG; in the PgBouncer admin console; pool_mode should be transaction).
Monitor GitLab logs for any prepared statement does not exist messages; their absence confirms compatibility.
Optionally, check query latency to ensure the overhead from disabling prepared statements is acceptable for your workload.

Missing diagnostic detail

To decide whether the modest overhead of disabling prepared statements is acceptable, please provide the typical number of distinct SQL statements executed per request (or per background job) in your GitLab instance. If this number is high, the overhead may become noticeable and you might prefer session‑mode despite its higher connection consumption.

Even when you disable Rails‑side prepared statements (prepared_statements: false) to make transaction‑mode PgBouncer work, PgBouncer still discards per‑connection session state (e.g., search_path, timezone, custom GUCs) after each transaction. If GitLab or any Rails initializer relies on those settings being preserved, you may see subtle behavior changes. To mitigate this, configure PgBouncer’s server_reset_query to re‑apply the needed session variables, for example:

server_reset_query = "SELECT set_config('search_path', '$user', public', false); SET timezone = 'UTC';"

This ensures transaction‑mode can be used safely without losing the session context Rails expects, while still avoiding prepared‑statement errors.

GitLab Rails ↔ PgBouncer: Can Transaction-Mode Pooling Work with Rails Prepared Statements?