jordanchan

Thanks for the feedback.

Indeed, one of the reasons behind inheriting tables was to use partial indexes - which should help with performance. Another was ease scaling out, if needed.

Using the @> operators on array columns is something I also looked into, mainly for materialized paths - I expect them to remain static, so no expensive updates to the paths/arrays. But actually, I don't think I will need many self relationships - that was the third reason for using inherited tables, so I could split entities into separate groups.

Of course, I have no idea how any of it will work out, since this was just a semi-serious line of inquiry initially, and everything was conceptual so far, except the feedback of experienced people such as yourself, on this thread - which has led me to pursue this seriously and actually try to build it out. After reading the responses on this thread, I now think it will be worth the effort.

You said it!

Actually, Facebook's TAO was an early inspiration for me as well, but it is really customized and complex too, so probably not something for a tiny team. But the concepts from TAO are applicable to many similar scenarios.

Graph db's are a very good idea. Its just that the solutions in the market are not very mature yet, and the community is not very large yet. Most of the downsides stem from these two facts.

So unless the solution you're building has a specific need for graph queries - in particular, complex, realtime and ad hoc graph queries, a traditional solution is a safer bet.

You can make v1 and v2 two separate nodes, if you need information of both of them. In this case you just link each version to its attributes. If you want the "latest" marked separately, you can have three nodes - one for "current", and the other two for the individual iterations. So initially "current" would link to a,b,c and later link x,y,z, and remove a. Or, you can have two nodes - current and previous iteration, and store the version number of the latest iteration as a field within the "current" node.

Agreed, my reasons for coming back to Postgres are quite similar. In general, one only needs to go a few layers in the graph. Recursive queries are needed for specific use cases. Even then, querying from the app, like you said, and/or building materialized views and materialized paths are very good ways of working around this, as long as the query is anticipated in advance and the view/path built before the query. So that reduces the real use of graph db's to an even smaller subset - complex ad hoc graph queries.

Of course, JSON is a good way to store ad hoc, or new relations which haven't yet been structured. But for frequently occurring relations, I'm preferring separate tables instead of JSON for ease of querying and joins. If number of relationships are large, querying within a long JSON field is not the best approach.

Multi model db's are a good idea indeed. They were the first things I had considered, and extensively so - both OrientDB and ArangoDB. I came back to traditional solutions simply for practical reasons of maturity, and ease of finding professional support.

Someone here mentioned about a Tinkerpop implementation that uses Postgres as the storage engine, I think that might offer the best of both worlds, but I am yet to look into it.

Thanks! That's a useful technique..

Yes, of course. So did Russia.

See the movie Farewell, based on actual events? http://www.rottentomatoes.com/m/farewell_2010/

The point being, if they could, and if it helped them, and if there was no real/strong enforcement against it, why wouldn't they have done so? Of course, not the Chinese govt. directly, that would be bad form; but a sponsored organization, I don't see why not, they are ambitious enough.

Ah, sure!

Good that you brought it up. Such questions were actually considered, but the answers arrived at were always vague and changeable. Not surprising for a startup I guess.

Much of it will be read heavy, some parts will be write heavy. "Ideally", we should be using different solutions for the different needs, but that's too much at the beginning. So the best bet will be the best balance, while avoiding a database/backend restructuring to the extent possible. How much data: not surprisingly, it will start small, and the upper limit depends on the future...

Geographic based system, yes, eventually; but they both support geospatial. There will be time based queries for analytical and reporting purposes, not the core of the app. So that can easily be offloaded to a separate system.

The structure of the data will be mostly relational, so we stick with one of the old school systems. For the little bits that will be either dynamic or non-relational, both MySQL and Postgres now support JSON.

Popularity came into the equation because the more popular a system is, the more solutions are developed around it. So We won't be the first to tackle any problem in a popular system, thus reusing what others have done will be enough for the most part.

Cheers!

Thanks for the feedback. So they're basically comparable, with a slight edge for MySQL. Guess I will play with both and pick whichever feels more handy.

I have seen many discussions on Postgres and MySQL on hacker news, so I thought I might ask. What's the argument against asking such questions? Sorry, I only just signed up, after being a passive follower for a very long time.