Setting Session State with Connection Events

This guide is part of ORM Connection Lifecycle Hooks. Every ORM offers a hook that runs when a connection is created and another that runs when it is checked out, and both are used for the same purpose: applying settings the application wants on every database session — application_name, statement_timeout, a search path, a time zone, a role. The choice between the two hooks has consequences that only appear under load or behind a proxy:

# a checkout hook running SET on every borrow
SELECT calls, query FROM pg_stat_statements ORDER BY calls DESC LIMIT 3;
 4812330 | SET application_name = $1
 4812330 | SET statement_timeout = $1
  980114 | SELECT id, total FROM orders WHERE customer_id = $1

Two thirds of all statements are session setup. The objective is to apply each setting at the right layer — server-side defaults, connect-time hooks or per-transaction statements — so that none of them runs on the hot path and none of them leaks between users behind a pooler.

Rapid incident diagnosis

Find out what runs per borrow. The database’s statement statistics show it immediately: SET statements with a call count close to the request count are checkout hooks, while a count close to the connection count are connect hooks.

SELECT calls, mean_exec_time, query FROM pg_stat_statements
WHERE query ILIKE 'set %' OR query ILIKE 'select set_config%'
ORDER BY calls DESC LIMIT 10;

Locate the hooks in code.

grep -rn "event.listens_for\|@event.listens_for" --include=*.py .          # SQLAlchemy
grep -rn "connectionInitSql\|ConnectionPreparer\|afterAcquire" --include=*.java --include=*.yml .
grep -rn "ActiveSupport::Notifications\|after_initialize\|connected?" --include=*.rb .

Check for state that must not persist. A setting applied at connect time persists for the life of the connection and is shared by every later borrower of that connection in the same pool — which is correct for application_name and wrong for anything user- or tenant-specific:

-- through a pooler, in autocommit, run several times: values must never differ
SELECT current_setting('app.tenant_id', true), current_setting('search_path');

Watch for pinning behind a proxy. RDS Proxy pins a session when it sees a session-level SET; PgBouncer’s transaction mode leaks it to the next borrower. Both are visible from the proxy’s own metrics, and both are caused by hooks that should have been server-side defaults.

Mathematical sizing or parameter formula

The cost of each layer is the number of round trips it adds:

role_default      : 0 extra round trips
connect_hook      : statements × connections_created_per_second
checkout_hook     : statements × borrows_per_second
transaction_scope : statements × transactions_per_second

A service at 2,000 borrows per second with two settings applied at checkout adds 4,000 round trips per second — often more traffic than the queries themselves. The same two settings at connect time, with connections created at 0.2 per second, add 0.4 round trips per second.

Latency cost per request. Each extra statement adds a network round trip to the request:

added_latency ≈ statements_per_borrow × round_trip_time

With a 0.4 ms round trip and two settings, every request pays 0.8 ms before its first query — which on a 5 ms request is a 16 % overhead.

Pool capacity cost. Because hold time rises, the pool needs proportionally more connections:

extra_connections ≈ requests_per_second × statements_per_borrow × round_trip_time

At 2,000 requests per second, two settings and 0.4 ms: 2000 × 2 × 0.0004 = 1.6 connections consumed purely by session setup.

When a checkout hook is genuinely needed. If a setting must vary per borrower — a tenant identifier, a user role — the hook is the wrong tool regardless of cost, because the value outlives the borrow. Such settings belong inside the transaction:

per-request state → SET LOCAL inside the transaction, never a checkout hook

Exact remediation & configuration

Prefer server-side defaults. Anything constant for a role belongs on the role, where it costs nothing and survives every pooling mode:

ALTER ROLE orders_app SET application_name = 'orders-api';
ALTER ROLE orders_app SET statement_timeout = '2s';
ALTER ROLE orders_app SET lock_timeout = '1s';
ALTER ROLE orders_app SET search_path = 'public';

SQLAlchemy: use connect, not checkout. The connect event fires once per DBAPI connection:

from sqlalchemy import event

@event.listens_for(engine, "connect")
def on_connect(dbapi_connection, connection_record):
    # runs once per physical connection
    with dbapi_connection.cursor() as cur:
        cur.execute("SET application_name = 'orders-api'")
    dbapi_connection.commit()

@event.listens_for(engine, "checkout")
def on_checkout(dbapi_connection, connection_record, connection_proxy):
    # keep this empty or nearly so; it runs on every borrow
    pass

Where a driver supports it, passing the settings in the connection string avoids even the connect hook:

engine = create_engine(
    "postgresql+psycopg://orders_app@db/orders"
    "?application_name=orders-api&options=-c%20statement_timeout%3D2s"
)

Hibernate and Spring Boot: connectionInitSql runs once per connection:

spring:
  datasource:
    hikari:
      connection-init-sql: "SET application_name = 'orders-api'"

For values that must vary per transaction, use a transaction-scoped statement in the service layer rather than any pool hook:

@Transactional
public List<Invoice> forTenant(UUID tenantId) {
    jdbc.queryForObject("SELECT set_config('app.tenant_id', ?, true)", String.class, tenantId.toString());
    return repo.findAll();
}

ActiveRecord: use the connection configuration, not a per-checkout callback:

# config/database.yml
production:
  adapter: postgresql
  url: <%= ENV["DATABASE_URL"] %>
  variables:
    application_name: orders-api
    statement_timeout: 2000

variables are applied when the connection is established, which is the ActiveRecord equivalent of a connect hook.

Keep per-request state transactional. Tenant identifiers, user ids and row-level security context must be set with SET LOCAL or set_config(..., true) inside the transaction that uses them. The full pattern, including the fail-closed policy that makes a missing value safe, is in Setting Tenant Context for Row-Level Security Under Transaction Pooling.

Clean up on return only when necessary. If a connect hook sets something a later borrower must not see, the design is already wrong. Reset hooks — SQLAlchemy’s reset, Hikari’s lack of one — should not be load-bearing for correctness.

Auditing what each layer applies

Over time, settings accumulate in several layers at once, and the effective value becomes hard to predict.

Read the effective settings from a pooled connection. Connect as the application role through the same path the application uses:

SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name IN ('application_name','statement_timeout','lock_timeout','search_path','idle_in_transaction_session_timeout');

The source column distinguishes a role default (database user) from a session SET (session) from the configuration file.

List role and database defaults explicitly:

SELECT r.rolname, d.datname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase;

Keep one inventory. A short table in the service’s documentation, listing every session setting, the layer that applies it and why, prevents the same setting being applied twice with different values:

Re-audit when adding a proxy. Moving behind PgBouncer or RDS Proxy changes which layers are safe: connect hooks still work, checkout hooks become per-transaction at best, and anything session-scoped set by the application becomes either a leak or a pinning cause.

Validation & verification

No SET on the hot path. After the change, session-setting statements should have a call count proportional to connections, not to requests:

SELECT calls, query FROM pg_stat_statements WHERE query ILIKE 'set %' ORDER BY calls DESC LIMIT 5;

Settings are actually in effect. From a pooled connection, confirm the values and their source:

SELECT current_setting('application_name'), current_setting('statement_timeout');

Nothing per-request persists. Through a pooler, in autocommit, read the per-request settings repeatedly; they must always be empty:

for i in $(seq 1 100); do
  psql "host=pgbouncer.internal port=6432 dbname=orders user=orders_app" -Atc \
    "SELECT coalesce(nullif(current_setting('app.tenant_id', true), ''), '<none>')"
done | sort | uniq -c

Latency improved measurably. Compare p50 request latency before and after removing checkout hooks; the difference should be close to statements_per_borrow × round_trip_time.

Frequently Asked Questions

Related

• ORM Connection Lifecycle Hooks — the parent guide to the events themselves.
• Setting Tenant Context for Row-Level Security Under Transaction Pooling — the safe pattern for per-request state.
• Configuring PostgreSQL Session Timeouts Without Breaking Connection Pools — setting timeouts at the role layer.
• Resolving RDS Proxy Session Pinning — what session-level settings cost behind a managed proxy.