Database connections

Each tenant has its own pool of PostgreSQL connections in every broker process. Reads can never take the whole pool, so writes keep moving however heavy the read load gets. The name caches each tenant keeps are a separate topic.

Pool size

The default tenant uses tenancy.default_tenant.max_connections, which is 32 by default. Every named tenant gets its own pool of tenancy.databases.max_connections, which is 4 by default. The platform pool, which reads the tenant registry, is database.platform_max_connections, 32 by default.

At most tenancy.residency.max_resident tenants stay loaded at once, 16 by default. So a process holds at most about this many connections, and the server has to fit them, with room for your own tools:

$$C_{\text{process}} = N \cdot P_t + P_0 + C_{\text{platform}} \qquad C_{\text{server}} \ge C_{\text{process}}$$

where:

• $N$ is tenancy.residency.max_resident, the tenants kept loaded.
• $P_t$ is a named tenant's pool, tenancy.databases.max_connections.
• $P_0$ is the default tenant's pool, tenancy.default_tenant.max_connections.
• $C_{\text{platform}}$ is every other connection the broker counts: the platform pool, maintenance, a repack, tenant builds and the checks for due work, which Connection capacity lists.
• $C_{\text{server}}$ is PostgreSQL's max_connections.

With the defaults, $N \cdot P_t + P_0 = 16 \cdot 4 + 32 = 96$ connections per process, and 152 with the rest. That fits the 200 the PostgreSQL image allows by default.

Tenants with requests in flight are never unloaded. The count can go over that limit for a while when more tenants than that are busy at once.

A tenant loaded only to run due background work, such as an expiry, a Snapshot still preparing or history past a conversion or retention horizon, is not unloaded to make room for tenancy.residency.woken_lifetime either, 1 minute by default. That work runs before the tenant can be unloaded and loaded again for it.

Behind PgBouncer, which you configure in a [database.pooler] section, $P_t$ is database.pooler.max_connections_per_tenant instead. The default is 2, since PgBouncer does the real pooling.

When to raise the defaults

The named-tenant defaults suit many small tenants on a server with the 200 connections the PostgreSQL image allows. Two kinds of load outgrow them.

• One named tenant has more than four requests in flight at once, a bulk ingest for example, and its requests get 503 with Retry-After: 1 while the database sits idle.

  Raise tenancy.databases.max_connections.

• More than 16 tenants are in steady use and keep being unloaded and loaded again, or andromeda_tenant_overdue_wakes rises because tenants loaded for periodic notifications find no free place.

  Raise tenancy.residency.max_resident.

Either change adds connections, so raise PostgreSQL's max_connections by what it adds, which is $N \cdot P_t$ in all, or put PgBouncer in front of the database.

Keeping room for writes

Without a limit, reads take connections in the order they arrive, and writes slow down as more readers pile in. On a 32-connection pool, entity creates fell from 4,754 per second to 1,978 beside 32 readers. Temporal writes fell from 65,468 instances per second to 11,411. Reads levelled off at about 620 per second. A bigger pool moves that plateau, but it doesn't remove the contention.

So read requests share only part of each pool. database.write_reserve_percent sets the share they never hold, 25 by default. With a pool of $P$ connections and a reserve of $r$ percent, reads may hold:

$$P_{\text{reads}} = P - \left\lfloor \frac{P \cdot r}{100} \right\rfloor$$

The reserve is rounded down to whole connections, so a pool too small to spare one keeps every connection for reads. With the default:

Pool size $P$	Reserve	Reads may hold
32	8	24
16	4	12
4	1	3
3	0	3
2	0	2
1	0	1

A write takes its connection from the whole pool. It always finds the reserve free, and it can use the whole pool while reads are idle. The reserve divides the pool you configure. It doesn't change the pool's size.

These count as read requests:

• Query Entities, including the identifier-only form, and the entities a query page or a linked entity hop reads by identifier
• Retrieve Entity
• the discovery reads, for entity types and attributes
• every statement of a temporal retrieval or query

Everything else uses the whole pool. That covers writes, and the reads a write needs, such as the state a merge, replace, delete or batch starts from. It also covers notification payloads, the periodic notification worker, expiry, maintenance and the name dictionaries. That work has already been admitted. Holding it back behind read requests would make writes wait on the read load after all.

A tenant's entity reads and temporal reads share one read allowance.

When the pool is full

A read request waits for its share and its connection together, within database.acquire_timeout. A write waits for a connection within the same time. When the time runs out, the request gets:

HTTP/1.1 503 Service Unavailable
Retry-After: 1

The problem type is ServiceUnavailable and the detail reads the datastore connection pool is saturated; retry shortly.

Keep database.acquire_timeout below request_limits.timeout. Then a full pool shows up as a quick 503 and not as a slow 500. Store outages covers the other 503s.

A read holds its connection only while its statement runs. It gives the connection back before the rows are put together.

Configuration

Key	Default	What it does
tenancy.default_tenant.max_connections	32	The default tenant's pool
tenancy.databases.max_connections	4	Each named tenant's pool
tenancy.residency.max_resident	16	How many tenants stay loaded at once
database.platform_max_connections	32	The platform pool: the tenant registry every tenant load reads, the schedule of tenants with due work, and the routes of forwarded subscriptions. It always connects straight to PostgreSQL
database.pooler.max_connections_per_tenant	2	Each tenant's pool when requests go through PgBouncer
database.write_reserve_percent	25	The share of each pool reads never hold. Must be below 100
database.acquire_timeout	"5s"	How long a request waits for a connection before it gets 503

An invalid value stops the broker at startup. The name cache keys are on the Name caches page.

If it goes wrong

• Steady 503 with Retry-After: 1, and the database is healthy

  The pools are too small for the load. Raise tenancy.databases.max_connections or tenancy.default_tenant.max_connections, and make sure PostgreSQL's max_connections covers the pools.

• Writes stall while reads are heavy

  Raise database.write_reserve_percent, so reads hold less of the pool.