Re: Why so slow?

"Bealach-na Bo" <bealach_na_bo(at)hotmail(dot)com> wrote:

> >If you don't need access to the old data constantly:
> >
> > - copy the live data to a new table
> > - TRUNCATE the old table (which needs an exclusive lock but is very fast)
> > - insert the data back in
> > - for an event log I would imagine this could work
>
> Obtaining exclusive locks on this table is very difficult, or rather,
> will make life very difficult for others, so I'm averse to running
> vacuum full or truncate (though I don't know how fast truncate is)
> on a regular basis. I might just get away with running it
> once a month, but no more.
>
> (Lazy) vacuum, however is a much more palatable option. But (lazy)
> vacuum does not always reclaim space. Will this affect performance and
> does this mean that a full vacuum is unavoidable? Or can I get away
> with daily (lazy) vacuums? Disk space is not an issue for me, but
> performance is a BIG issue. Of course, I realize that I could improve
> the latter with better schema design - I'm working on a new schema,
> but can't kill this one yet :|.

My understanding is basically that if you vacuum with the correct
frequency, you'll never need to vacuum full. This is why the
autovacuum system is so nice, it adjusts the frequency of vacuum according
to how much use the DB is getting.

The problem is that if you get behind, plain vacuum is unable to get things
caught up again, and a vacuum full is required to recover disk space.

At this point, it seems like you need to do 2 things:
1) Schedule lazy vacuum to run, or configure autovacuum.
2) Schedule some downtime to run "vacuum full" to recover some disk space.

#2 only needs done once to get you back on track, assuming that #1 is
done properly.

A little bit of wasted space in the database is OK, and lazy vacuum done
on a reasonable schedule will keep the level of wasted space to an
acceptable level.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com