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 <robertmh...@gmail.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
