Re: Populating huge tables each day

> -----Original Message-----
> From: Jim C. Nasby [mailto:decibel@decibel.org]
> Sent: Monday, June 27, 2005 6:55 PM
> To: Dann Corbit
> Cc: Ben-Nes Yonatan; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Populating huge tables each day
>
> On Mon, Jun 27, 2005 at 01:05:42PM -0700, Dann Corbit wrote:
> >
> > > -----Original Message-----
> > > From: Jim C. Nasby [mailto:decibel@decibel.org]
> > > Sent: Monday, June 27, 2005 12:58 PM
> > > To: Dann Corbit
> > > Cc: Ben-Nes Yonatan; pgsql-general@postgresql.org
> > > Subject: Re: [GENERAL] Populating huge tables each day
> > >
> > > On Mon, Jun 27, 2005 at 12:43:57PM -0700, Dann Corbit wrote:
> > > > I see a lot of problems with this idea.
> > > >
> > > > You mention that the database is supposed to be available 24x7.
> > > > While you are loading, the database table receiving data will
not be
> > > > available.  Therefore, you will have to have one server online
(with
> > >
> > > Why do you think that's the case?
> >
> > He's doing a bulk load.  I assume he will have to truncate the table
and
> > load it with the copy command.
>
> Don't ass-u-me; he said he'd be deleting from the main table, not
> truncating.
>
> > Is there an alternative I do not know of that is equally fast?
>
> Nope, truncate is undoubtedly faster. But it also means you would have
> downtime as you mentioned. If it were me, I'd probably make the
> trade-off of using a delete inside a transaction.

For every record in a bulk loaded table?

If it were that important that both servers be available all the time, I
would bulk load into a second table with the same shape and then rename
when completed.

Be that as it may, I don't think that there is enough information yet to
give good advice.