An unexpected find that freed 20GB of unused index space in PostgreSQL

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.

[ Parent | on HN ]

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?

[ Parent | on HN ]

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

[ Parent | on HN ]

Re:An unexpected find that freed 20GB of unused index space ... by jvolkman on Monday February 01, 2021 @12:44PM (#25991033)

I believe pg_repack can do that.

[ Parent | on HN ]