mirror of
https://github.com/hanzoai/skills.git
synced 2026-11-19 00:47:36 +00:00
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]>
504 lines
28 KiB
HTML
504 lines
28 KiB
HTML
<!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(
|
||
"postgresql://...",
|
||
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(
|
||
"postgresql://...",
|
||
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(
|
||
"postgresql://...",
|
||
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(
|
||
"postgresql://...",
|
||
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 "connection has been closed" errors</li><li>Clears stale connections</li></ul>
|
||
<p><strong>Recommended</strong>: Slightly less than database'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(
|
||
"postgresql://...",
|
||
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 "connection closed" 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'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(
|
||
"postgresql://user:pass@localhost/db",
|
||
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 = "postgresql"
|
||
url = env("DATABASE_URL")
|
||
}
|
||
|
||
// Connection string with pool params
|
||
DATABASE_URL="postgresql://user:pass@localhost/db?schema=public&connection_limit=10"
|
||
|
||
// Or in PrismaClient
|
||
const prisma = new PrismaClient({
|
||
datasources: {
|
||
db: {
|
||
url: process.env.DATABASE_URL + "?connection_limit=10&pool_timeout=20"
|
||
}
|
||
}
|
||
})</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 (
|
||
"gorm.io/driver/postgres"
|
||
"gorm.io/gorm"
|
||
)
|
||
|
||
dsn := "host=localhost user=postgres password=pass dbname=db"
|
||
db, err := gorm.Open(postgres.Open(dsn), &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::<PgConnection>::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 = {
|
||
'default': {
|
||
'ENGINE': 'django.db.backends.postgresql',
|
||
'NAME': 'db',
|
||
'USER': 'user',
|
||
'PASSWORD': 'pass',
|
||
'HOST': 'localhost',
|
||
'PORT': '5432',
|
||
'CONN_MAX_AGE': 600, # Connection lifetime (seconds), 0 = no pooling
|
||
'OPTIONS': {
|
||
'connect_timeout': 10,
|
||
}
|
||
}
|
||
}</code></pre>
|
||
<p><strong>Note</strong>: Django doesn'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 "user"
|
||
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("...", echo_pool=True)</code></pre>
|
||
<h3>Solutions</h3>
|
||
<p><strong>1. Increase pool size</strong>:</p>
|
||
<pre><code class="lang-python">engine = create_engine("...", 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 | < pool_size × 0.8 | | Wait time | Time waiting for connection | < 100ms | | Timeout errors | Connection timeout rate | 0% | | Connection age | Average connection lifetime | < 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 = 'mydb'
|
||
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 'Threads_connected';</code></pre>
|
||
<hr>
|
||
<h2>Best Practices</h2>
|
||
<h3>Do'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> < 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'ts</h3>
|
||
<p>❌ <strong>Don't set pool_size = max_connections</strong> (leave headroom) ❌ <strong>Don't leak connections</strong> (always close/return) ❌ <strong>Don't ignore timeout errors</strong> (sign of undersized pool) ❌ <strong>Don'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 < max_connections - 10</code></pre>
|
||
<h3>Configuration Template (SQLAlchemy)</h3>
|
||
<pre><code class="lang-python">from sqlalchemy import create_engine
|
||
|
||
engine = create_engine(
|
||
"postgresql://user:pass@host/db",
|
||
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 < 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> |