mongodb vs. postgres: modeling conditions

Question

We are now choosing a database for a project. The candidates are mongodb and postgres. Our users need to define conditional logic, e.g. user defines conditions (as strings) "x happens", "y leaves" etc, and can then concatenate them to conditions "if x happens AND (y leaves OR z comes)...".

Doing it in a single json document seems much more readable than in a relational database.

Do you see any way to model this reasonably in postgres? I know postgres 9.2 will support json but the querying capabilities seem clunky.

In mongo the verbs ("x happens") will be duplicated between the conditions. A verb update will affect multiple rows. Do you see a problem here?

EDIT: the mongo documents may look like this:

{
  "where": 
     [1,"x happens"],
     "and": 
     { "where": 
            [2,"y leaves"], 
               "or": 
            [3,"z comes"]
     }
}

In relation to MongoDB; Can you provide a sample document layout and maybe a possible query? The description is hard to visualise. — Sammaye, Jul 25 '12 at 11:24
Can you add an example of what your requirements are for querying this data? — Ross, Jul 25 '12 at 15:19
@Ross - I do not know that yet. I want to keep my system open to queries like "all conditions that use 'y leaves'". Also readability and maintainability are important. — Yaron Naveh, Jul 25 '12 at 15:28
well thats difficult to query in mongoDB if its arbitrarily nested so the example schema wouldnt work. — Ross, Jul 25 '12 at 15:30

score 0 · Accepted Answer · answered Sep 05 '12 at 02:58

Ok, in this area PostgreSQL and MongoDB are going to be fairly different and yet fairly similar in how you have to think about this. The big thing is get away from the strict relational idea. Your choices are object-relational or document-oriented. I would choose the former because I am more comfortable with it.

The key thing is that a you have to be able to reduce your conditions to bools that are definable on the fly. If you do this in PostgreSQL you will have some programming to do. The tradeoff is that you get ad hoc reporting that would be painful to do in MongoDB.

The key thing you can do in Postgres is model derivative data. So the key thing in PostgreSQL to make this manageable is to use table methods to check the constraints at run-time. To create a table method in PostgreSQL use a form like this:

CREATE OR REPLACE FUNCTION method_name(table_name) RETURNS return_type ....

You may find this helpful with more examples: http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-2-intro-to.html (yes, that's my blog).

The fact is that you can build some extremely sophisticated quasi-relational models on PostgreSQL and so I wouldn't rule it out in that regard. The question is whether you need the relational side, for ad hoc queries and reporting over sets of objects. If you do then Mongo alone won't cut it and you could either export from Mongo into Postgres or do things in PostgreSQL.

This isn't saying that PostgreSQL is the answer. If you never need to add new query types, maybe Mongo is better. However this is one of those cases where you really want to be using every advanced feature Pg offers you that is useful in your case if you are going that route.

mongodb vs. postgres: modeling conditions

1 Answers1