Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id E348E9FA5A0; Sat, 10 Feb 2007 21:12:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 48272-07; Sat, 10 Feb 2007 21:12:28 -0400 (AST) X-Greylist: delayed 00:23:15.942819 by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sumo.dreamhost.com (sumo.dreamhost.com [66.33.216.29]) by postgresql.org (Postfix) with ESMTP id 337549FA29D; Sat, 10 Feb 2007 21:12:29 -0400 (AST) Received: from spunkymail-a5.g.dreamhost.com (sd-green-bigip-119.dreamhost.com [208.97.132.119]) by sumo.dreamhost.com (Postfix) with ESMTP id CEED2178CCB; Sat, 10 Feb 2007 16:49:11 -0800 (PST) Received: from benjamin-arais-computer.local (adsl-75-36-67-4.dsl.irvnca.sbcglobal.net [75.36.67.4]) by spunkymail-a5.g.dreamhost.com (Postfix) with ESMTP id ED1FA14D6AA; Sat, 10 Feb 2007 16:49:08 -0800 (PST) Message-ID: <45CE6802.7070304@araisoft.com> Date: Sat, 10 Feb 2007 16:49:06 -0800 From: Benjamin Arai Organization: Araisoft User-Agent: Thunderbird 2.0b2 (Macintosh/20070116) MIME-Version: 1.0 To: pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: Problem indexing large database with large number of inserts (partial index building?) Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.16 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200702/144 X-Sequence-Number: 24609 Hi, I have a database (200GB+), I need to upload about 10GB of data each week. There are no deletions. My problem is that inserting takes a very long time due to the indexes. I can speedup inserting the data insertion if I drop the indexes but then I am left with the problem of rebuilding all of the indexes after the insertions have completed. Currently, I am doing some optimizations such as batching large number of inserts into a single commit and I have shut fsync off (fsync=off is ok for my project). At this point I am looking for some way to efficiently insert the data without having to rebuild the indexes every time. Is there some way to insert data but defer the INDEX building of new records until the end of a commit or something similar? Any other suggestions would be greatly appreciated. Benjamin