PostgreSQL and Pooled PreparedStatements

[Robert DiFalco]

In your readme you take a pretty strong position on the idea that a statement pool is a waste. However, I don't see anywhere that a Postgres JDBC connection performs statement caching. Am I wrong, or is this pool just not a good choice for PostgreSQL?

Thanks!

[Brett Wooldridge]

Postgres implements statement caching on the server-side.  However, activating it through JDBC is a little tricky.  The Postgres JDBC driver's Connection class has a proprietary method called setPrepareThreshold() that must be called, or else the driver will not inherently use the "PREPARE" command on the wire (it will instead treat even PreparedStatements as "one shot").

Even if you use another connection pool which provides "statement caching", without the above in reality each statement will be executing as "one shot".  What you will effectively be caching is simply the Java PreparedStatement objects and nothing more.  I have never seen this mentioned by other pools, probably because they never dove down into the PostgreSQL JDBC driver code.

Here is what you need to do.  First, you will need to use the dev branch of HikariCP until 1.2.7 is released (probably in a day or three).  I have added the ability to customize a connection before it is added to the pool.  What you need to do is create an implementation of the IConnectionCustomizer interface, like so:

public class PostreSQLConnectionCustomizer implements com.zaxxer.hikari.IConnectionCustomizer {

   public void customize(Connection connection) throws SQLException {

      ((AbstractJdbc2Connection) connection).setPrepareThreshold(1);

   }

}

Then set the HikariConfig connectionCustomizerClassName property to the qualified name of your class.

As noted above, switching to another pool that does "statement caching" will be of no actual benefit because what you really need is for PostgreSQL to cache the query plan.  The PostgreSQL query plans are associated with the session (Connection), not with individual PreparedStatements.

[Brett Wooldridge]

Interesting as that last post was, after reading the PostgreSQL JDBC driver source code further I discovered this gem.  The PGSimpleDataSource itself exposes a proprietary method, setPrepareThreshold(int threshold). This means you do not need to implement a connection customizer after all.  You just need to set the threshold via DataSource configuration, like this:

...

hikari.dataSourceClassName=org.postgresql.ds.PGSimpleDataSource

hikari.dataSource.prepareThreshold=1

...

That should do it.  Note, you can do this in the existing 1.2.6 release, you do not need the dev branch.

[Robert DiFalco]

'm not yet familiar with the hikari design but wouldn't it be easier to just do something like this?

     connectionProperties.set( "prepareThreshold", "1" );

I don't know where but I'm sure hikari has the ability to set connection properties used in the Connection Factory that get passed to "driver.connect()".

Also, I think there IS overhead associated with the client side preparation of statements. Have you bench marked the difference between pooling them client side and not? I would think just the SQL parsing would save some time, especially if you only match for caching based on the raw sql string matching the raw (unprocessed) sql string.

[Brett Wooldridge]

Your example:

connectionProperties.set( "prepareThreshold", "1" );

And mine (using a hikari.properties file):

hikari.dataSource.prepareThreshold=1

...are basically the same thing.  Mine was a properties file example.  Using code, and your example, you can do something like this:

Properties connectionProperties = new Properties()

connectionProperties("prepareThreshold", "1");

HikariConfig config = new HikariConfig();

config.setDataSourceProperties(connectionProperties);

...

There are several ways to configure HikariCP if you read the documentation.

If you'd like to take some measurements of the difference between reusing the client-side PreparedStatements vs. not I would be interested in the results.

[Brett Wooldridge]

Check this out: https://groups.google.com/forum/#!topic/hikari-cp/w46e3R3yG6I

[Brett Wooldridge]

Robert,

I've done some testing with PostgreSQL and here's what I found.  In calling the Connection.prepareStatement(String sql) method 100,000 times with a query of moderate complexity (containing 3 placeholders), the total time consumed was ~600ms.  This translates to approximately 0.006ms for a prepare statement.  It is certainly not costly by any means, but obviously it is more costly than if it were cached.  Caching the PrepareStatement results in approximately 10ms for the same 100,000 calls.

Whether 600ms spread out over 100,000 calls is impactful for your application or not only you can judge.  Before choosing a connection pool, especially where performance is a criteria, rather than making a decision on a single data point such as this, I recommend looking at the totality of what your application does and whether time gained in this use case is lost in other usages of the pool.

It is indeed a shame that the PostgreSQL driver does not implement statement caching.  I don't know if you are aware of the pgjdbc-ng (PostgreSQL JDBC Next Generation) project or not.  It is pre-release, but we are already using it in one of our products.  I have opened an enhancement request and offered to implement it.  If this is a feature you are interested in, please add a comment to that issue expressing support.

Best regards.