4

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?

Improve this question
2
  • 1
    I would assume it takes an exclusive lock, so the index won't be usable. But you could simple create an identical index (with a different name) on the new tablespace and once that is completed, drop the old one.
    – user1822
    Commented May 7, 2016 at 11:23
  • @a_horse_with_no_name The indexes are big. And could take a long time to create
    – Amandasaurus
    Commented May 8, 2016 at 9:48

1 Answer 1

Reset to default
2

Try to recreate the index concurrently, so the original one is still used while creating the new. 

CREATE INDEX CONCURRENTLY idx_name 
ON table_name
USING BTREE(col_name)
TABLESPACE new_tblspace
Improve this answer

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.