Re: PostgreSQL protocol 3 JDBC drivers, sub-protocols, and latency

On 19 November 2011 00:21, Stevo Slavić <sslavic@gmail.com> wrote:

> It seems newer jdbc drivers which support extended query sub-protocol are using it by default.

Not "newer" drivers - it's been doing that since 8.0, which is very
old now. What driver versions are you comparing to?

> This sub-protocol is advertised that "it might
> allow improvements in performance or functionality", but with high
> latency environment in fact it performs much worse since query
> execution time is (n-2) * latency bigger compared to simple query
> protocol, where n is number of messages extended query sub-protocol
> uses for each query. psql seems to be using (by default) "simple
> query" protocol with only one frontend and one backend message.

There's no reason the extended query protocol needs to be worse. in
general the driver tries to send many messages without waiting for
responses, so while there are several logical messages involved, there
are few round trips. Basically you will be seeing one round trip per
Sync or Flush message. How many of those do you see? I know I tried to
minimize the chattiness of the original implementation exactly to
avoid problems with high-latency connections, but it may have got
worse over time since it's not a case that many people appear to be
using. The original implementation would end up doing something like
this most of the time:

send Parse, Describe, Execute, Sync
wait for results

which is no worse than the simple query protocol for latency.

> Are there any (significant) down-sides in using protocol version 2
> instead of protocol version 3?

Yes, you'll lose all sorts of random features, using v2 should be a last resort.

Oliver