On Thu, Apr 16, 2015 at 8:01 AM, Bruce Momjian <br...@momjian.us> wrote: > > On Wed, Apr 15, 2015 at 07:12:11PM -0400, Tom Lane wrote: > > jltal...@adv-solutions.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. > > > > I'm afraid this idea is a nonstarter, because it will break existing > > applications, and in particular existing pg_dump output files, which > > expect to be able to determine an index's tablespace by setting > > "default_tablespace". (It is *not* adequate that the code falls back > > to "default_tablespace" if the new GUC is unset; if it is set, you've > > still broken pg_dump.) The incremental value, if indeed there is any, > > of being able to control index positioning this way seems unlikely to > > justify a backwards-compatibility break of such magnitude. > > I can see why someone would want this because random I/O, which is > frequent for indexes, is much faster on SSD than magnetic disks. > (Sequential I/O is more similar for the two.) >
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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
