Skip site navigation (1) Skip section navigation (2)

V3 protocol, batch statements and binary transfer

From: Andrea Aime <andrea(dot)aime(at)aliceposta(dot)it>
To: PostgreSQL JDBC Mailing List <pgsql-jdbc(at)postgresql(dot)org>
Subject: V3 protocol, batch statements and binary transfer
Date: 2004-03-30 08:44:42
Message-ID: 200403300844.42976.andrea.aime@aliceposta.it (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi jdbc driver hackers,
my name's Andrea and I'm hitting some serious performance problem with the driver.
At present I'm working with the Postgis GIS extension and I'm hitting very low
performance during mass data insertion due to the driver limitations. Basically,
I want to turn a 20 MB shapefile into a postgres table, but it has to be an import
function on the client side (windows pc) so I can't just go to the command line and
issue a copy. But that's just an example, in general I need to perform mass insert
or updates in a transactional environment from a client, usually a Windows PC.

As far as I can tell the low performance level is due to:
a) lack of true support of batch statements as introduced by the V3 protocol, that 
    makes the network latency bite me very badly while I'm inserting that 100000 rows 
b) use of the text mode instead of the binary one, more than doubling the size of data
    that are really transfered over the wire

That makes the insertion of the above file take more than 2 minutes on a 100MB ethernet
(oh, I have to pass thru 3 switches, so the latency is not that good). A
reasonable transfer time for that amount of data should be less than 30 seconds IMHO.

I'm wondering, why do you use the text mode instead of the more efficient binary one?
Secondly, reading the e-mails on the archive it appears that you are short of time 
for implementing the V3 protocol. Can I help somehow?

Best regards
Andrea Aime


Responses

pgsql-jdbc by date

Next:From: Freddy Villalba AriasDate: 2004-03-30 08:47:10
Subject: Re: Need help to access Postgresql from a windows PC using jdbc
Previous:From: joe rajDate: 2004-03-30 08:00:36
Subject: Need help to access Postgresql from a windows PC using jdbc

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group