Re: unused tuples constantly increasing

Chantal Ackermann <chantal(dot)ackermann(at)biomax(dot)de> writes:
> What I am doing: I want to build a tsearch index for one of the columns
> (called "sentence") in this table. For this I added a new column called
> "sentenceidx" which now has to be populated with the parsed text from
> "sentence", using txt2txtidx (comes with contrib/tsearch).

> The script I use updates 2000 rows at once, then runs a VACUUM VERBOSE
> on the table.

"Update" here means storing a new value into the column that was
formerly NULL? So the rows are getting wider as you update them?
How much wider (how big is sentenceidx compared to the rest of the row)?

I think what you're seeing is simply that the new, fatter rows don't
fit in the spaces that are freed up by vacuuming the old ones. If you
were updating enough rows so that many got freed on the same page,
there would be a better shot at recycling the space --- but AFAICT only
one or two per page are getting freed in any one VACUUM cycle. You are
getting some recycling: these messages show about 140 pages modified
in each cycle, but only 40 pages added, so it is recycling space. But
the table is inevitably going to grow, just because you are putting in
more data than was there before.

My advice is to be a little less frantic about space management.
The rule of thumb I usually use is that one should vacuum when there's
been about 10% turnover in a table. Vacuuming after only 0.1% turnover
is a waste of cycles.

regards, tom lane