From: Mischa Sandberg <mischa.sandberg@telus.net>
To: pgsql-performance@postgresql.org
Subject: Re: COPY vs INSERT
Date: Wed, 4 May 2005 16:11:32 -0700
Message-ID: <1115248292.427956a468a55@webmail.telus.net> (raw)
In-Reply-To: <1115246490.1709.233393619@webmail.messagingengine.com>
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>
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.
--
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: <1115248292.427956a468a55@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