Hello pgsql-jdbc users,
When executing same queries from psql and from application over WAN
with high latency it was easier to notice there is big difference in
query execution times. Where pgsql would execute statements in latency
+ couple of more ms, application using latest 9.1 jdbc driver would
take n * latency + couple of more ms. After turning on the logging
level (see [1] for connection params) I've noticed multiple backend
protocol messages being exchanged between the database and the driver,
each of them taking latency + couple more ms. Reading through protocol
specs (see [2]) I've found out about "extended query" sub-protocol,
which adds for every query multiple frontend and backend messages. It
seems newer jdbc drivers which support extended query sub-protocol are
using it by default. 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.
Since I can not eliminate latency, I'd like to force pgsql-jdbc driver
to use simple query sub-protocol. I couldn't find direct info in
pgsql-jdcb driver docs on how to force simple query sub-protocol.
Connecting info in [3] I've found out that if I put protocolVersion to
2 that simple query sub-protocol gets used and query times are on par
with psql. Version 3 of postgres frontend/backend protocol added
support for extended query sub-protocol, so it isn't available in
protocol version 2.
Is there a connection parameter in pgsql-jdbc which would configure
jdbc driver to force usage of simple query sub-protocol while still
using protocol version 3?
Are there any (significant) down-sides in using protocol version 2
instead of protocol version 3?
Thanks in advance!
Regards,
Stevo
[1] pgsql-jdbc driver specific connection parameters
http://jdbc.postgresql.org/documentation/80/connect.html#connection-parameters
[2] Postgres Frontend/Backend protocol
http://www.postgresql.org/docs/9.1/static/protocol-overview.html
[3] Summary of changes in Postgres Frontend/Backend protocol between
version 2 and 3
http://www.postgresql.org/docs/9.1/static/protocol-changes.html
