Building a MongoDB Clone in Postgres

Re:Building a MongoDB Clone in Postgres by DEinspanjer on Monday June 04, 2012 @01:52PM (#4065024)

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.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by rhizome on Monday June 04, 2012 @02:01PM (#4065075)

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.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by randomdata on Monday June 04, 2012 @02:21PM (#4065149)

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.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by qeorge on Monday June 04, 2012 @10:07PM (#4066977)

In my (limited) experience with Mongo, its just not like that.

You can't just (quickly) query any key in the document - you have to define indexes anyway or you'll be doing the equivalent of a table scan or Map/Reduce.

Document databases shine when you're just not doing that kind of query anyway, and you've got a ton of data to store. If you need to do e.g., fulltext and/or geospatial search you should probably use a search server anyway (I like Sphinx), and then just lookup by PK in whatever datastore you're using.

Its been mentioned elsewhere on this thread, but I'm also quite fond of the old FriendFeed method: serialize your arbitrary data into a (MySQL) BLOB, and then run a background process to build "indexes" (really tables) when you find out you need them.

Its ridiculously simple to start doing that, and there's no new tech to learn.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by rhizome on Monday June 04, 2012 @03:05PM (#4065346)

Wouldn't that kind of defeat the purpose of using a document database in the first place?

Yes. Use the right tool for the job.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by mgkimsal on Monday June 04, 2012 @10:01PM (#4066955)

Defining 'right', and being able to predict 'right' for future iterations/versions of an app's life is where that mantra seems to fall down.

I'm not a huge nosql fan right now, given that I sometimes need document-style schemaless data storage, but I always (eventually) need adhoc reporting and relational querying capabilities on projects. With that (fore)knowledge, I may as well always choose a relational db.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by DEinspanjer on Monday June 04, 2012 @03:49PM (#4065598)

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.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by rdtsc on Monday June 04, 2012 @02:19PM (#4065143)

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

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by rhizome on Monday June 04, 2012 @03:06PM (#4065351)

Serialized is already denormalized, you're talking about a kind of double-denormalization where you introduce metadata as a guide for the serialized-data queries. Spaghetti.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by jerrysievert on Monday June 04, 2012 @01:59PM (#4065063)

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.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by rarrrrrr on Monday June 04, 2012 @02:30PM (#4065191)

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...

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by rosser on Monday June 04, 2012 @02:34PM (#4065212)

I was just coming here to say exactly this. You could also use a partial index (that is, an index with a WHERE clause; so, "WHERE json_field LIKE '%foo.bar = baz%'" or whatever).

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by jerrysievert on Monday June 04, 2012 @02:35PM (#4065216)

you can, but in order to build a partial index, the function needs to be immutable. not exactly ideal in this case.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by rosser on Monday June 04, 2012 @02:40PM (#4065236)

Immutability is perfectly appropriate for that use-case, though. Creating an index via a function that may return different outputs when provided the same input is always going to lose.

[ Parent | on HN ]

Re:Building a MongoDB Clone in Postgres by disbelief on Monday June 04, 2012 @02:28PM (#4065182)

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.

[ Parent | on HN ]