Postgres：一张表多列还是多张表少列？

Question

我的问题涉及到Postgres的内部工作原理：

我有一张表格：

CREATE TABLE A (
   id SERIAL,  
   name VARCHAR(32),
   type VARCHAR(32) NOT NULL, 
   priority SMALLINT NOT NULL,
   x SMALLINT NOT NULL,
   y SMALLINT NOT NULL,
   start timestamp with time zone,
   end timestamp with time zone,
   state Astate NOT NULL,
   other_table_id1 bigint REFERENCES W,
   other_table_id2 bigint NOT NULL REFERENCES S,
   PRIMARY KEY(id)
); 

在其他表ID1、状态和其他表ID2上添加附加索引。

该表非常大，对其他表ID1、状态列进行了很多更新。开始和结束列进行了少量更新，但其余列是不可变的。（状态为列状态的枚举类型。）

我想知道是否有必要将最频繁更新的两列拆分到一个单独的表中。我希望获得的是性能提升，当我只查找该信息时，或者减少更新的负担，因为（也许？）读写较短的行成本更低。但我需要权衡一下，在需要一次性获取特定项目的所有数据时，连接的成本是否会增加。

曾经我认为每个列都是单独存储的。但后来我修改了我的想法，因为我在某个地方读到，缩小表格一侧的列宽确实会对使用另一列查找数据的性能产生积极影响（因为行是一起存储的，所以整个行长度会更短）。所以我现在的看法是，一行的所有数据在物理上都存储在磁盘上；因此，拆分表格的建议听起来会有所帮助。当我当前写入4个字节来更新状态时，我应该相信我正在重新编写实际上从未更改的64个字节的文本（名称、类型）吗？

我对表格“规范化”并不是很有经验，也不熟悉Postgres的内部情况，因此我正在寻找建议和特别是估算权衡的最佳实践，而不必首先进行工作，然后确定该工作是否有价值。这个更改需要大量的工作来重写已经高度优化过的查询，因此我宁愿在了解我可以期望的结果之前进入。谢谢，M。

- Mayur Patel

Answer 1

更新大行的成本是确定的。

公式可以帮助解决这个问题。如果您不进行拆分，则成本为

成本= xU + yS

其中：

U = 整行的更新（表未拆分）

S = 选择的成本

x，y = 操作计数

然后，如果您将其拆分，则尝试找出以下内容：

成本= gU1 + hU2 + xS1 + yS2

其中：

U1 = 较小表的更新（较低成本）

U2 = 较大表的更新（较低成本）

S1 = 从较小表中选择

S2 = 从较大表中选择

g，h，x，y = 单个操作发生的频率

因此，如果g >> h，则拆分它们会更划算。特别是如果x >> y，那么它真的很划算。

编辑：针对评论，我还要指出，如果数据库处于持续负载状态而不是空闲状态，则这些成本变得更加重要。如果服务器没有经历持续负载，只有每秒1或2个trx，并且长时间处于非活动状态（其中“长”=几秒钟），那么如果是我，我不会使代码变得复杂，因为性能收益看起来并不是一个真正可衡量的东西。

- Ken Downs

---

谢谢Ken，这正是我想要的方向。您是否认为U1和U2（以及U）都与列的宽度成比例？您是否认为（U1 + U2）== U在某些开销边际内（U1 + U2 <1.2 * U）？ - Mayur Patel

既然这两个表中的行之间存在1:1的关系，那么你认为连接操作的成本是（U1+U2）吗？ - Mayur Patel

@Mayur，几年前我进行了大量的写入测试，并发现在持续负载下，U1和U2绝对与行大小成比例。关键点是持续负载，如果您的更新“频繁”，但“频繁”意味着每20秒一次，并且数据库经常处于闲置状态几秒钟，那么分裂页面可能没有任何好处。 - Ken Downs

@Mayur，初步估计，是的，连接的成本为S1+S2。由于我们有索引、缓存等因素，这个成本会变得复杂，但操作次数是计算成本的基础。 - Ken Downs

Answer 2

PostgreSQL的一个实现细节与此相关，即它从不“更新”存储在磁盘上的行，而总是写入新版本。因此，与Oracle等数据库不同，将固定宽度的列放在一起并不能带来快速的优势（如果我没记错的话）。根据列是否倾向于一起更新，在不同的表中对列进行分组可以减少需要清理的垃圾。在这里，实验和测量结果至关重要。例如，如果您有一些经常更新的数据，则应该调查表上的“fillfactor”设置。该设置使PostgreSQL在插入时在表页中留下一些空闲空间，允许在可能的情况下将行的更新版本添加到先前版本所在的同一页中：这可以减轻更新的负担，因为这可能意味着指向行的索引不必更新，但代价是整个磁盘上的表占用更多的空间。正如Xaade所提到的，这个主题有很多材料可供参考。我想强调需要衡量任何更改的影响。有时候，看似大的胜利实际上并不是这样。

- araqnid

Answer 3

无论列是如何存储的，将其拆分开来都是值得的。这样可以减少并发问题，加快部分数据的查找速度，通过提供三个索引进行索引搜索而无需创建辅助键等等。

您可以通过欺骗或仅允许一次查看有限数量的行来减轻内部连接的影响。您可以通过提供界面而不是允许直接查找，仅在可见行上显示内部连接数据（屏幕上一次只能查看那么多行），为当前选择的行显示附加数据，或者仅允许每次查找X行并使用浏览按钮来欺骗。如果使用欺骗，请确保缓存扩展查找的结果。

- Lee Louviere

---

谢谢。关于“作弊”的建议并不适用于我的应用程序，因为人们很少浏览这些表格。这都是为机器而设计的，所以除非真的需要查看，否则列不会被检索。（例如 SELECT id、state、other_table_id2 WHEN...）有没有一种方法来估算连接成本的增加或减少表格大小的节省？ - Mayur Patel

看看数据库管理。它是一个完整的职业领域，因为需要大量分析才能确定这些事情。你不仅要考虑估计你的解决方案的机会成本，还要考虑每个解决方案的机会成本。你必须估计数据更新的时间，分割表可以减少碰撞。你必须为每种可能的组合计算这个，以找到最有效的解决方案。然而，当处理大量数据时，内部连接只是偶尔出现，表的某些部分可以独立更新，拆分总是有益的。 - Lee Louviere