Why Gitea Crashes Randomly with PostgreSQL + pgBouncer (And the One-Line Fix)

The most frustrating bugs are the ones that work perfectly in development but fail randomly in production. If you're running Gitea with PostgreSQL and pgBouncer, you've probably experienced this: everything seems fine during setup, then users start hitting 500 errors on repository views, pull requests, and commit pages with no clear pattern.

The culprit? A fundamental mismatch between how Gitea handles database queries and how pgBouncer manages connections.

The Prepared Statements Trap

Gitea relies heavily on prepared statements for database interactions. These are pre-compiled SQL queries that PostgreSQL can execute more efficiently by parsing them once and reusing the execution plan. It's a solid optimization strategy that works beautifully with direct PostgreSQL connections.

The problem emerges when pgBouncer enters the picture. In its default "session" pooling mode, pgBouncer tries to maintain prepared statement context across connection reuse, but this creates a brittle setup where prepared statements can become stale or misaligned between what Gitea expects and what pgBouncer actually delivers to PostgreSQL.

What makes this particularly insidious is the randomness. Sometimes the prepared statements work, sometimes they don't, depending on connection pool state, timing, and which specific queries trigger the issue. You'll see users reporting that refreshing the page sometimes fixes it, or that certain repositories work while others fail.

The One-Line Fix

The solution is surprisingly simple: change pgBouncer's pooling mode from "session" to "transaction". Here's how:

1# In your pgbouncer.ini file
2[databases]
3gitea = host=localhost port=5432 dbname=gitea user=gitea
4
5[pgbouncer]
6# Change this line:
7pool_mode = transaction
8# Instead of: pool_mode = session
9
10max_client_conn = 1000
11default_pool_size = 25

After making this change, restart pgBouncer:

1sudo systemctl restart pgbouncer
2# or
3sudo service pgbouncer restart

Transaction-level pooling disables prepared statement tracking entirely, which eliminates the compatibility issues with Gitea. Each transaction gets a fresh connection context, preventing the stale prepared statement problems that cause those mysterious 500 errors.

But Wait, There's More

While the pooling mode fix resolves the majority of cases, there are a few other gotchas that can cause similar symptoms:

Schema Search Path Issues: If you're using a non-"public" PostgreSQL schema (like "main"), connection pooling can break schema resolution. Gitea might successfully connect and run initial queries, but subsequent operations fail because the search path doesn't propagate correctly across pooled connections.

1-- Fix by explicitly setting the search path for your Gitea user
2ALTER ROLE gitea SET search_path TO main, public;

Database Configuration Drift: During Gitea installations or upgrades, database dialect flags can get misaligned. This is particularly common if you've switched between database types during testing.

1# In your app.ini, ensure the [database] section is clean
2[database]
3DB_TYPE = postgres
4HOST = 127.0.0.1:6432  # pgBouncer port, not direct PostgreSQL
5NAME = gitea
6USER = gitea
7PASSWD = your_password
8SSL_MODE = disable
9LOG_SQL = true  # Enable for debugging

Testing Your Fix

Here's a systematic approach to verify the solution:

1. Enable SQL logging in Gitea by adding LOG_SQL = true to your database configuration

2. Monitor both pgBouncer logs (/var/log/pgbouncer/pgbouncer.log) and PostgreSQL logs

3. Reproduce the issue by navigating to previously problematic pages (dashboards, PR lists, commit views)

4. Load test with multiple concurrent users to ensure the connection pooling works under pressure

If you're still seeing prepared statement errors in the logs after switching to transaction mode, that's a red flag that there might be additional configuration issues.

The Performance Trade-off

You might wonder: doesn't losing prepared statements hurt performance? In practice, the impact is minimal for most Gitea deployments. The overhead of re-parsing SQL queries is vastly outweighed by the benefits of stable connection pooling, especially under concurrent load.

Modern PostgreSQL versions are extremely efficient at query parsing, and the types of queries Gitea typically runs (repository metadata, user authentication, issue tracking) aren't complex enough to benefit significantly from prepared statement caching.

Why This Matters

This issue highlights a broader challenge in modern application deployment: the integration complexity between middleware components. pgBouncer and prepared statements are both excellent technologies that solve real problems, but their interaction creates emergent behavior that's difficult to predict or debug.

For teams running Gitea in production, this fix can mean the difference between a reliable development platform and one that randomly frustrates users with cryptic errors. More importantly, understanding the root cause helps you make informed decisions about your PostgreSQL architecture and avoid similar pitfalls with other applications.

If you're experiencing random 500 errors with Gitea, PostgreSQL, and pgBouncer, try the transaction pooling mode change first. It's a low-risk modification that addresses the most common cause of these issues, and you'll know within minutes whether it resolves your problems.