Clarification on prepared statements

[mikmatko]

Gino seems to support prepared statements to some extent. As there is no documentation yet, could you please clarify how the prepared statements currently work.

What is the relationship between Gino prepared statements, Sqlalchemy and asyncpg? As far as I understand,

• Sqlalchemy does not support prepared statements
• Asyncpg supports prepared statements

But as per MagicStack/asyncpg#51 (comment)

• Asyncpg caches queries internally and discourages the use of prepared statements in most cases
• Prepared statements are per-connection

So,

• Does Gino utilize the LRU cache used internally by asyncpg? Does connection pooling affect this? Or is Gino simply unaware of any of the asyncpg internals regarding these?
• => should prepared statements generally be utilized for repeated queries?
• => what would be the way to approach repeated queries, e.g. we have an insert statement that is repeated thousands of times (with different values of course)

Thank you for your time!

[wwwjfy]

Does Gino utilize the LRU cache used internally by asyncpg? Does connection pooling affect this? Or is Gino simply unaware of any of the asyncpg internals regarding these?

Yes, Gino invokes Asyncpg's prepare function and its cache is transparent even to Gino. As described in Postgresql, prepared statements are per-connection, so it can't be reused across connections.

=> should prepared statements generally be utilized for repeated queries?

Yes, if they're done in one connection or one transaction.

=> what would be the way to approach repeated queries, e.g. we have an insert statement that is repeated thousands of times (with different values of course)

Except prepare statements, you may also want to try multiple tuples in one statements, like INSERT INTO users (name) VALUES ("John"), ("Jane"). I haven't done benchmark, but for insertion, I feel prepared statements won't help a lot.

[mikmatko]

Thanks!