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

CREATE INDEX CONCURRENTLY pgcompact_index_17791 ON mytable USING gin (nodes) WITH (fastupdate=off);
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;
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;?

  • Re-creating an index will only reduce index bloat, never table bloat. The renaming is not really necessary unless you rely on that index name somewhere in your application. Jan 11 '16 at 15:10
  • @a_horse_with_no_name Sorry, you're right., I mistyped. Index bloat is all I'm concerned with here. Jan 11 '16 at 15:44

Look at the names more carefully. pgcompact_temp_index_17791 and pgcompact_index_17791 are not the same thing.

The reason to rename the old index is that you can't do "drop index concurrently" inside of a transaction.

  • D'oh! Thank you for spotting that! I clearly didn't read that closely enough. Jan 12 '16 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.