1

We are using PostgreSQL 10 and we need to reindex tables to reorganize data and free space.

The problem we met is that this operation locks tables that we need to remain available at all time.

We found a workaround, but this seems to free much less space than a standard reindex, so we turned that off.

Is there some solution that will allow us to reindex, and to have the table available all the time?

1

1 Answer 1

5

PostgreSQL documentation at https://www.postgresql.org/docs/10/sql-reindex.html says the following:

REINDEX is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch. However, the locking considerations are rather different. REINDEX locks out writes but not reads of the index's parent table. It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index. In contrast, DROP INDEX momentarily takes an exclusive lock on the parent table, blocking both writes and reads. The subsequent CREATE INDEX locks out writes but not reads; since the index is not there, no read will attempt to use it, meaning that there will be no blocking but reads might be forced into expensive sequential scans.

So based on your requirements, you could change REINDEX into DROP INDEX + CREATE INDEX, which is what Pg::Reindex that you link to does.

The associated PostgreSQL documentation about why REINDEX is useful for B-Trees indexes reiterates the idea (at https://www.postgresql.org/docs/10/routine-reindex.html):

REINDEX can be used safely and easily in all cases. But since the command requires an exclusive table lock, it is often preferable to execute an index rebuild with a sequence of creation and replacement steps. Index types that support CREATE INDEX with the CONCURRENTLY option can instead be recreated that way. If that is successful and the resulting index is valid, the original index can then be replaced by the newly built one using a combination of ALTER INDEX and DROP INDEX. When an index is used to enforce uniqueness or other constraints, ALTER TABLE might be necessary to swap the existing constraint with one enforced by the new index. Review this alternate multistep rebuild approach carefully before using it as there are limitations on which indexes can be reindexed this way, and errors must be handled.

Other than that the classical methodology is to create a new table, copy data into it, index it, and then rename it so that it takes the place of the old table. Of course you will need to handle properly accesses during that time, and all synchronization errors.

Also more generally how did you count about the free space gained with each method, and are you using autovacuum properly? Are you really so much constrained by space continously that you need to do this reindexing regularly? Otherwise you could schedule it during other maintenance windows.

1
  • Thanks a lot for the explanation. Yes, we need reindexing because database grows fast and we have about +7GiB free memory after reindex as well as just 200MiB after repack (on ~300Gb database). And also, yes we use avtovacuum Commented Nov 15, 2018 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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