Prepared statements with connection pool about asyncpg HOT 7 OPEN

So do I understand this correctly? @elprans

• If a PreparedStatement was made from a Connection that was acquired from a ConnectionPool, the prepared statement is not really invalid until the connection is closed, for example due to inactivity, but since there is no way for us to know when that happens all statements are considered invalid after pool.release(con).

Correct. Any resources derived from a connection released to the pool become invalid. This includes explicit prepared statements.

• PreparedStatement made from pools should only be used if you have a tight loop where you insert or run the same complicated query many times before releasing the connection.

Yes, but normally you don't need an explicit prepared statement. asyncpg query LRU cache will do that for you.

• All queries sent using con.fetch/fetchall/execute/executemany are turned into prepared statements in the background and can be cached in a LRU cache depending on the connection settings. The next time the same query on that connection is used the prepared statement is used automatically if the statement_cache is not disabled and it haven't been pushed out of the cache due to size or age.

Correct.

Question:

• Does the Connection query LRU cache get invalidated on pool.release(con) or when the connection is actually closed to due to max_inactive_connection_lifetime/max_queries?

The LRU cache does not get invalidated on pool.release().

• If we always use a few identical queries, do we still get the advantage of the pre-planing the queries due to the LRU query cache or will using conn.prepare() give us some other performance advantage?

conn.prepare() is mostly useful for occasions when you don't want to use the cached prepared statement for a specific query and instead prepare and execute it explicitly. You may want to do that if the generic plan created for the query is bad (see #243). In normal situations simply rely on the LRU cache.

from asyncpg.

Prepared statements are connection-specific, yes. If you really need to keep prepared statements around, then a WeakKeyDictionary conn: statements should work. asyncpg keeps it's own LRU cache of prepared statements per-connection, so you don't need to cache them unless you absolutely need to.

from asyncpg.

Thank you for answers. I'll stick just to asyncpg LRU cache.

from asyncpg.

So do I understand this correctly? @elprans

• If a PreparedStatement was made from a Connection that was acquired from a ConnectionPool, the prepared statement is not really invalid until the connection is closed, for example due to inactivity, but since there is no way for us to know when that happens all statements are considered invalid after pool.release(con).
• PreparedStatement made from pools should only be used if you have a tight loop where you insert or run the same complicated query many times before releasing the connection.
• All queries sent using con.fetch/fetchall/execute/executemany are turned into prepared statements in the background and can be cached in a LRU cache depending on the connection settings. The next time the same query on that connection is used the prepared statement is used automatically if the statement_cache is not disabled and it haven't been pushed out of the cache due to size or age.

Question:

Prepared statements and cursors returned by Connection.prepare() and Connection.cursor() become invalid once the connection is released.

• Does the Connection query LRU cache get invalidated on pool.release(con) or when the connection is actually closed to due to max_inactive_connection_lifetime/max_queries?

• If we always use a few identical queries, do we still get the advantage of the pre-planing the queries due to the LRU query cache or will using conn.prepare() give us some other performance advantage?

from asyncpg.

Am I correct to say, that prepared statement data itself in it's nature is the same for one connection or from an another connection from the same connection pool, assuming database is the same?

If yes, I'd prefer to compile SQL requests first and use them second. This would give me an opportunity to:

1. validate syntax of SQL queries on the start of my app and it's quite useful during development and testing
2. save a lot of time as it's quite usual that I query a huge object from a database about 100 times using ~7 queries per an object. (less typical ~2000 times to request of an object with the same structure).

from asyncpg.

@eirnym Prepared statements are a server resource and are per-connection.

from asyncpg.

@elprans thank your for an explanation

from asyncpg.