How long does a prepared statement work on the PostgreSQL server side?

Question

According to the PostgreSQL documentation , the prepared statement is bound to a session / database connection:

PREPARE creates a prepared statement. A prepared expression is a server object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, parsed, and rewritten. When the EXECUTE command is subsequently released, a prepared report is planned and executed.

Prepared statements are saved only during the current session database. When the session ends, the prepared expression is forgotten, so it must be recreated before being reused.

But then Marcus Vinand (author of the SQL Performance explanation) says that :

PostgreSQL does not have a common query plan cache, but it does have an additional query plan cache for prepared statements. This means that the developer is able to use a prepared statement with or without a cached query plan. But note that the cache is disabled when the prepared statement is closed.

Which one is correct?

Does the ready statement work as long as the connection to the database is open, so when using the connection pool, this can continue until the pool explicitly closes the physical connection or the prepared statement completes on the server side after closing the JDBC PreparedStatement.

Answer 1

So your question finally boils down to "how java.sql.PreparedStatement plays with PostgreSQL." See the answer to the question “how does this happen with server-prepared plans” at the end.

Here's the answer: it depends on the JDBC driver used.

TL; DR : in modern drivers prepared by the server, it remains until the connection dies or until the operator is expelled by another (normal LRU shutdown).

Note. A PostgreSQL server cannot share a prepared statement in database connections, so keeping a cache on each connection may be the best JDBC driver.

Note. The JDBC specification requires the use of ?, ? for binding placeholders, while the server wants $1, $2 , so JDBC drivers cache the so-called parsed SQL texts.

There are two well-known JDBC drivers: pgjdbc and pgjdbc-ng

pgjdbc

https://github.com/pgjdbc/pgjdbc

Since pgjdbc is 9.4-1202 , it automatically caches server-side plans when using PreparedStatement . Note. Statements are cached even if you close() PreparedStatement . To prepare for server preparation, you need to execute the request 5 times (this can be configured using prepareThreshold ).

Currently, a cache is implemented for each connection. By default, pgjdbc caches 256 ( preparedStatementCacheQueries ) requests and before preparedStatementCacheSizeMiB requests. This is a conservative setting, so you can adjust it. See the documentation for a description of the properties. The cache includes both parsed and prepared by the server operators.

github issue: https://github.com/pgjdbc/pgjdbc/pull/319

pgjdbc-ng

https://github.com/impossibl/pgjdbc-ng

I am not in pgjdbc-ng, but it looks like it does parsing (default cache size is 250 requests) preparation (default cache size is 50 requests). Support for prepared reports on the server side landed on February 24, 2014, so if you are using a somewhat recent version, you can get instruction caching.

Note. If you accidentally use very long queries, you can click OutOfMemory , since pgjdbc-ng cannot display entries based on the number of bytes stored.

The cache is designed for each connection, so it is transparently used, even if you close instructions.

I can not say much about the performance of pgjdbc-ng, although since the last time I tried to throw jmh, it has not succeeded with random exceptions.

github issue: https://github.com/impossibl/pgjdbc-ng/pull/69

Server-prepared plans

PostgreSQL has the PREPARE and DEALLOCATE to reference the instruction when sending EXEC for posting. It optimizes two things:

• When using the PREPARE d statement (in other words, prepared by the server), the client does not have to send the request text again and again. It simply sends the short name of the request and the values ​​for the binding variables.
• Starting with 9.2, the database is still trying to redistribute the first few executed queries. He does this to try if the request requires several plans or if the overall plan is good enough. In the end (right away, if the request has no parameters), the database can switch to the general plan .

In other words, PreparedStatement optimizes both JDBC-side query parsing and database-side query scheduling.

Further information here: http://blog.endpoint.com/2014/04/custom-plans-prepared-statements-in.html

Prepared statements in PL / pgSQL

According to the documentation, PostgreSQL caches query plans used in PL / pgSQL. This happens after several executions (3 or 5, I don’t remember the exact threshold), so after creating the stored procedure this can be a little slow, but then it will switch to cached plans (provided that the database agrees to use a common plan for a specific request) .

In other words, to achieve "cached execution plans" you need to either use the updated JDBC driver, or you can wrap all your requests in stored procedures. A procedure call will be rescheduled each time it is executed, but the call itself is usually much shorter than the requests that make up the procedure.

Answer 2

I think both of them are true, but the doubt is that the PostgreSQL documentation is usually more accurate than me. However, I think the PostgreSQL document may be inaccurate.

It should probably look like this:

Prepared statements are saved until DEALLOCATE ed and no more than the duration of the current database session.

Without verification, I would be sure that the JDBC driver frees the prepared statement on the server side when the JDBC PreparedStatement closes.