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) ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend