Our query timeout had to be set on the Postgres role, because the pooler throws away everything else

Our health endpoint is public. Open cogniprep.app/api/health and you get something like this:

{"status":"healthy","checks":{"database":{"status":"healthy","responseTime":62},...}}

62 milliseconds for a SELECT 1. Every request-path query in the app is an indexed lookup bounded by a LIMIT, and they all look like that. Which is exactly why I did not think to check what happens to a query that does not.

The answer, until recently, was: nothing. It ran until the serverless platform killed the function.

What we actually had

Checked against the live database rather than assumed:

current_user = postgres
pg_roles.rolconfig for postgres       = { search_path }         <- no statement_timeout
pg_roles.rolconfig for authenticator  = { statement_timeout=8s, lock_timeout=8s }

Two conclusions follow, and both of them were news.

The 8 second timeout protects a role we do not use. authenticator is the role PostgREST runs as, which is Supabase's REST API. All of our application data goes through Drizzle and postgres.js as postgres. The reassuring number in the catalog was for a code path the app never takes.

The 2 minute timeout you see in a SQL editor session is the editor's own. It applies statement_timeout = 2min at session level to itself. It is not in the role config, so the app does not inherit it. The effective ceiling for application queries was the database default, which in a default Supabase project is 0, meaning unlimited.

Three ways to set it, two of which do not work

The app connects through Supabase's Transaction Pooler, which is PgBouncer, on port 6543. That single fact eliminates most of the obvious fixes.

A client-issued SET statement_timeout is discarded. In transaction mode PgBouncer hands each transaction to an arbitrary backend and does not carry session state between transactions. There used to be exactly such a call in our connection module. It had been there for a long time, it looked like the responsible thing to do, and it had never once had an effect. Deleting a line that does nothing is the cheapest change in this whole story and the hardest to notice you need.

A startup parameter is worse than useless. postgres.js will happily send connection: { statement_timeout: '15s' }, and PgBouncer rejects startup parameters outside its allowed list. A config tweak turns into a total connection failure.

A role-level setting survives, because Postgres applies it. It lives in the catalog and is applied to every new session by the server, so there is no session state for the pooler to lose:

ALTER ROLE postgres SET statement_timeout = '15s';
ALTER ROLE postgres SET lock_timeout = '5s';
ALTER ROLE postgres SET idle_in_transaction_session_timeout = '30s';

The second and third are not decoration. lock_timeout caps time spent waiting on someone else's lock, which is the other way a request handler hangs forever, and it fires while the query is doing nothing at all. idle_in_transaction_session_timeout reclaims connections abandoned mid-transaction, which is a real event when a serverless function can be killed between BEGIN and COMMIT while holding locks.

Why 15 seconds and not 2

15 looks slack for an app whose queries finish in single-digit milliseconds. Three constraints pushed it there.

The heaviest legitimate query in the codebase is a nine-percentile aggregation in a cron job that scans a game's full score history. It is not a request-path query and it is allowed to take seconds.

The cron routes have a 60 second maxDuration. Keeping the database timeout below that means Postgres cancels the query and the job reports a clean error, instead of the platform killing the function mid-run and leaving you to guess.

And the awkward one: postgres is also the migration role. drizzle-kit uses the same DATABASE_URL, so any role-level timeout applies to migrations too. Most migrations are instant. A CREATE INDEX on a large table is not, and a timeout that kills a migration halfway is a worse outage than the slow query it was protecting you from.

The escape hatch, and why it survives the pooler

drizzle-kit runs each migration inside a transaction, and SET LOCAL is scoped to the transaction, which means transaction pooling cannot lose it. So a migration expected to be slow declares that for itself:

SET LOCAL statement_timeout = '30min';--> statement-breakpoint
CREATE INDEX ...;

CREATE INDEX CONCURRENTLY cannot run inside a transaction at all, so for that one the timeout is raised at session level in the SQL editor instead. Same idea, different scope, because the tool dictates it.

The part I am not pretending is finished

Running the application as postgres means request queries, migrations and manual admin all share one highly privileged role, and that is the only reason the timeout has to accommodate a CREATE INDEX at all. A dedicated least-privilege application role would let the request path be capped at two seconds where it belongs.

That is a bigger change than a timeout: new grants, a new credential, an environment update, and a careful look at what breaks. It is on the list as its own piece of work rather than bundled into this one, because a permissions change smuggled inside a performance fix is how you find out on a Friday which cron job needed a privilege nobody documented.

If you run on a transaction-mode pooler, the thing to take from this is narrower than the timeout value. Check where each of your connection settings is actually applied. Run SELECT rolname, rolconfig FROM pg_roles and compare it against what your application code thinks it is setting. On our database those two answers had disagreed for months, and the one in the catalog is the one that counts.

The endpoint above is a fine place to watch the result: cogniprep.app/api/health reports the database check's own response time on every call, and it has its own 5 second race on top of the role setting, because a health check that hangs is not a health check.