Re: 7.3.4 on Linux: UPDATE .. foo=foo+1 degrades massivly

On Thu, 22 Apr 2004, Dann Corbit wrote:

> > -----Original Message-----
> > From: Guy Fraser [mailto:guy(at)incentre(dot)net]
> > Sent: Thursday, April 22, 2004 8:44 AM
> > To: pgsql-general(at)postgresql(dot)org
> > Subject: Re: [GENERAL] 7.3.4 on Linux: UPDATE .. foo=foo+1
> > degrades massivly
> >
> >
> > Dann Corbit wrote:
> >
> > >>>A following VACCUM brings back return times to 'start' -
> > >>>
> > >>>
> > >>but I cannot
> > >>
> > >>
> > >>>run VACUUM any other minute (?). And it exactly vaccums as
> > >>>
> > >>>
> > >>many tuples
> > >>
> > >>
> > >>>as I updated.. sure thing:
> > >>>
> > >>>
> > >>Why not? You only have to vacuum this one table. Vacuuming it
> > >>once a minute should be doable.
> > >>
> > >>
> > >
> > >Shouldn't the Database server be the entity that decides
> > when vacuum is
> > >needed?
> > >
> >
> > How is the database supposed to know when you want to purge
> > records? Once a vacuum has been run, the table can not be
> > rolled back or time traveled.
>
> When I say commit or rollback, I don't need the dead records any longer.

OK. Scenario. There are 5,000 users connected. When one doesn't need
records anymore, should the database now poll att 4999 other users to see
if it's ok to flush those tuples? What method are you going to use to
find out if the tuples are invisible to every other transaction /
connection currently running?

> I think that the problems I am seeing are due to using a much older
> version of PostgreSQL. We use 7.1.3 here, because we have thoroughly
> tested it (many thousands of tests are in our regression suite). But if
> I delete too many records, the only way I can reclaim the space is to
> drop the table.
>
> We are working with the beta of 7.5 and perhaps it will cure all the
> ills that remain.

That is an old version. I would say that PostgreSQL is one of the few
products that has increased in stability with nearly every single release
cycle. Upgrading to 7.4 or 7.5 would probably be a very good idea.

Index bloat was fixed in 7.4. I'm not sure if there were any table bloat
problems not solved by full vacuums as far back as 7.1, but I don't
remember any problems with vacuuming back then.