Scott Marlowe wrote:
On Wed, Nov 12, 2008 at 8:13 AM, Jack Orenstein <[EMAIL PROTECTED]> wrote:
My application is running on 7.4. We have one huge table that drives
SNIP
We're in the process of upgrading to 8.3.4, so I'd appreciate any
throughs on whether and how this behavior will change with the newer
release.
You will not believe how much faster 8.3 is, and how much easier
maintenance is. You'll be like a kid in a candy store for months
looking at and using all the new features in it. The improvements are
enormous. Biggest difference for you is that 8.3 can do vacuums in a
background method (it sleeps x milliseconds between pages), can run 3
or more threads, and autovacuum daemon is on by default. For the most
part, your vacuuming issues will no longer exist.
Our 7.4 vacuuming strategy has gotten pretty complex:
- Entire database once a week.
- (Eventually) biggest table in database: Vacuumed/analyzed every 1000 updates
until there are 10,000 rows, to ensure that optimizer does the right thing,
(discussed recently on this mailing list).
- Medium-sized table containing single-row concurrency hotspots. Usually less
than 1M rows: vacuumed every 2000 updates.
- Single-row tables - these are permanent hotspots, updated in every
transaction: vacuumed every 2000 updates.
Can you comment on how I'll be able to simplify this vacuum schedule by relying
on autovacuum? Can you point me at a document describing how autovacuum decides
when to vacuum a table?
I've also had some trouble figuring out which VACUUMs should ANALYZE.
Originally, I had every vacuum also run analyze (except for the tiny-table
vacuums). But I ran into the "tuple concurrently updated" problem (see
http://archives.postgresql.org/pgsql-sql/2005-05/msg00148.php), so I've had to
back off from that. Are concurrent analyzes OK in 8.3?
Jack
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
