Go SQL-预备语句范围

Question

I'm building an API using the Go (1.6.x) sql package along with with PostGres (9.4). Should my prepared statements have application or request scope? Having read the docs it would seem more efficient to scope them at the application level to reduce the number of preparation phases. However, perhaps there are other considerations and prepared statements are not designed to live that long?

Answer 1

Prepared statements are so that you can execute repetitive SQL commands which may only differ in parameter values for example.

They are not meant to live "long" as a prepared statement may (they do if called from a transaction) reserve an active database connection ("long" means when they are not used; it is perfectly fine to repetitively execute a prepared statement many times even if this will take long). A connection is an expensive resource and should only be hold as long as needed. Just by creating a bunch of prepared statements and not closing them, you could run out of active / allowed connections and then block further communication to the db server.

Use a prepared statement if you want to execute the same insert, update or select statement with different parameters multiple times in one (HTTP) request. Do not use prepared statement to outlive (HTTP) requests.

In certain driver implementations and database servers prepared statements may also involve resources allocated on the DB server itself (not in the Go application). For example a prepared statement may be pre-compiled on the DB server and the server might prepare query execution plan, allocate certain resources such as memory for it. These may be permanently reserved until the prepared statement is closed.

There's an article (posted by Myles McDonnell in comments below) going into the implementation details of Prepared Statements in Go. It mentions that if prepared statements are not created from transactions, they release the connection back to the connection pool, but when needed, they try to reuse the same on which they were prepared (because if the db server aids / also plays an active role in prepared statement, it is bound to the connection at the server side). If not, they will re-prepare them on a new connection (causing undesirable performance overhead).

All in all, what you describe is a working model, and if you have a low number of prepared statements which are needed / executed in many subsequent requests, they can mean shorter response times. But it also means that on the other hand on the long run, they might result in that all your prepared statements will be prepared on all the connections of the pool. Decide whether this is acceptable in your case.

In general this should be avoided (and prepared statement be closed before the end of the HTTP request), but if you only have a few of them and you do need them in many requests following each other, you may move them out of request scope.