Support named prepared statements

[chasers]

Support named prepared statements

[softwarecurator]

+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

[janpio]

PgBouncer actually just merged support for this: pgbouncer/pgbouncer#845 (Not released yet, but in main branch)

[hamiltop]

What if two different clients linked to one pg connection make two different prepared statements with the same name?

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.

[chasers]

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 transaction mode behaves exactly like PgBouncer transaction mode and you could use this other mode if it's suitable for your workload and provides perf improvements.

[hamiltop]

For reference: a PR for pgbouncer that claims to make things work: pgbouncer/pgbouncer#757 (comment)

[chasers]

Very helpful! Thank you!

[chasers]

Another ref

[marcustut]

I personally think this would be an important issue to fix due to some client libraries rely on this to work properly, such as Rust's SQLx, at the moment it seems like only pgcat supports it.

[marcustut]

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.

[chasers]

Done, and deployed to production!!

[JelteF]

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)