Re: REINDEX vs VACUUM

Here is my understanding:

REINDEX recreates the index from scratch, using the data stored in the
underlying table. It is the same as dropping and recreating the index
manually, with regard to the impact on the index file. It can free up
physical space in the file system. REINDEX will not vacuum the index.

VACUUM does many different things. One of them is vacuuming indexes (for
the underlying table that is being vacuumed). VACUUM will remove index
entries that are pointing to dead rows in the underlying table. VACUUM
will not rebuild the entire index.

VACUUM recycles free index blocks (using FSM), but it does not (usually)
free up space for the file system physically. VACUUM FULL does that.

VACUUM FULL will vacuum the index, but it will do it by making a copy of
the index (file) and reorganizing its content in order to free up space
physically. In that regard, it is like REINDEX. Both VACUUM FULL and
REINDEX will block reads from the index during the process (by taking an
ACCESS EXCLUSIVE lock).

Regards,

tamas

2022. 12. 31. 22:04 keltezéssel, Hao Zhang írta:
> What is the difference between reindex and vacuum's impact on index
> file? I deleted an indexed row. Running either vacuum or reindex shows
> the index entry for the row is removed from the index page. I was
> under the impression that only reindex will remove dangling index
> entries. I am guessing that vacuum will not shrink the index file and
> will only add deleted index entries in the free space file for the
> index? But REINDEX is recreating the index file from scratch so it is
> like vacuum full for index?
>
> Thanks