Re: PATCH: default_index_tablespace

On 4/15/15 11:33 PM, Amit Kapila wrote:
> On Thu, Apr 16, 2015 at 8:01 AM, Bruce Momjian <bruce(at)momjian(dot)us
> <mailto:bruce(at)momjian(dot)us>> wrote:
>>
>> On Wed, Apr 15, 2015 at 07:12:11PM -0400, Tom Lane wrote:
>> > jltallon(at)adv-solutions(dot)net <mailto:jltallon(at)adv-solutions(dot)net> writes:
>> > > This small patch implements a new GUC (default_index_tablespace) plus
>> > > supporting code.
>> > > Originated from a customer request, the feature intends to make
>> > > creation of indexes on SSD-backed tablespaces easy and convenient
>> > > (almost transparent) for users: the DBA can just set it and
> indexes will
>> > > be placed in the specified tablespace --as opposed to the same
>> > > tablespace where the referenced table is-- without having to
> specify it
>> > > every time.
>> >
>
> Another way to provide different default tablespace for index could be
> to provide it at Database level. Have a new option INDEX_TABLESPACE
> in Create Database command which can be used to create indexes
> when not specified during Create Index command. This would also need
> changes in pg_dump (like while dumping info about database) but on
> initial look, it seems it can be done without much changes.

That's same idea that Stephen and I have discussed in the past.
Something like:

CREATE DATABASE name
SET TABLESPACE table_volume
SET INDEX TABLESPACE index_volume;

This has some real usability advantages. In the past I've written code
to move tables to where they need to be once the db update is complete.
The tables tend to be small or empty so this is not usually a big deal
- but sometimes it is. Trying to get a tablespace clause on every index
in the build scripts is a real PITA.

--
- David Steele
david(at)pgmasters(dot)net