Cheatsheet for running Postgres alongside SQLAlchemy in production.

PgBouncer config

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=primary dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600
query_wait_timeout = 30

pool_mode = transaction: each transaction gets a backend; max multiplexing.

App connects to PgBouncer

engine = create_async_engine(
    "postgresql+asyncpg://app@pgbouncer:6432/mydb",
    pool_size=10, max_overflow=10,
    pool_pre_ping=True, pool_recycle=300,
    connect_args={
        "statement_cache_size": 0,
        "prepared_statement_cache_size": 0,
        "server_settings": {"jit": "off"},
    },
)

Required tweaks for transaction-pool mode.

Role separation

-- App role (no superuser; no BYPASSRLS)
CREATE ROLE app_user WITH LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Migration role (DDL)
CREATE ROLE app_migrator WITH LOGIN PASSWORD '...';
GRANT ALL ON DATABASE mydb TO app_migrator;

-- Admin role
CREATE ROLE app_admin WITH LOGIN PASSWORD '...' BYPASSRLS;

App connects as app_user. Alembic as app_migrator. Admin paths as app_admin.

Statement timeout

ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

App queries auto-killed after 30s. Idle transactions killed after 60s.

For specific endpoints needing more:

async with session.begin():
    await session.execute(text("SET LOCAL statement_timeout = '5min'"))
    await session.execute(slow_stmt)

max_connections sizing

SHOW max_connections;             -- usually 100 default

Math:

workers × replicas × (pool_size + max_overflow) ≤ max_connections - admin_reserve

For 4 workers × 5 replicas × 20 pool = 400. Default 100 won’t cut it.

Options:

  • Raise max_connections to 500 (RAM cost ~10MB per conn).
  • PgBouncer to multiplex.
  • Smaller per-replica pool.

Connection limits per role

ALTER ROLE app_user CONNECTION LIMIT 200;

Per-role cap. Useful if multiple apps share a DB.

work_mem

ALTER SYSTEM SET work_mem = '64MB';        -- per sort/hash, per connection

Higher = faster complex queries. Lower = more connections fit.

For specific heavy queries:

await session.execute(text("SET LOCAL work_mem = '256MB'"))
await session.execute(complex_stmt)

maintenance_work_mem

ALTER SYSTEM SET maintenance_work_mem = '512MB';

For VACUUM, CREATE INDEX. Higher = faster maintenance.

shared_buffers

ALTER SYSTEM SET shared_buffers = '4GB';   -- ~25% of RAM

Postgres buffer cache. Restart required.

effective_cache_size

ALTER SYSTEM SET effective_cache_size = '12GB';   -- ~75% of RAM

Hint to planner about OS cache. No reset needed.

Connection counting

SELECT
    state, count(*)
FROM pg_stat_activity
WHERE datname = 'mydb'
GROUP BY state;

active / idle / idle in transaction / idle in transaction (aborted).

Long-running idle in transaction

SELECT pid, query, age(now(), state_change) AS idle_for
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change;

Likely app forgot to commit. Kill:

SELECT pg_terminate_backend(pid);

Lock timeouts

ALTER ROLE app_user SET lock_timeout = '5s';

Queries that wait > 5s for a lock get killed.

tcp_keepalives

In postgresql.conf:

tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 6

Catch dead connections (NAT timeouts, network issues).

Monitoring queries

  • pg_stat_activity — current activity.
  • pg_stat_statements — top queries.
  • pg_stat_user_tables — table stats.
  • pg_stat_user_indexes — index usage.
  • pg_stat_replication — replication state.
  • pg_locks — current locks.

Wrap in Prometheus exporters (postgres_exporter).

Common mistakes

  • App connects as superuser — security risk.
  • No statement_timeout — runaway queries lock up.
  • max_connections too low for pool — connection errors.
  • shared_buffers default (128MB) on big servers.
  • No tcp_keepalives — phantom idle connections.

Read this next

If you want my postgresql.conf + PgBouncer config templates, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .