From: Mischa Sandberg <mischa.sandberg@telus.net>
To: pgsql-performance@postgresql.org
Subject: Re: COPY vs INSERT
Date: Wed, 4 May 2005 21:41:46 -0700
Message-ID: <1115268106.4279a40a7481a@webmail.telus.net> (raw)
In-Reply-To: <Pine.BSO.4.56.0505041817480.2683@leary.csoft.net>
References: <OF93C70832.2AC188CE-ON85256FF6.0056B587-85256FF6.005774C2@us.ibm.com>
<21154.1115136741@sss.pgh.pa.us>
<1115146397.4277c89dc37b4@webmail.telus.net>
<1115246490.1709.233393619@webmail.messagingengine.com>
<1115248292.427956a468a55@webmail.telus.net>
<Pine.BSO.4.56.0505041817480.2683@leary.csoft.net>
Quoting Kris Jurka <books@ejurka.com>:
> On Wed, 4 May 2005, Mischa Sandberg wrote:
>
> > Copy makes better use of the TCP connection for transmission. COPY
> uses
> > the TCP connection like a one-way pipe. INSERT is like an RPC: the
> > sender has to wait until the insert's return status roundtrips.
>
> Not true. A client may send any number of Bind/Execute messages on
a
> prepared statement before a Sync message. So multiple inserts may
be
> sent
> in one network roundtrip. This is exactly how the JDBC driver
> implements batch statements. There is some limit to the number of
> queries
> in flight at any given moment because there is the potential to
> deadlock
> if both sides of network buffers are filled up and each side is
> blocked
> waiting on a write. The JDBC driver has conservatively selected 256
> as
> the maximum number of queries to send at once.
Hunh. Interesting optimization in the JDBC driver. I gather it is
sending a string of (;)-separated inserts. Sounds like
efficient-but-risky stuff we did for ODBC drivers at Simba ... gets
interesting when one of the insert statements in the middle fails.
Good to know. Hope that the batch size is parametric, given that you
can have inserts with rather large strings bound to 'text' columns in
PG --- harder to identify BLOBs when talking to PG, than when talking
to MSSQL/Oracle/Sybase.
--
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mischa.sandberg@telus.net
Subject: Re: COPY vs INSERT
In-Reply-To: <1115268106.4279a40a7481a@webmail.telus.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox