Re: PATCH: default_index_tablespace

On Wed, Apr 15, 2015 at 07:12:11PM -0400, Tom Lane wrote:
> 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.
>
> 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.)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +