Re: Subject: bool / vacuum full bug followup part 2

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> Yes, and yes, but don't hold your breath on the latter part --- that
> >> TODO item has been around for awhile. And it's gotten harder now that
> >> we have lazy VACUUM; that means we need to be able to condense indexes
> >> concurrently with other index operations.
>
> > Can you remind me why it is so hard to fix this. I do not expect lazy
> > vacuum to handle index shrinking, but it should be possible with full
> > vacuum.
>
> If you make that restriction then it might be less painful to do. I
> have not thought about doing it that way; I'm of the opinion that only
> a solution that lets lazy vacuum do it will be a real solution.

Let me give you my logic on this --- to me it seems lazy vacuum already
allows index reuse, but there is a restriction that the reuse can only
be used by new keys that are similar to expired keys. It is hard to
imagine how you would implement btree index page _reuse_ --- I guess you
could have the page in some kind of free page map and use that when you
need a new index page. Condensing index entries across pages into a single
page seems almost impossible under concurrent access, and without that
capability, it seems pretty useless.

Now, with full vacuum, people expect the disk space to be returned, and
we have a full lock on the relation, so it seems a much simpler problem
and one people would expect to free.

I am saying that concurrent freeing vs. exclusive lock freeing seem like
different problems to me, and that we should at least be able to get
exclusive lock freeing working, somehow, even if we have to punt and
just automatically reindex the index when a certain percentage of the
index is dead entries. I honestly think auto-reindex is going to be the
easiest and fastest solution in most cases. Heck, I think reindex is
faster than updating the index entries with the new post-vacuum location
in cases with a large percentage of dead tuples. (Worst case is
deleting 50% of the tuples because vacuum moves the other 50% and has to
update the index.) I think this was reported about a year ago. We even
have a TODO item:

* Improve speed with indexes (perhaps recreate index instead) [vacuum]

I guess what I am saying is that we have two index/vacuum problems, and
some type of auto-reindexing could fix all of them in one shot.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026