Django Postgres ArrayField与一对多关系 [英] Django Postgres ArrayField vs One-to-Many relationship

问题描述

对于数据库中的模型，我需要为特定字段存储大约300个值。如果我使用特定于Postgres的性能和查询简单性方面会有什么弊端？ zh_cn / dev / ref / contrib / postgres / fields /＃arrayfield rel = noreferrer> ArrayField 而不是具有一对多关系的单独表？

For a model in my database I need to store around 300 values for a specific field. What would be the drawbacks, in terms of performance and simplicity in query, if I use Postgres-specific ArrayField instead of a separate table with One-to-Many relationship?

推荐答案

如果使用数组字段

• 数据库中每行的大小将变大，因此Postgres将使用更多的吐司表（ http://www.postgresql.org/docs/9.5/static/storage-toast.html ）


• 每次您都获得该行，除非您专门使用 defer （ https://docs.djangoproject.com/en/1.9/ref/models/querysets/#defer ）字段，或者通过<$从查询中将其排除仅c $ c> 或值之类的东西，您每次迭代该行时都要付出加载所有这些值的费用。


• 基于该数组中的值进行过滤，虽然可能不会那么好，并且Django ORM使其不那么明显

• The size of each row in your DB is going to be a bit large thus Postgres is going to be using a lot more toast tables (http://www.postgresql.org/docs/9.5/static/storage-toast.html)
• Every time you get the row, unless you specifically use defer (https://docs.djangoproject.com/en/1.9/ref/models/querysets/#defer) the field or otherwise exclude it from the query via only, or values or something, you paying the cost of loading all those values every time you iterate across that row. If that's what you need then so be it.
• Filtering based on values in that array, while possible isn't going to be as nice and the Django ORM doesn't make it as obvious as it does for M2M tables.

如果使用M2M

• 您可以更轻松地过滤那些相关值


• 默认情况下，这些字段被推迟，您可以使用 prefetch_related 如果您只需要这些值的子集，就可以使用它们


• 由于键和额外的id字段，使用M2M时，数据库中的总存储量会略高


• 在这种情况下，由于密钥的原因，连接的成本可以忽略不计。

• You can filter more easily on those related values
• Those fields are postponed by default, you can use prefetch_related if you need them and then get fancy if you want only a subset of those values loaded
• Total storage in the DB is going to be slightly higher with M2M because of keys, and extra id fields
• The cost of the joins in this case is completely negligible because of keys.

个人我会说要使用M2M表，但是我不知道您的特定应用程序。如果您要处理大量数据，则可能值得获取代表性数据集并使用它测试这两种方法。

Personally I'd say go with the M2M tables, but I don't know your specific application. If you're going to be working with a massive amount of data it's likely worth grabbing a representative dataset and testing both methods with it.

这篇关于Django Postgres ArrayField与一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！