Database-Per-Tenant vs Schema-Per-Tenant Connection Pool Cost

The connection pool is where database-per-tenant quietly gets expensive: every isolated database needs its own pool, and idle connections multiply per tenant instead of being shared. This page sits in the Database-Per-Tenant Isolation section of the broader Multi-Tenant Database Isolation Models reference and compares the two models purely on pool economics.

Problem Framing

A PostgreSQL connection is not free. Each backend is a separate OS process holding roughly 5–10 MB of resident memory plus work_mem allocations, and max_connections is a hard ceiling enforced at server start. Whether a connection is idle or running a query, it occupies a slot. The model you pick for tenant isolation decides how many of those slots you burn to serve the same workload.

Schema-per-tenant — the design covered in Schema-Per-Tenant Architecture — keeps every tenant inside one database. One application process opens one pool against that database and rotates connections across tenants by changing search_path per transaction. A pool of 20 connections serves a thousand tenants, because at any instant only the actively-running queries hold a backend. Idle capacity is shared.

Database-per-tenant breaks that sharing. A connection to tenant_acme cannot run a query for tenant_globex — the database is the wrong one. So each tenant needs its own pool, and most client-side pool libraries (HikariCP, pg.Pool, SQLAlchemy's QueuePool) keep a configured minimum of connections open even when idle. With a per-DB minimum of 2 idle connections and 5,000 tenants, you are holding 10,000 backends open before a single query runs. That is the idle-connection multiplication problem, and it is the dominant cost difference between the models.

Step-by-Step Guide

1. Quantify the idle floor for each model

Before choosing, compute the connections held open at zero query load. This is the number that decides whether you hit max_connections first or run out of RAM first.

def idle_connections(model, tenants, app_instances,
                     min_per_pool=2):
    if model == "schema":
        # one pool per app instance, shared across tenants
        return app_instances * min_per_pool
    if model == "database":
        # one pool per tenant per app instance
        return app_instances * tenants * min_per_pool
    raise ValueError(model)

print(idle_connections("schema",   tenants=5000, app_instances=8))   # 16
print(idle_connections("database", tenants=5000, app_instances=8))   # 80000

For schema-per-tenant the idle floor is a constant times the number of application instances. For database-per-tenant it is multiplied by the tenant count, and that product is what blows past a realistic max_connections of 200–500. The 80,000 figure is not a typo — it is what eight horizontally-scaled app instances each holding two idle connections to five thousand tenant databases produces, and it is why naive database-per-tenant does not survive contact with a connection-pool library's defaults.

2. Set the per-DB pool minimum to zero

The single most effective lever for database-per-tenant is to stop holding idle connections at all. Configure the pool to open connections on demand and close them quickly when traffic ebbs.

# HikariCP per-tenant DataSource
minimumIdle=0
maximumPoolSize=4
idleTimeout=30000
keepaliveTime=0
maxLifetime=600000

minimumIdle=0 collapses the idle floor: a tenant that receives no traffic holds zero backends. idleTimeout=30000 reaps a connection 30 seconds after its last use, so a burst of activity grabs connections and a quiet period releases them. The cost is latency — a cold tenant pays a TCP handshake plus PostgreSQL backend fork (1–5 ms locally, more across an AZ) on its first query. For low-volume tenants that is an acceptable trade; for hot tenants you want a small non-zero minimum, which means you must tier pool config by tenant activity rather than apply one setting everywhere.

3. Front every database with PgBouncer in transaction mode

A client-side pool can only multiplex within one application process. PgBouncer sits between the app and PostgreSQL and pools at the server, so thousands of client connections collapse onto a handful of real backends.

[databases]
* = host=10.0.0.5 port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 5
max_db_connections = 50

pool_mode = transaction returns a server connection to the pool at the end of every transaction, so a connection is held only for the duration of a query, not the lifetime of a client session. default_pool_size = 5 caps real backends per database, and max_db_connections = 50 caps the total a single PgBouncer will open to PostgreSQL regardless of how many databases it fronts. This is the mechanism that makes database-per-tenant viable at scale: clients see one logical pool per tenant, PostgreSQL sees a bounded number of backends. The deeper mechanics live in Connection Pooling in Multi-Tenant Systems.

4. Apply the sizing formula and find the break-even

With idle floors and PgBouncer caps known, size the real backend demand and locate the tenant count where the two models cross.

def backends_needed(model, tenants, concurrent_queries,
                    pgb_pool_size=5):
    if model == "schema":
        # demand is driven by concurrent queries, not tenants
        return concurrent_queries
    if model == "database":
        # each active tenant DB needs up to pgb_pool_size backends
        active = min(tenants, concurrent_queries)
        return active * pgb_pool_size

# break-even: where database-per-tenant backends exceed schema's
for t in (10, 50, 100, 500):
    s = backends_needed("schema",   t, concurrent_queries=40)
    d = backends_needed("database", t, concurrent_queries=40)
    print(t, s, d)

The schema model's backend demand tracks concurrent queries and is flat in tenant count. The database model's demand scales with the number of active tenants times the PgBouncer pool size, so it crosses the schema model's flat line at a low tenant count and keeps climbing. The break-even is the point past which database-per-tenant requires more real PostgreSQL backends than schema-per-tenant to serve identical traffic — and that crossover decides whether your isolation requirement is worth its connection bill.

Verification

Measure the real backend count under load rather than trusting config. This query shows live backends grouped by database, which is the number that actually counts against max_connections.

SELECT datname,
       count(*) AS backends,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state = 'active') AS active
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname
ORDER BY backends DESC;

For schema-per-tenant you expect one datname row whose backends stays near your concurrent-query count. For database-per-tenant you expect many rows; the idle column is the diagnostic — if it is large, your per-DB minimumIdle is not zero or PgBouncer is not in transaction mode. Cross-check the PgBouncer side with SHOW POOLS; on its admin console: sv_idle and sv_active per database confirm the server-side pool is recycling connections instead of pinning them.

Failure Modes & Gotchas

FAQ

At what tenant count does database-per-tenant become too expensive on connections? With idle minimums at the library defaults, problems start in the low thousands of tenants because the idle floor alone approaches max_connections. With minimumIdle=0 and PgBouncer transaction pooling, the binding limit shifts from idle connections to active concurrency, and the model stretches to tens of thousands of tenants before backend demand forces sharding across clusters.

Does PgBouncer eliminate the cost difference between the two models? It narrows it sharply but does not erase it. PgBouncer caps real backends, yet database-per-tenant still pays for more pg_stat/catalog overhead, more pools to track client-side, and a cold-start latency on dormant tenants that schema-per-tenant never incurs. Schema-per-tenant remains cheaper on pure connection economics; you choose database-per-tenant when isolation, not cost, is the requirement.

Why not just raise max_connections instead? Each connection is a process holding several megabytes plus work_mem, so raising the ceiling trades a connection limit for a memory and context-switch limit. Past a few hundred backends PostgreSQL spends measurable CPU on lock contention and scheduling, which is the problem pooling exists to solve. Pool first, raise the ceiling only after.