Avoid tables locking while doing reindex 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?
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.