Re:An unexpected find that freed 20GB of unused index space ...
by brianwawok on Monday February 01, 2021 @10:51AM
(#25989672)
A vacuum full basically does this for a table, copying the data from location A to location B, cleaning up junk. I think index rebuilding may take a separate command?
[ Parent
| on HN ]
Re:An unexpected find that freed 20GB of unused index space ...
by Tostino on Monday February 01, 2021 @10:59AM
(#25989742)
Vacuum full does a index rebuild automatically. Since a vacuum full builds an entire new heap table, the old indexs are all pointing to the incorrect locations for all tuples, so it has no choice but to rebuild.
Re:An unexpected find that freed 20GB of unused index space ...
by brianwawok on Monday February 01, 2021 @12:16PM
(#25990651)
Excellent there you go.
Is there a way to just do the index build part, short of dropping an index and adding it back?
Re:An unexpected find that freed 20GB of unused index space ...
by Jedd on Monday February 01, 2021 @05:44PM
(#25995155)
This is described about a quarter the way into TFA.
> REINDEX INDEX CONCURRENTLY index_name;
(A vanilla REINDEX will lock the table, preventing writes, while it runs. The CONCURRENT creates a new index, replicates any updates to the original while it does so, and then does an atomic switcheroo at the end.)