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

361–370 of 388 posts

Re: Goodbye MongoDB, Hello PostgreSQL

#361

Post by jqm »

prottmann wrote: post #332
jqm wrote: post #285

I haven't used MongoDB in production (the comments regarding reliability have been around for awhile), but playing around with it, I do like the json format and query structure. The issue I have with SQL (MS sql in the case of work) is the amount of cleverness involved in some queries I have seen. Among the old timers, it seems almost a badge of honer to develop the longest, most clever SQL query that does everything…

That is then not a problem of the SQL-Language, it is more a problem of the database design. When you need really complex queries, you should think about a new db structure.

The largest database is vendor provided so we are stuck (for now).

Thanks for the insight.

Re: Goodbye MongoDB, Hello PostgreSQL

#362

Post by grey-area »

moe wrote: post #354
grey-area wrote: post #348

Earlier quoted context omitted.

You still need to supply the local variables to your ORM-like so that it can perform interpolation into the string (a feature which is actually best done by the database driver, NOT your ORM-like). Perhaps we can suggest this syntax: s.add "WHERE foo > ?", bar Or even this: s.Where "foo > ?", bar At that point, you've reinvented ActiveRecord or hundreds of other query builders, it also avoids you having to remember t…

Perhaps we can suggest this syntax: s.add "WHERE foo > ?", bar I'd say stick with keeping it a string and use: s.add "WHERE foo > $(bar)" but many query builders nowadays are very well thought out, and have a simple syntax which just echoes SQL And that's the problem. Everyone "echos" SQL, just slightly differently, each with their own idiosyncraticies. If all you do is echo anyway, why don't you just let me write pl…

If all you do is echo anyway, why don't you just let me write plain SQL, for christ's sake?

They usually do more. SQL is not very pretty or friendly IMO, ORMs clean it up a bit (for example allowing arbitrary ordering, sqli, construction of multiple queries from one base). The example I gave was an arel query - very similar to yours if you move the WHERE out into the function name.

Your ORM proposal above looks fine (and very similar to existing ones but with more strings), but personally I find it uglier than the many which are available, and you haven't actually tackled the protection against sqli or explained how that would work. Normally you'd pass that through to the db driver as a $n param, rather than trying to escape a string at the ORM level. If your ORM is going to infer details from the sql, it basically has to parse that sql first and handle all the same things that current ORMs do by using named functions to add SQL - you'd find the same issues with consistency and edge cases I imagine, and of course have to be very careful about parameters and sqli.

So if you don't like ORMs, don't use them, but you do have to be sure that your own ad-hoc home-grown ORM that you're writing instead covers all the bases current ones do. I prefer the syntax of the ones you're denigrating for simple queries, even if for complex queries it might be better to write sql directly (via the functions they provide), but would be interested to see it in action - when are you going to start :)

Re: Goodbye MongoDB, Hello PostgreSQL

#363

Post by jrochkind1 »

IgorPartola wrote: post #266
jrochkind1 wrote: post #261

Earlier quoted context omitted.

> This is the whole point of MongoDB: you assume the responsibility of managing the schema Who/what is the 'you' there? Don't 'you' have the responsibility of managing the schema either way? It's a question of whether you want to manage the schema through an rdbms, or... just in your application logic, I guess?

Manage as in write all the code that ensures adherence to the schema. If you say that the field called "score" is an int in a schema-ful DB, then insert an array, the DB will throw an error. If you do that in a schema-less DB, it will not unless you add a check yourself. If you are not using some type of unified DB access layer, you must perform this check every time you write a value. You must also perform the check…

> Manage as in write all the code that ensures adherence to the schema... ...This you take on doing much more work, yet gain flexibility

The flexibility you gain seems to mostly be... the flexibility to choose not to ensure adherence to the schema.

Which may be a valid choice in some cases, but I suspect a lot of people don't quite realize they are making it let alone analyze whether it is the right choice for them, and come to regret it.

Re: Goodbye MongoDB, Hello PostgreSQL

#364

Post by towelguy »

tom9729 wrote: post #298
JustGotHere wrote: post #210

Earlier quoted context omitted.

What I'd like to see is a universal SQL that can be translated to whatever dialect of SQL my current database is using. That way I won't have to relearn SQL every time I start a project with a different database engine.

You could use Teiid ( https://github.com/teiid/teiid ). The SQL dialect is similar to Postgres, and it has built-in translators to handle all of the popular relational databases (Postgres, Oracle, MySQL). Added bonus is that even some NoSQL databases are supported, and you can do things like join a table from a MySQL database against a collection in MongoDB. Full CRUD is supported for most translators. If you're usin…

Very interesting, thanks!

Re: Goodbye MongoDB, Hello PostgreSQL

#365

Post by tracker1 »

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…

Stuff like your example is where, in general I prefer a sproc, or something more like the following...

http://www.ivc.com/blog/better-sql-strings-in-io-js-nodejs-p...

Re: Goodbye MongoDB, Hello PostgreSQL

#366

Post by tracker1 »

rdtsc wrote: post #211
hammerandtongs wrote: post #145

Earlier quoted context omitted.

You've formulated the composibility reasonably in that sentence but it's important to remember that is not the only problem the world faces in data manipulation. I think the irony is that much of the problems of sql are related to a lack of tooling. The lack of tooling is result of large numbers of devs being distracted by ORM which moves that particular problem into the language ide/editor. Essentially we lack great…

> Essentially we lack great *.sql editing, ide, macro, refactoring because of the lack of focus on sql itself. Except that my other tools are just an editor like emacs and makefiles. So now to be productive I have to have and ide for sql, learn sql, learn macros. Interesting that SQL came first. If it was so easy and obvious to use it we would not have seen any ORMs by now. But every other project that uses SQL datab…

I think it's a mixed bag... and depends on what you want to accomplish... a lot of the time, I will find myself bypassing an ORM about 20% of the time... the other 80%, the ORM model is simpler.

I find LINQ + Entity Framework to be a blessing in C. Though I use the monad/function syntax not the LINQ query sugar when I use it.

Re: Goodbye MongoDB, Hello PostgreSQL

#367

Post by hugopeixoto »

YorickPeterse wrote: post #224
pmontra wrote: post #150

You probably had a replica set and maybe an off-site replica. What are you using now, a single PostgreSQL instance, a master-slave cluster or any other distributed setup? If positive, which one of the many psql distributed technologies are you using? Thanks.

Before (Mongo): 1 primary, 2 secondaries, 2 arbiters Now (Postgres): 1 primary Most of our applications require write access in some shape or form, so at least the default replication of Amazon RDS doesn't cut it. Besides that we don't really need it so far, don't see the need for it in the coming months either.

One problem I have with postgres right now is that whenever I want to upgrade the cluster (say from 9.1 to 9.3) there's downtime while the cluster is upgraded.

Not even vanilla replication helps, right now. I think I'd have to use something like slony to replicate between different versions of postgresql, but I never tried it.

How does upgrading postgresql versions work in RDS?

Re: Goodbye MongoDB, Hello PostgreSQL

#368

Post by IgorPartola »

pico303 wrote: post #321
IgorPartola wrote: post #4

> Another way of handling this is defining a schema in your models. For example, Mongoid, a popular MongoDB ODM for Ruby, lets you do just that. However, when defining a schema using such tools one should wonder why they aren’t defining the schema in the database itself. Bah. It's like they didn't know that schema-free data stores mean "there is no schema; different objects may have different fields". This is the who…

I'm not a fan of MongoDB, but you don't know what you're talking about. MongoDB is durable. While it doesn't quite support SQL transactions, it is durable. The data is journaled before being confirmed, and once confirmed will be written to disk. It can be consistent, but this sort of breaks the whole idea of scaling and distributing the load in Mongo, or causes massive performance problems, so that is something to co…

Please read the first answer to http://stackoverflow.com/questions/18488209/does-mongodb-jou... before assuming that the MongoDB journal is durable.

It comes down to this: there is no way to tell MongoDB to commit a write and not return until it is durable. You can tell it to fsync() it within at most 33 ms or so from when it returns, but not immediately. There is no control in it to say "fsync() this value because it is important, then return".

That is not as durable as other database engines, which do this based on number of commits that have not been fsynced to the journal (this value can be set to 1 causing every commit to be fsynced).

Consistency is a large topic, and I am sorry I mentioned it vs something more specific, such as:

MongoDB doesn't have transactions, so you can't make multiple updates truly atomic. That's annoying at least, and unusable at most. Two phase commits let you get about 50% there, but with no rollback your application code has to implement rollbacks. In lots of scenarios your application is not smart enough to rollback a "transaction", so your data will be screwed.

Eventual consistency is starting to come under fire precisely because it avoids the hard problem of database design and is offloading it on the application developer, who is typically not as versed in how to create a system where temporary inconsistencies are OK. At best, it means that the developer is now wasting productivity on addressing consistency issues.

You may be right about Foursquare keeping only an index of all the check-ins, not the entire dataset. The main point remains: why keep essentially archived data in RAM? MongoDB does not let you keep a partial index.

MongoDB tries to give up basic datastore guarantees to gain speed and flexibility. Once again, I am not arguing that it's a useless tool. It is useful. As a cache. Or in cases where data loss is acceptable. It is much less useful in cases where dataloss is catastrophic, such as financial transactions. It will also be much more expensive at scale, whereas other datastores optimize for the conditions where RAM is expensive and disk is cheap. You can still run into this case when you are talking about a terabytes to petabytes of data.

Re: Goodbye MongoDB, Hello PostgreSQL

#369

Post by IgorPartola »

jrochkind1 wrote: post #363
IgorPartola wrote: post #266

Earlier quoted context omitted.

Manage as in write all the code that ensures adherence to the schema. If you say that the field called "score" is an int in a schema-ful DB, then insert an array, the DB will throw an error. If you do that in a schema-less DB, it will not unless you add a check yourself. If you are not using some type of unified DB access layer, you must perform this check every time you write a value. You must also perform the check…

> Manage as in write all the code that ensures adherence to the schema... ...This you take on doing much more work, yet gain flexibility The flexibility you gain seems to mostly be... the flexibility to choose not to ensure adherence to the schema. Which may be a valid choice in some cases, but I suspect a lot of people don't quite realize they are making it let alone analyze whether it is the right choice for them,…

Which is pretty much my point. Most people don't realize the hidden cost here. It's the same reason you wouldn't use a dict for everything in Python.

Re: Goodbye MongoDB, Hello PostgreSQL

#370

Post by mtanski »

jrapdx3 wrote: post #315
mtanski wrote: post #292

Earlier quoted context omitted.

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(r…

It's def a step in the right direction. At least in terms of letting you mechanically transform queries.

The downside (IMO) is that it's still reflecting SQL the language ... not the relational model.

Post reply on HN