From: John A Meinel <john@arbash-meinel.com>
To: David Roussel <pgsql-performance@diroussel.xsmail.com>
Cc: Mischa Sandberg <mischa.sandberg@telus.net>
Cc: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Re: COPY vs INSERT
Date: Wed, 04 May 2005 18:23:55 -0500
Message-ID: <4279598B.5010908@arbash-meinel.com> (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>
David Roussel wrote:
>>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?
I think COPY also has the advantage that for index updates it only grabs
the lock once, rather than grabbing and releasing for each row. But I
believe you are right that fsync only happens on COMMIT.
>
> David
John
=:->
Attachments:
signature.asc (application/pgp-signature, 250B) download
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: john@arbash-meinel.com, pgsql-performance@diroussel.xsmail.com, mischa.sandberg@telus.net
Subject: Re: COPY vs INSERT
In-Reply-To: <4279598B.5010908@arbash-meinel.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