Re: Adding foreign key constraints without integrity

On Mon, 2006-06-19 at 16:39, Wes wrote:
> On 6/19/06 3:47 PM, "Jim Nasby" <jnasby(at)pervasive(dot)com> wrote:
>
> > FWIW, RAID5 isn't normally a good recipe for good database performance.
>
> Understood, but RAID 1 isn't always feasible. The database performs very
> well with the current hardware configuration - I have no complaints. The
> only time we have issues is during index rebuilds or a reload.

I think Jim was thinking of RAID 1+0. If you gotta use RAID 5, at least
have a contoller with several hundred megs of battery backed cache.
Better yet, use a RAID controller with a gig or so of BBU cache and run
RAID 1+0 on it. If you can.

> > Also, why are you frequently dumping and restoring?
>
> We have to periodically reindex due to the fact that vacuum processes
> indexes in index order instead of disk space order. A 'vacuum full' is
> currently taking about 24 hours. After a reindex, I expect that to drop to
> 3-4 hours until the index becomes fragmented. I don't think much can be
> done about the time to reindex (or the order vacuum processes indexes).

Actually, the same question about why your frequently dumping and
restoring applies to full vacuums. Why are you doing them? A properly
running database should not need vacuum fulls. Nor reindexing.

Standard vacuums, scheduled to run often enough, combined with a large
enough Free Space Map should mean never needing a full vacuum or
reindex. If vacuums slow your system down too much, then you don't have
enough I/O bandwidth, and need to see the previous point about a better
RAID setup.

You should look into slony. You can replicate from one version of pgsql
to another, a feature intended to be used for updating.

That way, you can put an 8.1 server behind your 7.4 or 8.0 server,
replicate to it, wait for it to catch up, shut down your app, fail over
your server to the 8.1 machine and viola, you're on 8.1 with minimum
downtime.