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

231–240 of 388 posts

Re: Goodbye MongoDB, Hello PostgreSQL

#231

Post by bgentry »

YorickPeterse wrote: post #140
bgentry wrote: post #7

In the future we might also move our Rails applications over to Sequel, but considering Rails is so tightly coupled to ActiveRecord we’re not entirely sure yet if this is worth the time and effort. Actually, with modern versions of Rails, using Sequel in place of ActiveRecord isn't bad at all. Nothing in Rails is really tied to ActiveRecord anymore. There are dependencies on ActiveModel, but you can easily make Seque…

While certainly possible we simply haven't really evaluated it yet in depth. In between releasing a bunch of upcoming features and upgrading Rails from 3.2 to 4.2 I'd rather wait with _also_ moving from ActiveRecord to Sequel for the time being.

yes, by all means upgrade yourself to the latest release of Rails before attempting to go "off the rails" :)

Re: Goodbye MongoDB, Hello PostgreSQL

#232

Post by bgentry »

izietto wrote: post #197
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…

This is a `negate` method in Arel (by Rails): class User Do this in SQL. Composability is the real boost, and you have composabiliy when you don't have to build a string in order to interact with the db.

unfortunately, where_values is also a private / unofficial API with no stability guarantees, so it can change at any time =/

Re: Goodbye MongoDB, Hello PostgreSQL

#233

Post by [deleted] »

YorickPeterse wrote: post #85
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…

It's not so much about not wanting to write/understand SQL (both are still very much required), but about composability. If you want to re-use bits of a SQL query written as a string literal your only option is string concatention or using some kind of string builder/template system. In both cases there's little validation of the query's correctness (syntax wise) until you actually run it. While I agree that many ORM…

[deleted]

Re: Goodbye MongoDB, Hello PostgreSQL

#234

Post by schrodinger »

joshuahutt wrote: post #179
ad_hominem wrote: post #135

Earlier quoted context omitted.

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…

I would write a SQL function that takes all of those as optional parameters and includes a lot of these: WHERE (_title IS NULL OR title LIKE _title) AND (_minPrice IS NULL OR price > _minPrice) AND (_maxPrice IS NULL OR price

That ends up being very inefficient as the optimizer will come up with one mediocre plan that works for all those criteria.

Re: Goodbye MongoDB, Hello PostgreSQL

#235

Post by spdustin »

angersock wrote: post #76
bau5 wrote: post #57

Earlier quoted context omitted.

No, this is bad. You shouldn't design a system that won't work from the outset. "Plan one to throw away" is about budgeting time, not about knowingly making big technical compromises when you write the first version.

Not quite--plan to throw away the internals , not the interface . This lets you do things like writing shitty hyperlinear naive solutions to get all the pieces of a system in place, and then going back and optimizing each in turn. You don't spend a lot of time fussing over little details, and instead make rapid progress. If you don't spend time on the interfaces between the pieces, though, you're absolutely screwed.

I'll say I agree with this corollary: the schema is the interface.

Re: Goodbye MongoDB, Hello PostgreSQL

#236

Post by wallyhs »

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…

> Or are there some advanced Postgres features that I'm not aware of that would allow all these combinations in some kind of prepared statement?

I can't speak specifically to Postgres, but, in my experience, there is nearly always a way to do what you want with bound parameters. No advanced features are required. It often results in poor performance and redundant code that is hard on the eyes. You will get what you deserve, but sometimes you don't have a choice.

Here is an example of the horror:

    WHERE (:1 IS NULL OR :1 = item_id)
      AND item_num BETWEEN NVL(:2,0) AND NVL(:2,9999999999)
      AND item_date BETWEEN NVL(:3,'01-jan-1900') AND NVL(:3,'31-dec-4712')
      AND item_name LIKE NVL(:4,'%')
    ORDER BY
      CASE :5 WHEN 'NUM' THEN item_num WHEN 'DATE' THEN item_date ELSE item_name END,
      CASE :6 WHEN 'NUM' THEN item_num WHEN 'DATE' THEN item_date ELSE item_name END
Edit: I suppose you could also parameterize the ascending vs. descending sort, although I have never tried. My first thought is to duplicate each line in the ORDER BY clause: one bind parameter for ASC and another for DESC. Have each CASE return a constant if the bound value is NULL, and then bind NULL for the direction you do not want. Yuck.

I am not advocating any of this but am pointing out that bind parameters can be abused in surprising ways if you are backed into a corner.

Re: Goodbye MongoDB, Hello PostgreSQL

#237

Post by lugg »

moe wrote: post #149
ad_hominem wrote: post #135

Earlier quoted context omitted.

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…

How exactly are you going to "just write SQL" if the actual query statement needs to change based on the user input? How about something like this: s = Select.new s.add "WHERE title LIKE #{title}" if title s.add "WHERE price Note how I deliberately shuffled the order and didn't bother with escaping. Also note how anyone who knows SQL could immediately work with this, learning curve: 5 seconds. Why is there no ORM tha…

Generally I'll go with something like this for these conditional search based queries

    select id, blah, boo
    from products
    where 1
    and (title like :title} or if(:title = '', false, true))
    and (price
Its a trade off, bit of extra complexity in the query for simpler code logic. Really, I avoid using sql for these types of "search" / find methods, they're probably going to be slow as hell as soon as you get a sizable data set.

I really despise orm's its essentially taking a black box (sql/database) and throwing it inside a much less tested, less optimized, less documented black box.

For what reason, I have no idea, nobody has ever been able to convince me there is a problem that needs solving. Almost every argument i've heard amounts to problems sql already solved a decade ago. I can only speculate but I'll hazard a guess it comes down to lack of enthusiasm to really learn sql properly (even though the person uses it / debugs it daily)

Re: Goodbye MongoDB, Hello PostgreSQL

#238

Post by bigpeopleareold »

Udik wrote: post #193
jballanc wrote: post #151

Earlier quoted context omitted.

I'd argue it's less about composability, and more about laziness (in the computational sense). As pointed out elsewhere, if you have all of the information needed to generate a dynamic query, it's often not a huge leap from an ORM to composing strings (especially given how relatively flexible SQL syntax can be). However , sometimes I want one part of my program to be responsible for one bit of a query, and a separate…

I agree with you example, in that case it is useful. It's just that it is a pain in the ass in so many other cases. I always wonder why developers (usually young and enthusiastic ones...) pick a set of the most challenging requirements they can think of and then use them to justify the usage of some library or pattern that makes life a nightmare in the simpler cases, which are obviously the majority of the use cases.…

The biggest challenge is to understand and find the simplest case and deal with the hard cases when they happen. I had to learn this over time.

Re: Goodbye MongoDB, Hello PostgreSQL

#239

Post by zo1 »

tannerj wrote: post #102

So let me ask a question. What should I use when I do need a schemaless database? Is NoSQL never the answer? I've got a project that needs to allow clients to create registration forms for different events that my company hosts. A lot of the registration data will have a defined shema ex: name, email, address. I feel like that stuff should go in a RDMS, but all the event specific stuff needs to be schemaless. I know…

>"I feel like that stuff should go in a RDMS, but all the event specific stuff needs to be schemaless."

Okay, you lost me there. Why does it need to be schema-less?

Re: Goodbye MongoDB, Hello PostgreSQL

#240

Post by spdustin »

I feel like I'm missing something. So many comments in the article and here along the lines of, "just issues a warning and not an error."

Am I the only one here who's thinking that this is correct behavior on the part of the DBMS? Three result codes from an operation: 1) everything is okay, 2) I'm sorry Dave, I can't do that (error) and 3) Okay, if you insist, but I'm going to change your data to make it work.

Am I the only one who thinks: the programmer should be aware of and respond appropriately to ALL THREE, not just 1 and 2.? That anything else is just laziness?

Or is that just me? Am I missing some subtle consideration here that results in my thought process being naive? If I'm being naive I do want to understand what I'm missing, because getting schemas right and having my code react when improper data types are being used is sometimes a pain, ORM or not. But I've always thought it was the right thing to do.

Post reply on HN