Goodbye MongoDB, Hello PostgreSQL • Hacker News Board

Re: Goodbye MongoDB, Hello PostgreSQL

#161

Post by api » Tue, Mar 10, 2015, 7:55 PM UTC

pjmlp wrote: post #121

api wrote: post #42

Earlier quoted context omitted.

Agreed, but it's important to understand what drove the adoption of NoSQL and schema-less stuff as well as the related trend of dynamic languages like Ruby and JavaScript. (1) SQL server software itself was clunky, hard to scale, complex to deploy in a clustered fashion, and generally "old" in a bad way. This made NoSQL seem like a breath of fresh air. (2) Startups! Ship now! MVP! Fail fast! The whole industry has be…

I imagine "whole industry" == Silicon Valley? I am yet to do any project that doesn't use SQL servers for data storage.

Yeah, I mean "Greater Silicon Valley" by which I mean SV itself and its wider diaspora. Other names include Startupistan, Hackerdom, etc.

Re: Goodbye MongoDB, Hello PostgreSQL

#162

Post by mbreese » Tue, Mar 10, 2015, 7:57 PM UTC

ad_hominem wrote: post #155

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…

This is a query builder. If you're using your programming language to dynamically assemble the SQL statement fragments at runtime, then you're using a query builder regardless of if it is a library with a fancy DSL that assembles an in-memory SQL AST or some kind of ad-hoc string concatenation you rolled yourself like this. The question I'm asking is pointed towards the people who are implying that you can "just use…

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.

Re: Goodbye MongoDB, Hello PostgreSQL

#163

Post by neunhoef » Tue, Mar 10, 2015, 7:59 PM UTC

Interesting article, did you consider any of the newer NoSQL solutions that offer a data model more similar to MongoDB but more, often configurable, consistency guarantees (like ArangoDB or RethinkDB)? I could imagine that the effort of a migration would have been considerably smaller...

Re: Goodbye MongoDB, Hello PostgreSQL

#164

Post by mbreese » Tue, Mar 10, 2015, 8:00 PM UTC

jimbokun wrote: post #160

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…

Now how is this an improvement over the Sequel example? I understand the impulse to "Just write SQL." But in practice, with all the string concatenation needed to generate actual queries, you can't really see what the SQL will be without running all the code in your head anyway.

In practice, I'd reduce this to a query building function in my own code, so the readability would be much better. The benefit is that you don't need to include another library for it to work. Well, that and it is a strategy that works for all languages, not just Ruby.

Re: Goodbye MongoDB, Hello PostgreSQL

#165

Post by acveilleux » Tue, Mar 10, 2015, 8:02 PM UTC

barce wrote: post #79

The section, "The Problem with Schemaless," blames the technology instead of whoever put the data in there in the first place. If the code has to handle both page.title and page_title, this is a feature of using a schemaless technology. Also, lots of the issues the author had with MongoDB are also to be found in MySQL, e.g. taking hours to recover from a corrupt database/datastore.

It's just that if you have a sizable team (or worse over a long time with low overlap) working on a product, you intrinsically get an accumulation of duplicates, deprecated, or both "keys" in your schema-less schema.

With a DB that enforces a schema, the overhead of modifying the schema tends to moderate that nature.

I once worked in a lab (Ph.D. students are atrocious programmers BTW) where I introduced a schema-less store (cheesy K/V store) to handle some mundane metadata caching on some medical imaging. It was intended to store 4-5 attributes per PK, I never touched it after setting it up for what I needed but showed it to colleagues.

Fast forward 10 years and there were something like 3000 attributes defined. Several hundreds of which were serialized blobs. Huge amount of overlap between the different attributes.

Almost all that because people didn't know what was already in there so they just did their own thing.

Re: Goodbye MongoDB, Hello PostgreSQL

#166

Post by IgorPartola » Tue, Mar 10, 2015, 8:03 PM UTC

colinhowe wrote: post #11

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…

We have a dataset much larger than RAM (600gb vs 60gb RAM). It's the working set that really matters. Accessing data outside the working set can be slow - unless you have SSDs :) Regarding consistent/durable: during the past four years, we've not had any problems on this front that weren't caused by us in. We've had an issue that was a misconfiguration on our part where we allowed writes to a server because we pulled…

Well, SSD's aren't nearly as fast as RAM, even today.

I think we are in agreement about the working data vs all data. I am saying that in most applications your working dataset is much smaller than your total data set. So why pay for hardware capable of holding your entire dataset in RAM when you don't need it?

I am surprised you are able to do this with Mongo. Last I checked, it simply did not handle this case, and started failing miserably if it was not able to fit all data into RAM.

Re: durability: I am not talking about the server going down, coming back up. I am talking about whether there is an fsync() when writing data. Set up a test case where you are writing data very rapidly to MongoDB, then pull the plug on the box it's running on. It'll come back up, but the data it told you it just wrote won't be there because it didn't checkpoint. Did you check that all your writes succeeded when you had node failures in your cluster? Most applications don't have the machinery to do this because generally the state necessary to check this is stored in the database, yet it's the database you are testing. The only way to test this is to also write logs (also atomically), and then verify DB data against logs. Or, just use a database that guarantees durability.

Re: consistency: MongoDB doesn't support transactions [1]. That's enough to exclude it from a large number of applications. Anything to do with money, for example, is out since you really don't want double spending to be a thing.

[1] http://docs.mongodb.org/manual/core/write-operations-atomici...

Re: Goodbye MongoDB, Hello PostgreSQL

#167

Post by michaelochurch » Tue, Mar 10, 2015, 8:04 PM UTC

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, geographical indexes... all of these things, people say "you can't do in a relational database" and that's not true. Postgres is fucking powerful and can do a lot, very well.

Arguably, PostgreSQL isn't always a "relational database". You can use it as a key-value store. It just happens that you often want relational logic in a multi-purpose, long-lived data store. The relational database seems to be an attractor; the requirements that accrue to a typical in-house "we can do it better"/NIH non-relational database often converge on it.

NoSQLs have their place at very large scale (100+ TB) and there are plenty of specialized reasons to use alternative databases-- I doubt that Postgres's full-text search is competitive with Elasticsearch-- but I feel like most of the anti-SQL sentiment is against the language. And sure, it's an ugly and outmoded language, but the database is one place where I'd rather have an ugly language and rock-solid tech than the other way around.

Finally, fuck ORMs.

Re: Goodbye MongoDB, Hello PostgreSQL

#168

Post by ivanr » Tue, Mar 10, 2015, 8:04 PM UTC

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…

There is. MyBatis, which is a minimal ORM that aims to keep you as close to SQL as possible, has support for dynamic SQL: https://mybatis.github.io/mybatis-3/dynamic-sql.html

Edit [responding to moe, below]: that's a matter of taste. I prefer to have my SQL _outside_ my code. If I have to write a little XML to make it happen, so be it. Additionally, unlike your example, a strict separation of SQL and data ensures SQL injection is not possible. That's also a worthy goal.

Re: Goodbye MongoDB, Hello PostgreSQL

#169

Post by ad_hominem » Tue, Mar 10, 2015, 8:07 PM UTC

mbreese wrote: post #162

ad_hominem wrote: post #155

Earlier quoted context omitted.

This is a query builder. If you're using your programming language to dynamically assemble the SQL statement fragments at runtime, then you're using a query builder regardless of if it is a library with a fancy DSL that assembles an in-memory SQL AST or some kind of ad-hoc string concatenation you rolled yourself like this. The question I'm asking is pointed towards the people who are implying that you can "just use…

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.

Disregarding my edit and how your query builder is irrelevant to my question anyway, if I change the requirement to allow the user to sort by multiple columns instead of one, that case statement is going to either combinatorially explode or you'll also have to add some ad-hoc SQL escaping to guard against SQL injection since you can't use bound parameters in ORDER BY clauses.

It's easy to see how libraries like Sequel get invented to help people do this, and I don't really see what the problem is when the DSL stays close to SQL semantics.

Re: Goodbye MongoDB, Hello PostgreSQL

#170

Post by zo1 » Tue, Mar 10, 2015, 8:11 PM UTC

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…

Python SQLAlchemy works a bit like this, if you use it that way:

    query = session.query(Products)

    if limit:
        query = query.limit(limit_val)

    if offset:
        query = query.offset(offset_val)

Sort is a bit tricky, but as I'm a noob at SQL Alchemy, I had some not-so-pretty boilerplate code implemented for decorating queries with custom sorts. But essentially it boiled down to:

    if sort_type == SORT_REVERSE_CONST:
         sort_obj = sort_obj.desc()
    else:
         sort_obj = sort_obj.asc()

    query = query.order_by(sort_obj)

And if you want to search through dynamic tables, change the initial query instantiation to something along these lines:

    query = session.query(retrieve_db_object(table_name))

And to execute:

    return query.all()