Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Goodbye MongoDB, Hello PostgreSQL

developer.olery.com

Post reply on HN
Print view View original

311–320 of 388 posts

Re: Goodbye MongoDB, Hello PostgreSQL

#311

Post by wallflower »

jaequery wrote: post #181

Care to share your experience in terms of performance of Postgresql's JSONB vs Mongo? for both selects/inserts? and how about nested search queries?

In general, NoSQL databases are good for storing unstructured data. They start to fall apart when you want to query that unstructured data. You will hate yourself if you want to query something that is nested inside something, especially if one of the nesting levels is of the array JSON type. This is because indexes are required (just like in a normal database). Many times, Map-Reduces are required since the queries don't run in reasonable runtime. At some point, because you are storing the entire JSON document, relational databases start winning the space game (they don't store the name of the column with every document, like NoSQL does).

Re: Goodbye MongoDB, Hello PostgreSQL

#312

Post by jhoffner »

Something that I need to call out here. In talking about how to handle the lack of fixed schemas within Mongo, the OP refers to using if/else blocks to get around field name changes. This shows a severe lack of understanding of how to work with Mongo at such a fundamental level. If using Mongoid (which he is) you simply need to run `Post.all.rename(title: :post_title)` to do the schema change. This may seem like a nitpick to some people, but honestly if that concept was lost on the developer then its clear that there is little to no credibility to be had in the reporting of all the other Mongo issues.

I'm glad that the OP is growing as a developer and starting to understand the merits of SQL and why having defined schemas is important. SQL is awesome and its not going to be replaced by NoSQL (at least not fully). However please make sure that you are building the foundation of your understanding by fully grasping the technologies that you are already using.

Re: Goodbye MongoDB, Hello PostgreSQL

#313

Post by darkarmani »

stephenboyd wrote: post #159
SnacksOnAPlane wrote: post #88

Earlier quoted context omitted.

I honestly don't think I've ever seen a valid use case for Mongo. If you're going to query your data, you have to know what fields you're looking for, right? So why not create a schema that has those fields?

Mongo doesn't stop you from using a schema, but you'll have to enforce it in your application code rather than the database itself.

> but you'll have to enforce it in your application code rather than the database itself.

You mean in every one of your application's code. You are using a schema no matter what, but it's a missing feature in mongo.

Re: Goodbye MongoDB, Hello PostgreSQL

#314

Post by darkarmani »

tannerj wrote: post #129
tstrimple wrote: post #120

Earlier quoted context omitted.

Mongo is rarely the answer for high performance, high transaction systems. I use it quite happily to prototype applications due to it's very low boilerplate overhead. If you need schema-less data storage in a "real" database, use PostgreSQL's JSON type. http://clarkdave.net/2013/06/what-can-you-do-with-postgresql...

But what about low performance, low transaction? Realistically the stuff I'd use it for wouldn't see much traffic. The big factor for me is schemaless. I don't want to create a new table each time there is an event with similar, but not exactly matching data between events. I mean, is MongoDB so bad that in any production setting the reliability is not there? Thanks for the link. I didn't know about the JSON type. Ma…

> is MongoDB so bad that in any production setting the reliability is not there?

Replication is for high-availability not for consistency. As long as you can live with that, the reliability is ok.

Re: Goodbye MongoDB, Hello PostgreSQL

#315

Post by jrapdx3 »

mtanski wrote: post #292
jgrahamc wrote: post #13

As a greying developer I am most amused by people discovering that 'old' technologies like SQL databases work really well. The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid. And one more thing: star = Sequel.lit('*') User.select(:locale) .select_append { count(star).as(:amount) } .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percen…

I would like to say lets step back and not conflate SQL and relational databases together. Clearly SQL as the language the primary way most people interact with relational database. In my my mind SQL as a language is a huge PITA. First, parsing of complex statements is expensive (there's workloads where SQL parsing takes more time then processing the results). Second, as SQL exists today (SQL2011) it's a large, compl…

Actually, some work on this has been done for CHICKEN Scheme in extensions "ssql" and "ssql-postgresql"[0]. I'm not too familiar with these particular eggs, but the flavor might be conveyed by this example:

    (ssql->sql #f '(select (columns (col actors name) 
                                    (count (col roles id)))
                     (from roles actors)
                     (where (= (col roles actor_id) (col actors id)))
                     (group (col actors name))))
translates to "SELECT actors.name, COUNT(roles.id) FROM roles, actors WHERE (roles.actor_id = actors.id) GROUP BY actors.name"

In the above sexpr, the SQL query is expressed as a Scheme/Lisp hierarchical list, which I think is a quite elegant notation. I'm sure other Scheme implementations provide variations on this theme of SQL generation. Using some macro magic, there would certainly be a lot of ways to organize it and probably it's not even all that hard to do.

[0] documentation at: http://wiki.call-cc.org/eggref/4/ssql

Re: Goodbye MongoDB, Hello PostgreSQL

#316

Post by hackles_raised »

bau5 wrote: post #131
wainstead wrote: post #126

Earlier quoted context omitted.

I had to look the quote up, because it didn't sound quite right. "Plan to throw one away; you will anyway." The quote is Fred Brooks from his book "Mythical Man Month." It seems to me the author's post reflects that quote. Granted they didn't throw the entirety of every application out, but arguably it's an entirely different application now. I would even wager there's months of refactorings ahead of them as they tak…

I think the author of the post has the right approach. They tried hard to build something that would work right, and since it didn't, they rewrote most of it. They had enough budget and willingness to rewrite it. The author learned from their mistakes, and probably won't be using MongoDB on a future project. The author of the comment I'm replying to seems to advocate a different approach where you choose tech and tec…

And what most people here have missed is that PGSQL is working for them 'because' they now know what their data looks like, MongoDB got them to that point. I can't speak for the OP but some of his examples speak to a lack of experience, which, had they started with PGSQL would likely have resulted in an intractable schema and we would now be reading a 'How MongoDB freed us' post.

Re: Goodbye MongoDB, Hello PostgreSQL

#317

Post by freebullets »

st3fan wrote: post #173

Yes if you have one MongoDB database that uses `title` and another one that uses `post_title` then you have to adjust your code for that. Guess what. Same thing applies to SQL.

In SQL you can't have a table whose title field is called either `title` or `post_title` depending on which record you're looking at.

Re: Goodbye MongoDB, Hello PostgreSQL

#318

Post by snambi »

ad_hominem wrote: post #135
bigpeopleareold wrote: post #124

Earlier quoted context omitted.

I once agreed with this, but now I don't. I just want to write SQL (dammit!). I can never, ever remember the intricacies of the Sequel API or any one of these query builder APIs. I am always looking up something that is rather trivial because I am thinking in SQL, the language, and always have to convert back to Ruby or whatever language I am working in. CTEs and SQL functions in PostgreSQL strike a good balance in t…

Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UPC (WHERE upc LIKE), a couple range filters for min/max prices (WHERE price =), and then on the results screen the user sort on a few different columns (ORDER BY) as well as paginate and set number of results per-page (LIMIT + OFFSET). How exactly are you going…

Answer : Learn SQL. It is very easy to pass variables.

Re: Goodbye MongoDB, Hello PostgreSQL

#319

Post by curiously »

michaelochurch wrote: post #167

My aversion to NoSQLs is derived from the readiness with which uninformed people dive into them, integrate them with their products, and create a web of complexity around something that should ideally be boring and reliable: the database. There are so many things that I've heard you "can't do in SQL" that are false, at least pertaining to Postgres. Semi-structured data, full-text search, "web scale" programming, geog…

> Finally, fuck ORMs.

Amen to that. Many times I wondered, geez I could've written this with a simple SQL query, here I am reading the docs.

Post reply on HN