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

271–280 of 388 posts

Re: Goodbye MongoDB, Hello PostgreSQL

#271

Post by imron »

solutionyogi wrote: post #9
pizza234 wrote: post #3

There is a mistake in the article, due to the OP not knowing an arguably basic notion about MySQL. > when defining a field as int(11) you can just happily insert textual data and MySQL will try to convert it. this is dependent on the SQL Mode, which is quite flexible. for example, the STRICT_ALL_TABLES will prevent strings to be inserted in INT fields: mysql> create table example ( `number` int(11) not null ); mysql>…

Personally, I would not use a database which is 'lax' by default instead of 'strict'. What other choices have they made which I need to learn OR it will bite me big in Production?

utf8 is not really utf8 and doesn't support 4 byte characters that exist outside the BMP (you'll need the separate utf8mb4 type for that, assuming you're not running on a legacy version that doesn't support that either).

Re: Goodbye MongoDB, Hello PostgreSQL

#272

Post by jbergens »

rdtsc wrote: post #214
mbreese wrote: post #162

Earlier quoted context omitted.

Yes, logically, it's a query builder. But it's one that doesn't force any extra dependencies in my project and is very easy to troubleshoot. Seriously, how is this different from "just use SQL". SQL was never really intended to be used by itself (except for manually typing queries into a console). Yes, what I wrote is code that writes code, but it's far different from a larger ORM or querying framework.

> SQL was never really intended to be used by itself. Hmm I thought that is how it was intended to be used? If it didn't, it would look like datalog, lisp or some binary protocol with prefixed lengths and whatnot. It was intended to be typed in by analysts at a console. Who would then print the report on the dot matrix printer and mail to the headquarters or something of that sort.

SQL was often combined with Cobol (might still be where they have Cobol).

Re: Goodbye MongoDB, Hello PostgreSQL

#273

Post by Cakez0r »

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…

Agree with all of your points. The thing that perhaps annoys me the most is that people seem to assume that nosql is magically fast and that sql is slow... Sure, a key-value pair lookup runs like shit off a shovel, but as soon as you want to run anything more complex than that, it's likely faster with a relational database and a few indexes...

Usually the same people that think running an application on a cloud platform magically makes it fast, when in reality a VPS would be cheaper and faster.

Re: Goodbye MongoDB, Hello PostgreSQL

#274

Post by jbergens »

mbreese wrote: post #137
YorickPeterse wrote: post #85

Earlier quoted context omitted.

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…

How often do you really reuse bits of a SQL query? SQL is the language that an ORM will generate. The question is, how much work is done to avoid using SQL and is it worth it? The only time that I've found ORMs useful for composing queries when I have to dynamically create a query at runtime based upon user input. And even in that case, today, I'd probably still just concatenate strings for a proper SQL statement.

It happens a lot for me. Basically you have to choose between copy-pasting a lot of sql code or trying to reuse parts when the business logic has multiple cases that are similar but not exactly equal.

Re: Goodbye MongoDB, Hello PostgreSQL

#275

Post by gfody »

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…

> So tl;dr: it's about composability, not being "lazy" or "ignorant" to SQL.

If you want composability you should factor the common sql fragments out to parameterized views (table valued functions), or just regular views. Doing this across a large project can really improve performance as well.

Re: Goodbye MongoDB, Hello PostgreSQL

#277

Post by macavity23 »

serve_yay wrote: post #17
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…

Gosh, no kidding. (I sorta disagree about ORMs though -- if you're selecting by ID it's monkey work to write those queries, but anything complex, sure, use SQL)

Right. A good ORM should deal with all your common cases (if not, your schema probably needs improving), and it saves you writing a lot of boilerplate code.

For the edge cases where you need complex joins and/or extra performance, don't try and bother with the ORM, just drop to SQL.

Re: Goodbye MongoDB, Hello PostgreSQL

#278

Post by clebio »

adrianmsmith wrote: post #194
mbreese wrote: post #143

Earlier quoted context omitted.

You might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses and join them with " AND " to avoid keeping track of the "WHERE"s and "AND"s, but you get the point...) sql = 'SELECT * FROM products' args = [] if title: sql += ' WHERE title LIKE %?%' args.append(title) if upc: if args: sql += ' AND' else: sql += ' WHERE' sql…

I know this isn't central to your point, but I thought I'd say it anyway. These days I have taken to doing: sql = "SELECT * FROM products WHERE TRUE" .... if upc: sql += ' AND upc LIKE %?%' .... This saves having to worry about "AND" and "WHERE", and the extra "WHERE TRUE" results in the same execution plan (thus the same performance).

Awesome. That's such a straight-forward solution to one of the crappy edge-cases that SQL is so bad about (ending lists without commas, like JSON requires too, being another).

Re: Goodbye MongoDB, Hello PostgreSQL

#279

Post by jacques_chester »

jrochkind1 wrote: post #260
AceJohnny2 wrote: post #196

Earlier quoted context omitted.

I think you're understating the cost of updating the structure of an in-use database.

I'm not even sure if you're referring to an rdbms or Mongo, which is kind of the other side there. I _think_ you're referring to an rdbms. But I also think people seriously understate the cost of changing the way your data is stored in a large in-use MongoDb too.

Mostly because an RDBMS will ask you to explain, explicitly, in complete detail, how you propose to consolidate the entire schema and existing data with the new changes without introducing paradoxes or inconsistencies.

Mongo won't. It will take you at your word. Even if that word is wrong.

Re: Goodbye MongoDB, Hello PostgreSQL

#280

Post by rpedela »

solidsnack9000 wrote: post #269
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…

Even with PL/pgSQL, there is no way to build a SQL statement dynamically _and_ safely -- it's all just string concatenation there, too. However, using `CASE`, `WITH` and `LATERAL` you can have a root `SELECT` that returns one of a few different queryable code paths, and `WITH`/`LATERAL` allow you to reuse definitions.

Use format() for safely constructing dynamic queries.

http://www.postgresql.org/docs/9.4/static/functions-string.h...

Post reply on HN