Should new partitions inherit their tablespace from their parent?

Dear Hackers,

I've just read the thread in [1] where there was a discussion on a bug
fix regarding index partitions not being created in the same
tablespace as the partitioned index was created.

One paragraph that stood out when reading the thread was:

On 8 November 2018 at 09:00, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> With regard to this patch, I think the new behavior is fine in and of
> itself, but I *do not* think it should have been back-patched and I
> *do not* think it should work one way for tables and another for
> indexes.

While I don't agree with that 100% as there's no option to specify
were an index partition gets automatically created when a new
partitioned table is ATTACHed, whereas with new partitioned tables you
can at least specify the TABLESPACE option.

Anyway, Robert mentioned that he does not think it should work one way
for partitioned tables and another for partitioned indexes.

Here's the current situation with partitioned tables:

create table listp (a int) partition by list(a) tablespace foo;
create table listp1 partition of listp for values in(1);
select relname,reltablespace from pg_class where relname like 'listp%';
relname | reltablespace
---------+---------------
listp | 0
listp1 | 0
(2 rows)

It does not seem very good that when I created the partitioned table
and asked for its tablespace to be "foo" that postgres ignored that.
However, a partitioned table has no storage, so you could entertain
claims that this is not a bug.

I do have experience with using partitioned tables in a production
environment. During my experience a series of events took place that I
don't think is unique to this one case. I think we could make life
easier for this case. Here's the scenario:

1. Start a business
2. Record some time series data.
3. Your business grows faster than you thought.
4. You painfully partition your time-series table so you can easily
get rid of older data.
5. Your business grows even more and the single disk partition you use
for data on the database server is filling up quickly.
6. Panic.
7. Add more disks and be thankful you partitioned your table back in #4.

Now, we can, of course, manage all this today, but new partitions
defaulting to the default tablespace might seem a little surprising.
Imagine the situation of the DBA removing the old partitions, it's
pretty convenient if, once they've done that they can then also glance
at free diskspace and then perhaps decide to change the default
partition tablespace to the disk partition with the most free space so
that the nightly batch job which creates new partitions puts them in
the best place. The DBA could set the default_tablespace GUC, but the
tablespaces for the partitioned table might be on slower storage due
to how large they become and they might not want all new tables to go
in there.

I think we can do better:

How about we record the tablespace option for the partitioned table in
reltablespace instead of saving it as 0. Newly created partitions
which don't have a TABLESPACE mentioned in the CREATE TABLE command
should be created in their direct parent partitioned tables
tablespace.

The above idea was mentioned in [2] and many people seemed to like it.
It was never implemented which likely lead to [1], which appears to
not be a great situation.

I've no patch, but I'm willing to go and write one if the general
consensus is that we want it to work this way.

[1] https://www.postgresql.org/message-id/flat/20181102003138.uxpaca6qfxzskepi%40alvherre.pgsql
[2] https://www.postgresql.org/message-id/flat/CAKJS1f9PXYcT%2Bj%3DoyL-Lquz%3DScNwpRtmD7u9svLASUygBdbN8w%40mail.gmail.com#089ce41fe9a33c340f7433e5f0018912

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services