Files
skills/public/database-database-connection-pooling.html
zeekayandHanzo Dev 9e10c1c10e Each llms index points at the other two
hanzoskills.com/llms.txt now opens with the install line, the API
reference index and the company index, so an agent that lands on any
one of the three can find the rest.

robots.txt claimed 537 documents while llms.txt counts 920. The number
is gone from robots.txt; llms.txt derives it.

public/ is regenerated from scripts/site.py, which it had fallen
behind: the copy-prompt menu and agent bar were in the generator and
not in the committed pages. tests/site.mjs passes against the result.

Co-authored-by: Hanzo Dev <[email protected]>
2026-09-11 00:27:59 -07:00

504 lines
28 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<!doctype html>
<html lang="en"><head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>database-database-connection-pooling</title>
<meta name="color-scheme" content="dark">
<style>
/* Committed to one visual world: a dark instrument panel. No light variant —
the dot field and the hairline rules only read on black, and a naive
inversion would wash both out. Every colour is painted explicitly so the page
never borrows a host background. */
@font-face{font-family:'Zen';src:url('/fonts/Zen-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
@font-face{font-family:'Zen';src:url('/fonts/Zen-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
@font-face{font-family:'Zen Pixel Square';src:url('/fonts/ZenPixel-Square.woff2') format('woff2');font-style:normal;font-display:swap}
:root{
--ink:#f4f4f2; --soft:#9c9c98; --faint:#6a6a66; --ground:#000; --panel:#0a0a0a;
--line:#232323; --line-hi:#3a3a3a; --accent:#f4f4f2;
--font-zen-sans:'Zen',-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif;
--font-zen-mono:'Zen Mono',ui-monospace,"SF Mono",SFMono-Regular,Menlo,Consolas,monospace;
--font-zen-pixel-square:'Zen Pixel Square',monospace;
--mono:var(--font-zen-mono);
--sans:var(--font-zen-sans);
/* Controls are pills and surfaces are soft, as on hanzo.ai. */
--r:999px; --r-soft:14px;
}
*{box-sizing:border-box}
html{-webkit-text-size-adjust:100%}
body{margin:0;background:var(--ground);color:var(--ink);font-family:var(--sans);
font-size:16px;line-height:1.65;-webkit-font-smoothing:antialiased}
.wrap{max-width:60rem;margin:0 auto;padding:0 1.75rem}
/* The dot field. A halftone made of one repeating radial-gradient, faded out
along the diagonal by a mask so it reads as a wave rather than wallpaper. */
/* No overflow clip here: the dot field is inset to the box already, and a clip
would cut off the copy-prompt menu. */
.hero{position:relative;border-bottom:1px solid var(--line)}
.hero::before{content:"";position:absolute;inset:0;
background-image:radial-gradient(var(--line-hi) 1px, transparent 1px);
background-size:7px 7px;
-webkit-mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
pointer-events:none}
.hero .wrap{position:relative;padding-top:5.5rem;padding-bottom:4rem}
nav{border-bottom:1px solid var(--line)}
nav .in{max-width:60rem;margin:0 auto;padding:1.1rem 1.75rem;display:flex;
justify-content:space-between;align-items:center;gap:1rem}
.brand{font-family:var(--mono);font-size:.85rem;letter-spacing:.14em;
text-transform:uppercase;color:var(--ink);text-decoration:none;
display:inline-flex;align-items:center;gap:.6rem}
.brand i{font-style:normal;color:var(--faint)}
.mark{width:1.15em;height:1.15em;display:block;flex:0 0 auto}
nav a.up{font-family:var(--mono);font-size:.72rem;letter-spacing:.14em;
text-transform:uppercase;color:var(--soft);text-decoration:none}
nav a.up:hover{color:var(--ink)}
h1{font-family:var(--mono);font-size:clamp(2rem,5.4vw,3.4rem);line-height:1.12;
letter-spacing:-.02em;margin:0 0 1.1rem;font-weight:500;text-wrap:balance}
h1 em{font-style:normal;color:var(--faint)}
.lede{color:var(--soft);font-size:1.05rem;max-width:52ch;margin:0 0 2.25rem}
.btns{display:flex;flex-wrap:wrap;gap:.7rem;margin:0 0 .5rem;align-items:center}
.btn{display:inline-flex;align-items:center;gap:.5rem;font-family:var(--mono);
font-size:.72rem;letter-spacing:.14em;text-transform:uppercase;
padding:.8rem 1.4rem;border:1px solid var(--line-hi);border-radius:var(--r);
color:var(--ink);text-decoration:none;background:transparent;cursor:pointer;
line-height:1.2;font-weight:400}
.btn:hover{background:#141414;text-decoration:none;border-color:var(--soft)}
.btn.solid{background:var(--ink);color:#000;border-color:var(--ink)}
.btn.solid:hover{background:#d8d8d4}
.btn .chev{width:.7em;height:.7em;flex:0 0 auto;transition:transform .15s ease}
.btn[aria-expanded="true"] .chev{transform:rotate(180deg)}
/* Copy-prompt selector. Each row copies the prompt for its own target. */
.copy{position:relative;display:inline-flex}
.menu{position:absolute;top:calc(100% + .5rem);left:0;z-index:20;min-width:21rem;
background:var(--panel);border:1px solid var(--line-hi);border-radius:var(--r-soft);
padding:.4rem;display:flex;flex-direction:column;
box-shadow:0 12px 32px rgba(0,0,0,.75)}
.menu[hidden]{display:none}
.mi{display:flex;align-items:center;gap:.7rem;width:100%;text-align:left;
padding:.62rem .8rem;border:0;border-radius:calc(var(--r-soft) - .35rem);
background:transparent;color:var(--ink);font-family:var(--mono);font-size:.76rem;
letter-spacing:.04em;text-decoration:none;cursor:pointer;line-height:1.3;
white-space:nowrap}
.mi:hover,.mi:focus-visible{background:#181818;outline:none}
.mi .mark{width:1.05rem;height:1.05rem;color:var(--soft)}
.mi:hover .mark{color:var(--ink)}
.mi u{margin-left:auto;color:var(--faint);text-decoration:none;font-size:.68rem;
letter-spacing:.1em;text-transform:uppercase}
/* Works-with bar: one scrolling row, name and mark per agent. */
.strip{border-bottom:1px solid var(--line);background:var(--panel)}
.strip .in{max-width:60rem;margin:0 auto;padding:.9rem 1.75rem;display:flex;
align-items:center;gap:1.25rem;font-family:var(--mono);font-size:.72rem;
letter-spacing:.1em;text-transform:uppercase;color:var(--faint)}
.strip b{color:var(--soft);font-weight:400;flex:0 0 auto;
border-right:1px solid var(--line);padding-right:1.25rem;align-self:stretch;
display:flex;align-items:center}
.bar{display:flex;align-items:center;gap:.5rem;flex:1 1 auto;min-width:0;
overflow-x:auto;scrollbar-width:none;scroll-snap-type:x proximity;
-webkit-overflow-scrolling:touch;
-webkit-mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent);
mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent)}
.bar::-webkit-scrollbar{display:none}
.agent{display:inline-flex;align-items:center;gap:.45rem;flex:0 0 auto;
padding:.4rem .85rem;border:1px solid var(--line);border-radius:var(--r);
color:var(--soft);white-space:nowrap;scroll-snap-align:start;
text-decoration:none;background:var(--ground)}
.agent:hover{color:var(--ink);border-color:var(--line-hi);text-decoration:none}
.agent .mark{width:.95rem;height:.95rem}
h2{font-family:var(--mono);font-size:1.1rem;letter-spacing:-.01em;margin:2.75rem 0 .75rem;font-weight:500}
h3{font-size:1rem;margin:2rem 0 .5rem;font-weight:600}
h4,h5,h6{font-size:.94rem;margin:1.5rem 0 .5rem;font-weight:600}
p{margin:0 0 1rem;max-width:70ch}
ul,ol{margin:0 0 1rem;padding-left:1.35rem;max-width:70ch}
li{margin:.22rem 0}
a{color:var(--ink);text-decoration:underline;text-underline-offset:3px;
text-decoration-color:var(--line-hi)}
a:hover{text-decoration-color:var(--ink)}
a:focus-visible{outline:1px solid var(--ink);outline-offset:3px}
code{font-family:var(--mono);font-size:.85em;background:var(--panel);
border:1px solid var(--line);border-radius:5px;padding:.1em .36em}
pre{background:var(--panel);border:1px solid var(--line);padding:1rem 1.1rem;
border-radius:var(--r-soft);overflow-x:auto;margin:0 0 1.25rem}
pre code{background:none;border:0;padding:0;font-size:.82rem;line-height:1.7}
blockquote{margin:0 0 1rem;padding-left:1rem;border-left:1px solid var(--line-hi);color:var(--soft)}
hr{border:0;border-top:1px solid var(--line);margin:2.5rem 0}
table{border-collapse:collapse;width:100%;margin:0 0 1.25rem;font-size:.92rem;
display:block;overflow-x:auto}
th,td{border:1px solid var(--line);padding:.5rem .7rem;text-align:left}
.curl{display:block;background:var(--panel);border:1px solid var(--line);
border-radius:var(--r-soft);padding:.95rem 1.1rem;font-family:var(--mono);
font-size:.8rem;color:var(--soft);overflow-x:auto;margin:0 0 2.5rem;
white-space:pre;line-height:1.9}
.curl b{color:var(--ink);font-weight:400}
.curl i{font-style:normal;color:var(--faint)}
/* index */
#q{width:100%;padding:.85rem 1.2rem;font-family:var(--mono);font-size:.85rem;color:var(--ink);
background:var(--panel);border:1px solid var(--line);border-radius:var(--r);margin:0 0 .6rem}
#q::placeholder{color:var(--faint);text-transform:uppercase;letter-spacing:.1em;font-size:.72rem}
#q:focus{outline:none;border-color:var(--soft)}
#count{color:var(--faint);font-family:var(--mono);font-size:.72rem;letter-spacing:.1em;
text-transform:uppercase;margin:0 0 2rem}
.cat{margin:2.5rem 0 .5rem;font-family:var(--mono);font-size:.7rem;letter-spacing:.16em;
text-transform:uppercase;color:var(--faint);border-bottom:1px solid var(--line);
padding-bottom:.45rem}
.row{display:block;padding:.6rem .9rem;margin:0 -.9rem;border-bottom:1px solid var(--line);
border-radius:var(--r-soft);text-decoration:none}
.row:hover{background:#0a0a0a}
.nm{font-family:var(--mono);font-size:.84rem;color:var(--ink);display:block}
.ds{color:var(--soft);font-size:.9rem;display:block;margin-top:.12rem}
footer{margin-top:4rem;padding:2rem 0 4rem;border-top:1px solid var(--line);
color:var(--faint);font-size:.85rem}
footer a{color:var(--soft)}
.fmap{display:grid;gap:1.75rem;grid-template-columns:repeat(auto-fit,minmax(11rem,1fr));
margin:0 0 2rem;font-size:.85rem}
.fmap b{display:block;font-family:var(--mono);font-size:.68rem;letter-spacing:.16em;
text-transform:uppercase;color:var(--faint);font-weight:400;margin:0 0 .6rem;
border-bottom:1px solid var(--line);padding-bottom:.4rem}
.fmap a{display:inline-block;color:var(--ink);text-decoration:none;margin:.3rem .35rem 0 0}
.fmap a:hover{text-decoration:underline}
.fmap div{color:var(--faint);line-height:1.5}
.fend{border-top:1px solid var(--line);padding-top:1.25rem;font-size:.82rem}
@media (max-width:640px){
.hero .wrap{padding-top:3.25rem;padding-bottom:2.75rem}
.btns{gap:.5rem}
.btn{padding:.75rem 1.15rem;font-size:.68rem}
.copy,.copy .btn{width:100%;justify-content:center}
.menu{left:0;right:0;min-width:0}
.strip .in{flex-direction:column;align-items:flex-start;gap:.6rem;padding:.9rem 1.75rem}
.strip b{border-right:0;padding-right:0}
.bar{width:100%}
}
</style>
<link rel="alternate" type="text/markdown" href="/database-database-connection-pooling.md">
<link rel="icon" href="/favicon.svg">
<link rel="canonical" href="https://hanzoskills.com/database-database-connection-pooling">
</head>
<!-- Reading this with a machine? Every page here is also plain markdown.
This one: https://hanzoskills.com/database-database-connection-pooling.md
Index: https://hanzoskills.com/llms.txt
Corpus: https://hanzoskills.com/all.md
Append .md to any path for the markdown twin. -->
<body>
<nav><div class="in">
<a class="brand" href="/"><svg class="mark" viewBox="0 0 67 67" fill="currentColor" aria-hidden="true"><path d="M22.21 67V44.6369H0V67H22.21Z"/><path d="M66.7038 22.3184H22.2534L0.0878906 44.6367H44.4634L66.7038 22.3184Z"/><path d="M22.21 0H0V22.3184H22.21V0Z"/><path d="M66.7198 0H44.5098V22.3184H66.7198V0Z"/><path d="M66.7198 67V44.6369H44.5098V67H66.7198Z"/></svg> HANZO<i>SKILLS</i></a>
<a class="up" href="/">All skills</a>
</div></nav>
<div class="wrap">
<h1>database-database-connection-pooling</h1><p class="lede">Configuring database connections for applications</p><h1>Database Connection Pooling</h1>
<p><strong>Scope</strong>: Connection pool configuration, sizing, ORM-specific patterns <strong>Lines</strong>: ~220 <strong>Last Updated</strong>: 2025-10-18</p>
<h2>When to Use This Skill</h2>
<p>Activate this skill when:</p>
<ul><li>Configuring database connections for applications</li><li>Debugging connection pool exhaustion</li><li>Optimizing connection pool settings</li><li>Choosing pool sizes for different workloads</li><li>Troubleshooting connection timeouts or leaks</li><li>Configuring ORMs (SQLAlchemy, Prisma, GORM, Diesel)</li></ul>
<h2>Core Concepts</h2>
<h3>What is Connection Pooling?</h3>
<p><strong>Problem</strong>: Creating new database connections is expensive (TCP handshake, authentication, initialization).</p>
<p><strong>Solution</strong>: Reuse connections via a <strong>pool</strong>.</p>
<p><strong>How it works</strong>:</p>
<ol><li>Application requests connection from pool</li><li>Pool provides idle connection (or creates new one if available)</li><li>Application uses connection for query</li><li>Application returns connection to pool (not closed)</li><li>Connection waits idle for next request</li></ol>
<p><strong>Benefits</strong>:</p>
<ul><li>Faster queries (no connection overhead)</li><li>Limits concurrent connections to database</li><li>Handles connection lifecycle automatically</li></ul>
<hr>
<h2>Key Parameters</h2>
<h3>Pool Size</h3>
<p><strong><code>pool_size</code></strong> (or <code>max_connections</code>): Maximum number of connections in pool.</p>
<pre><code class="lang-python"># SQLAlchemy example
engine = create_engine(
&quot;postgresql://...&quot;,
pool_size=10 # Max 10 connections
)</code></pre>
<p><strong>How to choose</strong>:</p>
<pre><code>pool_size = (num_threads or num_workers) × N
Where N = 1-3 connections per thread/worker</code></pre>
<p><strong>Examples</strong>:</p>
<ul><li>Web server with 10 workers → pool_size = 10-30</li><li>Single-threaded app → pool_size = 1-5</li><li>High-concurrency async app → pool_size = 20-50</li></ul>
<p><strong>Too small</strong>: Connection exhaustion, queries wait <strong>Too large</strong>: Database overload, memory waste</p>
<h3>Max Overflow</h3>
<p><strong><code>max_overflow</code></strong>: Additional connections beyond pool_size (temporary).</p>
<pre><code class="lang-python">engine = create_engine(
&quot;postgresql://...&quot;,
pool_size=10,
max_overflow=5 # Up to 15 total connections
)</code></pre>
<p><strong>Total connections</strong> = pool_size + max_overflow</p>
<p><strong>Use case</strong>: Handle traffic spikes without exhausting pool.</p>
<h3>Pool Timeout</h3>
<p><strong><code>pool_timeout</code></strong>: Seconds to wait for available connection.</p>
<pre><code class="lang-python">engine = create_engine(
&quot;postgresql://...&quot;,
pool_timeout=30 # Wait up to 30 seconds
)</code></pre>
<p><strong>What happens on timeout</strong>:</p>
<ul><li>Raise exception (e.g., <code>TimeoutError</code>)</li><li>Application can retry or return error to user</li></ul>
<p><strong>Recommended</strong>: 10-30 seconds</p>
<h3>Idle Timeout (Pool Recycle)</h3>
<p><strong><code>pool_recycle</code></strong>: Seconds before recycling idle connections.</p>
<pre><code class="lang-python">engine = create_engine(
&quot;postgresql://...&quot;,
pool_recycle=3600 # Recycle after 1 hour
)</code></pre>
<p><strong>Why recycle</strong>:</p>
<ul><li>Database may close idle connections (e.g., MySQL <code>wait_timeout</code>)</li><li>Prevents &quot;connection has been closed&quot; errors</li><li>Clears stale connections</li></ul>
<p><strong>Recommended</strong>: Slightly less than database&#x27;s <code>wait_timeout</code> (typically 1-8 hours).</p>
<h3>Pre-Ping</h3>
<p><strong><code>pool_pre_ping</code></strong>: Test connection before use (detect closed connections).</p>
<pre><code class="lang-python">engine = create_engine(
&quot;postgresql://...&quot;,
pool_pre_ping=True # Test connection before use
)</code></pre>
<p><strong>How it works</strong>: Issues lightweight query (<code>SELECT 1</code>) before returning connection.</p>
<p><strong>Pros</strong>: Prevents &quot;connection closed&quot; errors <strong>Cons</strong>: Slight overhead per query</p>
<p><strong>Recommended</strong>: Enable for production reliability.</p>
<hr>
<h2>Calculating Pool Size</h2>
<h3>Formula</h3>
<pre><code>Optimal pool size = (Tn × (Cm - 1)) + 1
Where:
Tn = Number of threads/workers
Cm = Average number of concurrent queries per request</code></pre>
<p><strong>Example 1</strong>: Web app with 20 workers, 1 query per request</p>
<pre><code>pool_size = (20 × (1 - 1)) + 1 = 1
pool_size = 20 (add buffer) → Use 20-30</code></pre>
<p><strong>Example 2</strong>: API with 10 workers, 3 queries per request (joining tables)</p>
<pre><code>pool_size = (10 × (3 - 1)) + 1 = 21
pool_size = 21 → Use 25-30</code></pre>
<h3>HikariCP Formula (Java)</h3>
<pre><code>connections = ((core_count × 2) + effective_spindle_count)
For SSDs (no spindles):
connections = (core_count × 2)</code></pre>
<p><strong>Example</strong>: 4-core server with SSD</p>
<pre><code>connections = 4 × 2 = 8</code></pre>
<h3>Conservative Approach</h3>
<p><strong>Start small, increase if needed</strong>:</p>
<ol><li>Start with <code>pool_size = num_workers</code></li><li>Monitor for pool exhaustion warnings</li><li>Increase by 25% if exhaustion occurs</li><li>Repeat until stable</li></ol>
<p><strong>Database limits</strong>: PostgreSQL default <code>max_connections = 100</code>, MySQL default <code>151</code>.</p>
<p><strong>Leave headroom</strong>: Don&#x27;t use all available connections (reserve for admin, monitoring).</p>
<hr>
<h2>ORM-Specific Configuration</h2>
<h3>SQLAlchemy (Python)</h3>
<pre><code class="lang-python">from sqlalchemy import create_engine
engine = create_engine(
&quot;postgresql://user:pass@localhost/db&quot;,
pool_size=10, # Base pool size
max_overflow=5, # Burst capacity
pool_timeout=30, # Wait time for connection
pool_recycle=3600, # Recycle after 1 hour
pool_pre_ping=True, # Test before use
echo_pool=True # Log pool events (debug only)
)</code></pre>
<p><strong>Pool types</strong>:</p>
<ul><li><code>QueuePool</code> (default): Thread-safe, multiple threads</li><li><code>NullPool</code>: No pooling (creates new connection each time)</li><li><code>StaticPool</code>: Single connection (SQLite)</li></ul>
<h3>Prisma (Node.js/TypeScript)</h3>
<pre><code class="lang-typescript">// In schema.prisma
datasource db {
provider = &quot;postgresql&quot;
url = env(&quot;DATABASE_URL&quot;)
}
// Connection string with pool params
DATABASE_URL=&quot;postgresql://user:pass@localhost/db?schema=public&amp;connection_limit=10&quot;
// Or in PrismaClient
const prisma = new PrismaClient({
datasources: {
db: {
url: process.env.DATABASE_URL + &quot;?connection_limit=10&amp;pool_timeout=20&quot;
}
}
})</code></pre>
<p><strong>Parameters</strong>:</p>
<ul><li><code>connection_limit</code>: Max connections (default: <code>num_cpus × 2 + 1</code>)</li><li><code>pool_timeout</code>: Wait time in seconds (default: 10)</li></ul>
<h3>GORM (Go)</h3>
<pre><code class="lang-go">import (
&quot;gorm.io/driver/postgres&quot;
&quot;gorm.io/gorm&quot;
)
dsn := &quot;host=localhost user=postgres password=pass dbname=db&quot;
db, err := gorm.Open(postgres.Open(dsn), &amp;gorm.Config{})
sqlDB, err := db.DB()
// Configure pool
sqlDB.SetMaxOpenConns(25) // Max connections
sqlDB.SetMaxIdleConns(5) // Idle connections
sqlDB.SetConnMaxLifetime(time.Hour) // Max lifetime
sqlDB.SetConnMaxIdleTime(10 * time.Minute) // Max idle time</code></pre>
<p><strong>Parameters</strong>:</p>
<ul><li><code>SetMaxOpenConns</code>: Total max connections</li><li><code>SetMaxIdleConns</code>: Idle connections in pool</li><li><code>SetConnMaxLifetime</code>: Max connection age</li><li><code>SetConnMaxIdleTime</code>: Max idle duration before close</li></ul>
<h3>Diesel (Rust)</h3>
<pre><code class="lang-rust">use diesel::r2d2::{self, ConnectionManager};
use diesel::pg::PgConnection;
let manager = ConnectionManager::&lt;PgConnection&gt;::new(database_url);
let pool = r2d2::Pool::builder()
.max_size(10) // Max connections
.min_idle(Some(2)) // Min idle connections
.connection_timeout(Duration::from_secs(30))
.idle_timeout(Some(Duration::from_secs(600)))
.build(manager)?;</code></pre>
<h3>Django (Python)</h3>
<pre><code class="lang-python"># settings.py
DATABASES = {
&#x27;default&#x27;: {
&#x27;ENGINE&#x27;: &#x27;django.db.backends.postgresql&#x27;,
&#x27;NAME&#x27;: &#x27;db&#x27;,
&#x27;USER&#x27;: &#x27;user&#x27;,
&#x27;PASSWORD&#x27;: &#x27;pass&#x27;,
&#x27;HOST&#x27;: &#x27;localhost&#x27;,
&#x27;PORT&#x27;: &#x27;5432&#x27;,
&#x27;CONN_MAX_AGE&#x27;: 600, # Connection lifetime (seconds), 0 = no pooling
&#x27;OPTIONS&#x27;: {
&#x27;connect_timeout&#x27;: 10,
}
}
}</code></pre>
<p><strong>Note</strong>: Django doesn&#x27;t have true pooling by default. Use <code>django-db-pool</code> or <code>pgbouncer</code> for pooling.</p>
<hr>
<h2>Connection Pool Exhaustion</h2>
<h3>Symptoms</h3>
<pre><code>Error: QueuePool limit of size 10 overflow 5 reached
Error: FATAL: too many connections for role &quot;user&quot;
Error: Timeout waiting for connection from pool</code></pre>
<h3>Causes</h3>
<ol><li><strong>Too many concurrent requests</strong>: Traffic spike exceeds pool size</li><li><strong>Connection leaks</strong>: Connections not returned to pool</li><li><strong>Long-running queries</strong>: Connections held for too long</li><li><strong>Incorrect pool sizing</strong>: Pool too small for workload</li></ol>
<h3>Debugging</h3>
<pre><code class="lang-python"># SQLAlchemy: Check pool status
print(engine.pool.status())
# Output: Pool size: 10 Connections in pool: 2 Current Overflow: 0 Current Checked out connections: 8
# Log pool events
engine = create_engine(&quot;...&quot;, echo_pool=True)</code></pre>
<h3>Solutions</h3>
<p><strong>1. Increase pool size</strong>:</p>
<pre><code class="lang-python">engine = create_engine(&quot;...&quot;, pool_size=20, max_overflow=10)</code></pre>
<p><strong>2. Fix connection leaks</strong>:</p>
<pre><code class="lang-python"># ❌ BAD: Connection leak
conn = engine.connect()
result = conn.execute(query)
# Connection never returned!
# ✅ GOOD: Use context manager
with engine.connect() as conn:
result = conn.execute(query)
# Connection automatically returned</code></pre>
<p><strong>3. Optimize slow queries</strong>: Use <code>postgres-query-optimization.md</code></p>
<p><strong>4. Use external pooler</strong>: PgBouncer, AWS RDS Proxy</p>
<hr>
<h2>External Connection Poolers</h2>
<h3>PgBouncer</h3>
<p><strong>What</strong>: External connection pooler for PostgreSQL.</p>
<p><strong>Use case</strong>: Multiple applications share database, need more than 100 connections.</p>
<pre><code class="lang-ini"># pgbouncer.ini
[databases]
mydb = host=localhost dbname=mydb
[pgbouncer]
pool_mode = transaction # Or session, statement
max_client_conn = 1000 # Client connections
default_pool_size = 20 # Connections to database per user/database pair
reserve_pool_size = 5</code></pre>
<p><strong>Pool modes</strong>:</p>
<ul><li><strong>session</strong>: Hold connection for session (safest, default)</li><li><strong>transaction</strong>: Release connection after transaction (most efficient)</li><li><strong>statement</strong>: Release after each statement (dangerous, breaks transactions)</li></ul>
<p><strong>Recommended</strong>: <code>transaction</code> mode for web apps.</p>
<h3>AWS RDS Proxy</h3>
<p><strong>What</strong>: Managed connection pooler for RDS/Aurora.</p>
<p><strong>Benefits</strong>:</p>
<ul><li>Automatic failover</li><li>IAM authentication</li><li>Handles connection spikes</li></ul>
<p><strong>Configuration</strong>:</p>
<pre><code>Max connections: 100
Idle timeout: 1800 seconds</code></pre>
<hr>
<h2>Monitoring</h2>
<h3>Key Metrics</h3>
<p>| Metric | What to Monitor | Threshold | |--------|----------------|-----------| | Pool size | Current active connections | &lt; pool_size × 0.8 | | Wait time | Time waiting for connection | &lt; 100ms | | Timeout errors | Connection timeout rate | 0% | | Connection age | Average connection lifetime | &lt; pool_recycle |</p>
<h3>Tools</h3>
<p><strong>PostgreSQL</strong>:</p>
<pre><code class="lang-sql">-- Check current connections
SELECT count(*), state
FROM pg_stat_activity
WHERE datname = &#x27;mydb&#x27;
GROUP BY state;
-- Check connection age
SELECT now() - backend_start AS age, query
FROM pg_stat_activity
ORDER BY age DESC;</code></pre>
<p><strong>MySQL</strong>:</p>
<pre><code class="lang-sql">SHOW PROCESSLIST;
SHOW STATUS LIKE &#x27;Threads_connected&#x27;;</code></pre>
<hr>
<h2>Best Practices</h2>
<h3>Do&#x27;s</h3>
<p>✅ <strong>Start with conservative pool size</strong> (num_workers) ✅ <strong>Enable pool_pre_ping</strong> for reliability ✅ <strong>Set pool_recycle</strong> &lt; database timeout ✅ <strong>Use context managers</strong> to return connections ✅ <strong>Monitor pool metrics</strong> in production ✅ <strong>Use external pooler</strong> for high concurrency</p>
<h3>Don&#x27;ts</h3>
<p>❌ <strong>Don&#x27;t set pool_size = max_connections</strong> (leave headroom) ❌ <strong>Don&#x27;t leak connections</strong> (always close/return) ❌ <strong>Don&#x27;t ignore timeout errors</strong> (sign of undersized pool) ❌ <strong>Don&#x27;t use same pool across processes</strong> (each process needs its own)</p>
<hr>
<h2>Quick Reference</h2>
<h3>Pool Sizing Cheatsheet</h3>
<pre><code>Workload Pool Size
────────────────────────────────────────
Web app (10 workers) 10-20
API (20 workers) 20-40
Background jobs (5 workers) 5-10
Single-threaded app 1-5
High-concurrency async 30-50
Database limit &lt; max_connections - 10</code></pre>
<h3>Configuration Template (SQLAlchemy)</h3>
<pre><code class="lang-python">from sqlalchemy import create_engine
engine = create_engine(
&quot;postgresql://user:pass@host/db&quot;,
pool_size=20, # Base pool
max_overflow=10, # Burst capacity
pool_timeout=30, # Wait timeout
pool_recycle=3600, # Recycle after 1h
pool_pre_ping=True, # Test before use
)</code></pre>
<hr>
<h2>Common Pitfalls</h2>
<p>❌ <strong>Pool size too large</strong> - Database overload, memory waste ✅ Calculate based on workers/threads, not arbitrary large number</p>
<p>❌ <strong>No pool recycling</strong> - Stale connections, timeout errors ✅ Set pool_recycle &lt; database timeout</p>
<p>❌ <strong>Connection leaks</strong> - Pool exhaustion ✅ Always use context managers or explicit close</p>
<p>❌ <strong>One pool for all apps</strong> - Contention, hard to debug ✅ Each application instance has its own pool</p>
<hr>
<h2>Related Skills</h2>
<ul><li><code>postgres-query-optimization.md</code> - Optimize queries to reduce connection time</li><li><code>database-selection.md</code> - Connection pooling considerations per database</li><li><code>orm-patterns.md</code> - ORM best practices for connection management</li></ul>
<hr>
<p><strong>Last Updated</strong>: 2025-10-18 <strong>Format Version</strong>: 1.0 (Atomic)</p>
<footer>
<div class="fmap">
<div><b>Build</b>
<a href="https://hanzo.app">hanzo.app</a> app builder
<a href="https://hanzo.codes">hanzo.codes</a> code with an agent
<a href="https://hanzoskills.com">hanzoskills.com</a> what an agent reads</div>
<div><b>Run</b>
<a href="https://console.hanzo.ai">console.hanzo.ai</a> cloud console
<a href="https://api.hanzo.ai">api.hanzo.ai</a> the API</div>
<div><b>Account</b>
<a href="https://hanzo.id">hanzo.id</a> sign in
<a href="https://pay.hanzo.ai">pay.hanzo.ai</a> top up
<a href="https://billing.hanzo.ai">billing.hanzo.ai</a> invoices and usage</div>
<div><b>Company</b>
<a href="https://hanzo.ai">hanzo.ai</a> canonical
<a href="https://hanzo.ventures">hanzo.ventures</a> ventures</div>
</div>
<div class="fend"><a href="/database-database-connection-pooling.md">This page as plain text</a> · <a href="/llms.txt">Index for agents</a></div>
</footer>
</div></body></html>