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.