Thread: PostgreSQL vs Mongo

All;

One of our clients is talking about moving to Mongo for their
reporting/data mart.  I suspect the real issue is the architecture of
their data mart schema, however I don't want to start pushing back if I
can't back it up.

Anyone have any thoughts on why we would / would not use Mongo for a
reporting environment.

what are the use cases where mongo is a good fit?
what are the drawbacks long term?
is mongo a persistent db or simply a big memory cache?
does mongo have advantages over Postgres hstore?
etc...

Thanks in advance...

/Kevin

On Wed, Oct 16, 2013 at 10:30 AM, CS DBA <cs_dba@consistentstate.com> wrote:
> All;
>
> One of our clients is talking about moving to Mongo for their reporting/data
> mart.  I suspect the real issue is the architecture of their data mart
> schema, however I don't want to start pushing back if I can't back it up.
>
> Anyone have any thoughts on why we would / would not use Mongo for a
> reporting environment.
>
> what are the use cases where mongo is a good fit?
> what are the drawbacks long term?
> is mongo a persistent db or simply a big memory cache?
> does mongo have advantages over Postgres hstore?
> etc...

Couple random thoughts:
*) I think the real feature to be looking at is postgres json
improvements.   One of the reasons people migrate off relational
technology difficulty with mapping data to client side structures.
JSON is doing what xml failed to do: provide a standard way of doing
that.  Postgres sql+json features allow this to be done in query
itself which is a huge simplification vs standard techniques although
it will require above average SQL skills to do it right.

*) All databases are 'in memory' databases essentially.  The real
trade off is what consistency guarantees you give up in order to to
get faster writing and/or multi-master replication.  My understanding
of mongo is that you give up a lot.  This puts a lot of effort on the
programmer to manage data conflicts that SQL basically does for you.
That might be a good tradeoff in some cases.

*) Moving away from relational technology has a lot of hidden costs
that are hard to see until you finish the project.  In my experience,
exotic backends tend to be correlated with project problems or even
failure.

But really what we need to know is what is prompting your client to
make the move.  The rebuttal argument (should it be necessary to make
one) will depend a lot on what the reasoning is.

merlin

Hi,

On 17 October 2013 02:30, CS DBA <cs_dba@consistentstate.com> wrote:
> Anyone have any thoughts on why we would / would not use Mongo for a
> reporting environment.

hm.. I wouldn't use anything which doesn't support rich SQL as a
backed for reporting system. In mongo, simple selects are fine but
anything complex requires map-reduce style query. Hence you need real
developers to maintain it.

> what are the use cases where mongo is a good fit?
> what are the drawbacks long term?
> is mongo a persistent db or simply a big memory cache?
> does mongo have advantages over Postgres hstore?

This is very good summary: http://aphyr.com/posts/284-call-me-maybe-mongodb
(entire "Call me maybe" series is good: http://aphyr.com/tags/jepsen,
even Postgres is there)

The problem with all NoSQL solutions is that they are selected based
on the features and APIs not based on consistency vs. availability
tradeoffs. Moreover, distributed system should have partition
tolerance and many NoSQL dbs simply drop data in this case.

--
Ondrej

If they pull out the "Mongo is faster than PostgreSQL" card, I'll state that
I investigated this for my current employer and with roughly equivalent
configurations (because it's impossible to get exactly equivalent) I was
getting roughly the same performance from each.  It's difficult to measure
exactly, but I would recommend that if performance is a reason pulling you
toward Mongo, that you don't listen to the internet hype and actually test
both systems with your workload before assuming one will be faster than the
other.

On Wed, 16 Oct 2013 20:11:43 -0700 Chris Travers <chris.travers@gmail.com> wrote:

> On Wed, Oct 16, 2013 at 8:30 AM, CS DBA <cs_dba@consistentstate.com> wrote:
>
> > All;
> >
> > One of our clients is talking about moving to Mongo for their
> > reporting/data mart.  I suspect the real issue is the architecture of their
> > data mart schema, however I don't want to start pushing back if I can't
> > back it up.
> >
>
> You want to find out what the issues are before making your pitch.  Get
> specifics.
>
> >
> > Anyone have any thoughts on why we would / would not use Mongo for a
> > reporting environment.
> >
> > what are the use cases where mongo is a good fit?
> >
>
> The argument for NoSQL in reporting is where the variety of data makes
> traditional reporting difficult.  This is a niche case, and not a typical
> data mart.
>
>
> > what are the drawbacks long term?
> >
>
> If you use the wrong tool for the job. you are going to find yourself coded
> into corners.  The tradeoff is that if you allow data variety on the way
> in, you can't ensure simple mathematical transformation of that data to be
> meaningful on the way out.  This means that the precision of your answers
> goes down once you eliminate schemas.  Where you don't have to, you should
> not go with a NoSQL solution for reporting.
>
> After all, reporting really is the forte of SQL and has been for a long
> time.
>
>
> > is mongo a persistent db or simply a big memory cache?
> > does mongo have advantages over Postgres hstore?
> >
>
> I assume Mongo docs can be nested JSON?  Also you have some intraquery
> parallelism at least between nodes.  The latter can be solved with careful
> use of Postgres-XC.  The former would make XML on PostgreSQL a better
> comparison.
>
> In general these things need to be details-oriented.  It is critically
> important to find out if they are considering it due to hype or whether
> they have real reasons for the comparison.  Maybe in some cases, NoSQL
> options may be better, but these are relatively rare, particularly in
> analytic environments.
>
> --
> Best Wishes,
> Chris Travers
>
> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> lock-in.
> http://www.efficito.com/learn_more.shtml


--
Bill Moran <wmoran@potentialtech.com>

On Wed, Oct 16, 2013 at 09:30:59AM -0600, CS DBA wrote:
- All;
-
- One of our clients is talking about moving to Mongo for their
- reporting/data mart.  I suspect the real issue is the architecture of
- their data mart schema, however I don't want to start pushing back if I
- can't back it up.
-
- Anyone have any thoughts on why we would / would not use Mongo for a
- reporting environment.
-
- what are the use cases where mongo is a good fit?
- what are the drawbacks long term?
- is mongo a persistent db or simply a big memory cache?
- does mongo have advantages over Postgres hstore?
- etc...
-
- Thanks in advance...
-
- /Kevin

I work with both.

Mongo doesn't really seem approprite for a datamart. Mongo supports
Map Reduce and has an Aggregation framework (which will give you a lot
of the functionality of SQL but is much more esoteric)

You need an index for every query you run and every possibly sort order.
Mongo will cancel you're query if the result set hits a certian size
w/o an index.

Doing ad-hoc queries is HARD. and there are no joins. If it's not in
your document you basically have to pull both documents into your app
and join them by hand.

Writes block reads, massive updates (like into a datamart) will need to "yield"
to allow reads to happen and that only happens at a pagefault.

You need to have enough memory to store you're "working set". or performance tanks
In a datamart your working set is frequently the whole thing.

People throw around the "Schemaless" thing, but really there is some schema. you
have to know what you want your document to look like. So this means schema changes
as you grow your product, etc.

In a datamart you're not going to use 10gen's idea schema change methodology
of "Only Apply Data Model Changes when you access a record" That works if you're
ooking up a single document at a time, but not if you're mostly doing range scans
and aggregations.

Mongo is very limited on how it can sort, we have a number of "sort fields" added
to our document that give us a different indexable sort order. like you can't
do ORDER BY CASE statements.

IMO Mongo, like most NoSQL solutons, address write scaling and availablity
by making it easier to do. You can generally shard w/o bothering the application
too much and you get free seamless failover with the replica sets.


Hope this is helpful

Dave