postgresql - Postgres 分区表 - 转换为声明分区

Question

对于冗长的帖子，我深表歉意。我试图了解所有细节。

我们最近将 Postgres AWS RDS 从 9.5 升级到了 11.1。

我们有几个使用继承实现的大型分区表，我们正在考虑将其转换为声明式分区。(我说的是 5TB 的分区数据)。在我推进之前，我想确定我的方法。

例如，这里是我们如何创建具有继承性的分区表。该表有一个主键和一个索引。继承的分区有一个检查约束和一个索引。 (未显示的是将新行放入正确分区的主表上的触发器。)

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

我知道我可以通过执行以下操作将此表转换为声明性分区表:

创建一个新的分区表:

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;

删除 test_201908 表上的继承:

alter table test_201908 no inherit test;

然后将这张表添加到分区表中。 doco 表示在加载数据之前保持检查约束。

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

分区显示为附加到表:

\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')

我的问题是关于索引发生了什么。当您检查分区时，您会看到从分区表继承的主键和原始索引 (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)

如果我向 test_part 表添加一个新分区

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

新表有主键和索引，但索引有一个系统生成的名称。

$\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)

查看我刚刚创建的对象的 pg_class:

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

分区表上的索引有一个 relkind 为 I，分区上的索引有一个 relkind 为 i。查看 pg_indexes主表上没有索引条目:

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

分区上的索引确实显示了:

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

那么，这个分区表的索引是否正确？ (是的，这个困惑中埋藏着一个问题)。我找不到任何关于如何 分区索引有效，但分区“索引”似乎只是一个定义，真正的索引在分区本身上。有没有办法列出与分区索引关联的所有索引？有没有办法查看分区索引是否有效？

此外，doco 还讨论了使用 CREATE INDEX ON ONLY 选项在分区表上创建索引。我不认为这个适用于我需要做的事情。我说得对吗？

“如上所述，可以在分区表上创建索引并自动应用它们到整个层次结构。这非常方便，因为不仅现有分区会被索引，但将来创建的任何分区也会。一个限制是无法使用创建此类分区索引时的 CONCURRENTLY 限定符。为了克服长时间的锁定，可以仅在分区表上使用 CREATE INDEX；这样的索引被标记为无效，并且分区不会自动应用索引。可以创建分区索引分别使用 CONCURRENTLY，然后使用 ALTER INDEX .. ATTACH PARTITION 附加到父级上的索引。一旦所有分区的索引都附加到父索引，父索引将自动标记为有效。”

Answer 1

索引 test_idx1_201908 自动转换为分区索引 test_201909_metric_idx 的一个分区。它的名称与其他索引分区不同并不重要。

您可以使用以下查询来验证:

SELECT relispartition FROM pg_class WHERE relname = 'test_idx1_201908';

结果应为 TRUE，表示该索引是分区索引的分区。

我有两点与您的问题无关的评论:

1. 我注意到您没有为分区键定义范围的上限。

   您指定的上限被排除，所以您应该这样写

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

2. 这可能为时已晚，但您应该为分区列选择 date 而不是 numeric。

   这将使一切变得更简单、更易读，并且不可能输入错误的日期，例如 20190335。