Re: Adding foreign key constraints without integrity

On 6/19/06 3:47 PM, "Jim Nasby"  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.

> This won't help with constraint checking, but you should consider
> turning fsync off during your restore.

I didn't think about doing that, but I wonder how much it would help?  The
actual data load only takes about 6 hours or so.  It's all the index
building and constraint checking that takes the time.  This time seems to be
spent mostly writing temp files and sorting.

> 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).

I wouldn't say we dump/reload frequently.  Right now I'm doing a trial run
for the 8.x upgrade - I have to make sure I can complete it in the allotted
time.  However, about a year ago, I had to do multiple reloads in a 2 week
period.  We had database corruption, and had to reload from source to get a
good database - multiple times.  We had a defective disk array that wasn't
reporting any errors but kept corrupting the database.

Besides PG upgrades, I have to consider how long it takes to recover from
system failures if I can't use the offline file system backup.  I'll be
switching to online backups once we get upgraded, but if a reload fails
there, I'll again have to fall back to the weekly source backup.

Wes