On a Tuesday afternoon in September every service that uses our orders database stopped for twenty six minutes. pg_stat_activity showed forty queries from the customer search endpoint, the oldest running for four minutes, under a database role whose statement_timeout is five seconds.
That timeout is set on the role with ALTER ROLE, and Postgres applies it to every new session the role opens. Our services reach the database through PgBouncer in transaction pooling mode, with forty server connections shared between all of them. In the summer the hourly sales export for finance moved onto our platform and was given the same role. Its queries take minutes, so its first statement was SET statement_timeout = 0.
In transaction pooling a client holds a server connection only for the length of one transaction. The SET ran as a transaction of its own, the connection went back to the pool with no timeout, and the next client to be handed it, usually a web request, inherited the setting. PgBouncer normally cleans a connection with DISCARD ALL before reusing it, but the documentation is clear that this reset query is not used in transaction pooling, because clients are not supposed to use session features at all. Every hour the export left a few connections without a timeout, and each lasted until PgBouncer retired it, up to an hour later.
None of that mattered until a scraper started calling our search endpoint with single letters, which produce a query that runs for minutes. On a normal connection it was cancelled at five seconds. On the poisoned ones it kept going, held its server connection, and the pool filled with them until every other request was waiting.
The export now has its own role, its own pool and a long timeout set on that role. Anything that needs a different setting for one transaction uses SET LOCAL, which ends with the transaction. PgBouncer has a query_timeout as a backstop for everything. A check in CI fails any code that issues a session level SET through the pooler. And we alert on any active query older than thirty seconds, by role.
Behind a transaction pooler a session belongs to whoever uses the connection next. A setting we changed for one job became the setting for strangers.
– Sergey Shinder
