Thread: Index File locations

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?

Regards
Callum

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 ]


--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

  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.