e.g. https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-sta...
Elixir's Ecto can (and that Go example) so if you use Elixir (or Go) you can use prepared statements with PgBouncer (or Supavisor!) in transaction mode.
The main reason prepared statements improves throughout is that it caches query plans.
As I understand it, binary_parameters does not do that. Each query is parsed and evaluated individually (perhaps equivalent to unnamed prepared statements) and so nothing is saved when you execute the same query again.
Maybe binary_parameters is a different thing. Need to look into it more but this is a good one to add to the docs. And maybe officially support named prepared statements natively if lots of clients don't support unnamed prepared statements but definitely seems like we'd have to do much more accounting.
Anyways, I'm probably wrong here but it looks like I'm about to figure this out now :D
So if Postgres is doing query plan caching by session then caches would be built up as that query hits other db connections. In theory, this should be better.
A prepared statement can be executed with either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call. Use of a generic plan avoids planning overhead, but in some situations a custom plan will be much more efficient to execute because the planner can make use of knowledge of the parameter values. (Of course, if the prepared statement has no parameters, then this is moot and a generic plan is always used.)
By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.
"If successfully created, a named prepared-statement object lasts till the end of the current session, unless explicitly destroyed. An unnamed prepared statement lasts only until the next Parse statement specifying the unnamed statement as destination is issued."
https://www.postgresql.org/docs/current/protocol-flow.html#P...
Conclusion: I still want named prepared statements to take advantage of generic plans.
Ah `binary_parameters` is just this Go lib thing. Ecto just sends unnamed prepared statements where (TiL) query plans are not cached.