DB.Query prepares statements under the hood

[aaronbrashears]

If any arguments are passed to Query, lib/pq appears to prepare the statement under the hood. In some workloads, this creates a constant workload of parse, bind, select, parse, bind, select, parse, bind, select, etc on the server if all queries are just one-shot.

It seems like it would be better to only prepare the statement when calling DB.Prepare and use the Stmt interface to issue the query. This way, a developer can avoid the round-trip overhead of prepared statements when they know their workload does not benefit from it.

[johto]

It seems like it would be better to only prepare the statement when calling DB.Prepare and use the Stmt interface to issue the query. This way, a developer can avoid the round-trip overhead of prepared statements when they know their workload does not benefit from it.

Using protocol-level prepares for this is fine, it's just that our implementation is subpar. See #209 for my ideas on how to fix it. (Someone was working on it around a month ago, but he seems to have disappeared. Perhaps I should pick it up at some point).

[aaronbrashears]

Will fixing issue #209 also address using pgbouncer as an intermediary proxy in transaction mode?

We have hundreds of go and non-go processes talking to postgresql. Since there are significant chunks of processing time, we leave the connection open outside of a transaction and let pgbouncer manage the postgresql backends so that they become shared resources. With lib/pq, this does not work because the prepared query is associated with the backend and the backend may be swapped between a prepare and an execute.

I recognize this is not exactly a postgresql driver issue, though it is a valid use case that makes it difficult to integrate our go code on a shared postgresql server.

[johto]

Will fixing issue #209 also address using pgbouncer as an intermediary proxy in transaction mode?

I'd have to read some source code to say for sure, but instinctively I would say it doesn't.

We have hundreds of go and non-go processes talking to postgresql. Since there are significant chunks of processing time, we leave the connection open outside of a transaction and let pgbouncer manage the postgresql backends so that they become shared resources. With lib/pq, this does not work because the prepared query is associated with the backend and the backend may be swapped between a prepare and an execute.

Right.

I recognize this is not exactly a postgresql driver issue, though it is a valid use case that makes it difficult to integrate our go code on a shared postgresql server.

Personally, I'd blame this on pgbouncer's inability to provide sane behaviour with the special unnamed prepared statement in transaction pooling mode. But if we did want to do something about this in the driver, we'd have to implement something like #233, and I haven't heard of anyone willing to put in the hours (weeks, months?) to make that happen.

That said, if you're comfortable with it, you could implement this in your app and only call Query() after having substituted the parameters in yourself. But heed that that's a path full of dragons.

[jberkus]

Personally, I'd blame this on pgbouncer's inability to provide sane behaviour with the special unnamed prepared statement in transaction pooling mode.

No, not really. The purpose of transaction mode is to try to aggressively reuse connections. If you've done a prepare, it can't know over what period you intend to use that prepare ... you could go back to it minutes or even hours later. In that case, you're in de-facto session mode, so why not use session mode in the first place?

There's a more important reason to want to have prepare-less queries available. That's the round-trip time required for things like utility statements and no-op queries; in those cases, the prepare-bind-execute loop can take an order of magnitude longer than executing the actual query. Note that this is an issue with JDBC as well; in some Java workloads, I've seen a majority of application time devoted to round-tripping to the database server rather than doing any work on either the application code or the database.

[johto]

Personally, I'd blame this on pgbouncer's inability to provide sane behaviour with the special unnamed prepared statement in transaction pooling mode.

No, not really. The purpose of transaction mode is to try to aggressively reuse connections. If you've done a prepare, it can't know over what period you intend to use that prepare ... you could go back to it minutes or even hours later. In that case, you're in de-facto session mode, so why not use session mode in the first place?

The unnamed prepared statement is special. It is planned differently, and re-preparing to it automatically replaces the previous one. What practically every single client does is to prepare the unnamed prepared statement, immediately execute it, and then forget all about its contents. I don't see why pgbouncer couldn't have an option to grab a connection from the pool when the client prepares a query on the unnamed statement, and release the connection once the query has been executed to completion.

There's a more important reason to want to have prepare-less queries available. That's the round-trip time required for things like utility statements and no-op queries; in those cases, the prepare-bind-execute loop can take an order of magnitude longer than executing the actual query. Note that this is an issue with JDBC as well; in some Java workloads, I've seen a majority of application time devoted to round-tripping to the database server rather than doing any work on either the application code or the database.

You don't actually have to do more than one round-trip to execute a prepared statement provided you don't need to look at the data types of the parameters. See e.g. what PQexecParams in libpq does.

[jberkus]

For anyone playing the home game, we've had further discussion and some ideas about this on IRC. Stay tuned.

[cbandy]

There's a more important reason to want to have prepare-less queries available. That's the round-trip time required for things like utility statements and no-op queries; in those cases, the prepare-bind-execute loop can take an order of magnitude longer than executing the actual query.

Do we really need to wait for a round-trip, or can we send the Parse, Bind and Execute messages in tandem then receive the ParseComplete and BindComplete complete messages after? (Maybe this is what is happening in the gist in of #209.)

[johto]

There's a more important reason to want to have prepare-less queries available. That's the round-trip time required for things like utility statements and no-op queries; in those cases, the prepare-bind-execute loop can take an order of magnitude longer than executing the actual query.

Do we really need to wait for a round-trip, or can we send the Parse, Bind and Execute messages in tandem then receive the ParseComplete and BindComplete complete messages after? (Maybe this is what is happening in the gist in of #209.)

Right now we do, since we need to know the types of the parameters to decide whether to encode values into the bytea format or not. But yeah, #209 is all about having an option to make that go away. I'm almost done with a patch there, too, but I've prioritized other projects recently.