Thread: PATCH: default_index_tablespace
Hi, 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. Feedback appreciated. Thanks, / J.L.
Attachment
jltallon@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. regards, tom lane
On Wed, Apr 15, 2015 at 07:12:11PM -0400, Tom Lane wrote: > jltallon@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.) -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
* Bruce Momjian (bruce@momjian.us) wrote: > On Wed, Apr 15, 2015 at 07:12:11PM -0400, Tom Lane wrote: > > jltallon@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.) 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
On Thu, Apr 16, 2015 at 8:01 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Wed, Apr 15, 2015 at 07:12:11PM -0400, Tom Lane wrote:
> > jltallon@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
>
> On Wed, Apr 15, 2015 at 07:12:11PM -0400, Tom Lane wrote:
> > jltallon@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.
<p dir="ltr"><br /> On 15 Apr 2015 19:12, "Tom Lane" <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br/> ><br /> > I'm afraid this idea is a nonstarter, because it will break existing<br /> > applications,and in particular existing pg_dump output files, which<br /> > expect to be able to determine an index'stablespace by setting<br /> > "default_tablespace". (It is *not* adequate that the code falls back<br /> >to "default_tablespace" if the new GUC is unset; if it is set, you've<br /> > still broken pg_dump.) The incrementalvalue, if indeed there is any,<br /> > of being able to control index positioning this way seems unlikely to<br/> > justify a backwards-compatibility break of such magnitude.<p dir="ltr">Just brainstorming here but that justmeans "default_tablespace" needs to take precedence. We could have a default_table_tablespace and default_index_tablespacewhich default_tablespace overrides. Or we could allow a mini config language in default_tablespacelike "table=space1,index=space2".
On 4/15/15 11:33 PM, Amit Kapila wrote: > On Thu, Apr 16, 2015 at 8:01 AM, Bruce Momjian <bruce@momjian.us > <mailto:bruce@momjian.us>> wrote: >> >> On Wed, Apr 15, 2015 at 07:12:11PM -0400, Tom Lane wrote: >> > jltallon@adv-solutions.net <mailto:jltallon@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. >> > > > 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 notusually 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@pgmasters.net
>> > >> > 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.) Got it. Thank you for the feedback. >> 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.. Any suggestions on how to do it "properly"? Does Greg Stark's suggestion (at <CAM-w4HPOASwsQMdGZqjyFHNubbUnWrUAo8ibci-97UKU=poDbg@mail.gmail.com>) make sense to you? This approach might suffer from the same problem as mine, though. It seems to me, IMVHO, a limitation in pg_dump ---since 8.0 when tablespace support for CREATE INDEX was implemented--- that we should fix. Keeping backwards compatibility is indeed required; I just did not think about pg_dump at all :( I don't mind reworking the patch or redoing it completely once there is a viable solution. Thanks, / J.L.
J.L., * jltallon@adv-solutions.net (jltallon@adv-solutions.net) wrote: > Any suggestions on how to do it "properly"? > Does Greg Stark's suggestion (at > <CAM-w4HPOASwsQMdGZqjyFHNubbUnWrUAo8ibci-97UKU=poDbg@mail.gmail.com>) > make sense to you? > This approach might suffer from the same problem as mine, though. Well, Greg's suggestion was intended to specifically avoid breaking pg_dump by having two new GUCs and having default_tablespace take precedence, if set. > It seems to me, IMVHO, a limitation in pg_dump ---since 8.0 when > tablespace support for CREATE INDEX was implemented--- that we > should fix. > Keeping backwards compatibility is indeed required; I just did not > think about pg_dump at all :( Limitation strikes me as not quite the right term, but I certainly agree that it's unfortunate that pg_dump uses that GUC instead of adding the TABLESPACE clause to the CREATE INDEX, then again, there are likely to be historical reasons for that. Unfortunately, not break existing pg_dump-generated files is pretty tough. > I don't mind reworking the patch or redoing it completely once there > is a viable solution. Having three GUCs in the end might work but it seems kind of grotty to have the more-specific GUCs be overridden by the less-specific GUC. We could throw a warning if the more-specific GUC is attempted to be set while the less-specific GUC is set, and vis-versa, and essentially make them "either/or". That'd cause additional warnings to be thrown when restoring an older dump, but if pg_dump was modified to use the TABLESPACE clause for CREATE INDEX for new dump files then that's only a temporary situation. Thanks! Stephen