Skillversitas

Connection Pooling and Proxy

A long-running app - a web server handling requests on many threads, a worker pool - cannot use one raw connection: a connection is not thread-safe, and opening a new one per request is a cost paid on every request. The answer is a pool: a fixed set of connections that the app borrows and returns, managed for it. Peewee's playhouse.pool provides the PooledDatabase (and the ProxyDatabase for the deferred-binding case), and this chapter covers the pool's construction, the borrow/return lifecycle, the pool sizing, and the proxy pattern that lets the models bind before the real connection is chosen.

PooledDatabase: The Pool

from playhouse.pool import PooledPostgresqlDatabase

# the pool wraps a backend class - same parameters as the raw one:
db = PooledPostgresqlDatabase(
    "appdb",
    user="app",
    password="secret",
    host="db.internal",
    port=5432,
    max_connections=20,     # the pool size
)

# the API is the SAME as the raw database - the models do not know:
class Note(Model):
    text = TextField()
    class Meta:
        database = db

# a query borrows a connection, runs, and returns it:
db.connect()                # opens the pool (warms it)
rows = Note.select().execute()
db.close()                  # drains the pool

PooledPostgresqlDatabase (and the PooledMySQLDatabase, PooledSqliteDatabase) is the pool, constructed with the backend's parameters plus the pool controls - max_connections is the size, the cap on how many connections the pool holds. The public API is identical to the raw database class, which is the point: the models point at db exactly as they would at a raw connection, and the pool is invisible to the query code - a query borrows a connection from the pool, executes, and returns it, and the borrow/return is handled inside the call. The connect()/close() manage the pool's lifetime (open and warm it at startup, drain it at shutdown) rather than a single connection. The switch from a raw connection to the pool is a one-line change in database.py - the class name - because the API is the same, which is the design that makes the pool a drop-in for the long-running case.

Pool Sizing and the Lifecycle

from playhouse.pool import PooledPostgresqlDatabase

# sizing: the pool is a bounded resource - too small starves the
# threads, too large exhausts the database's connection limit
# the working rule: pool_size ~ (core_count * 2) + effective_spindle_count,
# kept BELOW the database's max_connections (leave headroom for
# migrations, admin, and the other apps sharing the server)
db = PooledPostgresqlDatabase(
    "appdb", user="app", password="secret",
    host="db.internal", max_connections=20,
)

# the per-operation lifecycle the pool manages for you:
#   borrow -> run -> return, on every query
# a connection held too long (an open transaction, a blocked query)
# is a pool slot not available to the next thread - the pool's
# health is the sum of its connections' health

The pool size is the tuning knob, and it is a balance: too small and the threads queue for a connection (the app slows under load even though the database is fast), too large and the app exhausts the database server's connection limit (the database slows, and the other apps sharing it suffer). The working estimate is a function of the app's concurrency and the database's cap, with headroom left for the non-pool connections (the migration scripts, the admin tooling). The lifecycle the pool manages - borrow, run, return on every query - means a connection's health is a pool concern: a connection stuck in a long transaction or a slow query holds its slot, and the pool's observable symptom under that is the queue of waiting threads, which is why a "the app is slow under load" investigation starts at the pool's wait time and the database's active-connection count. The pool is the resource, and it is sized and monitored like any other bounded resource.

ProxyDatabase: Bind Now, Connect Later

from playhouse.pool import ProxyDatabase

# the proxy holds NO backend yet - the models bind to it at import
# time, and the REAL database is initialized at startup, once the
# config (the environment, the feature flag) is known
db = ProxyDatabase()

class Note(Model):
    text = TextField()
    class Meta:
        database = db          # bound to the proxy, not a real db

# at startup, after reading the config:
from peewee import SqliteDatabase
from playhouse.pool import PooledPostgresqlDatabase

def init_db():
    url = os.environ.get("DATABASE_URL", "sqlite:///app.db")
    if url.startswith("postgres"):
        db.initialize(PooledPostgresqlDatabase.from_url(url))
    else:
        db.initialize(SqliteDatabase(url.split("///")[-1]))
    db.connect()

init_db()
# the models now run against the REAL database through the proxy
# - the import order is free: models.py can be imported before
#   the connection exists, because the proxy accepts the binding

The ProxyDatabase solves the import-order problem: the models need to declare Meta.database = db at import time, but the real connection is not constructed until startup, when the environment is read. The proxy is a stand-in that accepts the binding and forwards every call to the database it is later initialized with - the models import and bind to the proxy, init_db() constructs the real connection (a raw one or a PooledDatabase) and hands it to the proxy, and from then on the queries flow through to it. The pattern is the same one the connections chapter's database.py uses, with the proxy adding the decoupling that a large codebase needs: any module can import the models without the connection existing, and the startup sequence decides the backend. The proxy is the tool for the case where "which database" is not known at import time - a multi-environment deploy, a test that swaps in an in-memory database, a feature that adds a second database.

The Working Rules

  • Raw connection for scripts, pool for servers. A one-thread script does not need a pool; a multi-threaded app does, and the PooledDatabase is the drop-in (the same API, a class-name change in database.py).
  • Size the pool below the database's cap, with headroom for the non-pool connections, and treat the pool's wait time as the first metric when the app slows under load.
  • Do not hold a connection across slow work - a long transaction or a network call inside a query block holds a pool slot; keep the transaction (the transactions chapter) as short as the atomic operation.
  • Use the proxy when the backend is decided at startup, so the models bind at import and the real database is injected at init - the import order is free, and the test can swap in an in-memory database through the same proxy.
  • Warm and drain deliberately - connect() at startup, close() at shutdown, so the pool's lifetime matches the process's and the connections are not opened lazily under the first load.

Summary

The PooledDatabase is the multi-threaded answer to the not-thread-safe connection: a bounded set of connections (max_connections) that every query borrows and returns, with the same public API as the raw database so the switch is a class-name change in database.py and the models are untouched. Size the pool below the database's connection cap with headroom for the non-pool connections, and read the pool's wait time as the first symptom when the app slows under load. The ProxyDatabase is the deferred-binding variant - the models bind at import, initialize injects the real (pooled or raw) database at startup once the config is known, and the test swaps the backend through the same proxy. The rules are the resource-management rules applied to connections: warm at startup, drain at shutdown, keep the transaction short so a slot is not held across slow work, and pool for the server, raw for the script. The pool is what makes the one-connection-per-thread rule operable at the concurrency a real app runs at.