Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 9C9682E2D35 for ; Sat, 26 Jan 2008 07:42:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 32083-04 for ; Sat, 26 Jan 2008 07:42:32 -0400 (AST) Received: from kuber.nabble.com (kuber.nabble.com [216.139.236.158]) by postgresql.org (Postfix) with ESMTP id C4A5E2E02C5 for ; Sat, 26 Jan 2008 07:42:46 -0400 (AST) Received: from isper.nabble.com ([192.168.236.156]) by kuber.nabble.com with esmtp (Exim 4.63) (envelope-from ) id 1JIjQr-0002yM-2y for pgsql-performance@postgresql.org; Sat, 26 Jan 2008 03:42:45 -0800 Message-ID: <15107074.post@talk.nabble.com> Date: Sat, 26 Jan 2008 03:42:45 -0800 (PST) From: growse To: pgsql-performance@postgresql.org Subject: Re: How do I bulk insert to a table without affecting read performance on that table? In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Nabble-From: nabble@growse.com References: <15099164.post@talk.nabble.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.03 tagged_above=0 required=5 tests=BAYES_00=-2.599, DNS_FROM_OPENWHOIS=1.13, WHOIS_MYPRIVREG=1.499 X-Spam-Level: X-Archive-Number: 200801/243 X-Sequence-Number: 28482 Scott Marlowe-2 wrote: > > On Jan 25, 2008 5:27 PM, growse wrote: >> >> Hi, >> >> I've got a pg database, and a batch process that generates some metadata >> to >> be inserted into one of the tables. Every 15 minutes or so, the batch >> script >> re-calculates the meta data (600,000 rows), dumps it to file, and then >> does >> a TRUNCATE table followed by a COPY to import that file into the table. >> >> The problem is, that whilst this process is happening, other queries >> against >> this table time out. I've tried to copy into a temp table before doing an >> "INSERT INTO table (SELECT * FROM temp)", but the second statement still >> takes a lot of time and causes a loss of performance. > > Can you import to another table then > > begin; > alter table realtable rename to garbage; > alter table loadtable rename to realtable; > commit; > > ? > >> >> So, what's the best way to import my metadata without it affecting the >> performance of other queries? >> >> Thanks, >> >> Andrew >> -- >> View this message in context: >> http://www.nabble.com/How-do-I-bulk-insert-to-a-table-without-affecting-read-performance-on-that-table--tp15099164p15099164.html >> Sent from the PostgreSQL - performance mailing list archive at >> Nabble.com. >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >> > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > This is a possibility. My question on this is that would an ALTER TABLE real RENAME TO garbage be faster than a DROP TABLE real? -- View this message in context: http://www.nabble.com/How-do-I-bulk-insert-to-a-table-without-affecting-read-performance-on-that-table--tp15099164p15107074.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.