Re: Pushing PostgreSQL to the Limit (urgent!)

--- Curt Sampson <cjs@cynic.net> wrote:

> On Mon, 15 Jul 2002, Chris Albertson wrote:
> >
> > I have a similar application.  I am storing astronomical data
> > from a set of automated cameras.  The data just floods in
> > forever.  I can see a billion rows in the future.
> > I find that I _can_ keep up using only modest hardware IF I use
> > "COPY" and not "INSERT" to input the data.  "COPY" is much, much
> > faster.  Also indexes help with SELECT speed not really hurt
> > COPY/INSERT speed so you need to ballance.
>
> Right. You may find it worthwhile to drop the indexes, import, and
> rebuild
> instead of import with the indexes in place, if you're not doing
> queries
> at the same time. Or maybe partial indexes could help....

I took that route on one application.  Not sure if it was a smart move.
The trouble is that yes, the import goes faster but then you have to
re-build the dropped index.  Can take a long time.  So the database
is not usable untill the time to import PLUS the time to index.
Leaving the index in place means both are done at once.

Maybe the best thing would be to use a multi-CPU machine and do the
inport using several processes.  In the Intel world dual CPU
systems are cost effective.  But I agree experiment. but you _must_
us full size tables.  I made the mistake once of prototyping using
small cacheable tables.  It did not scale to larger 1E7 size tables.

>
> cjs
> --
> Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974
> http://www.netbsd.org
>     Don't you know, in this new Dark Age, we're all light.  --XTC
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


=====
Chris Albertson
  Home:   310-376-1029  chrisalbertson90278@yahoo.com
  Cell:   310-990-7550
  Office: 310-336-5189  Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Yahoo! Autos - Get free new car price quotes
http://autos.yahoo.com