Обсуждение: make default TABLESPACE belong to target table.
Dear pgsql community, I've been using postgres for a long time. Recently I'm doing table sharding over a bunch of pgsql instances. I'm using multiple tablespaces one per disk to utilize all the IO bandwidth. Things went on pretty well, however there is a troublesome problem I have when adding auxiliary structures to sharded tables, such as Indexes. These objects have their storage default to the database's tablespace, and it's difficult to shard them by hand. I'd like to implement this small feature --- making table's auxiliary structures store their data to the target table's tablespace by default. I've done a thorough search over the mailing list and there is nothing relevant. Well I may miss some corners :-) What do you think? Regards, Amos
On Fri, Nov 25, 2016 at 4:48 PM, Amos Bird <amosbird@gmail.com> wrote: > I've been using postgres for a long time. Recently I'm doing table > sharding over a bunch of pgsql instances. I'm using multiple tablespaces > one per disk to utilize all the IO bandwidth. Things went on pretty > well, however there is a troublesome problem I have when adding > auxiliary structures to sharded tables, such as Indexes. These objects > have their storage default to the database's tablespace, and it's > difficult to shard them by hand. > > I'd like to implement this small feature --- making table's auxiliary > structures store their data to the target table's tablespace by default. > I've done a thorough search over the mailing list and there is nothing > relevant. Well I may miss some corners :-) So you would like locate those index tablespaces into the same tablespace as its parent relation when the index is created for a unique index or as a primary key? Perhaps we could have a session-level parameter that enforces the creation of such indexes on the same tablespace as the table... But what would be the difference with default_tablespace? I think that you are looking for a replacement for something that is already doable. -- Michael
> So you would like locate those index tablespaces into the same > tablespace as its parent relation when the index is created for a > unique index or as a primary key? Yes, and I'd like this behavior take effect when default_tablespace is set to something like "parent". > But what would be the difference with default_tablespace? What do you mean? AFAIK, default_tablespace option cannot tell which tablespace the parent table is in. > I think that you are looking for a replacement for something that is > already doable. Hmm, I've done my research and asked around IRC channels. There is little info come to my mind. could you give me some hint? Michael Paquier writes: > On Fri, Nov 25, 2016 at 4:48 PM, Amos Bird <amosbird@gmail.com> wrote: >> I've been using postgres for a long time. Recently I'm doing table >> sharding over a bunch of pgsql instances. I'm using multiple tablespaces >> one per disk to utilize all the IO bandwidth. Things went on pretty >> well, however there is a troublesome problem I have when adding >> auxiliary structures to sharded tables, such as Indexes. These objects >> have their storage default to the database's tablespace, and it's >> difficult to shard them by hand. >> >> I'd like to implement this small feature --- making table's auxiliary >> structures store their data to the target table's tablespace by default. >> I've done a thorough search over the mailing list and there is nothing >> relevant. Well I may miss some corners :-) > > So you would like locate those index tablespaces into the same > tablespace as its parent relation when the index is created for a > unique index or as a primary key? Perhaps we could have a > session-level parameter that enforces the creation of such indexes on > the same tablespace as the table... But what would be the difference > with default_tablespace? I think that you are looking for a > replacement for something that is already doable.
On Fri, Nov 25, 2016 at 10:47 PM, Amos Bird <amosbird@gmail.com> wrote: >> So you would like locate those index tablespaces into the same >> tablespace as its parent relation when the index is created for a >> unique index or as a primary key? > > Yes, and I'd like this behavior take effect when default_tablespace is > set to something like "parent". The only scenario where this would be useful is when using ALTER TABLE ADD CONSTRAINT in which case a fresh index is built (not USING INDEX). That's a bit narrow, because it would mean that you would either append a TABLESPACE clause to this existing clause, or create a storage parameter to enforce all indexes created for a relation on a wanted tablespace... For the other cases you could just do something like that, and that's what the large majority of people would care about: SET default_tablespace TO 'foo'; CREATE TABLE foobar (id int PRIMARY KEY); But that's not the one you are interesting in, so likely a storage parameter is what pops up in my mind, with parameter defined at table creation: CREATE TABLE foo (id primary key) WITH (constraint_default_tablespace = foo) TABLESPACE bar; In this case the parent relation gets created in tablespace bar, but its primary key gets in tablespace foo. -- Michael
I'd like to implement this small feature --- making table's auxiliary
structures store their data to the target table's tablespace by default.
I've done a thorough search over the mailing list and there is nothing
relevant. Well I may miss some corners :-)
I had a similar problem in writing the range_partitioning extension: CREATE TABLE y (LIKE x INCLUDING ALL) didn't set the tablespace of y to match x.
I don't have a solution, I'm just indicating a similar need in userland.
> I had a similar problem in writing the range_partitioning extension: CREATE > TABLE y (LIKE x INCLUDING ALL) didn't set the tablespace of y to match x. > I don't have a solution, I'm just indicating a similar need in userland. Cool, I didn't think of that. It seems this feature is at least useful for extension devs like us. I'll start coding a POC patch. What do you think of making default tablespace derived from parent table?
> The only scenario where this would be useful is when using ALTER TABLE > ADD CONSTRAINT in which case a fresh index is built (not USING INDEX). > That's a bit narrow, because it would mean that you would either > append a TABLESPACE clause to this existing clause, or create a > storage parameter to enforce all indexes created for a relation on a > wanted tablespace... For the other cases you could just do something > like that, and that's what the large majority of people would care > about: > SET default_tablespace TO 'foo'; > CREATE TABLE foobar (id int PRIMARY KEY); > But that's not the one you are interesting in, so likely a storage > parameter is what pops up in my mind, with parameter defined at table > creation: CREATE TABLE foo (id primary key) WITH > (constraint_default_tablespace = foo) TABLESPACE bar; > In this case the parent relation gets created in tablespace bar, but > its primary key gets in tablespace foo. How about making a storage parameter "default_tablespace" that also covers CREATE INDEX and other stuff?
On Sat, Nov 26, 2016 at 11:25 AM, Amos Bird <amosbird@gmail.com> wrote: > How about making a storage parameter "default_tablespace" that also > covers CREATE INDEX and other stuff? That's exactly the idea, the one at relation-level gets priority on the global one defined in postgresql.conf. -- Michael
On Sat, Nov 26, 2016 at 8:57 AM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Sat, Nov 26, 2016 at 11:25 AM, Amos Bird <amosbird@gmail.com> wrote: >> How about making a storage parameter "default_tablespace" that also >> covers CREATE INDEX and other stuff? > > That's exactly the idea, the one at relation-level gets priority on > the global one defined in postgresql.conf. > What will such a storage parameter (default_tablespace) mean at table level and how it will different from existing default_tablespace? I think the usage asked by Amos is quite genuine, but not sure if introducing default_tablespace as a storage level parameter is the best way to address it. Another way could be that we allow the user to specify something like tablespace_name <inherit parent>/<inherit table> or something like that. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Amit Kapila <amit.kapila16@gmail.com> writes:
> What will such a storage parameter (default_tablespace) mean at table
> level and how it will different from existing default_tablespace? I
> think the usage asked by Amos is quite genuine, but not sure if
> introducing default_tablespace as a storage level parameter is the
> best way to address it. Another way could be that we allow the user
> to specify something like tablespace_name <inherit parent>/<inherit
> table> or something like that.
That seems overcomplicated, and it will also pose quite some hazard
for pg_dump for example. It feels like "action at a distance", in
that creation of an index will now depend on properties that aren't
immediately obvious.
I was thinking about introducing a new GUC, named something like
default_index_tablespace, which would need to have at least these
behaviors:
1. index tablespace is same as default_tablespace (the backwards
compatible, and therefore the default, behavior).
2. index tablespace is same as table's tablespace.
3. default_index_tablespace names a specific tablespace.
Point 3 isn't in the current request but I'm pretty sure I've heard
it requested in the past, so that people can conveniently put all
tables in tablespace X and all indexes in tablespace Y.
If we just did points 1 and 2 then a bool GUC would suffice. I'm
not sure how to handle all three cases cleanly. We could define
default_index_tablespace as empty to get point 1 or a tablespace
name to get point 3, but that leaves us having to use some magic
string for point 2, which would be messy --- what if it conflicts
with someone's choice of a tablespace name?
regards, tom lane
> I was thinking about introducing a new GUC, named something like > default_index_tablespace, which would need to have at least these > behaviors: Should we also consider the chidren tables as mentioned by Corey?
On Sun, Nov 27, 2016 at 1:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > If we just did points 1 and 2 then a bool GUC would suffice. I'm > not sure how to handle all three cases cleanly. We could define > default_index_tablespace as empty to get point 1 or a tablespace > name to get point 3, but that leaves us having to use some magic > string for point 2, which would be messy --- what if it conflicts > with someone's choice of a tablespace name? Just using a special value for default_tablespace would be enough as well. -- Michael
On 11/27/16 2:01 AM, Amos Bird wrote: > >> I was thinking about introducing a new GUC, named something like >> default_index_tablespace, which would need to have at least these >> behaviors: > > Should we also consider the chidren tables as mentioned by Corey? Wouldn't point #2 handle that? > 2. index tablespace is same as table's tablespace. As for a magic value, based on other recent discussion the idea of "-1" came to me, since per the docs[1] that's not a valid identifier... but experimentation shows it's fine if you double-quote it. :/ 1: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
Jim Nasby writes: > On 11/27/16 2:01 AM, Amos Bird wrote: >> >>> I was thinking about introducing a new GUC, named something like >>> default_index_tablespace, which would need to have at least these >>> behaviors: >> >> Should we also consider the chidren tables as mentioned by Corey? > > Wouldn't point #2 handle that? Hmm, does index tablespace also take care of child tables' tablespace? I was thinking it only handles indexes. regards, Amos
On Sat, Nov 26, 2016 at 9:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Amit Kapila <amit.kapila16@gmail.com> writes: >> What will such a storage parameter (default_tablespace) mean at table >> level and how it will different from existing default_tablespace? I >> think the usage asked by Amos is quite genuine, but not sure if >> introducing default_tablespace as a storage level parameter is the >> best way to address it. Another way could be that we allow the user >> to specify something like tablespace_name <inherit parent>/<inherit >> table> or something like that. > > That seems overcomplicated, and it will also pose quite some hazard > for pg_dump for example. It feels like "action at a distance", in > that creation of an index will now depend on properties that aren't > immediately obvious. > > I was thinking about introducing a new GUC, named something like > default_index_tablespace, which would need to have at least these > behaviors: > > 1. index tablespace is same as default_tablespace (the backwards > compatible, and therefore the default, behavior). > > 2. index tablespace is same as table's tablespace. > > 3. default_index_tablespace names a specific tablespace. > > Point 3 isn't in the current request but I'm pretty sure I've heard > it requested in the past, so that people can conveniently put all > tables in tablespace X and all indexes in tablespace Y. > > If we just did points 1 and 2 then a bool GUC would suffice. I'm > not sure how to handle all three cases cleanly. We could define > default_index_tablespace as empty to get point 1 or a tablespace > name to get point 3, but that leaves us having to use some magic > string for point 2, which would be messy --- what if it conflicts > with someone's choice of a tablespace name? > Yeah, I think coming with a clean way to handle all three might be messy. How about if just handle 2 and 3? If the table is created with default_tablespace, then automatically it will be created in default_tablespace. Do you think maintaining backward compatibility is important in this case? -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
On Mon, Nov 28, 2016 at 6:19 PM, Amit Kapila <amit.kapila16@gmail.com> wrote: > On Sat, Nov 26, 2016 at 9:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> If we just did points 1 and 2 then a bool GUC would suffice. I'm >> not sure how to handle all three cases cleanly. We could define >> default_index_tablespace as empty to get point 1 or a tablespace >> name to get point 3, but that leaves us having to use some magic >> string for point 2, which would be messy --- what if it conflicts >> with someone's choice of a tablespace name? >> > > Yeah, I think coming with a clean way to handle all three might be > messy. How about if just handle 2 and 3? > Or maybe just 1 and 2 with a bool GUC. Another point to think in this regard is what if tomorrow somebody requests something similar for Create Materialized View? Isn't it better to introduce a GUC default_tablespace_parent or default_parent_tablespace? -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Amit Kapila writes: > Another point to think in this regard is what if tomorrow somebody > requests something similar for Create Materialized View? Isn't it > better to introduce a GUC default_tablespace_parent or > default_parent_tablespace? That's exactly what I have in mind :)