I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production system, and the index is useful for making read performance acceptable, so I want the index to be usable as an index while it is being moved from one tablespace to another. Data writes are not a problem, they can be halted, turning the database into essentially a "read-only database".
But I can't find information in the postgres docs about this.
The PostgreSQL 9.3 docs for ALTER INDEX don't mention locks:
This form changes the index's tablespace to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. See also CREATE TABLESPACE.
The PostgreSQL 9.4 docs for ALTER INDEX mention locks but doesn't say which locks, and only when refering to ALL IN TABLESPACE form.
This form changes the index's tablespace to the specified tablespace and moves the data file(s) associated with the index to the new tablespace. To change the tablespace of an index, you must own the index and have CREATE privilege on the new tablespace. All indexes in the current database in a tablespace can be moved by using the ALL IN TABLESPACE form, which will lock all indexes to be moved and then move each one. This form also supports OWNED BY, which will only move indexes owned by the roles specified. If the NOWAIT option is specified then the command will fail if it is unable to acquire all of the locks required immediately. Note that system catalogs will not be moved by this command, use ALTER DATABASE or explicit ALTER INDEX invocations instead if desired. See also CREATE TABLESPACE.
So if I run an ALTER INDEX ... SET TABLESPACE ... will I still be able to read from the index while it is being moved?
Best Answer
Try to recreate the index concurrently, so the original one is still used while creating the new.