Re: PATCH: default_index_tablespace

* Bruce Momjian (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 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.)

The general idea is something I've brought up previously also (I believe
it was even discussed at the Dev meeting in, uh, 2013?) so I'm not
anxious to simply dismiss it, but it'd certainly have to be done
correctly..

Thanks,

Stephen