Re: Specific questions about wraparound and vacuum

On Wed, Aug 08, 2007 at 09:55:54PM -0400, Nick Fankhauser wrote:
> We did in fact just increase the fsm values significantly based on the
> feedback we were getting from the vacuum messages. We do nightly
> non-full vacuums. Am I to understand that if we increase our fsm
> allocation to a sufficient size, we should not be losing any space?

Correct, though of course you could build up substantial bloat during
the day.

Back when I was running a 7.4 database, I made use of pg_autovacuum from
contrib to help keep things in hand, but you might not be able to handle
vacuums firing off at random times without vacuum cost delay. Vacuuming
key tables more frequently via cron might be a better strategy, but if
you do want to run pg_autovac I've got a script that will help.

BTW, http://decibel.org/~decibel/pervasive/fsm.html is something I wrote
while at Pervasive that explains how the FSM works.

> The "modern version" upgrade is on our wish list, but as it's a
> production system incorporating many technologies, we've had
> priorities elsewhere for a while, and 7.4 has been so darn stable and
> productive that the only motivation to move forward is so I don't have
> to feel ashamed to admit how far back we are. On the bright side, it's
> an indication of how good postgresql is that a growing business has
> had no issues with a quite old version.
>
> Regards,
> -Nick
>
>
> On 8/8/07, Decibel! <decibel(at)decibel(dot)org> wrote:
> > On Wed, Aug 08, 2007 at 12:07:14PM -0400, Nick Fankhauser wrote:
> > > 2) If a regular (non-full) vacuum will not reset the XID. Will a
> > > dump/restore take care of wraparound? We have done this in the past for
> > > space reclamation because we seem to be able to dump/restore more quickly
> > > than we can do a full vacuum.
> >
> > If you're doing that you need to re-evaluate your vacuuming strategy and
> > possibly your free space map settings. You should normally never need to
> > use pg_dump(all) or vacuum full to reclaim space.
> >
> > If you've got the ability to take enough downtime to dump and restore,
> > you should really use that opportunity to upgrade to a modern version,
> > too.
> > --
> > Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
> > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
> >
> >
>
>
> --
> ------------------------------------------------------------------
> Nick Fankhauser
> nickf(at)doxpop(dot)com
> http://www.doxpop.com
> 765.965.7363
> 765.962.9788 (Fax)
> Doxpop - Public Records at Your Fingertips.
>

--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)