Database Connection Pooling Patterns
Creating fresh database connections per request is expensive due to TCP and TLS handshakes. A connection pool keeps a warm pool of active connections to recycle instantly across incoming threads 🏊.
import sqlite3, queue
class SQLitePool:
def __init__(self, size=3):
self.pool = queue.Queue(maxsize=size)
for _ in range(size):
self.pool.put(sqlite3.connect(":memory:"))
def get_connection(self):
return self.pool.get()
def release_connection(self, conn):
self.pool.put(conn)
pool = SQLitePool(2)
c = pool.get_connection()
print("Acquired warm DB connection from pool! 🏊")
pool.release_connection(c)
# 28. Database Connection Pooling Patterns Creating fresh database connections per request is expensive due to TCP and TLS handshakes. A connection pool keeps a warm pool of active connections to recycle instantly across incoming threads 🏊. ```python import sqlite3, queue class SQLitePool: def __init__(self, size=3): self.pool = queue.Queue(maxsize=size) for _ in range(size): self.pool.put(sqlite3.connect(":memory:")) def get_connection(self): return self.pool.get() def release_connection(self, conn): self.pool.put(conn) pool = SQLitePool(2) c = pool.get_connection() print("Acquired warm DB connection from pool! 🏊") pool.release_connection(c) ``` In high-concurrency PostgreSQL and MySQL production backends, connection pools like PgBouncer or SQLAlchemy's QueuePool prevent database processes from crashing under traffic spikes! 🏊 --- ### Question What performance bottleneck does database connection pooling solve? 🧠 - [ ] a. Eliminates the latency of repeatedly opening, authenticating, and closing TCP/SSL sockets per request 🏊 - [ ] b. Increases the maximum storage capacity of hard drives - [ ] c. Translates SQL into machine code at runtime - [ ] d. Enables SQLite to run on multiple physical computers --- ### Challenge Create `class Pool` with `queue.Queue`. Pre-fill with 2 in-memory connections. Acquire one with `.get()`, execute `SELECT 1`, and release with `.put()`. Print queue size with `.qsize()`. 🎯 <details> <summary>Solution</summary> ```python import sqlite3, queue class Pool: def __init__(self): self.q = queue.Queue() self.q.put(sqlite3.connect(":memory:")) self.q.put(sqlite3.connect(":memory:")) def acquire(self): return self.q.get() def release(self, c): self.q.put(c) p = Pool() conn = p.acquire() res = conn.execute("SELECT 1").fetchone()[0] p.release(conn) print(p.q.qsize()) ``` </details>