-
-
Notifications
You must be signed in to change notification settings - Fork 56
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Support named prepared statements #69
Comments
To clarify this case: We generally want both clients to use the same prepared statement. A classic "one process per request" infrastructure often means thousands of client connections and hundreds of server connections. Let's assume 10:1. If each of those processes handles 100 requests before getting cycled, then a given query used once in each request would use 5 custom plans and 95 generic plans for a 95% cache hit rate. If that prepared_statement is available to all clients using that server connection then we'd instead see a 99.5% hit rate. |
For sure. I think it really depends on how the client handles things though. Do they recreate the prepared statement on a connection if it doesn't exist? How do they name them? I'm thinking a separate pool mode which pins client connections to database connections would be good. So |
For reference: a PR for pgbouncer that claims to make things work: pgbouncer/pgbouncer#757 (comment) |
Very helpful! Thank you! |
PgBouncer actually just merged support for this: pgbouncer/pgbouncer#845 (Not released yet, but in main branch) |
Would this be released on existing Supabase's cloud hosted databases? I would really like to use it with my Supabase database, it is currently using supavisor as its connection pool. |
+1 for this since pgbouncer has a solution and since supabase forces you to use supavisor now it seems to be worth wild to add |
Done, and deployed to production!! |
I feel like this issue should probably be re-opened given that this doesn't add support for protocol level named prepared statements (which are the ones that basically all client libraries use). Related issue and my comment there can be found here: #239 (comment) |
No pooler currently supports named prepared statements. Lots of SQL clients which use their own pool do e.g. Elixir's Ecto uses named prepared statements by default and ships with it's own pooling logic.
It seems reasonable for a pooler to be able to support them. Named prepared statements can increase throughput significantly because the query plan is cached.
Unnamed prepared statements do not use a cached query plan because it is deallocated with the next unnamed prepared statement.
Questions
What if two different clients linked to one pg connection make two different prepared statements with the same name?
Because it's an async protocol can we add metadata to messages that Postgres will include coming back? Then we can use that to route the right messages to the right clients.
Can we use pipelining to multiplex?
Ideas
There would be some contention here probably because clients would have to wait on a specific conn but it would work. Question is does the contention negate the perf from the prepared statement. With another mode
transaction
pooling would work as expected but you could use this new mode if it works for your workload and provides perf gains.The text was updated successfully, but these errors were encountered: