Questions about Partitioned Tables and Indexes - Mailing list pgsql-general

We recently upgraded our Postgres AWS RDS from 9.5 to 11.1.  

We have several large partitioned tables implemented using inheritance that we are considering converting to declarative partitioning.
(I'm talking about 5TB of partitioned data).   I want to be sure of my methodology before I push forward.

For example here is how we would have created a partitioned table with inheritance.  The table has a primary key and an index.  The inherited partition has a check constraint and an index.  (Not shown is the trigger on the primary table that would put the new rows in the correct partition.)

CREATE TABLE test
(
    date_key numeric(15,0) NOT NULL,
    metric numeric(15,0) NOT NULL,
    value numeric(28,5) NOT NULL,
    CONSTRAINT test_pkey PRIMARY KEY (date_key,metric)
)
TABLESPACE pg_default;

CREATE INDEX test_idx1
    ON test USING btree
    (metric)
    TABLESPACE pg_default;

CREATE TABLE test_201908
(
    CONSTRAINT const_test_chk CHECK (date_key >= 20190801::numeric AND date_key <= 20190831::numeric)
)
    INHERITS (test)
TABLESPACE pg_default;

CREATE INDEX test_idx1_201908
    ON test_201908 USING btree
    (metric)
    TABLESPACE pg_default;

AMZGQ3DW=> \d+ edibben.test
                                       Table "edibben.test"
  Column  |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+---------+--------------+-------------
 date_key | numeric(15,0) |           | not null |         | main    |              |
 metric   | numeric(15,0) |           | not null |         | main    |              |
 value    | numeric(28,5) |           | not null |         | main    |              |
Indexes:
    "test_pkey" PRIMARY KEY, btree (date_key, metric)
    "test_idx1" btree (metric)
Child tables: edibben.test_201908


AMZGQ3DW=> \d+ edibben.test_201908
                                   Table "edibben.test_201908"
  Column  |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+---------+--------------+-------------
 date_key | numeric(15,0) |           | not null |         | main    |              |
 metric   | numeric(15,0) |           | not null |         | main    |              |
 value    | numeric(28,5) |           | not null |         | main    |              |
Indexes:
    "test_idx1_201908" btree (metric)
Check constraints:
    "const_test_chk" CHECK (date_key >= 20190801::numeric AND date_key <= 20190831::numeric)
Inherits: edibben.test

I know that I can convert this table into a declarative partitioned table by doing the following:

Create a new partitioned table:

CREATE TABLE test_part
(
    date_key numeric(15,0) NOT NULL,
    metric numeric(15,0) NOT NULL,
    value numeric(28,5) NOT NULL,
    CONSTRAINT test_part_pkey PRIMARY KEY (date_key,metric)
) PARTITION BY RANGE (date_key)
TABLESPACE pg_default;

CREATE INDEX test_part_idx1
    ON test_part USING btree
    (metric)
    TABLESPACE pg_default;

Drop the inheritance on the test_201908 table:

alter table test_201908 no inherit test;

And then add this table to the partitioned table.  The doco says to keep the check constraint in place until after the data is loaded.

alter table test_part
attach partition test_201908
for VALUES FROM (20190801) TO (20190831);

The partition shows up attached to the table:

 \d+ edibben.test_part
                                    Table "edibben.test_part"
  Column  |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+---------+--------------+-------------
 date_key | numeric(15,0) |           | not null |         | main    |              |
 metric   | numeric(15,0) |           | not null |         | main    |              |
 value    | numeric(28,5) |           | not null |         | main    |              |
Partition key: RANGE (date_key)
Indexes:
    "test_part_pkey" PRIMARY KEY, btree (date_key, metric)
    "test_part_idx1" btree (metric)
Partitions: edibben.test_201908 FOR VALUES FROM ('20190801') TO ('20190831')

My question is about what happens to the indexes.  When you examine the partition you see the primary key inherited from the partition table
and the original index (test_idx1_201908).

AMZGQ3DW-> \d+ edibben.test_201908
                                   Table "edibben.test_201908"
  Column  |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+---------+--------------+-------------
 date_key | numeric(15,0) |           | not null |         | main    |              |
 metric   | numeric(15,0) |           | not null |         | main    |              |
 value    | numeric(28,5) |           | not null |         | main    |              |
Partition of: edibben.test_part FOR VALUES FROM ('20190801') TO ('20190831')
Partition constraint: ((date_key IS NOT NULL) AND (date_key >= '20190801'::numeric(15,0)) AND (date_key < '20190831'::numeric(15,0)))
Indexes:
    "test_201908_pkey" PRIMARY KEY, btree (date_key, metric)
    "test_idx1_201908" btree (metric)
Check constraints:
    "const_test_chk" CHECK (date_key >= 20190801::numeric AND date_key <= 20190831::numeric)

If I add a new partition to the test_part table

CREATE TABLE test_201909 PARTITION OF test_part
    FOR VALUES FROM ('20190901') TO ('20190930');

The new table has the primary key and the index but the index has a system generated name.

 $\d+ edibben.test_201909
                                   Table "edibben.test_201909"
  Column  |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
----------+---------------+-----------+----------+---------+---------+--------------+-------------
 date_key | numeric(15,0) |           | not null |         | main    |              |
 metric   | numeric(15,0) |           | not null |         | main    |              |
 value    | numeric(28,5) |           | not null |         | main    |              |
Partition of: edibben.test_part FOR VALUES FROM ('20190901') TO ('20190930')
Partition constraint: ((date_key IS NOT NULL) AND (date_key >= '20190901'::numeric(15,0)) AND (date_key < '20190930'::numeric(15,0)))
Indexes:
    "test_201909_pkey" PRIMARY KEY, btree (date_key, metric)
    "test_201909_metric_idx" btree (metric)



Looking at pg_class for the objects I just created:

AMZGQ3DW=> select relname, reltype, relkind,relowner from pg_class where relname like 'test%';
        relname         | reltype | relkind | relowner
------------------------+---------+---------+----------
 test_201908            |  365444 | r       |    98603
 test_201908_pkey       |       0 | i       |    98603
 test_idx1_201908       |       0 | i       |    98603

 test_201909            |  366498 | r       |    98603
 test_201909_metric_idx |       0 | i       |    98603
 test_201909_pkey       |       0 | i       |    98603

 test_part              |  365449 | p       |    98603
 test_part_idx1         |       0 | I       |    98603
 test_part_pkey         |       0 | I       |    98603

The indexes on the partitioned table have a relkind of I and the indexes on the partitions have a rekind of i.  Looking at pg_indexes
there are no entries for the indexes on the primary table:

AMZGQ3DW=> select schemaname, tablename, indexname from pg_indexes where schemaname = 'edibben' and tablename = 'test_part';
 schemaname | tablename | indexname
------------+-----------+-----------
(0 rows)

The indexes on the partitions do show up:

AMZGQ3DW=> select schemaname, tablename, indexname from pg_indexes where schemaname = 'edibben' and tablename like 'test%' order by tablename;
 schemaname |  tablename  |       indexname
------------+-------------+------------------------
 edibben    | test_201908 | test_201908_pkey
 edibben    | test_201908 | test_idx1_201908
 edibben    | test_201909 | test_201909_pkey
 edibben    | test_201909 | test_201909_metric_idx
 
 So, is this partitioned table properly indexed?  (yes there was a question buried in this mess).  I can't find any documentation of how the
 partitioned indexes work but it appears that the partitioned 'Index' is just a definition and that the real indexes are on the partitions themselves.  Is there a way to list all  of the indexes associated with a partitioned index?  Is there a way to see if the partitioned index is valid?

Also, the doco talks about creating the index on the partitioned table with the CREATE INDEX ON ONLY option.  I don't think this
applies to what I need to do.  Am I right?

"As explained above, it is possible to create indexes on partitioned tables and they are applied automatically
to the entire hierarchy. This is very convenient, as not only the existing partitions will become indexed,
but also any partitions that are created in the future will. One limitation is that it's not possible to use
the CONCURRENTLY qualifier when creating such a partitioned index. To overcome long lock times,
it is possible to use CREATE INDEX ON ONLY the partitioned table; such an index is marked invalid,
and the partitions do not get the index applied automatically. The indexes on partitions can be created
separately using CONCURRENTLY, and later attached to the index on the parent using ALTER INDEX .. ATTACH PARTITION.
Once indexes for all partitions are attached to the parent index, the parent index is marked valid automatically."