Re: performance question: protocol v2 vs v3

Guillaume Cottenceau wrote:
> I have conducted tests that seem to indicate that using protocol
> v2 gives slightly better insert performance as using protocol v3.

[...]

> Using a prepared statement on an autocommit=true connection:

[...]

> Using jdbc:postgresql:dbname?charSet=UTF8&protocolVersion=2,
> clock time is:
> 
> #1: 00:58.519
> #2: 00:56.678
> #3: 00:56.222
> 
> Using jdbc:postgresql:dbname?charSet=UTF8&protocolVersion=3,
> clock time is:
> 
> #1: 01:01.404
> #2: 00:59.331
> #3: 01:00.091
> 
> I know this is not massive. However, I was about to switch from
> protocol v2 to protocol v3 wholly, but now, I'm wondering if
> anyone can give any insight on this. Also, is there any known
> downsides in sticking to protocol v2 - since it's very old now.

An explanation is maybe that prepared statements are handled
using the extended query protocol, which only exists in v3.
With the extended query protocol, each statement is first parsed,
then parameters are bound to the statement and then the statement
is executed, leading to three client-server round trips.

With protocol v2, the JDBC driver converts the statements
to simple statements that contain the parameters, so there will
be fewer client-server round trips.

Do you you call conn.prepareStatement for each statement, or do
you reuse the prepared statements?  In the latter case, you might see
better performance with protocol v3.

Yours,
Laurenz Albe