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
