Kieren Scott <kierenscott@hotmail.com> wrote:
> Could you explain what causes index bloat when running vacuum
> full?
To collapse the space, it copies tuples to locations closer to the
front of the table. The index needs to contain references to the
old and new tuple copies until the VACUUM FULL commits or rolls
back. VACUUM FULL doesn't attempt to reorganize the index or free
index space, so the space previously held by index entries pointing
to the old tuples (if you commit) or the new tuples (if you roll
back) will be dead space. It may be reused by later index
insertions, but the empty space is likely to cause a lot of
partially-filled blocks to bog down performance unless there is a
REINDEX.
-Kevin