pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Benjamin Arai <benjamin@araisoft.com>
To: pgsql-general@postgresql.org
To: pgsql-admin@postgresql.org
Subject: Problem indexing large database with large number of inserts (partial index building?)
Date: Sat, 10 Feb 2007 16:49:06 -0800
Message-ID: <45CE6802.7070304@araisoft.com> (raw)

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



Message-ID: <45CE6802.7070304@araisoft.com>
Permalink:  ../45CE6802.7070304@araisoft.com/
Also on:    postgresql.org/message-id/45CE6802.7070304@araisoft.com

 · 

reply

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-admin@postgresql.org
  Cc: benjamin@araisoft.com, pgsql-general@postgresql.org
  Subject: Re: Problem indexing large database with large number of inserts (partial index building?)
  In-Reply-To: <45CE6802.7070304@araisoft.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