Default tablespace for indexes in postgres

Question

Just wondering if its possible to set a default tablespace in postgres to keep indexes. Would like the databases to live on the default tablespace for postgres, however, would like to get the indexes on a different set of disks just to keep the i/o traffic separated.

It does not appear to me that it can be done without going in and doing an ALTER index TABLESPACE command, and then the index is moved and will stay there, but the databases and indexes are part of a django app, so non-django intervention can cause some problems.

Answer 1

It doesn't look like there's a way to specify a default index tablespace (that is other than the database's own default tablespace). I've always specified them manually. It's fairly easy to write a query to check for indices that are in the wrong tablespace, so you could at least install that as part of your database monitoring.

IMHO you shouldn't have to be worried about making changes like that to the database outside of the app since it's a database implementation detail the app doesn't grok. If the app has a problem with it, the app is broken.