PostgreSQL connection pooling with PgBouncer

<p>My FastAPI application has 20 workers and each worker has a SQLAlchemy connection pool of 5. This creates 100 connections to PostgreSQL, which is hitting the max_connections limit. I need connection pooling at the database level.</p>

<p>Run PgBouncer as a sidecar for connection multiplexing:</p> <div class="highlight"><pre><span></span><code><span class="c1"># pgbouncer.ini</span> <span class="k">[databases]</span> <span class="na">mydb</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">host=postgres port=5432 dbname=mydb</span> <span class="k">[pgbouncer]</span> <span class="na">listen_addr</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">0.0.0.0</span> <span class="na">listen_port</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">6432</span> <span class="na">pool_mode</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">transaction</span><span class="w"> </span><span class="c1"># Best for async apps (not session mode)</span> <span class="na">max_client_conn</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">1000</span><span class="w"> </span><span class="c1"># Connections FROM app to PgBouncer</span> <span class="na">default_pool_size</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">20</span><span class="w"> </span><span class="c1"># Connections FROM PgBouncer to Postgres</span> <span class="na">reserve_pool_size</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">5</span> <span class="na">server_idle_timeout</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">600</span> <span class="na">auth_type</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">md5</span> <span class="na">auth_file</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s">/etc/pgbouncer/userlist.txt</span> </code></pre></div> <div class="highlight"><pre><span></span><code><span class="c1"># docker-compose.yml</span> <span class="nt">services</span><span class="p">:</span> <span class="w"> </span><span class="nt">pgbouncer</span><span class="p">:</span> <span class="w"> </span><span class="nt">image</span><span class="p">:</span><span class="w"> </span><span class="l l-Scalar l-Scalar-Plain">pgbouncer/pgbouncer</span> <span class="w"> </span><span class="nt">volumes</span><span class="p">:</span> <span class="w"> </span><span class="p p-Indicator">-</span><span class="w"> </span><span class="l l-Scalar l-Scalar-Plain">./pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini</span> <span class="w"> </span><span class="nt">environment</span><span class="p">:</span> <span class="w"> </span><span class="nt">DB_HOST</span><span class="p">:</span><span class="w"> </span><span class="l l-Scalar l-Scalar-Plain">postgres</span> <span class="w"> </span><span class="nt">DB_USER</span><span class="p">:</span><span class="w"> </span><span class="l l-Scalar l-Scalar-Plain">myapp</span> <span class="w"> </span><span class="nt">DB_PASSWORD</span><span class="p">:</span><span class="w"> </span><span class="l l-Scalar l-Scalar-Plain">myapp</span> <span class="w"> </span><span class="nt">api</span><span class="p">:</span> <span class="w"> </span><span class="nt">environment</span><span class="p">:</span> <span class="w"> </span><span class="c1"># Point to PgBouncer, not postgres directly</span> <span class="w"> </span><span class="nt">DATABASE_URL</span><span class="p">:</span><span class="w"> </span><span class="l l-Scalar l-Scalar-Plain">postgresql+asyncpg://myapp:myapp@pgbouncer:6432/mydb</span> </code></pre></div> <p>App-level: reduce SQLAlchemy pool size (PgBouncer handles multiplexing):</p> <div class="highlight"><pre><span></span><code><span class="n">engine</span> <span class="o">=</span> <span class="n">create_async_engine</span><span class="p">(</span> <span class="n">settings</span><span class="o">.</span><span class="n">database_url</span><span class="p">,</span> <span class="n">pool_size</span><span class="o">=</span><span class="mi">2</span><span class="p">,</span> <span class="c1"># Small — PgBouncer multiplexes</span> <span class="n">max_overflow</span><span class="o">=</span><span class="mi">3</span><span class="p">,</span> <span class="p">)</span> </code></pre></div> <p>Key points: - <code>transaction</code> mode: connection returned to pool after each transaction — best for async - <code>session</code> mode: connection held for entire session — incompatible with prepared statements - Prepared statements are NOT compatible with PgBouncer transaction mode — disable them - asyncpg: set <code>server_settings={'options': '-c statement_timeout=30000'}</code> not prepared statements</p>