我有大约 1.5TB 的 json 数据,需要导入 PostgreSQL 数据库的 2 亿条记录,并且想就推荐的表分区方式提供一些建议。目标模式相当简单,将是一个单一的表,如:
widgets:
id text,
name text,
description text,
country text, -- (~100 unique values, with ~30% of records in one country)
continent text, -- (6 unique values)
link text,
quality smallint,
class text, -- (around 100 unique values)
tags jsonb,
properties_1 jsonb,
properties_2 jsonb
该表将用于分析查询,而不是事务性的。数据集也很稳定,因此无需担心初始加载后的未来插入。大多数查询将包含大陆或国家/地区的 WHERE 相等性,以及描述、标签、属性_* 上的各种 WHERE 子句
我最初考虑按大陆分区以提高查询性能,因为大多数查询将使用大陆过滤器。
在大陆字段上使用 LIST 进行分区:
CREATE TABLE widgets_north_america PARTITION OF widgets
FOR VALUES IN ('North America')
CREATE TABLE widgets_north_america PARTITION OF widgets
FOR VALUES IN ('Europe')
... 4 other continent partitions
但是,分区仍然非常大,因为它只将数据分成 6 个分区。并且我希望 30% 的数据位于单个分区中阅读这个有用的答案后:我应该在什么时候拆分或分区一个非常大但简单的表我现在正在考虑是否应该将分区强制分成足够小的块,以便每个可以放入内存,(100-200?)。如果是这样,我想我会在具有模 100 的唯一 id 字段上使用 HASH 分区。
在唯一 id 字段上使用 HASH 进行分区:
CREATE TABLE widgets_hash_1 PARTITION OF widgets
FOR VALUES WITH (MODULUS 100, REMAINDER 0);
CREATE TABLE widgets_hash_2 PARTITION OF widgets
FOR VALUES WITH (MODULUS 100, REMAINDER 1);
...(其他 98 个分区表)
这种方法的缺点似乎是我根本不需要在 id 字段上进行查询,因此我没有从分区键中获得任何好处,但是具有较小的分区可能允许表在查询期间适合内存,并增加查询表现。
硬件:我有 32GB RAM、一个 2TB SSD 和四个 14TB HDD 可用于此。
鉴于我的情况,我应该如何对这张表进行分区?关于索引的任何建议:多列与单独的索引?这是我第一次处理接近这种大小的任何数据库,因此在考虑这个问题时很难考虑实际的权衡应该是什么。
对于针对大陆的查询,您可以轻松地将它们改写为针对国家/地区吗?如果是这样,按国家划分可能是最好的选择(但预计会受到法国和土耳其人的反击)。
通过与其他任何事物无关的事物进行分区是没有意义的。它是否适合内存并不重要,如果从来没有理由将它(并且只有它)放在内存中。
除此之外,您需要深入了解您的查询实际上是什么,而不仅仅是 30,000 英尺的摘要。
也许您根本不应该对表进行分区。
数据不是按表缓存,而是按 8kB 数据块缓存,因此表或分区大小无关紧要。
此外,如果数据是静态的,您可以创建任意数量的索引以加快访问速度,并且对大表的索引扫描不会比对小表的索引扫描慢。
可能有意义的是使用预先聚合的数据创建物化视图以加快查询速度,但这是另一回事。
对表进行分区有一个可能的原因:如果您的很多查询
GROUP BY
某个列,它可以加快这些查询在该列上进行分区并设置enable_partitionwise_aggregate
为on
.