Support persistent prepared statements for postgres.

[stoksc]

Maybe I'm wrong here, but the way I see to prepare statements currently, you would call prepare, get back the statement and use it. I don't see, eyeing the code, that tokio-postgres will do any smart persistence of the prepared statements and not reprepare them until needed (like npgsql for c# for example).

I feel like adding this functionality into bb8 wouldn't be too hard. Potentially the user could specify statements in something like bb8_postgres::PostgresConnectionManager::new_with_prepared, bb8_postgres::PostgresConnectionManager::connect could prepare the statements and populate them into a custom type Connection (that's just a wrapper around the tokio_postgres::Client) in the ManageConnection impl that allows the user to access them.

Any thoughts?

[djc]

So you're saying the pool could cache prepared statements somehow? I'm not sure your wording of "persistent" makes sense to me, since presumably the prepared statements wouldn't actually be persisted to disk or anywhere else -- just cached in memory.

[stoksc]

By persistent, I just meant you don't need to re-prepare statements every time you grab a connection from the pool, that there is some concept of statements that are kept prepared for you. That they are persistent on the database connection you have.

If you see here, in theory maybe dont need to call cl.Prepare, I could call cl.query_one(&cl.Statement(name), &[])).

When you call new_from_stringlike, could have a new_from_stringlike_with_statements that takes a extra function that takes a connection and returns some keyed prepared statements on it.

I had a branch on my local with the idea, if you were interested I could flush it out. If you look at the link in the original post from npgsql auther, preparing the statements ahead of time (with postgres) makes a huge deal.

It's part of the reason the actix-web benchmarks look so good, you can see the statements are prepared once and kept with the connection object here.

[djc]

So you would store like a LRU hashmap String -> PreparedStatement or whatever with the connection, size configured by the builder? Sounds like a good feature to me!

[stoksc]

Yeah exactly, seems like we're on the same page. I'm waiting for the async ecosystem to stabalize a bit before I try flesh out the project I was working on. When I come back around to it, I'll want this probably and I'll open a pr, maybe with some benchmarks.

[stoksc]

didn't mean to close and comment, just comment

[rxdiscovery]

Hello,

If it's any help, deadpool-postgres already offers this feature, and it's a significant performance booster. (prepare_cached)

Take inspiration from this crate's code..

[djc]

Contributions in this direction welcome, but unfortunately I'm unlikely to have time to work on this myself anytime soon.