Throttling Tenant Connections on Budget Breach

A tenant that has blown its budget keeps burning compute for as long as its connections stay open, so the fastest way to stop the bleed is to throttle the connections themselves — this page shows the exact psycopg v3 and PgBouncer mechanics to cap a role, drain its live backends, and roll everything back when spend recovers.

Back to: Database Quota Boundary Design

Connection throttling is the immediate-effect lever in a quota boundary. Setting a per-role default_transaction_read_only hold stops new transactions, but a tenant already holding dozens of connections keeps consuming compute until each session ends on its own. Lowering the role’s CONNECTION LIMIT prevents the next connection but does nothing to the ones already open. To actually bring a runaway tenant down to a ceiling now, you combine three moves: cap the role, terminate the backends that exceed the cap, and enforce a matching pool-level limit in PgBouncer so the pooler itself refuses to open more server connections. This is the connection-specific mechanism that the sibling page on enforcing soft and hard quotas in PostgreSQL defers to when its hard tier needs to bite live sessions.

The throttle must be as reversible as it is decisive. A budget breach is often transient — a batch job finishes, a projection corrects, a credit posts — and when spend recovers the tenant should return to unthrottled service with no manual cleanup. Every action below has a paired rollback, and the whole sequence is safe to run repeatedly because each step converges toward a target rather than blindly re-issuing itself.

Prerequisites

  • A role that can terminate backends and alter roles. pg_terminate_backend requires membership in the pg_signal_backend predefined role (or superuser), and lowering another role’s CONNECTION LIMIT requires CREATEROLE with admin over that role. Grant a dedicated quota_admin role exactly those two capabilities and nothing more.

    -- Run once as superuser.
GRANT pg_signal_backend TO quota_admin;      -- lets it call pg_terminate_backend
GRANT tenant_acme TO quota_admin WITH ADMIN OPTION;  -- lets it ALTER the tenant role

  • PgBouncer admin access (optional but recommended). To adjust pool ceilings you need to reach the PgBouncer admin console — connect to the virtual pgbouncer database as a user listed in admin_users, and have write access to pgbouncer.ini to persist per-database limits across restarts.

  • Python 3.10+ and the client libraries.

    pip install "psycopg[binary]>=3.1" "pydantic>=2.6"

  • Connection strings in the environment. export QUOTA_DSN="postgresql://quota_admin@db.internal:5432/appdb" for the database and export PGB_ADMIN_DSN="postgresql://pgbadmin@pgbouncer.internal:6432/pgbouncer" for the pooler admin console. Keep passwords in ~/.pgpass.

Step-by-Step Implementation

The throttle runs in three phases against two surfaces — the database role and the pooler — followed by a rollback path. The sequence below shows the request lifecycle: cap the role, enumerate the tenant’s backends, terminate the ones over the ceiling, then lower the PgBouncer pool.

Connection-throttle sequence: cap the role, terminate excess backends in a loop, then lower the PgBouncer poolThe Controller sends ALTER ROLE CONNECTION LIMIT to PostgreSQL and gets an ack. It queries pg_stat_activity filtered to the tenant role and receives a list of backend pids. A loop over the backends above the ceiling calls pg_terminate_backend per pid, each acknowledged as terminated. The Controller then instructs the PgBouncer admin console to set max_db_connections for the tenant database and RELOAD, which PgBouncer acknowledges.ControllerPostgreSQLPgBouncerloop[pids over ceiling]ALTER ROLE ... CONNECTION LIMIT nackSELECT pid FROM pg_stat_activityactive backend pidspg_terminate_backend(pid)terminatedset max_db_connections + RELOADack

Step 1 — Lower the role’s connection ceiling

The first move is a single ALTER ROLE ... CONNECTION LIMIT. This is the durable ceiling: even after backends are terminated, it stops the tenant’s application from immediately re-opening the slots. Read the current limit first so the change is idempotent.

import os
import logging

import psycopg
from psycopg import sql
from psycopg.rows import dict_row

log = logging.getLogger("throttle")


def set_connection_limit(conn: psycopg.Connection, role: str, limit: int) -> bool:
    """Set the role CONNECTION LIMIT (-1 = unlimited). Returns True if it changed."""
    current = conn.execute(
        "SELECT rolconnlimit FROM pg_roles WHERE rolname = %s", (role,)
    ).fetchone()
    if current is None:
        raise LookupError(f"role {role!r} does not exist")
    if current[0] == limit:
        return False  # already at target — idempotent no-op
    conn.execute(
        sql.SQL("ALTER ROLE {} CONNECTION LIMIT {}").format(
            sql.Identifier(role), sql.Literal(limit)
        )
    )
    log.warning("connection limit for %s set to %s", role, limit)
    return True

>>> set_connection_limit(conn, "tenant_acme", 5)
WARNING:throttle:connection limit for tenant_acme set to 5
True

Step 2 — Terminate the backends that exceed the ceiling

Capping the role does not close existing sessions, so the throttle enumerates the tenant’s live backends from pg_stat_activity and terminates the ones above the new ceiling. Terminate the least-valuable first: idle and idle in transaction sessions before active ones, oldest first, so an in-flight query is the last thing killed. pg_terminate_backend sends SIGTERM and returns a boolean.

def terminate_excess_backends(conn: psycopg.Connection, role: str,
                              keep: int) -> list[int]:
    """Terminate the tenant's backends beyond `keep`, least-valuable first.

    Returns the list of pids that were terminated.
    """
    # Order so we drop idle/idle-in-txn before active, oldest first.
    rows = conn.execute(
        """
        SELECT pid, state, backend_start
        FROM pg_stat_activity
        WHERE usename = %s
          AND pid <> pg_backend_pid()      -- never target our own session
        ORDER BY CASE state
                   WHEN 'idle in transaction (aborted)' THEN 0
                   WHEN 'idle in transaction' THEN 1
                   WHEN 'idle' THEN 2
                   ELSE 3                    -- 'active' last
                 END,
                 backend_start ASC
        """,
        (role,),
    ).fetchall()

    victims = [r["pid"] for r in rows[keep:]]  # everything past the ceiling
    terminated: list[int] = []
    for pid in victims:
        ok = conn.execute(
            "SELECT pg_terminate_backend(%s)", (pid,)
        ).fetchone()[0]
        if ok:
            terminated.append(pid)
    log.warning("terminated %d backend(s) for %s: %s",
                len(terminated), role, terminated)
    return terminated

WARNING:throttle:terminated 3 backend(s) for tenant_acme: [51992, 51987, 51981]

pg_terminate_backend returns true when the signal is delivered, not when the backend has fully exited; a backend in the middle of a long C function may take a moment to notice. For a slow-to-die session, pg_cancel_backend (which only cancels the current query with SIGINT) is the gentler first attempt, escalating to pg_terminate_backend if the pid persists on the next tick.

Step 3 — Cap the PgBouncer per-database pool

If tenants reach PostgreSQL through PgBouncer, the pooler holds its own server-side connections and will happily re-open them right up to its configured pool size. Lower max_db_connections for the tenant’s database so the pooler itself refuses to exceed the ceiling. The durable change is in pgbouncer.ini; the live change is a RELOAD on the admin console.

import configparser


def set_pgbouncer_db_limit(ini_path: str, db_name: str, max_conns: int) -> None:
    """Persist max_db_connections for one database in pgbouncer.ini."""
    parser = configparser.ConfigParser()
    parser.read(ini_path)
    line = dict(
        item.split("=", 1) for item in parser["databases"][db_name].split()
    )
    line["max_db_connections"] = str(max_conns)
    parser["databases"][db_name] = " ".join(f"{k}={v}" for k, v in line.items())
    with open(ini_path, "w") as fh:
        parser.write(fh)


def reload_pgbouncer(admin_dsn: str) -> None:
    """Apply pgbouncer.ini changes live via the admin console.

    The admin console only accepts the simple query protocol, so autocommit
    is required and no parameters may be bound.
    """
    with psycopg.connect(admin_dsn, autocommit=True) as adm:
        adm.execute("RELOAD")
    log.info("pgbouncer reloaded")

INFO:throttle:pgbouncer reloaded

With max_db_connections=5 on the tenant’s database entry, PgBouncer queues or rejects the sixth concurrent server connection regardless of how many clients connect — the throttle then holds at the pool boundary even before a request reaches PostgreSQL.

Step 4 — Orchestrate the throttle and its rollback

The controller applies all three moves on breach and reverses all three on recovery. Because each function reads its target’s current value first, running the controller every tick converges without churn. The throttle path lowers the ceiling, drains the excess, then caps the pool; the restore path lifts the pool, then the ceiling — order matters so the tenant is never briefly capped at the role but unlimited at the pool.

from decimal import Decimal
from pydantic import BaseModel


class ThrottleConfig(BaseModel):
    role: str
    db_name: str
    throttled_limit: int = 5       # connections allowed while over budget
    normal_limit: int = -1         # -1 == unlimited on recovery
    pgb_ini: str = "/etc/pgbouncer/pgbouncer.ini"


def throttle_tenant(db_dsn: str, admin_dsn: str, cfg: ThrottleConfig) -> dict:
    """Apply the full throttle: cap role, drain backends, cap pool."""
    with psycopg.connect(db_dsn, autocommit=True, row_factory=dict_row) as conn:
        changed = set_connection_limit(conn, cfg.role, cfg.throttled_limit)
        killed = terminate_excess_backends(conn, cfg.role, keep=cfg.throttled_limit)
    set_pgbouncer_db_limit(cfg.pgb_ini, cfg.db_name, cfg.throttled_limit)
    reload_pgbouncer(admin_dsn)
    return {"limit_changed": changed, "terminated": killed}


def restore_tenant(db_dsn: str, admin_dsn: str, cfg: ThrottleConfig,
                   pool_normal: int = 50) -> dict:
    """Reverse the throttle when spend recovers: lift pool, then lift role."""
    set_pgbouncer_db_limit(cfg.pgb_ini, cfg.db_name, pool_normal)
    reload_pgbouncer(admin_dsn)
    with psycopg.connect(db_dsn, autocommit=True) as conn:
        changed = set_connection_limit(conn, cfg.role, cfg.normal_limit)
    return {"limit_restored": changed}


if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO)
    cfg = ThrottleConfig(role="tenant_acme", db_name="appdb")
    result = throttle_tenant(os.environ["QUOTA_DSN"],
                             os.environ["PGB_ADMIN_DSN"], cfg)
    print(result)

WARNING:throttle:connection limit for tenant_acme set to 5
WARNING:throttle:terminated 3 backend(s) for tenant_acme: [51992, 51987, 51981]
INFO:throttle:pgbouncer reloaded
{'limit_changed': True, 'terminated': [51992, 51987, 51981]}

Verification

Confirm the ceiling, the backend count, and the pool limit all landed.

# 1. Role ceiling is in place.
psql "$QUOTA_DSN" -tc \
  "SELECT rolconnlimit FROM pg_roles WHERE rolname = 'tenant_acme';"

# 2. Live backend count is at or under the ceiling.
psql "$QUOTA_DSN" -tc \
  "SELECT count(*) FROM pg_stat_activity WHERE usename = 'tenant_acme';"

# 3. PgBouncer pool is capped.
psql "$PGB_ADMIN_DSN" -c "SHOW DATABASES;"

Expected shape — the role limit is 5, the live count has converged to 5 or fewer, and SHOW DATABASES reports the tenant database’s max_connections as 5:

 rolconnlimit
--------------
            5

 count
-------
     5

    name    | host       | max_connections | current_connections
------------+------------+-----------------+---------------------
 appdb      | db.internal|               5 |                   5

A quick assertion for a test harness:

with psycopg.connect(os.environ["QUOTA_DSN"], row_factory=dict_row) as c:
    n = c.execute(
        "SELECT count(*) AS n FROM pg_stat_activity WHERE usename = %s",
        ("tenant_acme",),
    ).fetchone()["n"]
    assert n <= 5, f"throttle did not converge: {n} backends still open"

Gotchas & Edge Cases

  • pg_terminate_backend needs the right privilege. Without membership in pg_signal_backend (or superuser), the call returns an error, not false. A quota_admin missing that grant silently fails to drain — verify the grant is in place, since the throttle looks like it is working while backends survive.
  • Never terminate your own backend. The pid <> pg_backend_pid() guard is not optional; without it a controller pooled onto the same role can terminate its own connection mid-loop and abort the throttle halfway through.
  • The application will reconnect instantly. Terminating backends without first lowering CONNECTION LIMIT (and the pool) is pointless — a healthy client reconnects in milliseconds. Always cap before you drain, which is why Step 1 precedes Step 2.
  • PgBouncer admin only speaks the simple query protocol. RELOAD, SHOW, and SET on the admin console reject the extended protocol and bound parameters. Connect with autocommit=True and pass literal SQL; a normal parameterized execute will fail against the pgbouncer database.
  • max_db_connections limits server connections, not clients. In transaction-pooling mode many clients share few server connections, so capping max_db_connections throttles concurrency without necessarily refusing client connects — clients queue instead. If you need to refuse clients outright, lower max_client_conn or PAUSE the database.
  • Terminating an idle in transaction backend rolls back its work. That is usually the intent when reclaiming a leaked transaction, but confirm the tenant is not mid-migration before draining; correlate with the live-session view described in extracting pg_stat_activity for cost tracking.
  • Restore in the reverse order you throttled. Lift the pool ceiling before the role ceiling; if you lift the role first, the tenant’s clients rush in and immediately hit the still-low pool limit, producing a confusing burst of pool-exhaustion errors during recovery.
  • A raw throttle is not a notification. Draining a tenant’s connections is disruptive enough that owners must hear about it; emit the breach signal to alert routing and escalation so the on-call and the tenant learn why their connections dropped.

Frequently Asked Questions

Why lower CONNECTION LIMIT and terminate backends, rather than just one?

They solve different halves of the problem. CONNECTION LIMIT is a forward-looking gate: it stops the next connection but ignores every session already open. pg_terminate_backend is retroactive: it closes existing sessions but does nothing to prevent immediate reconnection. Only the two together bring a tenant that is already over-committed down to a ceiling and keep it there.

Should I use pg_cancel_backend or pg_terminate_backend?

pg_cancel_backend sends SIGINT and cancels only the current query, leaving the connection alive — gentler, and often enough to stop a runaway query without dropping the session. pg_terminate_backend sends SIGTERM and closes the whole backend. A good escalation is to cancel first, then terminate on the next tick any pid that is still consuming, so you disrupt the tenant as little as the budget breach allows.

Does this work behind PgBouncer in transaction-pooling mode?

Yes, but the surfaces differ. The role CONNECTION LIMIT and backend termination act on the PostgreSQL side, while max_db_connections acts on the pooler. In transaction pooling the pooler multiplexes many clients over few server connections, so the pool cap is what actually bounds tenant concurrency; the role cap is a backstop for direct connections that bypass the pooler.

How do I make the throttle idempotent?

Each function reads its target’s current value before writing: set_connection_limit checks rolconnlimit, terminate_excess_backends only kills sessions beyond the ceiling, and the PgBouncer step writes a config value and reloads. Running the controller repeatedly therefore converges toward the target state instead of re-issuing identical changes, so a per-minute loop is safe.

What happens to in-flight transactions when I terminate a backend?

The backend receives SIGTERM, its current transaction is rolled back, and the client sees the connection drop with an AdminShutdown-class error. No committed data is lost, but uncommitted work in that session is discarded. This is why the termination order drains idle and idle in transaction sessions first and leaves active sessions for last.

Back to: Database Quota Boundary Design