Re: REINDEX vs VACUUM

I have looked more into it and have found that VACUUM FULL (and CLUSTER) 
does in fact rebuild indexes, see for example: 

https://github.com/postgres/postgres/blob/c8e1ba736b2b9e8c98d37a5b77c4ed31baf94147/src/backend/commands/cluster.c#L1463

I have also run some tests and have come to understand that REINDEX 
seems to "vacuum" the index in the sense that a subsequent VACUUM on the 
underlying table will not find any removable entries in the index.

Ron is right, the term "vacuum" may be misleading.

2023. 01. 04. 16:34 keltezéssel, Ron írta:
> 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
>>
>>
>