Re: Populating huge tables each day - Mailing list pgsql-general

From Jim C. Nasby
Subject Re: Populating huge tables each day
Date
Msg-id 20050628204918.GE50976@decibel.org
Whole thread Raw
In response to Re: Populating huge tables each day  ("Dann Corbit" <DCorbit@connx.com>)
Responses Re: Populating huge tables each day
List pgsql-general
On Tue, Jun 28, 2005 at 10:36:58AM -0700, Dann Corbit wrote:
> > 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?
Sure. If the data's only being loaded once a day, it probably doesn't
matter if that delete takes 10 minutes.

> 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.
Interesting idea, though the problem is that AFAIK everything will block
on the rename. If everything didn't block though, this might be a better
way to do it, although it potentially complicates the code greatly
(think about needing to add indexes, rebuild RI, etc.)
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

pgsql-general by date:

Previous
From: "Ben Hallert"
Date:
Subject: Re: Generate a list of (days/hours) between two dates
Next
From: Matt Miller
Date:
Subject: Re: Building Latest (8.1)