Problem indexing large database with large number of inserts (partial index building?)

From: Benjamin Arai <benjamin(at)araisoft(dot)com>
To: pgsql-general(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Problem indexing large database with large number of inserts (partial index building?)
Date: 2007-02-11 00:49:06
Message-ID: 45CE6802.7070304@araisoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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

Browse pgsql-admin by date

  From Date Subject
Next Message Benjamin Arai 2007-02-11 00:56:21 Priorities for users or queries?
Previous Message Ezequias Rodrigues da Rocha 2007-02-10 22:46:38 Re: Password Policy

Browse pgsql-general by date

  From Date Subject
Next Message Benjamin Arai 2007-02-11 00:56:21 Priorities for users or queries?
Previous Message jason@ohloh.net 2007-02-10 22:46:42 flaky hardware?