6 ms·

Feature request that would make me consider moving off of pgbouncer: make prepared statements work in transaction pooling mode. We've found that we can double

Feature request that would make me consider moving off of pgbouncer: make prepared statements work in transaction pooling mode. We've found that we can double our throughput by using prepared statements, but can't do so because we use pgbouncer in transaction pooling mode. Our newer systems (on Rust) bypass pgbouncer in favor of an in process pool so we get to take advantage of prepared statements there, but our legacy systems would benefit a lot from it.

chasers 4y ago
Can your client use the `binary_parameters` param? e.g. https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/ 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.
- phamilton 4y ago
  I don't believe that provides the performance improvements we desire. 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.
  - chasers 4y ago
    Yeah what you're looking for is "unnamed prepared statements". If your client supports this then you can use PgBouncer and get the throughput benefits. 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.
    - phamilton 4y ago
      I'm not sure I understand. Are you saying unnamed prepared statements would enable query plan caching? I don't understand how that would work.
      - chasers 4y ago
        I assumed it did because I would think Postgres caches the query plan by some statement hash not just by name. Anyways, I'm probably wrong here but it looks like I'm about to figure this out now :D
      - chasers 4y ago
        I will say that we are trying to keep the connection to the database open as long as possible and only disconnect when necessary. PgBouncer's db connections are more dynamic. 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.
        phamilton 4y ago
        Postgres does no query planning caching outside of per prepared statement. From the docs: 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. https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html
- chasers 4y ago
  > 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. Ah `binary_parameters` is just this Go lib thing. Ecto just sends unnamed prepared statements where (TiL) query plans are not cached.
sitharus 4y ago
That would be very difficult to do, Postgres prepared statements are attached to connections so prepared statements would require connection pinning of some sort. It would be nice to have a wider scope for prepared statements.
- phamilton 4y ago
  I understand why it's difficult, but most in-process connection poolers will manage prepared statements for you even as the connection pool grows and shrinks. I know it's more complicated, but I imagine pgbouncer could detect "prepare foo as..." and "execute foo(...)" and track whether a given session has had "foo" prepared. If not, rerun the "prepare foo as ..." on a session if "execute foo(...)" is received. That's essentially what the in-process version does. There would be some complications around conflicts for a given named prepared statement, but that could be solved with some requirements on clients (e.g. use source hash in prepared statement names).
  - chasers 4y ago
    I’m thinking can just use :erlang.phash2 to pin a client connection process to a db connection process. The only thing is a slow query could block a db connection for others pinned to it.
    - phamilton 4y ago
      Interesting approach, but that would be messy when multiple instances of an application all try to prepare the same query with the same name on the same server connection. Clients would need to know that an "prepared statement with that name already exists" error is ok and they should go ahead and execute it. Should we take this discussion into a GitHub issue?
      - chasers 4y ago
        For sure > https://github.com/supabase/supavisor/issues/69 https://github.com/supabase/supavisor/issues/69
vira28 4y ago
There are existing patches to support prepared statements in PgBouncer https://github.com/pgbouncer/pgbouncer/pull/510 https://github.com/pgbouncer/pgbouncer/pull/510 https://github.com/pgbouncer/pgbouncer/issues/695 https://github.com/pgbouncer/pgbouncer/issues/695 FWIW, we are running a modified patch.
- phamilton 4y ago
  Interesting PR. It looks like https://github.com/pgbouncer/pgbouncer/pull/757 https://github.com/pgbouncer/pgbouncer/pull/757 is more inline with multiple clients using the same prepared statements. I'll be watching that closely.

chasers 4y ago

Can your client use the `binary_parameters` param? e.g. https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/ 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.

phamilton 4y ago
I don't believe that provides the performance improvements we desire. 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.
- chasers 4y ago
  Yeah what you're looking for is "unnamed prepared statements". If your client supports this then you can use PgBouncer and get the throughput benefits. 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.
  - phamilton 4y ago
    I'm not sure I understand. Are you saying unnamed prepared statements would enable query plan caching? I don't understand how that would work.
    - chasers 4y ago
      I assumed it did because I would think Postgres caches the query plan by some statement hash not just by name. Anyways, I'm probably wrong here but it looks like I'm about to figure this out now :D
    - chasers 4y ago
      I will say that we are trying to keep the connection to the database open as long as possible and only disconnect when necessary. PgBouncer's db connections are more dynamic. 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.
      - phamilton 4y ago
        Postgres does no query planning caching outside of per prepared statement. From the docs: 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. https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html
chasers 4y ago
> 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. Ah `binary_parameters` is just this Go lib thing. Ecto just sends unnamed prepared statements where (TiL) query plans are not cached.

sitharus 4y ago

That would be very difficult to do, Postgres prepared statements are attached to connections so prepared statements would require connection pinning of some sort. It would be nice to have a wider scope for prepared statements.

phamilton 4y ago
I understand why it's difficult, but most in-process connection poolers will manage prepared statements for you even as the connection pool grows and shrinks. I know it's more complicated, but I imagine pgbouncer could detect "prepare foo as..." and "execute foo(...)" and track whether a given session has had "foo" prepared. If not, rerun the "prepare foo as ..." on a session if "execute foo(...)" is received. That's essentially what the in-process version does. There would be some complications around conflicts for a given named prepared statement, but that could be solved with some requirements on clients (e.g. use source hash in prepared statement names).
- chasers 4y ago
  I’m thinking can just use :erlang.phash2 to pin a client connection process to a db connection process. The only thing is a slow query could block a db connection for others pinned to it.
  - phamilton 4y ago
    Interesting approach, but that would be messy when multiple instances of an application all try to prepare the same query with the same name on the same server connection. Clients would need to know that an "prepared statement with that name already exists" error is ok and they should go ahead and execute it. Should we take this discussion into a GitHub issue?
    - chasers 4y ago
      For sure > https://github.com/supabase/supavisor/issues/69 https://github.com/supabase/supavisor/issues/69

vira28 4y ago

There are existing patches to support prepared statements in PgBouncer https://github.com/pgbouncer/pgbouncer/pull/510 https://github.com/pgbouncer/pgbouncer/pull/510 https://github.com/pgbouncer/pgbouncer/issues/695 https://github.com/pgbouncer/pgbouncer/issues/695 FWIW, we are running a modified patch.

phamilton 4y ago
Interesting PR. It looks like https://github.com/pgbouncer/pgbouncer/pull/757 https://github.com/pgbouncer/pgbouncer/pull/757 is more inline with multiple clients using the same prepared statements. I'll be watching that closely.