PgBouncer

Every named tenant has a database of its own, and in each broker process a connection pool of its own. Without a pooler, the connections a broker may hold grow with every tenant it keeps loaded, and that keeps tenancy.residency.max_resident small. PgBouncer in transaction mode breaks the link. A loaded tenant that isn't serving requests holds almost no PostgreSQL connections, so one broker can keep hundreds of tenants loaded.

1. AndromedaA pool of 2 for each loaded tenant
2. requests and background work of every tenant
3. PgBouncerTransaction mode, one pool per tenant database
4. a server connection for each transaction in flight
5. PostgreSQLAlso reached directly, for migrations, maintenance and the platform database

AndromedaA system Andromeda doesn't run

Pointing the broker at PgBouncer

[database]
connection = "postgres://andromeda:secret@postgres:5432/andromeda"

[database.pooler]
connection = "postgres://andromeda:secret@pgbouncer:6432/andromeda"
max_connections_per_tenant = 2

The pooler forwards to the same PostgreSQL, so the two connections normally differ only in host and port. Either one can also be a table of its parts, host, port, user, password, database and parameters. The passwords then come from ANDROMEDA__DATABASE__CONNECTION__PASSWORD and ANDROMEDA__DATABASE__POOLER__CONNECTION__PASSWORD, without being escaped into a URL.

database.connection names PostgreSQL itself

Never point database.connection at the pooler. The work that stays on it keeps session state across transactions, which a transaction pooler breaks.

With the section present, the traffic splits in two.

• Through the pooler

  Each tenant's requests and background work, from a pool of database.pooler.max_connections_per_tenant connections, 2 by default, in place of tenancy.databases.max_connections.

• Straight to PostgreSQL

  Creating tenant databases, migrations and maintenance. The platform database, which holds the tenant registry, with its pool of database.platform_max_connections. And the check for due background work on a tenant that is still being created, because its database may not have its defaults yet, and the pooler would keep the server connection the check opened and later serve the tenant's requests from it without them.

The login

The login the broker uses through PgBouncer must be the role in database.connection. Snapshot schemas are created over the serving pool, so the role that logs in through PgBouncer owns them, while the role in database.connection upgrades and maintains them. With two different roles, the schemas belong to a role other than the one working on them, and only a superuser in database.connection can get past that. That role also needs CREATE on each tenant database. PostgreSQL lists every privilege.

Configuring PgBouncer

This pgbouncer.ini is a starting point.

[databases]
* = host=postgres port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

max_client_conn = 5000
default_pool_size = 2
min_pool_size = 0
reserve_pool_size = 1
reserve_pool_timeout = 3
server_idle_timeout = 5

max_user_connections = 120
max_db_connections = 20

ignore_startup_parameters = extra_float_digits,IntervalStyle,DateStyle,TimeZone,client_encoding
query_wait_timeout = 15

These are the settings that matter most.

• pool_mode = transaction

  A server connection is lent for one transaction and returned at commit. A PostgreSQL connection belongs to one database, so PgBouncer keeps a separate pool for each tenant database.

• One wildcard route, without user=

  * = host=... port=... serves every tenant database, including ones created later, without a reload. Leave user= off. PgBouncer hands an idle server connection to a waiting pool only when both use the same login. With a forced user, every database gets a login object of its own, and a newly loaded tenant waits for another tenant's idle connection to time out, however many sit idle.

• One shared login

  List the broker's login in auth_file, with auth_type = scram-sha-256 in production. Every tenant database is then served as that one login, which is what makes max_user_connections a cap on the whole fleet.

• server_idle_timeout

  How soon an idle server connection is closed. Keep it to a few seconds. A loaded tenant with nothing to do goes quiet, so its connections close soon after its last request.

• Prepared statements

  The broker uses prepared statements, so keep PgBouncer's max_prepared_statements above zero.

Sizing

With a pooler, PostgreSQL sees roughly as many connections as the active tenants hold, plus the direct ones.

$$C_{\text{seen}} \approx A \cdot p + D$$

Here $A$ is the number of active tenants, $p$ is default_pool_size, and $D$ is the direct connections, the ones that never go through the pooler.

• The platform pool, database.platform_max_connections
• Two for each tenant that may be loading at once, tenancy.build.max_concurrent
• Maintenance, three on the platform database and one in the tenant it is working on
• Two for a repack, in the tenant whose table it copies
• Creating new tenant databases
• Checking for due work on tenants still being created, one at a time

PgBouncer's max_user_connections caps the first term for the whole fleet, so PostgreSQL's max_connections has to cover that cap and the direct connections, with room to spare.

$$C_{\text{server}} > U + D$$

Here $C_{\text{server}}$ is PostgreSQL's max_connections and $U$ is max_user_connections. Database connections explains the same sum without a pooler. From there:

• Raise tenancy.residency.max_resident well above what you would use without a pooler

  Memory, not connections, limits it now, and a larger cache means fewer cold loads.

• Keep default_pool_size small, 2 to 4

  A tenant rarely has more than a couple of transactions in flight.

• Keep query_wait_timeout below request_limits.timeout, and long enough to absorb bursts

  A transaction still waiting when it runs out gets 503 with Retry-After: 1. In testing, 4 seconds turned a wave of 32 new tenants into 503s, and 15 seconds absorbed it.

• Keep database.acquire_timeout and tenancy.build.admission_timeout below request_limits.timeout

  The broker doesn't start otherwise.

Why quiet tenants hold no connections

A loaded tenant has background work on its database, such as expiry sweeps, the notification and history queues, periodic notifications and Snapshot recovery. Each runs only when something is due. The broker reads the tenant's earliest due work once, and every task rests until then, or until a request reaches the tenant. A tenant with nothing due makes one small read every 16 expiry.sweep_interval periods, and nothing else. Its pool goes quiet, and PgBouncer closes the server connections after server_idle_timeout.

So the connections PostgreSQL sees follow the tenants that are active, not the tenants that are loaded.

When PgBouncer or PostgreSQL is down

While PgBouncer can't reach PostgreSQL, it fails the broker's transactions, and it remembers a failed login for server_login_retry. A PgBouncer restart cuts the broker's connections in the middle of their transactions. Either way, clients get 503 with Retry-After: 5, never 500, and the broker's background work resumes once the database is back. Keep server_login_retry short, so a restarted PostgreSQL is picked up within a few retries. Store outages describes the rest.

What pooling doesn't fix

PgBouncer bounds what many tenants cost in connections. The other costs of one database per tenant remain.

• TimescaleDB background workers

  TimescaleDB runs a scheduler in every database that has the extension, so the schedulers grow with the tenant count. The broker installs no TimescaleDB job and needs none of them. Size timescaledb.max_background_workers only for the jobs you add yourself.

• Autovacuum

  Autovacuum visits the databases in turn. With hundreds of them, each one is visited less often, and bloat can build up for longer before it is cleaned.

• Catalog overhead

  Every database has a catalog of its own, and the total number of relations grows with the tenant count.

• The first request after a tenant is loaded

  Loading a tenant reads every entity location to build its spatial index, which takes longer the more data the tenant holds. entity.spatial_index.consistency = "store-verified" builds no index and skips the read. Spatial index weighs the choice.

Checking it works

Connect to PgBouncer's admin console as a user listed in its admin_users, and list the pools.

psql -h pgbouncer -p 6432 -U pgbouncer_admin pgbouncer -c 'SHOW POOLS'

After a few requests to different tenants, there is one pool per tenant database, each named andromeda_tenant_.... Leave the broker idle for longer than server_idle_timeout, and the sv_idle and sv_active counts of the quiet tenants drop to zero.

Then ask the broker whether its direct and pooled connections fit the server.

curl -s http://localhost:1056/ngsi-ld/v1/version

The connections object of the postgresql component should say "verdict": "sufficient". Connection capacity explains the numbers.

If it goes wrong

• Requests to a tenant nobody has used for a while wait, then get 503

  Other tenants' idle connections take up max_user_connections. Check that the wildcard route has no user=, and that server_idle_timeout is short.

• Steady 503 with Retry-After: 1 under load

  Transactions run out of query_wait_timeout while they queue. Raise max_user_connections if PostgreSQL has room, or raise query_wait_timeout to ride out bursts.

• The broker can't connect through PgBouncer at all

  PgBouncer refuses a startup parameter it doesn't know. Keep the ignore_startup_parameters line above, and check auth_file and auth_type.