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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-jdbc by date

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