postgresql - 如果我想节省空间，是否必须创建代理键？

Question

假设我有一张非常大的 table ，上面有这样的车主:

OWNERSHIP
owner    | car
---------------
steven   | audi
bernahrd | vw
dieter   | vw
eike     | vw
robert   | audi
... one hundred million rows ...

如果我将其重构为:

OWNERSHIP
owner    | car <-foreign key TYPE.car_type
---------------
steven   | audi
bernahrd | vw
dieter   | vw
eike     | vw
robert   | audi
...


TYPE
car_type      |
---------------
audi
vw

我是否赢得了空间方向或速度方向的任何东西，或者我是否需要为此在 car_type 上创建一个 INTEGER 代理键？

Answer 1

整数将占用4个字节，即one more byte than "vw" will .碰巧，PostgreSQL enums take up 4 bytes too ，因此您不会通过切换到此表示在存储方面获得任何好处(除了它对更改枚举本身造成的困难之外)。无论哪种方式，查询都将一样快，因为对于这样大小的表，您无论如何都会查询索引。数据库性能，尤其是当表变大时，本质上是 I/O 的问题，而不是 CPU 性能。我不相信整数索引会比短字符串索引更小或更快，尤其是当您有大量行引用非常小的可能值集时。它肯定不会成为您应用程序的瓶颈。

即使我们假设您能够使用人工 key 恢复 4 个字节，您将节省多少存储空间？理想情况下，4 字节乘以 1 亿行大约为 400 MB。您是否对存储压力如此之大，以至于需要在您的数据库服务器上挤出这样的少量存储空间？这是假设您将其重构到自己的表中并使用适当的外键。

当然，正确回答这个问题的方法是根本不从第一原则出发。拿你的 1 亿行表来双向工作。然后自己检查尺寸，如下所示:

SELECT pg_size_pretty(pg_total_relation_size('ownership')));
SELECT pg_size_pretty(pg_total_relation_size('ownership2')));

像这样使用 EXPLAIN ANALYZE 进行测试查询:

EXPLAIN ANALYZE SELECT * FROM ownership WHERE car = 'audi';
EXPLAIN ANALYZE SELECT * FROM ownership2 WHERE car_id = 1;

比成本更关注实际花费的时间，但一定要看成本。如果可能，请在与您的产品相同的数据库服务器上执行此操作；如果不是，则使用具有相同 PostgreSQL 配置的类似机器。然后，您将有明确的数字告诉您您付出了什么以及您得到了什么。我怀疑您会发现使用人工 key 时空间使用情况会稍差一些，而性能却相当。

如果这就是您所发现的，请执行相关操作并使用自然键，而不必担心优化物理存储。空间是您拥有的最便宜的商品。