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

381–388 of 388 posts

Re: Goodbye MongoDB, Hello PostgreSQL

#381

Post by jeltz »

troels wrote: post #157
esilverberg2 wrote: post #78

The author's assertion that "Another problem with MySQL is that any table modification (e.g. adding a column) will result in the table being locked for both reading and writing. This means that any operation using such a table will have to wait until the modification has completed." is no longer correct as of Mysql 5.6: http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-o... If you specify ALGORITHM=INPLACE,L…

It's not exactly a common operation either, so basing the choice of rdbms on it seems a bit arbitrary.

It happens a couple of times every month in our production databases. If it required locking tables we would have to schedule downtimes during the early mornings every time this happens would would have been a pain.

Re: Goodbye MongoDB, Hello PostgreSQL

#382

Post by bau5 »

hackles_raised wrote: post #316
bau5 wrote: post #131

Earlier quoted context omitted.

I think the author of the post has the right approach. They tried hard to build something that would work right, and since it didn't, they rewrote most of it. They had enough budget and willingness to rewrite it. The author learned from their mistakes, and probably won't be using MongoDB on a future project. The author of the comment I'm replying to seems to advocate a different approach where you choose tech and tec…

And what most people here have missed is that PGSQL is working for them 'because' they now know what their data looks like, MongoDB got them to that point. I can't speak for the OP but some of his examples speak to a lack of experience, which, had they started with PGSQL would likely have resulted in an intractable schema and we would now be reading a 'How MongoDB freed us' post.

The author said they used Mongoose to define the schema, and since they needed to define the schema, they'd rather just do it in the database. So I don't think they'd have been limited by using SQL from the start. Either they learned how to evolve a schema or they didn't have a schema that evolves well even when they were using MongoDB.

Re: Goodbye MongoDB, Hello PostgreSQL

#383

Post by collyw »

dasil003 wrote: post #304
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…

Where this line of reason goes completely off the rails is in thinking that schema-less databases are a hedge for future uncertainty. Yes they let you churn out code slightly faster, but then your data becomes a ball of mud just as quickly as your code base, except the former is much much worse because when you pivot you still need your data even if you decide to chuck out all the code that goes with it. In fact, a t…

The "schemaless" argument is nonsense as well. No schema enforcement at the database level means more checking at the application level (more work basically).

Re: Goodbye MongoDB, Hello PostgreSQL

#384

Post by collyw »

fennecfoxen wrote: post #112
chousuke wrote: post #64

Earlier quoted context omitted.

I may just be rehashing sibling arguments here, but to me that particular API looks very much what I think an SQL-wrapping library ought to be: a replacement for string concatenation and something that allows you to treat SQL queries as data. I don't know anything about Sequel, but that example still feels close enough to SQL. My experience with ORMs has been that I eventually end up regretting using one if I try to…

In my experience, it's great to start with ORMs, and they help you go really fast, but if the project is important then sooner or later you're going to find a case where you need to go around it and write custom SQL (probably for performance reasons). And that's fine, but it does mean that you should pick an ORM that plays nicely with that workflow (or otherwise design your ORM to be compatible with it). Back when I…

This sums up my experience as well. Great for all the tedious insert update type stuff, but more hassle than they are worth for complex queries.

Re: Goodbye MongoDB, Hello PostgreSQL

#385

Post by collyw »

CWuestefeld wrote: post #230
bbcbasic wrote: post #203

Earlier quoted context omitted.

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 requir…

Out of interest, are there more types of SQL injection, or are they all a case of inserting a semicolon, and running a second query afterwards to get the information you want?

Surely that is something that could be disabled at the driver level for security? Make sure it only send one statement at a time.

Correct me if I have missed something.

Re: Goodbye MongoDB, Hello PostgreSQL

#386

Post by collyw »

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…

You are unlikely to gain space savings, as it stores the keys as well as the values to all records.

Re: Goodbye MongoDB, Hello PostgreSQL

#387

Post by CWuestefeld »

collyw wrote: post #385
CWuestefeld wrote: post #230

Earlier quoted context omitted.

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 requir…

Out of interest, are there more types of SQL injection, or are they all a case of inserting a semicolon, and running a second query afterwards to get the information you want? Surely that is something that could be disabled at the driver level for security? Make sure it only send one statement at a time. Correct me if I have missed something.

That's what it boils down to, but limiting execution to a single command would only half fix the issue anyway. It would still be possible to put an early termination into a string and comment out everything else in the command, thereby completely changing what it meant.

Passing data properly through parameters isn't really very hard, there's no point in looking for incomplete work-arounds.

Re: Goodbye MongoDB, Hello PostgreSQL

#388

Post by merb »

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, it's called anorm. https://www.playframework.com/documentation/2.3.x/ScalaAnorm

You don’t need another DSL to access relational databases SQL is already the best DSL for accessing relational databases. We don’t need to invent something new. Moreover the SQL syntax and features can differ from one database vendor to another.

Post reply on HN