From: Christopher Petrilli <petrilli@gmail.com>
To: Mischa Sandberg <mischa.sandberg@telus.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: COPY vs INSERT
Date: Wed, 4 May 2005 19:29:29 -0400
Message-ID: <59d991c40505041629449b2b1b@mail.gmail.com> (raw)
In-Reply-To: <1115248292.427956a468a55@webmail.telus.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>
On 5/4/05, Mischa Sandberg <mischa.sandberg@telus.net> wrote:
> Quoting David Roussel <pgsql-performance@diroussel.xsmail.com>:
>
> > > COPY invokes all the same logic as INSERT on the server side
> > > (rowexclusive locking, transaction log, updating indexes, rules).
> > > The difference is that all the rows are inserted as a single
> > > transaction. This reduces the number of fsync's on the xlog,
> > > which may be a limiting factor for you. You'll want to crank
> > > WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
> > > One of my streams has 6K records; I run with WB=1000, CS=128.
> >
> > So what's the difference between a COPY and a batch of INSERT
> > statements. Also, surely, fsyncs only occur at the end of a
> > transaction, no need to fsync before a commit has been issued,
> > right?
>
> Sorry, I was comparing granularities the other way araound. As far as
> xlog is concerned, a COPY is ALWAYS one big txn, no matter how many
> putline commands you use to feed the copy. With inserts, you can choose
> whether to commit every row, every nth row, etc.
>
> 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.
I have found even greater performance increases by using COPY FROM
<filename> not COPY FROM STDIN. This allows the backend process to
directly read the file, rather than shoving it over a pipe (thereby
potentially hitting the CPU multiple times). My experience is that
this is anywhere from 5-10x faster than INSERT statements on the
whole, and sometimes 200x.
Chris
--
| Christopher Petrilli
| petrilli@gmail.com
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: petrilli@gmail.com, mischa.sandberg@telus.net
Subject: Re: COPY vs INSERT
In-Reply-To: <59d991c40505041629449b2b1b@mail.gmail.com>
* 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