Re: Performance and WAL on big inserts/updates

sailesh(at)EECS(dot)Berkeley(dot)EDU writes:
> - Re uni-directional logs

> Of course. I forgot about PG's non-in-place update mechanisms and the
> use of VACCUUM .. with versioning there are really no undo logging
> necessary. I guess that means that during VACCUUM you might have to
> significant work in indexes ? I'm assuming that you never merge index
> pages.

Yes, VACUUM has to delete dead index entries as well as dead heap
tuples, and there are some fine points about making sure that happens
in a safe order.

I believe the current state of index space recovery is

* btree: recycles emptied index pages via a freelist; can return empty
pages to the OS if they're at the end of the index file, but will not
move pages around to make it possible to return more empty pages.
(This is all new behavior as of 7.4, before we didn't do anything about
reclaiming dead space in btrees.) Does not try to merge partially-full
pages (this is a possible future improvement but there are concurrency
problems that would need to be solved).

* hash: recycles empty pages via a freelist, never returns them to OS
short of a REINDEX. I think it does merge partially-empty pages within
each bucket chain. No provision for reducing the number of buckets
if the index population shrinks (again, short of REINDEX).

* rtree, gist: no page recycling that I know of, but I've not looked
carefully.

regards, tom lane