Hacker News Reader

Comment by egeozcan | Hacker News Reader

I'm nearly sure one day someone will write a MongoDB compatibility layer on top of PostgreSQL

PostgreSQL has native json support now. What else is missing? Just a protocol implementation?

I'd love to see MongoDB give up and become a PostgreSQL consultancy.

Everybody I talk to in the field has the exact same Mongo story: "We love JSON! We use JSON everywhere! We just wanted a DB with native JSON support. We didn't look at the implementation details. We only looked at their marketing. Now we wake up at 3am to fix it every night and lose data every day. Somebody help us. We love JSON."

I really would love it if somebody could go back in HN-Time and track the data on Mongo posts & comments. I've always been slightly skeptical about it, but it always seemed to me that there was a long love affair with it overall. Then people started voicing their frustrations and the community was divided and now it looks acrimonious for everyone.

Isn't that true for most of those 'flavour of the month' technologies?

First a shiny new piece of technology shows up that promises to solve all the issues you have with a mature and widely adopted solution. People get exited and at some point the media picks up on it und starts the hype cycle. More and more decision makers hear about the technology (probably aided by marketing) and decide to adopt it. Implementation takes place and the new technology is deployed into the live environment. Some time goes by and the first issues appear, workarounds and tweaks are devised to mitigate those. After even more time the technology's inherent flaws become apparent. At this point either someone else develops a new iteration of that technology with the promise of solving those issues or the technology is abandoned altogether as it is unable to deliver sufficient value and cannot be fixed.

Totes. I just wanna see pretty graphics of the trend. These things interest me.

coughbitcoincough

http://en.wikipedia.org/wiki/Hype_cycle

Is there an algorithm which measures negativity in a given text? Considering the level of grammatical errors and sarcasm involved, one would need a really complicated system, I'd bet. A google search brought me to Sentiment Analysis page on Wikipedia[0], which, after the initial skimming, doesn't seem to link to any implementations.

[0]: http://en.wikipedia.org/wiki/Sentiment_analysis

I know of one implementation: http://nltk.org

> What else is missing? Just a protocol implementation?

Yes. Actually that's why I said that I'm nearly sure.

As a side note: We may also need some rumors on being "web scale" (Actually I don't even know much about the events/comments/whatever which lead to that famous video but I still find it funny)

It is probably not as simple as "supports json now". Imagine if HN comments were stored as a JSON document:

    Client A: Read JSON.
    Client B: Read JSON.
    Client A: Append new comment to json document.
    Client B: Append new comment to json document.
    Client A: Save JSON
    Client B: Save JSON

A's comment will get deleted. My understanding is that Mongo DB does have a way to append a record within a document, but Postgres does not.

I am in no way advocating for MongoDB (I dislike it). I am just saying that I understand that MongoDB has much more sophisticated updates capability than Postgres.

In that case you can (should) just "SELECT ... FOR UPDATE" in your transaction. This should prevent the issue. Client B will wait with the read until client A commits.

Or you can use serialisable transaction isolation and retry on conflict.

For the simple cases you do not even need that.

Indeed. Depends on whether you need to do a read-update cycle or you can just do it in a single SQL command.

I am pretty sure that just running UPDATE is enough. If an UPDATE happens to get stuck waiting for a lock it is re-evaluated.

EDIT: Found where it is explained in the documentation. I do not see why this should not also apply to JSON.

http://www.postgresql.org/docs/9.3/static/transaction-iso.ht...

... or you can include an ETag (optimistic lock) in your rows/mongo documents and do a conditional update to ensure no writes are interposed with your reads.

Mongo also doesn't have a concept of a transaction so it needs these types of atomic update mechanisms in order to be able to do much of anything sanely. This isn't a problem with SQL which has ACID. I'm not saying that's always the right thing or a good thing, just that your example and conclusion isn't technically correct. Mongo's update capabilities are not more sophisticated but are necessarily different.

That being stated, I'll agree that similar mechanisms for the JSON fields would make sense for Postgres to consider in the future.

> That being stated, I'll agree that similar mechanisms for the JSON fields would make sense for Postgres to consider in the future.

I think they are working on adding modification functions for hstore right now, and the plan is to also add them to JSON once that is done.

You could easily implement your own such functions right now, since PostgreSQL already support atomic modifications in of columns with UPDATE with no overwriting. This statement is safe to use to increment a counter.

  UPDATE c SET counter = counter + 1 WHERE id = 42;

And since that is the case this should also be safe.

  UPDATE c SET data = my_json_array_append(data, 7) WHERE id = 42;

There is nothing magical about "+".

Postgres CAN do exactly this, in fact, this is the sort of thing it excels at, the transactions in postgres can do this, and so much more.

I think what you're talking about is appending with a single statement, which Postgres can also do. You can deserialize JSON inside a postgres query, alter parts of it, serialize it again, then store it in a single atomic transaction using an UPDATE.

But then that could be handled by the compatibility layer.

This is exactly what people call ACID TRANSACTION. Which completely lacked on Mongo. MySQL also lacked this, so people had to deal with data loss until they get InnoDB.

Next time you choose a database, always check the ACID compliance. It will make your life far easier.

> I am just saying that I understand that MongoDB has much more sophisticated updates capability than Postgres.

How many isolation levels does MongoDB support?

Funny you mention this, we've written one as we transition from MongoDB to PostgreSQL. It was actually much easier than I expected, because MongoDB doesn't really do anything - all the JOIN logic is in code and it mostly consists of "find" and "findOne" calls with minor filtering that is easily translatable to SQL.

The hardest part is re-training all the devs to stop thinking like Mongo devs (ie. "I must make five queries and join the info in code") and let the DB do the heavy lifting it was designed to do.

I hope so. I've become so used to Mongoid's API and I've never really liked the "invisible" properties of Active Record.

Yeah, it would be cool if AR came with a `field` method that could be made non-optional through configuration, and enforce that only those fields are accessible.

Not exactly what you were looking for, but you can connect from PostgreSQL to MongoDB via Foreign Data Wrappers - https://github.com/citusdata/mongo_fdw

I know about Foreign Data Wrappers and use them extensively. To clarify, what I want is to have a quick engine swap for some legacy apps without touching anything but config files.

Haha, did you just refer to Mongo in the context of legacy apps? I feel old and tired, and we're not even 10 years into this....

Well, I guess "legacy" doesn't always mean "old" - not that I didn't have a weird smile while typing that particular word.

https://github.com/umitanuki/mongres

https://github.com/JerrySievert/mongolike

https://github.com/citusdata/mongo_fdw

There is already one on top of DB/2.

http://www.theregister.co.uk/2013/06/05/ibm_db2_mongodb/

That's sensible when you consider that postgresql used to be a SQL layer over postgres. Not sure if that is the case now.

> That's sensible when you consider that postgresql used to be a SQL layer over postgres.

Were they ever actually separate layers? I thought that PostgreSQL was a rename of Postgres that happened shortly (one-two versions) after they swapped query languages from the Ingres-derived QUEL to SQL.

Postgres95 had both if I remember. Sorry was nearly 20 years ago now - bit rusty :)

Or was it PostgresME?

Reply on news.ycombinator.com