postgresql - 何时使用复合类型和数组以及何时规范化数据库？

Question

是否有关于何时规范化数据库或仅使用复合类型和数组的指南？

当使用数组和复合类型时，我可以只使用一个表。我还可以规范化数据库并使用几个表和连接。

你如何决定哪个选项是最好的？

Answer 1

大多数时候，坚持归一化。除其他事项外，保持数据库相当规范化有助于锁定粒度。例如，如果您有一个包含两个数组的“父”对象，则您不能有同时添加/更新/修改数组成员的事务。如果他们是普通的边 table ，你可以。 (不过，如果您想要序列化行为，您仍然可以在更新子对象之前SELECT ... FOR UPDATE父行)。

更新一个数组以添加/替换/删除一个值是昂贵的，因为 PostgreSQL 必须将数组所在的整个元组重写为 MVCC 更新。 (它有一些袖手旁观的 TOAST 技巧可以提供帮助，但不是很多)。行中嵌入的复合类型也是如此。

充满数组和组合的大宽行意味着表扫描速度较慢，这意味着常用值的提取速度较慢。

IIRC 您不能将外键定义到复合类型的字段中，因此您会发现自己正在解决这个问题或放弃引用完整性，而这本来是好的。同上数组(有 was work to get foreign keys to arrays to work，但我认为它从未被提交过)。

许多客户端驱动程序(PgJDBC、psqlODBC、psycopg2 等)对数组和组合的支持不完整甚至不存在，因此您通常会将它们扩展为元组以进行客户端驱动程序交互。有些东西，比如复合类型的数组，使用起来真的很痛苦。

大多数 ORM，包括像 Hibernate 这样的常见 ORM，除了最简单的最低公分母 SQL 功能之外，完全不擅长使用任何东西。迟早有人会想要将其中一个指向您的数据模型，这时将接踵而至的是哀号和咬牙切齿。 OTOH，不要将垃圾 ORM 容纳到避免使用将大大改进数据模型和解决现实世界问题的功能的地步——例如，如果您可以选择存储 native hstore 字段，或使用 EAV 架构，考虑仅使用 jstore(或更好，在 9.4 中，json 具有 hstore 功能)。

(反过来说，这意味着拥有最“面向对象”程序的人通常拥有最纯粹的关系数据库，因为他们的工具很烂)。

报告生成工具之类的东西同样会与组合和数组斗争，因此您通常会着手创建 View 以无论如何呈现数据库的规范化外观。然后 ON INSERT OR UPDATE OR DELETE ... DO INSTEAD 在 View 上触发以启用写入。在这一点上它变得丑陋。

我个人建议在将某些东西建模为“类型”是合乎逻辑的时候保留复合 Material 。考虑一下，比如说，如果您的数据模型需要您跟踪原始时区中的时间戳。对此没有内置类型(不，这不是“带时区的时间戳”所做的，尽管有名称，感谢 SQL 委员会)，因此您可以创建一个复合类型来存储 (timestamp without time zone, tzname ) 并在您的数据模型中始终如一地使用它。

同样，我倾向于在查询中大量使用数组，但在数据模型中使用得不多。当你想为了性能故意对某些东西进行非规范化时，它们很有用，但这通常是在物化 View 或类似 View 中完成的。即使它是对主要数据模型的更改，这也是您应该根据适当的性能审查来做的事情，而不仅仅是“优化”您还不知道速度很慢的东西。