Spring Boot JdbcTemplate - disable statement cache?

Question

I have a Spring Boot backend that I communicate with using a REST-API. This backend uses Spring JdbcTemplate to connect and execute queries on a PostgreSQL database.

I have found an issue where one request becomes significantly slower after being repeated exactly 10 times. I have narrowed the issue down to the part of code that is using the JdbcTemplate to retrieve data from the database. More specifically, the problem occurs on the second iteration of each tomcat worker-thread:

[nio-8080-exec-1] --- GET /myresource - Execution time 400 ms
[nio-8080-exec-2] --- GET /myresource - Execution time 300 ms
[nio-8080-exec-3] --- GET /myresource - Execution time 285 ms
...
[io-8080-exec-10] --- GET /myresource - Execution time 200 ms

Now each tomcat worker has received and handled one request each, the next time one of these workers receives the same request, which uses the exact same query, the execution time is 10-15 times longer:

[nio-8080-exec-1] --- GET /myresource - Execution time 6000 ms
[nio-8080-exec-2] --- GET /myresource - Execution time 5500 ms
[nio-8080-exec-3] --- GET /myresource - Execution time 6700 ms

I have tried running the same query using psql or pgAdmin and there is no issue. This leads me to believe that the JdbcTemplate is caching the query somehow for each worker and the second time the query is run the cache kicks in and for some reason it is much slower, but I am not sure. I have also tried changing tomcat to jetty/undertow but the same problem occurs there so I believe it must have to do with the JdbcTemplate.

Is there any way to disable this type of caching with the JdbcTemplate, or is there anything else I can do to avoid this behaviour?

Thanks!

EDIT:
My application.yaml:

spring:
    datasource:
        platform: postgres
        url: my-jdbc-url
        username: my-user
        password: my-password

The code creates the query with WHERE/AND clauses dynamically based on the parameters in the request, but the same request parameters always creates the same query. Code:

public List<MyDatatype> runQuery(MyParams params) {
    String sql = createSqlFromParams(params);
    List<Object> params = createParamsList(params);
    return jdbcTemplate.query(sql, params.toArray(), myDatatypeRowMapper());
}

The query would look like this in the end (using postGIS-functions to order by distance between coordinates):

SELECT * FROM my_table 
WHERE x IN [1,2,3] 
AND y BETWEEN 0 AND 1000
AND z BETWEEN 0 AND 500
ORDER BY geom <-> other_geom
LIMIT 1000;

EDIT 2:
As per the suggestion by @M.Deinum, adding
spring.datasource.hikari.data-source-properties.preparedStatementCacheQueries=0 solved the issue!

The `JdbcTemplate` doesn't cache anything, it is merely a wrapper around the `javax.sql` package. So if anything it is the JDBC driver itself that is at play here or your database setup (the `spring.datasource` properties) it influencing this. — M. Deinum, Oct 21 '20 at 13:59
Can you add your `application.properties` to your question as well as the code that is using the `JdbcTemplate`. The number 10 makes me suspicious as that is the default pool-size in use for connections, so you might actually have a connection leak due to improper usage of the `JdbcTemplate`. I have a faint suspicion if you add `spring.datasource.hikari.maximum-pool-size=5` it will happen after 5 requests. — M. Deinum, Oct 21 '20 at 14:07
@M.Deinum Thank you for your reply! I added more information to the question. I tried changing the maximum pool size to 5 but the problem still occurred after 10 requests. — Olof H, Oct 21 '20 at 15:24
Try adding `spring.datasource.hikari.data-source-properties.preparedStatementCacheQueries=0` to the `application.properties` this will disable [Server Prepared Statements](https://jdbc.postgresql.org/documentation/head/server-prepare.html). Could be that the GIS extension is messing things up. Also which driver version are you using? — M. Deinum, Oct 22 '20 at 05:23
@M.Deinum I am using the 'org.postgresql:postgresql' driver, with postgres version 12.3 and postgis version 3.0.1. Adding ```spring.datasource.hikari.data-source-properties.preparedStatementCacheQueries=0``` solved the issue! Thank you so much for your help! I have updated the question to reflect this. How can I make your answer the accepted answer? — Olof H, Oct 22 '20 at 06:19
But which driver version? The one managed by Spring? Or do you explicitly specify a version for the driver? — M. Deinum, Oct 22 '20 at 06:36
@M.Deinum I have not specified any specific version, this is what my ```build.gradle``` looks like. ```implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'``` ```implementation 'org.postgresql:postgresql'``` I don't do any other configuration outside of having that in my ```build.gradle``` and the properties that I have in my ```application.yml```. I autowire the JdbcTemplate and let spring manage the datasource. — Olof H, Oct 22 '20 at 06:39

score 1 · Accepted Answer · answered Oct 22 '20 at 06:42

Assuming you are using the default connection pool, HikariCP, in Spring Boot you can use the spring.datasource.hikari.data-source-properties to supply additional, driver specific properties.

To disable Server Prepared Statements you need to include the preparedStatementCacheQueries property and set the value to 0 (default is 256).

spring.datasource.hikari.data-source-properties.preparedStatementCacheQueries=0

This will disable the whole caching and might influence different areas of your application.

These related questions here and here seem to hint that you might want to check your disk, indexes etc. instead of disabling the query cache.

Spring Boot JdbcTemplate - disable statement cache?

1 Answers1