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
>>
>>
>