Consider disabling prepared statement caching by default

[Danden1]

Hi,

I would like to suggest reconsidering the default behavior of prepared statement caching.

In my opinion, configuration and caching strategies for prepared statements are more appropriate to be handled at the database level rather than at the application or driver level.

In fact, the HikariCP documentation explicitly advises against implementing statement caching outside the driver or database:

Many connection pools, including Apache DBCP, Vibur, c3p0 and others offer PreparedStatement caching. HikariCP does not. Why?

At the connection pool layer PreparedStatements can only be cached per connection. If your application has 250 commonly executed queries and a pool of 20 connections you are asking your database to hold on to 5000 query execution plans — and similarly the pool must cache this many PreparedStatements and their related graph of objects.

Most major database JDBC drivers already have a Statement cache that can be configured, including PostgreSQL, Oracle, Derby, MySQL, DB2, and many others. JDBC drivers are in a unique position to exploit database specific features, and nearly all of the caching implementations are capable of sharing execution plans across connections. This means that instead of 5000 statements in memory and associated execution plans, your 250 commonly executed queries result in exactly 250 execution plans in the database.

Using a statement cache at the pooling layer is an anti-pattern, and will negatively impact your application performance compared to driver-provided caches.

— Source: https://github.com/brettwooldridge/HikariCP#statement-cache

Additionally, our team has actually encountered increased PostgreSQL memory usage in production environments that appeared to be related to prepared statement caching behavior.

Because of these concerns, it may be safer to disable prepared statement caching by default and allow users to explicitly enable it when appropriate for their workload.

If this change is considered reasonable, I would be willing to contribute a small patch to adjust the default configuration.

Thank you.

[Squiry]

Using a statement cache at the pooling layer is an anti-pattern, and will negatively impact your application performance compared to driver-provided caches.

r2dbc-postgresql is not a pooling layer, it is a driver level, like pgjdbc. And it provides a prepared statement cache just like pgjdbc does.

[Danden1]

@Squiry Thank you for the feedback. I understand that this project operates at the driver level, so it may not be considered an anti-pattern in the same sense as pooling-layer caching.

However, I still have a concern.

In the PostgreSQL JDBC driver(https://jdbc.postgresql.org/documentation/use/), the prepared statement cache is bounded (e.g., a limit of 256 entries and a total size of 5 MB). This prevents unbounded growth of cached statements and related memory usage.

Given that, would it make sense for this project to also impose a reasonable upper bound on the number of cached queries (for example, limiting it to 256 or a configurable value)? Allowing unlimited caching (e.g., using -1 for an unbounded cache) could potentially impact service stability due to excessive memory usage on the database side.

I would appreciate clarification on whether such safeguards are intentionally omitted or if adding a bounded default would be worth considering.

[Squiry]

R2dbc-postgresql provides bounded prepared statement cache. Cache size is controlled by preparedStatementCacheQueries url parameter. It is unbounded by default, because usually applications use finite number of prepared statements.
Typical source of prepared statement explode is using IN clause with a different number of placeholders generated by your code. You can avoid this by using postgresql arrays in parameters and reuse the same statement for any size of in input. But even with this kind of prepared statement caching Postgresql doesn't suffer that much from caching, unless it is thousands of them, iircl.

[Danden1]

Thank you for the explanation — that makes sense. I agree that good application design should usually prevent this issue.

However, I am still a bit unsure whether having “no limit” as the default is the best choice. From a backend perspective, safer defaults sometimes help prevent unexpected problems, especially in less controlled environments. Since issues seem unlikely unless the count grows very large, I wondered if a reasonable default limit could be beneficial.

In particular, in distributed systems with many application instances and large connection pools, the number of connections can grow significantly, and an unbounded per-connection cache could potentially increase memory usage on the database side as well.

It is also possible that I am missing something, as I am still relatively early in my career.

Thanks again for your time.