postgresql – 选择正确的分区规则

概述我正在建立一个新的PostgreSQL 9数据库,它将包含数百万(或数十亿)行.所以我决定使用PostgreSQL继承来分区数据. 我创建了一个这样的主表(例如简化)： CREATE TABLE mytable( user_id integer, year integer, CONSTRAINT pk_mytable PRIMARY KEY (user_id, year)); 和1 我正在建立一个新的Postgresql 9数据库,它将包含数百万(或数十亿)行.所以我决定使用Postgresql继承来分区数据.

我创建了一个这样的主表(例如简化)：

CREATE table mytable(  user_ID integer,year integer,CONSTRAINT pk_mytable PRIMARY KEY (user_ID,year));

和10个分区表：

CREATE table mytable_0 () inheritS (mytable);CREATE table mytable_1 () inheritS (mytable);...CREATE table mytable_9 () inheritS (mytable);

我知道将始终使用唯一的user_ID条件从应用程序访问行.
所以我想使用基于user_ID的规则在10个表中“相当”地传播数据.

要调整主表上的查询,我的第一个想法是使用模数检查约束：

ALTER table mytable_0 ADD CONSTRAINT mytable_user_ID_check CHECK (user_ID % 10 = 0);ALTER table mytable_1 ADD CONSTRAINT mytable_user_ID_check CHECK (user_ID % 10 = 1);...

问题是,当我使用user_ID上的条件查询主表“mytable”时,Postgresql分析器检查所有表并且不受益于检查约束：

EXPLAIN SELECT * FROM mytable WHERE user_ID = 12345;"Result  (cost=0.00..152.69 rows=64 wIDth=36)""  ->  Append  (cost=0.00..152.69 rows=64 wIDth=36)""        ->  Seq Scan on mytable  (cost=0.00..25.38 rows=6 wIDth=36)""              Filter: (user_ID = 12345)""        ->  Seq Scan on mytable_0 mytable  (cost=0.00..1.29 rows=1 wIDth=36)""              Filter: (user_ID = 12345)""        ->  Seq Scan on mytable_1 mytable  (cost=0.00..1.52 rows=1 wIDth=36)""              Filter: (user_ID = 12345)"..."        ->  Seq Scan on mytable_9 mytable  (cost=0.00..1.52 rows=1 wIDth=36)""              Filter: (user_ID = 12345)"

如果我使用这样的经典CHECK CONSTRAINT(以及与该规则匹配的重新分区)：

ALTER table mytable_0 ADD CONSTRAINT mytable_user_ID_check CHECK (user_ID BETWEEN 1 AND 10000);ALTER table mytable_1 ADD CONSTRAINT mytable_user_ID_check CHECK (user_ID BETWEEN 10001 AND 20000);...

它将仅扫描与条件匹配的表(在此示例中为mytable和mytable_1)：

"Result  (cost=0.00..152.69 rows=64 wIDth=36)""  ->  Append  (cost=0.00..152.69 rows=64 wIDth=36)""        ->  Seq Scan on mytable  (cost=0.00..25.38 rows=6 wIDth=36)""              Filter: (user_ID = 12345)""        ->  Seq Scan on mytable_1 mytable  (cost=0.00..1.52 rows=1 wIDth=36)""              Filter: (user_ID = 12345)"

但是使用这样的检查约束很难维护,因为将在表中填充的用户范围将在多年内发生变化.在不久的将来,成千上万,可能是数百万甚至更多……

我可以使用什么规则在10个表上平均分配我的数据,这些表可以受益于检查约束,以便主表上的SELECT只扫描正确的表…？

谢谢,
尼科

解决方法 限制是与计划者而不是分配本身.手册中详细介绍了它：

http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

你提到的有两件事需要考虑.

首先,您说所有访问都将通过主键进行.这意味着您将不会从分区中获得任何性能优势(至少在正常使用情况下不会).每个分区的索引都会更小,但PG需要先选择要检查的分区.如果您需要重新索引或类似的话,您将获得的收益 – 您可以单独重新索引每个分区.

其次,你说你可能有数千到数十亿行.这让我得出两个结论：

>或许可以将决定留待以后.等到你需要分区.
>你不太可能想要10个分区,分别有两千行和二十亿个.

如果要进行分区,请按范围进行分区 – 比如100,000行或每个分区100万行.添加一个cron-job来检查所使用的最大ID,并在需要时创建一个新分区(也许每天一次).

就个人而言,我会离开它直到我需要它.如果你认为它更有可能你以后需要它们,也许有一个单独的分区作为一个全能.

总结

以上是内存溢出为你收集整理的postgresql – 选择正确的分区规则全部内容，希望文章能够帮你解决postgresql – 选择正确的分区规则所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。