基于varchar字段的第一个字母的分区表 [英] Partitioning table based on first letter of a varchar field
问题描述
我有一个庞大的表(超过1B条记录),这些表对表分区有特定要求:
(1)是否可以对表进行分区
例如:
对于以下3条记录:
a-blah
a-blah2
b-blah
a-blah
和 a-blah2
将进入 A分区, b-blah
将进入 B分区。 / p>
(2)如果使用Postgres无法实现上述目的,那么将大的生长表均匀分区的一种好方法是什么?(不按分区进行分区创建日期-因为这些记录没有这些内容。)
您可以在<$ c $中使用表达式c> partition by 子句,例如:
创建表my_table(名称文本)
按列表划分(left(name,1));
为(’a’)中的值创建表my_table_b
my_table
的分区;
为(’b’)中的值创建表my_table_b
my_table
的分区;
结果:
插入my_table
值
('abba'),('alfa'),('beta');
选择 a作为分区,从my_table_a中命名。
所有
选择 b作为分区,从my_table_b中命名;
分区|名称
----------- + ------
a | abba
a |阿尔法
b | beta
(3行)
如果分区不区分大小写,则可以使用
创建表my_table(name text)
按列表分区(lower(left(name,1)));
阅读文档:
I have a massive table (over 1B records) that have a specific requirement for table partitioning:
(1) Is it possible to partition a table in Postgres based on the first character of a varchar field?
For example:
For the following 3 records:
a-blah
a-blah2
b-blah
a-blah
and a-blah2
would go in the "A" partition, b-blah
would go into the "B" partition.
(2) If the above is not possible with Postgres, what is a good way to evenly partition a large growing table? (without partitioning by create date -- since that is not something these records have).
You can use an expression in the partition by
clause, e.g.:
create table my_table(name text)
partition by list (left(name, 1));
create table my_table_a
partition of my_table
for values in ('a');
create table my_table_b
partition of my_table
for values in ('b');
Results:
insert into my_table
values
('abba'), ('alfa'), ('beta');
select 'a' as partition, name from my_table_a
union all
select 'b' as partition, name from my_table_b;
partition | name
-----------+------
a | abba
a | alfa
b | beta
(3 rows)
If the partitioning should be case insensitive you might use
create table my_table(name text)
partition by list (lower(left(name, 1)));
Read in the documentation:
这篇关于基于varchar字段的第一个字母的分区表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!