Re: Index File locations

On 08/27/10 8:43 AM, A. Kretschmer wrote:
> In response to Callum Scott :
>> Hi All,
>>
>> I am looking for a way to define where index files should be located.
>>
>> I am wanting to store the index files on a separate SSD from the database
>> itself. I can see that there is a way to define this from within the
>> database. Is there a way to do this at a system level within the configuration
>> file?
> You have to define a own tablespace and then you can create new indexes
> on this tablespace.
>
> test=# \h create tablespace
> Command: CREATE TABLESPACE
> Description: define a new tablespace
> Syntax:
> CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory'
>
> test=# \h create index
> Command: CREATE INDEX
> Description: define a new index
> Syntax:
> CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
> ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS {
> FIRST | LAST } ] [, ...] )
> [ WITH ( storage_parameter = value [, ... ] ) ]
> [ TABLESPACE tablespace ]
> [ WHERE predicate ]
>

you can also ALTER INDEX indexname SET TABLESPACE tablespacename;

this will move the existing index to the specified tablespace.

note that you want to do this when your applications are quiescent as
this will require an exclusive access lock for the duration of the move.