database - 按字符前缀的 Postgres 分区

Question

美好的一天，

我想检查在列前缀上对 Postgres 表进行分区的最佳方法是什么。我有一个大表(+- 300 7.5 亿行 x 10 列)，我想在第 1 列的前缀上对其进行分区。数据看起来像:

ABCDEF1xxxxxxxx
ABCDEF1xxxxxxxy
ABCDEF1xxxxxxxz
ABCDEF2xxxxxxxx
ABCDEF2xxxxxxxy
ABCDEF2xxxxxxxz
ABCDEF3xxxxxxxx
ABCDEF3xxxxxxxz
ABCDEF4xxxxxxxx
ABCDEF4xxxxxxxy

他们只会被 10 个分区即 ABCDEF0...->ABCDEF9...

我目前所做的是制作如下表格:

CREATE TABLE public.mydata_ABCDEF1 (
CHECK ( col1 like 'ABCDEF1%' )
) INHERITS (public.mydata);

CREATE TABLE public.mydata_ABCDEF2 (
CHECK ( col1 like 'ABCDEF2%' )
) INHERITS (public.mydata);

等然后是类似逻辑的触发器:

IF ( NEW.col1 like 'ABCDEF1%' ) THEN 
    INSERT INTO public.mydata_ABCDEF1 VALUES (NEW.*);
ELSIF ( NEW.imsi like 'ABCDEF2%' ) THEN
    INSERT INTO public.simdata_ABCDEF2 VALUES (NEW.*);

我担心以这种方式进行分区是否会加快查询时间？或者我是否应该考虑在 substr 上进行分区(不确定如何)，或者我是否应该在该列上创建一个带有前缀和分区的新列？

如有任何建议，我们将不胜感激。

Answer 1

我知道这是一个老问题，但我添加了这个答案以防其他人需要解决方案。

Postgres 10 允许范围分区 https://www.postgresql.org/docs/10/static/ddl-partitioning.html .

虽然文档中的示例使用日期范围，但您也可以使用字符串范围，因为 Postgres(主要)使用 ASCII 排序。下面的代码创建一个父表，然后创建两个子表，这取决于您的特定代码，应该根据提供的前缀自动对任何字母数字进行分类。范围必须不重叠，这就是为什么我根本无法创建从 ABCDEF1 到 ABCDEF2 的范围。

CREATE TABLE mydata (...) PARTITION BY RANGE (col1);
CREATE TABLE mydata_abcdef1 PARTITION OF mydata 
  FOR VALUES FROM ('ACBCDEF1') to ('ABCDEF1z');
CREATE TABLE mydata_abcdef1 PARTITION OF mydata 
  FOR VALUES FROM ('ACBCDEF2') to ('ABCDEF2z');