Building a MongoDB Clone in Postgres

http://legitimatesounding.com/blog/building_a_mongodb_clone_in_postgres_part_1.html

by jerrysievert • 14 years ago

161 95 14 years ago

legitimatesounding.com

NewtonsFolly• 14 years ago

Sounds legit.

nsanch9• 14 years ago

Unless he's planning to build sharding on postgres too, I think he's missing the point.

jerrysievert4• 14 years ago

while sharding is an important aspect of mongodb, i don't consider it the most important feature.

jshen9• 14 years ago

that doesn't mean that others don't find it to be an extremely important feature.

nsanch9• 14 years ago

I don't know if it's the _most_ important feature, but I wouldn't build a serious site on top of anything that didn't have some sort of built-in sharding story.

With postgres you have to roll your own. If you want to bridge the gap from postgres to mongo, I think that's where you have to start.

dickeytk2• 14 years ago

It also seems to be the flakiest feature

jeffdavis5• 14 years ago

"Unless he's planning to build sharding on postgres too, I think he's missing the point."

NoSQL seems to have three general claims (I'm not saying whether these are correct or not): (1) ease of administration in some cases; (2) different data model; (3) better performance or availability in some situations.

The author is clearly addressing the second, and you are clearly talking about the third.

nsanch9• 14 years ago

Good point.

bri3d2• 14 years ago

Should be pretty trivial for him to shard collections across multiple databases with the same level of intelligence as MongoDB's automated sharding simply using the primary key, since MongoDB doesn't join.

Not sure how sharding is the point of MongoDB, though - in most of the universe, sharding is a database architecture/schema-level thing, not a database-server level thing, and for good reason - it's pretty darn hard for a database server to shard effectively without some knowledge of the app layer (and which keys are likely to become hot).

Personally, if I really wanted a flexible-schema "document based" database, I'd have implemented this using the FriendFeed K/V + Index model ( http://backchannel.org/blog/friendfeed-schemaless-mysql ) plus Postgres's HStore functionality, storing K/V per document in an HStore rather than in one giant K/V table like FriendFeed. That way I wouldn't need to use V8 and JSON parsing to run queries, and the mythic MongoDB-style "sharding" would be just as easy (just distribute the document -> hstore table across shards keyed on ID again).

nsanch9• 14 years ago

I disagree that sharding can ever be a trivial problem if you're going to try to tackle moving data between shards while staying online. I'm not saying it's impossible, just that it's not trivial.

mark_l_watson2• 14 years ago

You have a good point. I must admit that after many years of using and loving PostgreSQL I have never had to scale it out: that is outside of my experiences.

On the other hand, old fashion MongoDB master slave configurations and now replica sets have been easy for me to set up when on just a few occasions I had to use a scaled out MongoDB setup.

einhverfr4• 14 years ago

Easily enough done on Postgres-XC, and you still get full ACID compliance across shards as well!

StavrosK• 14 years ago

This is (almost) exactly what Goatfish[1] does, except it uses SQLite, and can create SQL indexes on any arbitrary field. I needed a schemaless, embeddable store, so I decided to go with that, since Postgres already has the hstore. It's still very simple and preliminary, but it works, and it's very useful for prototyping.

I'd like to develop it some more, if people found it useful and started using it.

[1] https://github.com/stochastic-technologies/goatfish

DEinspanjer2• 14 years ago

It is interesting, but what I really need in the JSON functionality of PG is some internal representation that will allow fast and efficient exploration of the JSON blob within the query. i.e. being able to refer to a single attribute within the select/where/groupby clauses without having to pay the toll of serde every time.

jerrysievert4• 14 years ago

exactly. calling the find_in_obj() function can get fairly expensive, especially given the need to execute JSON.parse() for every call.

there is definitely a lot of room for improvement in the postgres native JSON toolkit. i am hoping that building this exposes more of those issues and helps move it forward.

disbelief• 14 years ago

But the same is true for MongoDB. If you're querying an attribute of a document that isn't indexed, Mongo has to scan all of the Documents in the collection for it. The solution to this is to have additional indexes on any attributes you want to be able to query by. This holds true both in Mongo and this Postgres implementation.

rarrrrrr• 14 years ago

I haven't tried it, but I think you would be able to use a PostgreSQL functional index (i.e. indexing the result of a function call to extract a particular bit of data from the JSON.)

http://www.postgresql.org/docs/9.1/interactive/indexes-expre...

rhizome3• 14 years ago

Isn't this a category error? If you need to query within serialized data, you don't want to serialize. Normalize your database for this.

rdtsc• 14 years ago

Or de-normalize and keep your data serialized but also keep an additional index of data you want to query.

randomdata• 14 years ago

Wouldn't that kind of defeat the purpose of using a document database in the first place? Being able to throw unstructured data into the system, and then being able to query on that data once the space is better understood is where the document databases really shine.

If you are able to start with rigid structure, you, in many cases, could have just used a relational database to begin with.

DEinspanjer2• 14 years ago

Well, what I need is expressive and flexible querying over document data that doesn't have a rigid schema. There are several document stores that provide filtering and retrieval matching that, but they all have various limitations when it comes to aggregation.

jeffdavis5• 14 years ago

Relational databases have a history of absorbing the advantages of other systems when they come along, particularly changes to the model (cf. object databases and XML databases). As the author shows, a similar thing will happen quite quickly for document models.

Architectural changes are slower, but you can also start to see this happening in postgres with features like unlogged tables (i.e. don't write to the recovery log for changes to this table) and transaction-controlled async commit (i.e. don't wait for this transaction to hit disk). More changes are in the works.

MongoDB will have a chance to have an impact, and will be successful if they are able to keep innovating. If they just stand still or incrementally improve, they will be marginalized. Now that Mongo has center stage in the NoSQL movement, it will be interesting to see what they do next.

autarch• 14 years ago

I'd phrase this differently. Once you implement Mongo in Postgres, what you have isn't a relational database.

What you are taking advantage of is the incredibly solid underlying infrastructure of Pg, which provides reliability, scalability, transactions, replication, etc.

But the data model itself is no longer relational from the application's perspective.

jeffdavis5• 14 years ago

Every data model is just a degenerate special case of every other data model ;)

mtkd• 14 years ago

I do encourage any SQL user, who hasn't already tried MongoDB, to fire it up and try it themselves. Mongoid in Ruby is fairly fast to get started.

I've been using SQL since early 90s. For web apps and large collections I've started using MongoDB more recently. It's one of the most exciting technologies I've used in a long time.

It takes a while to stop thinking SQL, but once you pass that it's really very primitive (in a good way) - and frictionless for development. It really fits what I want to do with web apps in particular.

So far I've not needed to scale it, but I've less apprehension about that than I used to have about scaling SQL back in the day - the large denormalised tables I used to build from SQL for performance are now the default.

SQL still has a place and MongoDB is no replacement for complex models, but give it a try before buying that you can do it all with Postgres tweaks.

bad_user2• 14 years ago

Experimenting with new technologies is encouraged, especially since cross-pollination of ideas happen this way.

However I would actually advise developers to stop and think if they really need MongoDB or the latest fad, because their current relational database, such as PostgreSQL, does a mighty fine job for most of their needs.

Why? Because I have never seen angry opinions about PostgreSQL losing people's data. Or about how the modeling tools and architecture exposed by PostgreSQL are insufficient for certain problems ... not until you're operating at Google's scale, and MongoDB won't save you there ;)

     give it a try before buying that you can do it all 
     with Postgres tweaks

My main problem with most NoSQL solutions is that I have to tweak the problems I have to fit the solution, instead of the other way around. Technologies that can be tweaked simply rock.

taligent5• 14 years ago

I guess if only I "woke up" I would realise that PostgreSQL is the answer to all my problems. Guess what. PostgreSQL is just another SQL database. It's definitely one of the best ones but it still stuffers from all the same issues, limitations and frustrations. Many of which stem not from the database itself but from the relational modelling and tools to support it.

I use Java + MongoDB and life is significantly better now that I don't have to worry about the domain model so much. I can have lists, maps etc and can add classes or make changes seamlessly. It's worth the tradeoffs for me.

k_bx2• 14 years ago

> My main problem with most NoSQL solutions is that I have to tweak the problems I have to fit the solution, instead of the other way around. Technologies that can be tweaked simply rock.

Well, I would disagree. While it's great that sql databases let you do lots of joins, I would lose imagination of my query complexity very fast (also, you can't review all sql-queries stopping people from doing joins and "where tablename.status=active" in every joined table). While MongoDB would restrict you from that, but gives you "documents". I'd say it's a lot of power of complexity-clarity (having no joins, but power-enough documents).

p.s.: and yes, I agree with you that developers are ok with postgres on most of cases. more of that, it's better to have transactions and other stuff until it's performance-urgent to disable it).

hobb00013• 14 years ago

> However I would actually advise developers to stop and think if they really need MongoDB or the latest fad, because their current relational database, such as PostgreSQL, does a mighty fine job for most of their needs.

Every time I've had to use an ORM, it always became a headache sooner or later. It got to the point where I stopped even trying to perform an automatic mapping; I reverted back to using explicit SQL in my code and avoided the "abstraction" altogether.

With a document store like MongoDB, though, the mapping between object and document is almost seamless. It's actually really quite comfortable.

dickeytk2• 14 years ago

I really wish I could agree with you, but in my experience, ActiveRecord is just way easier to deal with than Mongoid.

mark_l_watson2• 14 years ago

I agree: Mongoid is great.

I haven't done this in a few years, but Datamapper had the nice capability to map a singe model classes to multiple backends (e.g., use PostgreSQL for some attributes, MongoDB for others).

PostgreSQL and MongoDB cover most of my data store requirements and are a pleasure to use.

semiquaver• 14 years ago

I use datamapper pretty extensively to join mysql tables with mongodb collections, but I've always used associations between several models to do this. Do you have any links talking about this capability?

vampirechicken2• 14 years ago

> SQL still has a place and MongoDB is no replacement Let's flip this: NoSQL is finding it's place in the database ecosystem.

SQL is the name of a database query language, NoSQL is a term that identifies a set of new, ostensibly non-relational, data storage engines that so implement an SQL parser despite SQL being the Standard Query Language, with twenty years of history behind it.

My opinion - They decided to call it NoSQL, not because SQL requires a relation database, but because writing a smart query optimizer is hard task. Much harder than writing the storage engine itself.

I'd much rather access data from Mongo, Riak, Redis, and Couch using the SELECT, INSERT, UPDATE and DELETE statements that I have known since 1988, instead of having to learn four new database APIs.

einhverfr4• 14 years ago

"I'd much rather access data from Mongo, Riak, Redis, and Couch using the SELECT, INSERT, UPDATE and DELETE statements that I have known since 1988, instead of having to learn four new database APIs."

Doesn't work because those are set operations which are meaningless outside the relational world. If you want to use set operations, use a relational database.

shykes• 14 years ago

Here's a quick an easy way to deploy both Mongo and Postgres, and start comparing them:

    git clone http://github.com/shykes/sqlwars
    cd sqlwars
    pip install dotcloud
    dotcloud create sqlwars
    dotcloud push sqlwars
    
    # Open mongo shell
    dotcloud run sqlwars.mongo mongo

    # Open postgres shell
    dotcloud run sqlwars.postgres psql

Happy hacking!

roncohen• 14 years ago

If you just need a simple key/value, a good way to go is HSTORE in PostgreSQL. It allows only string key/values, and not complex structures. Use the JSON datatype if you need lists, nested objects etc.

With HSTORE you get indexes on keys.

k_bx2• 14 years ago

I wonder if postgres will adopt BSON instead of JSON and implement all the querying -- that would be so much better.

p.s.: guys who disagree -- why would you do that? I mean, BSON lets you quickly skip (embedded) documents you're not interested in, since it stores their size. Also it has type info and some additional types. So it's just "better JSON" for storing and navigating, and if postgres wants to have querying in JSON, they will either take BSON or invent the wheel for something similar.

baq• 14 years ago

so, um, yeah... http://www.postgresql.org/docs/9.1/static/hstore.html

jerrysievert4• 14 years ago

still key/value, no deep packet inspection.

peterhunt• 14 years ago

This is the right way to think about building a "NoSQL" datastore.

I think that schemaless, eventually-consistent data stores have a place and are useful. I just think that most of the current efforts are throwing away years of investment in SQL datastores. Rather than thinking of NoSQL as a brand-new paradigm shift that requires a ground-up reimplementation, we need to think of it as layer of abstraction on top of MySQL and memcache (or your preferred setup). Re-implementing all of the work that has gone into these projects is a bad idea and is contrary to The Unix Way.

Thinking of SQL databases as a storage engine rather than the kitchen sink is the key to building a scalable system.

Building a MongoDB Clone in Postgres

Related Stories