Support named prepared statements

[chasers]

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.

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. ref

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

• Another pool mode
• Pin a client connection to a db conn such that each client would always get the same db conn from the pool

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.

[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.

[janpio]

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

[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.

[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

[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)