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

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

更新数组以添加/替换/删除值是很昂贵的,因为PostgreSQL必须重写数组作为MVCC更新的整个元组. (它有一些TOAST技巧可以帮助,但不是吨).嵌入行的Ditto复合类型.

数组和复合体的大行宽表示较慢的表扫描,这意味着对常用值的读取速度较慢.

IIRC您无法将外键定义到复合类型的字段中,因此您会发现自己正在解决这个问题,或者放弃参考完整性,而这样做很好. Ditto数组(有was work to get foreign keys to arrays to work,但我认为它没有被评出).

许多客户端驱动程序(PgJDBC,psqlODBC,psycopg2等等)对数组和组合都有不完整到不存在的支持,因此无论如何,您经常会将它们扩展为元组以进行客户端驱动程序交互.有些东西,比如复合类型的数组,使用它真的很痛苦.

大多数ORM,包括像Hibernate这样的常见ORM,完全不习惯使用除了最完全简单的最低公分母SQL特性之外的任何东西.迟早,某人会想要在你的数据模型中指出其中一个,此时会发出许多嚎叫和咬牙切齿的信息. OTOH,不要容纳垃圾ORM,以避免使用能够大大改善数据模型和解决实际问题的功能 – 例如,如果您可以选择存储本机hstore字段,或使用EAV架构,考虑只使用jstore(或更好,在9.4,json与hstore功能).

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

像报告生成工具这样的东西同样会遇到复合材料和数组,所以你经常会创建视图来为数据库提供标准化的外观.然后ON INSERT或UPDATE或DELETE … DO INSTEAD触发视图以启用写入.在这一点上,它变得丑陋.

就个人而言,我建议保留复合材料,以便将某些东西建模为“类型”.例如,假设您的数据模型要求您跟踪其原始时区中的时间戳.这里没有内置类型(不,这不是“带时区的时间戳”,不管名称,感谢SQL委员会),所以你可以创建一个存储的复合类型(没有时区的时间戳,tzname)并使用在您的数据模型中始终如一.

同样,我倾向于在查询中大量使用数组,但在数据模型中却没有.当你想要故意对某些东西进行异常化以获得性能时,它们很有用,但这通常是在物化视图或类似视图中完成的.即使它是对主数据模型的改变,它也是你应该根据适当的性能评估做的事情,而不仅仅是“优化”你不知道的东西还很慢.