Scalable PostgreSQL Connection Pooler
Question for anyone who knows a lot about Postgres connection pooling:<p>We want to move to using a connection pooling infrastructure component, but so far, we haven&#x27;t been able to figure out how to do so given our setup.<p>We&#x27;ve got a wacky data model where we have N Postgres schemas, each of which has the same tables (but with different data, obviously.) They&#x27;re not tenants; more like separate &quot;datasets.&quot; Picture something like: each schema is a digraph, with tables like &quot;vertices&quot;, &quot;edges&quot;, &quot;edge_labels&quot;, etc. (Not what we&#x27;re doing, but it&#x27;s close.)<p>In theory, we could solve our problem by just sticking all the &quot;vertices&quot; tables together as list-partitions of one mega &quot;vertices&quot; table keyed by dataset_id; but 1. it&#x27;s kind of incoherent — the data makes no sense when combined together like that, it&#x27;s only valid when considered in isolation; and 2. it&#x27;s very operationally convenient for us to manage datasets as separate schemas, re: security, naming, onlining&#x2F;offlining data, etc.<p>Also, while each user request is only going to interact with a single dataset (schema), a given user might care about a lot of datasets, and make requests about many of them at arbitrary times—so, as far as I can tell, we don&#x27;t have the sort of stable load per dataset that would allow us to hold separate connection pools per dataset.<p><i>And</i>, since there&#x27;s no way to make a schema name into a bind variable, fully-qualifying our queries means generating dynamic SQL strings, which is both expensive on the business layer, and on the SQL query planner, which can&#x27;t just recognize+unify its plans from the query&#x27;s history from other schemas.<p>Right now, the way we query the data, is that in our business layer, before each query, we have a middleware that injects a statement like this:<p><pre><code>    SET search_path TO &#x27;name_of_dataset&#x27;, &#x27;public&#x27;;
</code></pre>
We then run a non-fully-schema-qualified query, and it finds whichever tables that have been made visible to it.<p>Our queries currently don&#x27;t run in transactions (i.e. we&#x27;re using JDBC auto-commit on the client side), because they&#x27;re by-and-large just single (complex) SELECT statements. So the middleware-generated statement above (for now) runs as a separate statement, in a separate single-statement transaction, on the same connection, that runs the query. So per-statement connection-pooling would break everything, as our queries would be being routed to backend connections &quot;primed&quot; with the wrong search_path.<p>And I get the feeling that <i>transaction</i>-level connection pooling won&#x27;t work for us either (at least for now), because our whole operational problem <i>currently</i> is that our HTTP requests acquire DB connections and then sit on them while other things are processed, depleting the connection pool; and if we turned off JDBC auto-commit, our search_path-injecting middleware (which injects its statement on connection-pool checkout) would just end up <i>starting a DB transaction</i> at the beginning of each of those HTTP requests, where the business layer would then be sitting around with an <i>idle in transaction</i> DB connection, mapping directly to an open pgBouncer backend connection, completely destroying any wins connection-pooling would gain us. We&#x27;d be right back where we started.<p>So, in other words, we want&#x2F;need <i>per-statement</i> pooling; but we need it to allow us to also specify the search path <i>per statement</i>.<p>I&#x27;ve seen that at least pgBouncer has an explicit WONTFIX for this requirement, since in their minds it conflicts with their &quot;can&#x27;t tell it apart from a regular Postgres session&quot; session-state semantics.<p>Should we bite the bullet and move to putting everything in one schema + list-partitioning + an explicit dataset_id column, so that we <i>can</i> parameterize out the dataset per-query using a bind variable? Or something else?