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

221–230 of 388 posts

Re: Goodbye MongoDB, Hello PostgreSQL

#221

Post by hammerandtongs »

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 also use emacs(evil-mode actually). An ide is not much different then a fully configured vim or emacs.

sql-mode for some databases(mysql and oracle iirc) reads the DDL of the tables you are typing about, why is this not better and more general?

Sql is so well established and general as a solution that not learning it is a poor choice.

Re: Goodbye MongoDB, Hello PostgreSQL

#222

Post by zaroth »

rikkus wrote: post #154
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…

Here's an example in C#. Imagine you're querying a database of products (here represented by integers). Users can enter filter parameters - you want to build your query dynamically based upon what they enter. With LINQ, you can do this kind of composing with no effort. You also get to run the same code on any kind of Queryable, so if you feel like doing some of the work in RAM and some using a DB, your query is usual…

FYI, you can also write it this way, although you may find it less readable, it emits the exactly same query to the DB, and I personally find it much more pleasing;

  IEnumerable Search(...) {
     return result
        .Where(i => !f.EvensOnly        || i % 2 == 0)
        .Where(i => !f.Minimum.HasValue || i >= f.Minimum)
        .Where(i => !f.Maximum.HasValue || i
Nit: EvensOnly should not be nullable since it has only two states.

Re: Goodbye MongoDB, Hello PostgreSQL

#223

Post by TylerE »

masklinn wrote: post #70
aethertap wrote: post #48

Earlier quoted context omitted.

Your comment made me realize why I prefer to work in ORMs instead of raw SQL, even though I'm frequently frustrated by their limitations - it all comes down to composability. The mongodb syntax looks like it supports composing statements much more readily than SQL. It makes me wonder if there's been any serious work done making a query language that's fully as general as SQL, but is designed to be safely and easily c…

> The mongodb syntax looks like it supports composing statements much more readily than SQL. It makes me wonder if there's been any serious work done making a query language that's fully as general as SQL, but is designed to be safely and easily composable so that you can build queries up from parts. Haven't you noticed that it's exactly what the Sequel snippet does? It's also what SQLAlchemy's Expression Language do…

In some ways it does, but also remember it's a MUCH less rich query language. It's a lot easier to make a simple api mapping when you don't have such exotic things as "joins".

Re: Goodbye MongoDB, Hello PostgreSQL

#224

Post by YorickPeterse »

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.

Re: Goodbye MongoDB, Hello PostgreSQL

#225

Post by kyberias »

quackerhacker wrote: post #25
super_sloth wrote: post #2

Is there anybody here who has run MongoDB at moderate scale with good results? As in a few terabytes of data, >10k ops/second territory. I've been really disappointed with its reliability and performance in situations where I've been around that.

I've been using mongo on 39M+ records (tracking financial tick data) across 73 assets (collections) and my queries take anywhere between 2-3mins depending on complexity. You can always run db.currentOp() in the mongo shell to see what process is taking forever as well. Let me clarify why though, there methods of optimizing a query by adding another field, but since I have to traverse my records with the sort() cursor…

I don't get this. If any relational database choked with that small number of rows, it would be thrown away immediately. That's a couple of orders of magnitude too slow.

Re: Goodbye MongoDB, Hello PostgreSQL

#226

Post by ad_hominem »

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

This actually answers the question, although I would imagine the ORDER BY handling will look pretty messy (CASE statement perhaps?).

And I don't know what it would look like if the requirements changed to allow ordering by multiple columns with different possible sort directions... that might get back into dynamic SQL using a RETURN QUERY EXECUTE type of thing, which is basically using a query builder in your query language.

Re: Goodbye MongoDB, Hello PostgreSQL

#227

Post by netghost »

JonnieCache wrote: post #66
jonahx wrote: post #59

Earlier quoted context omitted.

jeremy is incredible. he is more helpful and available (and certainly more knowledgeable) than most paid support people for premium products. if you are learning sequel, stop by the irc channel and he'll probably answer your question.

seriously the man is a hero. I owe him about a brewerys worth of drinks. EDIT: I just went to look at the github: 1821 stars, zero issues. And yes, github is the projects official bug tracker. There are 662 closed issues. I must see if I can arrange for work to send him some money...

Or a brewery!

Re: Goodbye MongoDB, Hello PostgreSQL

#228

Post by tstrimple »

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…

Most people who run into performance issues on Mongo are putting a lot of data into it. I've personally never had problems with it for side projects, but my tolerance for failure and data loss in those scenarios are quite a bit different than what most people expect out of production systems. It's definitely possible to use Mongo in production successfully, you just have to be aware of the tradeoffs and plan accordingly.

Re: Goodbye MongoDB, Hello PostgreSQL

#229

Post by dustingetz »

bigpeopleareold wrote: post #124
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…

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…

Orms are not about syntax, they're about things you can't natively "think about" in SQL, like inheritance, composition, references and graph navigation

Re: Goodbye MongoDB, Hello PostgreSQL

#230

Post by CWuestefeld »

bbcbasic wrote: post #203
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…

In a compiled language like C#, "wrapping" SQL is a good idea, at the very least to catch bugs at compile time rather than runtime. Also makes it easier to switch from one DB provider to another if you need to. You are less likely to suffer from SQL injection if you use at least some kind of wrapper, although a minimal wrapper that takes a SQL with placeholders and arguments would do for that. My preferred way to wra…

You are less likely to suffer from SQL injection if you use at least some kind of wrapper

Pretty much all of our coding guidelines on my team are just guidelines. The one absolute law is that all data going to the DB must be paramaterized, nothing goes in as string substitutions.

But virtually all of our DB access (like, at least 99%) is by stored proc anyway. We're just barely able to keep up with performance requirements by tuning things just right, and in my experience, EF isn't able to generate queries that are as efficient as we can by hand with some fiddling. I know that sounds like "I write in assembler, 'cause no compiler is as good as me", but that is what the reality seems like.

Post reply on HN