PostgreSQL数组可以优化加入吗？ [英] Can PostgreSQL array be optimized for join?
问题描述
如果数组的元素是数据本身，例如标签
I see that Postgres array is good for performance if the array's element is the data itself, e.g., tag
http://shon.github.io/2015/12/21/postgres_array_performance.html
如果我使用数组作为存储整数外键的方法呢？除非存在外键约束问题，否则建议使用整数数组存储外键吗？
How about if I use array as a way to store foreign keys of integer? Barring foreign key constraint problem, is it advisable to store foreign keys with integer array?
应用程序应针对报告或分析进行优化。因此，如果应用程序在大多数情况下最终都会将数组连接到表中，比如说该应用程序需要显示外键的标签/标题/名称，那么使用数组存储外键是否还可以？
Apps should optimize for report or analytics. So if the app will end up joining the array to table most of the time, say the app need to show the label/title/name of the foreign key, is it still ok to use array for storage of foreign keys?
如果数组较小，而不是使用联结表（例如电影流派整数复选框），性能会更好吗？
Would the performance be better when array is smallish as compared to using a junction table, say checkboxes of movie genres integer?
如果数组有成千上万个，不使用数组而仅使用联结表，性能会更好吗？
How about if the array is in thousands, would the performance be better when not using array and just use junction table instead?
推荐答案
否，对于通用表而言，将FK存储在数组中绝不是一个好主意。首先，首先要提到一个事实：数组元素的外键约束尚未实现（尚未）。
No, storing FKs in an array is never a good idea for general purpose tables. First an foremost, there is the fact you mentioned in passing: Foreign key constraints for array elements are not implemented (yet). This alone should void the idea.
有人试图实现Postgres 9.3的功能，但由于严重的性能问题而被阻止。
There was an attempt to implement the feature for Postgres 9.3 that was stopped by serious performance issues. See this thread on pgsql-hackers.
此外，虽然在某些用例中可以使用数组提高读取性能，但是写入性能暴跌。想一想：要从长数组中插入，更新或删除单个元素，现在必须为每个固定元素编写一个带有整个数组的新行版本。而且我也看到很严重的锁争用。
Also, while read performance can be improved with arrays for certain use cases, write performance plummets. Think of it: To insert, update or delete a single element from a long array, you now have to write a new row version with the whole array for every canged element. And I see serious lock contention ahead, too.
如果您的表是 只读 ，则想法开始更有意义。但是之后，我会考虑使用具有非规范化数组的物化视图 < 标准化的多对多实现：
- How to implement a many-to-many relationship in PostgreSQL?
同时，MV可以包括所有联接表并产生一个平面表，以实现更好的读取性能（对于典型的用例） 。这样，您可以获得参照完整性和良好的读取（和写入）性能-以开销和管理MV的额外存储为代价。
While being at it, the MV can include all join tables and produce one flat table for even better read performance (for typical use cases). This way you get referential integrity and good read (and write) performance - at the cost of the overhead and additional storage for managing the MV.
这篇关于PostgreSQL数组可以优化加入吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
