Transaction pooling is not possible with prepared statements

[lastmjs]

As I've been digging into proxying requests to postgres because of large serverless load, I've come to realize that transaction pooling with node-postgres does not seem possible for prepared statements. The two major types of proxies that I've studied are AWS RDS Proxy for Postgres and pgBouncer. Neither seem to support prepared statements in the way that node-postgres executes them. If I am wrong and there is any way to currently achieve transaction pooling with node-postgres, please let me know. This seems a major roadblock for certain types of highly-scalable applications, such as serverless applications.

There may be a possible solution as hinted to here: https://github.com/brianc/node-postgres/issues/2266 and in this blog post: https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/

Until something like the above is implemented in pg, I don't believe transaction pooling is possible with prepared statements. You might say just not to use prepared statements, but they are widely used and I believe quite vital. For example, I use the Graphback project which uses Knex under-the-hood. Knex uses prepared statements for every query. There is no easy way for me to remove these dependencies from my application, it would take a major architectural overhaul.

[why-el]

Yes, this is true for any client library that enables prepared statements. That defeats the purpose of transaction pooling since a prepared statement needs and has its state stored with the entire session. There are workarounds and forks that make for instance pgbouncer work without prepared statements, but I don't know how simple it is to make it work here. However, the docs for Knex should mention this important fact, especially for people running their applications behind proxies as RDS Proxy where their connections will be pinned unnecessarily.

[AlexAurand]

I'm dealing with a similar issue. Is there any way to do one of the following?

1. Tell pg not to use prepared statements
2. Prevent connection pinning while using prepared statements

[ericalves]

Same problem.

I use the Loopback framework which uses loopback-connector-postgresql which uses node-postgres to make the connection.

I tested the Heimdall Data proxy, but it doesn't work well because transaction statements are enabled.

Any work around?