Re: Question on REINDEX

"Tambet Matiisen" <t(dot)matiisen(at)aprote(dot)ee> writes:
> Is my current understanding correct:

> 1) VACUUM defragments each page locally - moves free space to the end of
> page.

> 2) VACUUM FULL defragments table globally - tries to fill up all
> partially free pages and deletes all resulting empty pages.

Both versions of VACUUM do within-page defragmentation. Also, both
versions will remove entirely-empty pages at the end of a table.
The difference is that VACUUM FULL actively attempts to make pages
at the end empty, by moving their contents into free space in earlier
pages. Plain VACUUM never does cross-page data movement, which is
how come it doesn't need as strong a lock.

BTW, VACUUM FULL does the data movement back-to-front, and stops as soon
as it finds a tuple it cannot move down; which is a reasonable strategy
since the goal is merely to make the file shorter. But it's entirely
likely that there will be lots of empty space left at the end. For
instance the final state could have one 4K tuple in the last page and
up to 4K-1 free bytes in every earlier page.

> 3) Both VACUUM and VACUUM FULL do only local defragment for indexes.

> 4) If you want indexes to become fully defragmented, you need to
> REINDEX.

I don't think "defragment" is a notion that applies to indexes, at least
not in the same way as for tables. It's true that there is no
cross-page data movement in either case. In the last release or two
we've been able to recognize and recycle entirely-empty pages in both
btree and hash indexes, but such pages are almost never returned to the
OS; they're put on a freelist for re-use within the index, instead.

If you allow the table to grow to much more than its "normal" size,
ie, you allow many dead tuples to be formed, then getting back to
"normal" size is going to require VACUUM FULL + REINDEX (or you can use
CLUSTER or some varieties of ALTER TABLE). This is not the recommended
maintenance process however. Sufficiently frequent plain VACUUMs should
generally hold the free space to a tolerable level without requiring
any exclusive locking.

> Hmm, thanks for a tip. BTW, is output of
> select count(1), sum(relpages) from pg_class where relkind in
> ('r','i','t')
> good estimate for max_fsm_relations and max_fsm_pages?

Within that one database, yes --- don't forget you must sum these
numbers across all DBs in the cluster. Also you need some slop
in the max_fsm_pages setting because of quantization in the space
usage. It's probably easier to let VACUUM VERBOSE do the calculation
for you.

regards, tom lane