4 ms·

It's nothing new that PostgreSQL is a great tool for doing analytics, even coming from MongoDB. I'm very happy that MongoDB took this route, it speaks a lot abo

It's nothing new that PostgreSQL is a great tool for doing analytics, even coming from MongoDB. I'm very happy that MongoDB took this route, it speaks a lot about their capabilities in the non-OLTP world. Having said that, I very biasedly say that there's a much better solution to this connector, which doesn't flatten out the MongoDB data: it's called ToroDB (https://github.com/torodb/torodb https://github.com/torodb/torodb). ToroDB, open source, speaks the MongoDB protocol, transforms documents to relational tables (without any kind of flattening, and without having to define any schema) and stores data in a RDBMS. More precisely, PostgreSQL. Current development version (repl branch) speaks the replication protocol, and hence can replicate live from a MongoDB into PostgreSQL. No connector needed, no flattening, no FDWs, nothing else. Just add a new "slave" (ToroDB) to your replica set and you're good to go. It goes even further: if you want pure data warehousing, ToroDB will soon support GreenPlum. Some initial benchmarks (http://www.slideshare.net/8kdata/torodb-scaling-postgresql-like-mongodb http://www.slideshare.net/8kdata/torodb-scaling-postgresql-l..., slide #42) show 25x-75x improvement between doing aggregate queries in MongoDB and their equivalent queries in GreenPlum's distributed SQL. Now that MongoDB 3.2 ships with PostgreSQL "included", feel free to try ToroDB. It's always better the original :) Note: I am a ToroDB developer.

buffyoda 11y ago
I think ToroDB is super cool, and I wish your project the best of luck! You can't go wrong building something on PostgreSQL. :) That said, PostgreSQL FDW is NOT a great option for MongoDB analytics. Not only is the data model so different that you lose the ability to answer many types of questions, but Multicorn supports only basic pushdown (conjunctions of simple relational operators on original columns). What this means is that analytics via PostgreSQL via FDW via Multicorn via MongoDB suffers from (a) very poor expressive power, and (b) ridiculously slow performance, since nearly any type of query will require at least one full table scan on all the source tables (in some cases, especially with arrays, many more full table scans may be required for a single query!). Better off just using ToroDB. Am I right? :)
- ahachete 11y ago
  Thank you, John De Goes. I definitely agree. While PostgreSQL FDWs are a great way of extending Postgres, I don't see they are a good fit for this use case. Not only there are a lot of pushdowns not supported (although that is in the process of being improved), but more importantly, as you mentioned, this connector is going to impose a lot of full table scans for even the simplest queries. I'm dying to benchmark this connector against ToroDB. But unfortunately, the MongoDB proprietary license agreement explicitly forbids any kind of benchmark. I guess they have reasons to do so ;))))))) I cannot be objective saying that you should better of using ToroDB, but I definitely think so. I also want to congratulate you. I think Quasar and SlamData have gone very far, and I'd encourage you to keep on pushing it. While this connector may or may not adversely affect SlamData, there's always room for differentiation and improvement. Good luck!

buffyoda 11y ago

I think ToroDB is super cool, and I wish your project the best of luck! You can't go wrong building something on PostgreSQL. :) That said, PostgreSQL FDW is NOT a great option for MongoDB analytics. Not only is the data model so different that you lose the ability to answer many types of questions, but Multicorn supports only basic pushdown (conjunctions of simple relational operators on original columns). What this means is that analytics via PostgreSQL via FDW via Multicorn via MongoDB suffers from (a) very poor expressive power, and (b) ridiculously slow performance, since nearly any type of query will require at least one full table scan on all the source tables (in some cases, especially with arrays, many more full table scans may be required for a single query!). Better off just using ToroDB. Am I right? :)

ahachete 11y ago
Thank you, John De Goes. I definitely agree. While PostgreSQL FDWs are a great way of extending Postgres, I don't see they are a good fit for this use case. Not only there are a lot of pushdowns not supported (although that is in the process of being improved), but more importantly, as you mentioned, this connector is going to impose a lot of full table scans for even the simplest queries. I'm dying to benchmark this connector against ToroDB. But unfortunately, the MongoDB proprietary license agreement explicitly forbids any kind of benchmark. I guess they have reasons to do so ;))))))) I cannot be objective saying that you should better of using ToroDB, but I definitely think so. I also want to congratulate you. I think Quasar and SlamData have gone very far, and I'd encourage you to keep on pushing it. While this connector may or may not adversely affect SlamData, there's always room for differentiation and improvement. Good luck!