Re: REINDEX vs VACUUM

I don't think VACUUM FULL (copy the table, create new indices and other
metadata all in one command) actually vacuums tables.  It's a misleading name.

Something like REBUILD TABLE would be a better name.

On 1/4/23 07:25, Rébeli-Szabó Tamás wrote:
> 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
>
>

--
Born in Arizona, moved to Babylonia.