我正在设置一个新的 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)"
而如果我使用像这样的经典检查约束(以及匹配该规则的重新分区):
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 只扫描正确的表...?
Thanks,
Nico