Poisoned Postgres connection pools

# Poisoned Postgres connection pools — PlanetScale Source: [https://planetscale.com/blog/postgres-poisoned-connection-pools](https://planetscale.com/blog/postgres-poisoned-connection-pools) Did you know you can poison your Postgres connection pool? Most people have no idea what this means, but it could take down your entire database if you're not careful managing your connection pooler\.An engineer's worst nightmare is waking up to a seemingly read\-only database with no clear issue in sight\. Unfortunately, this is exactly what one of our discord friends ran into on a Tuesday evening\. At PlanetScale, we've helped many customers debug this issue across a variety of programming languages, ORMs, and app platforms\. All of them boil down to complex interactions with connection pooling\. ## [What is a connection pool](https://planetscale.com/blog/postgres-poisoned-connection-pools#what-is-a-connection-pool) Connection pools are what let Postgres scale to thousands of simultaneous connections without overwhelming it with too many query\-processing backends\. [PgBouncer](https://planetscale.com/docs/postgres/connecting/pgbouncer)is the most common connection pooler for Postgres, and is what powers PlanetScale Postgres clusters\.PgBouncer maintains multiple connections to the database, and multiplexes client connections over them\.This is how 1,000 clients can connect to a Postgres instance while only using 20\-50 direct connections under the hood\. ## [What are poisoned connection pools](https://planetscale.com/blog/postgres-poisoned-connection-pools#what-are-poisoned-connection-pools) When PgBouncer runs in transaction mode, the most practical mode for the majority of apps and the default for PlanetScale clusters, each underlying connection can be reused across multiple clients\.This is what allows them to use a lower number of direct connections through PgBouncer\. Each new client has an ability to set attributes throughout the lifecycle of its queries to the database\. When a previous client leaves the pool in a undesired state, it can impact future client queries from working properly, leaving that pool "poisoned" with stale state\. Often, this leaked state will cause major disruptions for future queries\.If a query set`default\_transaction\_read\_only = on`or applied session characteristics such as`SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY`, the underlying connection will be stuck with that state\. For one customer I was helping, the latter case caused their PgBouncer connections to get stuck as read\-only sessions\.In one route in their API, they had set`SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY`for a read only transaction, unknowingly forcing the session into read only\-mode permanently, even outside the transaction\. Every time another API request was run after that route was hit, PgBouncer reused the previous underlying connection that was still set to read\-only, causing a flood of errors\. If you have ever seen Postgres error code`25006`or seen writes fail with the below error, you have probably poisoned your pool\. ``` ERROR: cannot execute INSERT in a read-only transaction ``` Note Poisoned pools have different errors than a read\-only database cluster\.If your database is in read\-only mode due to disk usage, you will see the following error: ``` pg_readonly: invalid statement because cluster is read-only ``` This error not only shows up from poisoned pools, but can also be returned from attempts to send write queries to a replica\.The first thing to check if you think you have poisoned pools is to ensure the errors are not from a replica\. With PlanetScale Insights under the Errors tab, you can see a breakdown of every SQL error from the primary or replica\. ## [The antidote to poisoned pools](https://planetscale.com/blog/postgres-poisoned-connection-pools#the-antidote-to-poisoned-pools) The quick fix to restore a poisoned pool is to execute`DISCARD ALL`\.`DISCARD ALL`resets the connection state to its defaults, removing`default\_transaction\_read\_only = on`and any other session settings\. This fix needs to be applied to every connection the pooler holds, because it's hard to tell which individual connection\(s\) are at fault\.This can be accomplished by creating a script that calls both in parallel using as many connections as possible, or using the`pscale`cli\. `pscale branch connections top \[database\] \[branch\]`lets you see every session currently running, allowing you to kill specific sessions you suspect may be poisoned\. The next step is fixing the application code that is causing the leaks in the first place\. ### [Quick fix with PlanetScale's MCP](https://planetscale.com/blog/postgres-poisoned-connection-pools#quick-fix-with-planetscales-mcp) [PlanetScale's MCP](https://planetscale.com/docs/connect/mcp)server can see query insights, errors, and even mint temporary connection strings to check for stuck session variables\.Combining PlanetScale's MCP with the context of your codebase, an agent can quickly find where your codebase is accidentally modifying session state\. If you have a poisoned pool, set up the PlanetScale MCP server and instruct your agent to find and fix the suspected culprit with the following prompt: ``` Use the PlanetScale MCP server and this repository to find and fix connection pools stuck in read-only mode (25006 / cannot execute INSERT in a read-only transaction). Search for session-level SET default_transaction_read_only, abandoned BEGIN transactions, and timeout paths that skip ROLLBACK. Prefer transaction-scoped read-only with strict timeouts, or replica connections. Reset with ROLLBACK or DISCARD ALL. ``` You can also point an agent at the PlanetScale[documentation on read\-only](https://planetscale.com/docs/postgres/troubleshooting/read-only-mode#read-only-transactions-vs-cluster-read-only-mode)issues for more context\. ## [Preventing read\-only poisoned pools](https://planetscale.com/blog/postgres-poisoned-connection-pools#preventing-read-only-poisoned-pools) To prevent read\-only poisoned connection pools, the easiest solution is to send read traffic to a replica instead of enforcing read\-only on primary transactions\.When it's not possible to target a replica, ensure all transactions have strict timeouts and shouldn't set session variables such as`default\_transaction\_read\_only`when connecting to the database through PgBouncer\. Refactoring a codebase can be a daunting task, but ORMs like Drizzle can make this easy with[replica routing](https://orm.drizzle.team/docs/read-replicas)\.Combined with the PlanetScale MCP, keeping your application safe from poisoned pools can be an afternoon of work instead of a month long refactor\. If you want to keep your connection pool healthy and your application online, install our[MCP server](https://planetscale.com/docs/connect/mcp)and see if you're in danger of poisoned queries today\.