大型的PostgreSQL表：是添加列还是创建一个新表来存储元数据更好？

Question

我有一个大型表格（约200万行），每一行代表一张图片。我想为每个图像以JSON格式存储EXIF元数据。 每个图像的JSON blob大小约为6KB。

这些EXIF元数据不会经常查询/使用，我想知道将其存储在具有两列（imageid，exifjson）的单独表中是否更有效，还是PostgreSQL可以将其作为现有表上的text列处理。 我不希望添加列显著减慢普通查询表格的速度，也不希望数百万个6KB文本值拖慢PostgreSQL。

- Ben Hoyt

---

如果剩余的行小于6K，则将exif-blobs放入单独的表中，将使典型查询（不使用exif）的“工作集”的占用空间大大减小，这意味着您可以更好地利用缓冲区空间。（而不是用不需要的东西淹没它） - wildplasser

Answer 1

我会将那一列进行TOAST处理。

 ALTER TABLE ... ALTER <column> SET STORAGE <EXTERNAL|EXTENDED>;
 -- EXTERNAL - out-of-line storage, not compression[1]
 -- EXTENDED - both compression and out-of-line storage

PostgreSQL已经尝试使用它来存储大于~2kB的数据。

[1] "所使用的压缩技术是LZ家族中一种相当简单且非常快速的成员。"

- kupson

Answer 2

最好使用单独的表，但如果使用现有的表也可以。除非您在使用现有的“select * from”查询检索此字段，否则您几乎不会受到影响。而且，您永远不会用这样的数据填满postgres，因为它几乎具有无限的阈值：

• 最大数据库大小 无限制
• 最大表大小 32 TB
• 每行最大大小 1.6 TB
• 每个字段的最大大小 1 GB
• 每个表的最大行数 无限制
• 每个表的最大列数 250-1600，取决于列类型
• 每个表的最大索引数 无限制

http://www.postgresql.org/about/

关于在选择其他列数据时的干扰：

非常长的值也存储在后台表中，以便它们不会干扰对较短列值的快速访问。

http://www.postgresql.org/docs/current/static/datatype-character.html

- Timur Sadykov

---

谢谢。听起来大致正确，但你有任何参考资料可以支持吗？ - Ben Hoyt