The statement caching feature caches SQL statements for each individual connection. This means that when an SQL statement with an identical string is next executed, the analysis and creation of the statement can be skipped. This improves performance in cases such as when an SQL statement with an identical string is executed within a loop or method that is executed repeatedly. Furthermore, the statement caching feature can be combined with the connection pooling feature to further enhance performance.
The mechanism used by the statement caching feature is described below:
When determining if an SQL statement contains an identical string, the check is case-sensitive.
Only SQL statements prepared using the prepareStatement method or the prepareCall method are targets for statement caching.
When the close method of the PreparedStatement class or the CallableStatement class is executed, the specified SQL statement is registered in the cache. When the prepareStatement method or the prepareCall method is executed, the feature checks if the specified SQL statement exists in the cache. If it exists, the analysis and creation processes for the statement can be skipped.
Specify the upper limit of the cache using the setMaxStatements method of the PGConnectionPoolDataSource class or the PGXADataSource class. The statement will not be cached unless a valid value other than 0 is specified. If the upper limit of the cache is reached, the least recently used cache will be discarded (this is known as a Least Recently Used (LRU) system).
If "false" is specified in the setPoolable(boolean) method of the PreparedStatement class, the SQL statement will not be registered in the cache, even if the close method of the prepared SQL statement is executed.
Cache registration controls
You can configure whether to cache SQL statements using the setPoolable(boolean) method of the PreparedStatement class when the statement caching feature is enabled.
Values that can be configured are shown below:
SQL statements will not be cached, even when the statement caching feature is enabled.
SQL statements will be cached if the statement caching feature is enabled.
Number of caches
Specify the number of SQL statements with different strings per connection.
Note
The specified values may become unsuitable when changes are made to the application, so periodically execute pg_stat_statement to check and update to suitable values if required.
Cached SQL statement information will consume a certain amount of memory, so consideration must be given to the upper limit for memory consumption. A guide to estimating the memory consumed by the statement caching feature is shown below:
Client
connsPerAppProc x numOfCaches x 2 KB (*1)
Server
maxConnsPerAppProc x numOfCaches x 10 KB (*1)
*1: Approximate memory consumption per SQL statement
