I have a large PostgreSQL 9.3 database. One index on one table is quite large and appears to be bloated. I am using pgcompact from pgtoolkit to reduce database bloat without heavy table locks. However it would not reindex that large index, since it's a GIN index. It suggested this SQL snippet as a way to reindex this index. (
mytable is the table,
myindex is the index.
myindex is just a GIN index on the
nodes (bigint) column of
CREATE INDEX CONCURRENTLY pgcompact_index_17791 ON mytable USING gin (nodes) WITH (fastupdate=off); BEGIN; SET LOCAL statement_timeout TO 1000; ALTER INDEX public.myindex RENAME TO pgcompact_temp_index_17791; ALTER INDEX public.pgcompact_index_17791 RENAME TO myindex; END; DROP INDEX CONCURRENTLY public.pgcompact_temp_index_17791;
Will this snippet actually reindex the
myindex index and reduce
table index bloat?
What is the point of renaming the old index (
myindex) to the name of the temporary index (
pgcompact_temp_index_17791), and then renaming it back again? Won't that result in overwriting the currently being created temp index and then just renaming it back, meaning that
myindex isn't rebuilt at all? The author of pgtoolkit appears quite knowledgeable, but I don't understand how this works, and I don't want to run SQL on my database without understanding it.
If this approach works, what's the difference between this and doing
REINDEX INDEX myindex;?