Questions about direction of SQL Client and deprecation of JDBC Client

[colin....@canopytax.com]

My company is currently using the now deprecated JDBC Client quite heavily. We have over a dozen microservices with probably a couple thousand distinct queries running on this client (Postgresql driver with C3P0). In some of our new services, we have started to use the new SQL Client (PgPool, specifically) and have noticed some major issues that are causing us to consider switching back to the deprecated client. Being deprecated though, we know support will be limited going forward and we will have to switch to something else at some point. Unfortunately, the SQL Client in its current state does not come close to meeting all our needs. In addition to my previous post about how long-lived PgPool connections crash our database, here's just a few of the features we currently use that don't seem to be in the new client:

1) Min pool size

2) Initial pool size

3) Per-connection timeout settings

4) Connection testing on check-in/out

5) Idle connection testing

6) Pgbouncer support (transaction mode)

7) Connection expiry options (max connection age, for example)

I think I understand the general direction behind the SQL Client (unified pooling, more control over drivers, etc.) but it seems odd to me that you would deprecate the much more feature rich alternative before gaining feature parity, or close to it. Can you please help me understand the reasoning behind this move and the future direction and timing around the JDBC Client removal? As it is now, our best option moving forward is to basically fork the JDBC Client and move away from Vert.x clients entirely, because we're being painted into a corner.

[Julien Viet]

Hi,

first, we understand the SQL client connection pool is lacking several
features, but that's a different question you are asking here.

the Vert.x JDBC Client currently provides two API implementations:

1/ the Vert.x 3 JDBC client API
2/ the Vert.x 4 SQL client API (defined in vertx-sql-client)

Both use JDBC and the a JDBC connection pool.

I think what confuses you is that the JDBPool is using the PoolOptions
of SQL client that is limited somehow.

You can use the JDBC pool from any data source, so it means you can
pass a JDBC pool to use with the SQL client API, e.g

HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl("jdbc:h2:mem:testDB?shutdown=true");
client = JDBCPool.pool(vertx, ds);

Concerning deprecation:

1/ the Vert.x 3 JDBC API is supported for the lifetime of Vert.x 4,
but is deprecated (because the API is replaced) and going away in
Vert.x 5
2/ the Vert.x 4 JDBC API is supported for the lifetime of Vert.x 4 and beyond

So to sum-up

- you can continue using the Vert.x 3 JDBC API with Vert.x 4
- but you should migrate to the new Vert.x 4 JDBC API based on
vertx-sql-client that will be supported beyond Vert.x 4
- you can create a Vert.x 4 JDBC API pool with any datasource as shown above

I hope it answers your questions and doubts.

HTH

Julien

> --
> You received this message because you are subscribed to the Google Groups "vert.x" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
> To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/b4c96e59-0159-44f0-8d03-abb411e75da6n%40googlegroups.com.

[Asher Tarnopolski]

hi colin,

speaking about the issues you've experienced while using pgpool, 

once I've seen your previous post about it i started to be worried since we are using pgpool

too. i set up the exact environment you've described there for a test but

couldn't reproduce your issue.ended up leaving a test with 100 queries/sec to run for almost 

a week - nothing. didn't see any significant change in  memory usage what so ever.

is there a chance you can still provide your reproducer for this issue?

cheers 

[colin....@canopytax.com]

Julien, thank you so much for the explanation. I was definitely under the mistaken impression that we basically had to use PgPool, and was unaware of the option to pass in our own datasource. That should allow us to continue using something like C3P0 while migrating our legacy Vert.x 3 code to the newer API.

As for a reproducer for the previous issue, I have been super busy and unable to put one together. Frankly, it's been pushed even lower in my priorities since we are moving toward other pooling methods, such as pgbouncer. Also, we were able to mostly remedy the issue by setting a very low idleTimeout so that connections get expired often. Before doing so, our production databases would completely run out of memory and crash every couple of days. For the higher usage ones, we just added in a scheduled restart of the application every night and it resets connections and releases the huge amount of memory used back to the DB. But I will try my best to get something put together soon, if possible.