vertx-pg-client : how to compile PreparedStatements efficiently

D M

unread,

Sep 24, 2021, 4:51:57 AM9/24/21

to ve...@googlegroups.com

Hello dear vert.x community

I'm working on a project where we typically have basic CRUD statements to execute on a PGSQL database, no complex transaction.

So it's logical to use prepared statements in order to gain execution time.

My question is : how to compile PreparedStatements once and cache them at PgPool level ?

My first idea was to manage manually a cache of PreparedStatement using SqlConnection.prepare(), and reuse the PreparedQueries later.

However I'm not sure if these PreparedStatements are linked to a given SqlConnection only or the whole PgPool.

Then I noticed connectOptions.setCachePreparedStatements(true). Could you give more details about this option ? In other words, in the following code from the vert.x documentation, is the compilation of the PreparedStatement done only once if the block is executed several times?

connectOptions.setCachePreparedStatements(true);
client
  .preparedQuery("SELECT * FROM users WHERE id = $1")
  .execute(Tuple.of("julien"), ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println("Got " + rows.size() + " rows ");
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });

Thanks for your help

Dominique

Asher Tarnopolski

unread,

Sep 25, 2021, 4:53:57 AM9/25/21

to vert.x

yes, if you enable the use of cache the prepared statement is

reused. the cache management is based on LRU (least used eviction)

and the cache size is configurable.

Julien Viet

unread,

Sep 27, 2021, 2:07:12 AM9/27/21

to vert.x

Hi,

you could indeed use the prepare from a connection and cache the
result yourself.

If you don't use it and enable setCachePreparedStatement, then
internally the client maintains a cache of prepared statement that is
equivalent to caching the prepared statement yourself.

The code you shown is equivalent only if the statement is cached until
it is evicted from the cache because it is full and the statement is
not used anymore (it's an LRU policy).

The benefit of preparing yourself a statement is to ensure that the
statement is valid from the database perspective, i.e when you have a
result from prepare then you are sure the prepared query is valid.

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/CAJSoigtbXuFF_1EckCOezgPwjYFEmmHufLEzczv8poppPF7Pug%40mail.gmail.com.

Julien Viet

unread,

Sep 27, 2021, 2:07:32 AM9/27/21

to vert.x

there is even a cache filter you can set :-)

> --
> 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/a9a60de8-3638-4121-b9a9-92514d8fc5e9n%40googlegroups.com.

Dominique Morel

unread,

Sep 27, 2021, 6:15:34 AM9/27/21

to vert.x

Hi

thanks both for your responses.

I will use the internal cache then, it should fit my need.

I'm just a bit surprised for the moment because I cannot see the difference (in term of execution time) between simple queries and prepared queries with cache. Also when activating the log we did not see any log related to statement compilation. But I think we need to take a step back from our test first.