Good news for PostgreSQL users

[Brett Wooldridge]

I've been working with the guys over at [pgjdbc-ng](https://github.com/impossibl/pgjdbc-ng), a new high-performance Netty-based JDBC driver for PostgreSQL.  Being Netty based, rather than one thread per connection blocked in the pool, there is a core set of threads (=processor cores) that use NIO to handle all connections, so no matter now many connections you have in your pool the number of idle threads will not increase.

But the best news of all is that we've implemented PreparedStatement caching, which is not available in the "old" official PostgreSQL JDBC driver.  While the release level is currently 0.4 on the driver, it is extremely stable and feature complete, and we've been running it in our pre-production environment without issues.   If you're a PostgreSQL user, I strongly recommend checking it out.  And give a tweet about it if it works for you.

-Brett

[Robert DiFalco]

Cool! Any pointers on how to best set this up with HikariCP?

[Ray Vanderborght]

Yes, I could use some pointers too. Hoping it's just switching out the jar! :)

We're also using the standard jdbc3 driver currently so not sure if there are added upgrade pains associated with going to 4.

Would you advise migrating to the standard jdbc4 driver as a first step?

Also, do you know if pgjdbc-ng will work with postgres 9.1?

The latest commit seems to suggest it adds support for it, but that commit was added after the latest current release.

I hit some snags building that latest in maven, so I figured it may not be fully baked yet.  

[Robert DiFalco]

Well, I set it up like this. I could only get 0.30 from MVN. Unfortunately, while it works great in IntelliJ I get a bunch of "error parsing command tag" exceptions when I try to run the same unit tests from Maven. :(

    @Bean

    public DataSource dataSource() throws Exception {

        HikariDataSource config = new HikariDataSource();

        config.setAutoCommit( false );

        config.setMaximumPoolSize( 10  );

        config.setDataSource( createDataSource() );

        return config;

    }

    public DataSource createDataSource() throws Exception {

        URI dbUrl = new URI( databaseUrl );

        String username = dbUrl.getUserInfo().split(":")[0];

        String password = dbUrl.getUserInfo().split(":")[1];

        PGDataSource dataSource = new PGDataSource();

        dataSource.setHost( dbUrl.getHost() );

        dataSource.setPort( dbUrl.getPort() );

        dataSource.setDatabase( dbUrl.getPath().substring( 1 ) );

        dataSource.setUser( username );

        dataSource.setPassword( password );

        return dataSource;

    }

[Robert DiFalco]

Here's the actual stack trace. Might actually be a HikariCP issue as this driver does not have these exceptions with other pools.

Caused by: java.sql.SQLException: java.io.IOException: error parsing command tag

at com.impossibl.postgres.jdbc.PGConnectionImpl.execute(PGConnectionImpl.java:452)

at com.impossibl.postgres.jdbc.PGStatement.executeStatement(PGStatement.java:372)

at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:105)

at com.impossibl.postgres.jdbc.PGSimpleStatement.execute(PGSimpleStatement.java:124)

at com.zaxxer.hikari.proxy.StatementJavassistProxy.execute(StatementJavassistProxy.java)

[Robert DiFalco]

Tracking it down, looks like this driver at least at 0.30 doesn't support TRUNCATE statements or statements that set a byte[] to a bytea field.

[Brett Wooldridge]

You might look at the BlobTest for usage, and also checkout this closed defect covering some aspects of blob usage.  Might still be a bug, but the BlobTest seems to cover truncate.

[Robert DiFalco]

Oh no that's truncating a Blob. I'm talking about the SQL statement TRUNCATE. As in http://www.postgresql.org/docs/9.1/static/sql-truncate.html.

Also the simple JPA case of having a "@Column private byte[] byteaColumn" currently doesn't work and I couldn't find a workaround for it so I could get past it. I create two issues over there for these.

[Guillaume Smet]

Hi Ray,

On Friday, March 14, 2014 9:56:35 PM UTC+1, Ray Vanderborght wrote:

Also, do you know if pgjdbc-ng will work with postgres 9.1?

The current master does: Kevin commited 9.1 support 20 days ago.

There's no released version supporting it at the moment.

--
Guillaume

[Ray Vanderborght]

Thanks Guillaume,

I was able to build the latest (with 9.1 support) after figuring out how to disable checkstyle in maven, however the the lack of support for JPA byte[] blobs that Robert found is a showstopper for our application. Will definitely take another look once it gets resolved.

[Brett Wooldridge]

Please add a comment to the existing bug.  Those guys were moving fast with daily progress until the beginning of the year, and then a major slowdown in activity.  Give them a nudge - it's so close!

-Brett

[Ray Vanderborght]

Looks like they fixed it!

Sort of tangentially related, I have an question about upgrading the driver in my application and I'm not sure where to turn.

I inherited a fairly old legacy system when I joined the company I work for, and no one who built this system is left to ask.

I apologize if this is not the right venue for this question...

The issue I have is that there's a mystery jar in my app, binary only, that has some postgres driver classes in it along with other unrelated stuff. Those postgres driver classes are overriding the the standard jdbc driver that I also have on the classpath. Some time ago I removed that mystery jar, thinking it wasn't needed, and then with the app running on the standard jdbc3 driver I found that performance dropped so badly I had to revert.

So looking at why, I found that all selects were being wrapped in cursors when using the mystery jar's driver. I described it here:

http://stackoverflow.com/questions/14347236/using-spring-3-to-generate-sql-that-uses-cursors-in-postgres

At the time I thought the problem was related to the spring upgrade I was doing, but it's in fact just this mystery jar containing (I think) custom jdbc driver code in it that's making the difference.

So anyway, is that cursor behavior with "fetch forward" I'm seeing normal? Can I enable it with some option in other drivers to get faster selects? Is my analysis of this off base to begin with and the performance drop I saw really attributable to something else entirely? Help!

I'd hate to be stuck on this weird mystery postgres driver forever due to performance issues with upgrading!

[Ray Vanderborght]

Ah, I think see what's happening.

My old 'mystery' driver is ignoring hibernate.jdbc.fetch_size entirely and just always fetching 100 via a cursor no matter what. The newer driver only uses a cursor if there are a 'fetch_size' number of results. Thus the drop in performance when all those queries with <100 results suddenly weren't wrapped in cursors.

My confidence is restored, I should be able to upgrade the driver and then tune the fetch sizes to get the performance the app needs.

I'm probably going to upgrade to the standard jdbc4 driver first, then pgjdbc-ng later... then put Hikari into the mix after that.

Sorry for the long winded flailing question. Looks like I can finally get off this 5+ year old jdbc driver now :)

[Brett Wooldridge]

Glad you found a solution ... I was googling FORWARD_ONLY and Hibernate and various other

combinations.  I'm excited about pgjdbc-ng.  If you have a tech following on Twitter, tweet it out.

-Brett

[Robert DiFalco]

It seems cool but I'm curious what kind of difference it will make overall. Driver overhead is usually a pretty small portion of an apps running time. So it would be good to know where this driver will really shine over the old one. If it's threading with many concurrent connections, I'm not sure that will help me. I write my servers to be stateless and scale OUT rather than UP -- I would add more instances not more threads to an existing instance. So far I'm not noticing a perceptible performance difference, but you never know, I'll keep playing with it.