X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 986A55372D for ; Wed, 4 May 2005 19:41:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29018-08 for ; Wed, 4 May 2005 22:41:38 +0000 (GMT) Received: from out2.smtp.messagingengine.com (out2.smtp.messagingengine.com [66.111.4.26]) by svr1.postgresql.org (Postfix) with ESMTP id E6488535EF for ; Wed, 4 May 2005 19:41:36 -0300 (ADT) Received: from web2.messagingengine.com (web2.internal [10.202.2.211]) by frontend1.messagingengine.com (Postfix) with ESMTP id E82F1C89C07; Wed, 4 May 2005 18:41:38 -0400 (EDT) Received: by web2.messagingengine.com (Postfix, from userid 99) id 0A3073657; Wed, 4 May 2005 18:41:30 -0400 (EDT) Message-Id: <1115246490.1709.233393619@webmail.messagingengine.com> X-Sasl-Enc: OtoNTuGhbhgq/6iTxxqGceWlq/NRzJSDfjE6VLCsAAxr 1115246490 From: "David Roussel" To: "Mischa Sandberg" , "pgsql-perform" Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="ISO-8859-1" MIME-Version: 1.0 X-Mailer: MIME::Lite 1.5 (F2.73; T1.001; A1.64; B3.05; Q3.03) References: <21154.1115136741@sss.pgh.pa.us> <1115146397.4277c89dc37b4@webmail.telus.net> Subject: Re: COPY vs INSERT In-Reply-To: <1115146397.4277c89dc37b4@webmail.telus.net> Date: Wed, 04 May 2005 23:41:30 +0100 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200505/40 X-Sequence-Number: 12204 > 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? David