Re: vacuum, performance, and MVCC

Adding back pgsql-hackers.

Mark Woodward wrote:
> > Mark Woodward wrote:
> >
> >> Hmm, OK, then the problem is more serious than I suspected.
> >> This means that every index on a row has to be updated on every
> >> transaction that modifies that row. Is that correct?
> >
> > Add an index entry, yes.
> >
> >> I am attaching some code that shows the problem with regard to
> >> applications such as web server session management, when run, each
> >> second
> >> the system can handle fewer and fewer connections. Here is a brief
> >> output:
> >> [...]
> >> There has to be a more linear way of handling this scenario.
> >
> > So vacuum the table often.
>
> That fixes the symptom, not the problem. The problem is performance
> steadily degrades over time.

No, you got it backwards. The performance degradation is the symptom.
The problem is that there are too many dead tuples in the table. There
is one way to solve that problem -- remove them, which is done by
running vacuum.

There are some problems with vacuum itself, that I agree with. For
example it would be good if a long-running vacuum wouldn't affect a
vacuum running in another table because of the long-running transaction
effect it has. It would be good if vacuum could be run partially over a
table. It would be good if there was a way to speed up vacuum by using
a dead space map or something.

I have a pending patch which adds the possibility of "non-transactional
catalogs". Maybe that could evolve into non-transactional user tables
eventually, which would have problems of their own but maybe could be
used in certain very limited scenarios, like the sessions table.

--
Alvaro Herrera Developer, http://www.PostgreSQL.org
"Oh, great altar of passive entertainment, bestow upon me thy discordant images
at such speed as to render linear thought impossible" (Calvin a la TV)