PostgreSQL JSONB即将到来.现在使用什么? Hstore? JSON? EAV? [英] Postgresql JSONB is coming. What to use now? Hstore? JSON? EAV?

问题描述

在经历了有关DB/NoSQL的关系研究辩论之后，我得出的结论是，我将继续使用PG作为数据存储.该决定的很大一部分是宣布JSONB达到9.4.我的问题是，现在我应该怎么做，在知道要迁移到(我的意思是现在使用！)jsonb的基础上，从头开始构建应用程序?对我来说，DaaS选项将在9.3上运行一段时间.

After going through the relational DB/NoSQL research debate, I've come to the conclusion that I will be moving forward with PG as my data store. A big part of that decision was the announcement of JSONB coming to 9.4. My question is what should I do now, building an application from the ground up knowing that I want to migrate to (I mean use right now!) jsonb? The DaaS options for me are going to be running 9.3 for a while.

根据我的判断，如果我错了，请更正我，因为我将对hstore列中的许多键进行很多查询，并且如果我使用纯json，则hstore的运行速度会更快一些.我将无法利用索引/GIN等功能.但是，我可以利用与json嵌套的优势，但是运行任何查询都将非常缓慢，并且用户会感到沮丧.

From what I can tell, and correct me if I'm wrong, hstore would run quite a bit faster since I'll be doing a lot of queries of many keys in the hstore column and if I were to use plain json I wouldn't be able to take advantage of indexing/GIN etc. However I could take advantage of nesting with json, but running any queries would be very slow and users would be frustrated.

那么，我是否应该围绕hstore或json数据类型的当前版本，"good ol" EAV或其他版本来构建我的应用程序?我应该以某种方式构造数据库和应用程序代码吗?任何建议将不胜感激.我确信在等待下一个正式发布的PostgreSQL时，其他人也会面临同样的问题.

So, do I build my app around the current version of hstore or json data type, "good ol" EAV or something else? Should I structure my DB and app code a certain way? Any advice would be greatly appreciated. I'm sure others may face the same question as we await the next official release of PostgreSQL.

我要构建的应用程序的一些其他详细信息:

A few extra details on the app I want to build:

-非常关系化(下面有一个例外)
-强大的社交网络方面(群组，朋友，喜欢，时间轴等)
-基于具有可变的用户分配属性(可能是10或1000+)的单个对象(这是无模式设计需求发挥作用的地方)

-Very relational (with one exception below)
-Strong social network aspect (groups, friends, likes, timeline etc)
-Based around a single object with variable user assigned attributes, maybe 10 or 1000+ (this is where the schema-less design need comes into play)

在此先感谢您的输入！

推荐答案

这要视情况而定.如果您希望拥有大量用户，非常高的事务量或每个查询获取大量的属性，我会说使用HSTORE.但是，如果您的应用程序开始时会很小并且会随着时间增长，或者获取属性的事务相对较少，或者每个查询只能获取少量事务，那么请使用JSON.即使在后一种情况下，如果您没有获取许多属性，而是经常在查询的WHERE子句中检查一个或两个键，则可以创建一个功能索引来加快处理速度:

It depends. If you expect to have a lot of users, a very high transaction volume, or an insane number of attribute fetches per query, I would say use HSTORE. If, however, you app will start small and grow over time, or have relatively few transactions that fetch attributes, or just fetch a few per query, then use JSON. Even in the latter case, if you're not fetching many attributes but checking one or two keys often in the WHERE clause of your queries, you can create a functional index to speed things up:

CREATE INDEX idx_foo_somekey ON foo((bar ->> 'somekey'));

现在，当您拥有WHERE bar ->> somekey时，它应该使用索引.

Now, when you have WHERE bar ->> somekey, it should use the index.

当然，当您可以使用嵌套数据并升级到jsonb时，会更容易.

And of course, it will be easier to use nested data and to upgrade to jsonb when it becomes available to you.

因此我将倾向于JSON，除非您确定在有机会升级到9.4之前一定要大量使用密钥获取来踢服务器.但是要确保这一点，我要说的是，现在对预期的查询量进行一些基准测试，然后看看哪种方法最适合您.

So I would lean toward JSON unless you know for sure you're going kick your server's ass with heavy use of key fetches before you have a chance to upgrade to 9.4. But to be sure of that, I would say, do some benchmarking with anticipated query volumes now and see what works best for you.

这篇关于PostgreSQL JSONB即将到来.现在使用什么? Hstore? JSON? EAV?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！