Who Else Wants To Use PostgresSQL Instead Of MySQL?
In a previous post I mentioned that we use PostgreSQL as our database server. Common sense might indicate that we use MySQL as that is what the core team is using and what would probably be considered to be the defacto standard for Rails development.So why are we using PostgreSQL?
Probably the biggest reason we use PostgresSQL is the simplest...I like it more than MySQL. This is one of the main reasons why we picked Rails as our development platform. When all things are equal, pick the one that is more enjoyable to use as you're going to be spending lots of time with it and it helps with motivation during those long hours of a startup.
Both databases are perfectly capable and I've used both on live sites but I've always found MySQL to be harder to use from the command line. This may be because I don't know how to use all the features but that's is part of the problem isn't it? Learning PostgreSQL on the command line was very easy and intuitive, MySQL isn't.
The other reason I like PostgreSQL over MySQL is that it's a completely free and open project. With MySQL it feels like it's a commercial project from a business that makes the code available (which it is). There is nothing wrong with this but I feel more comfortable knowing that PostgreSQL ownership is more distributed and that there is no large single point of failure in the project.
If you are picking based on technical issues you'll find a holy war that runs deep for many years about both projects. It seems to boil down that MySQL may be a bit faster but PostgreSQL is more reliable. With the latest revisions of each I'm not convinced that either is true. MySQL seems reliable enough for the millions of sites using it and PostgreSQL is fast enough for our site.
For ProductCritic, either database would have been fine so we picked the database that we find is more enjoyable to work with.
This post brought to you by:
Do you love computers? If you are looking for more information on Buying a brand New Computer, sign online. Computer technology changes constantly. If you want to stay on top of the Computer Operating Systems, do your research. If you are interested, you can check out the Computer based Buying Guide!
Labels: backend, development, rails, ruby
Posted by Gerry |
permalink
11 Comments:
sudo mysql;
create database x;
grant all on x.* to x identified by 'x';
\q
mysql -u x x -p;
mysql> create table x(i integer);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'x(i integer)' at line 1
mysql> create table xy(i integer);
Query OK, 0 rows affected (0.01 sec)
why?
mysql> insert into xy values(9999999999);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from xy;
+------------+
| i |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)
what warning? and why is it a warning? that's an error. mysql actively destroys your data instead of failing with an error if you do something wrogn as above.
bravo for using postgresql. but mentioning mysql in the same breath and suggesting that they're similarly capable is a bug. don't do that.
PostgreSQL is definitely the better of the two. Unambiguous licensing, stability, and it's just plain nice to work with.
Thanks for the examples confirming some of my thoughts about MySQL. My comment about them being similiar was meant to be for this particular site which doesn't (appear) to have any edge cases where these types of problems will be an issue.
I've seen other similar examples of edge cases which has always given me an uneasy feeling towards MySQL but it's hard to really knock it given the size and number of sites it is running it's obviously capable.
That said, these types of examples combined with (in my opinion) a more intuitive usage and as jmc pointed out a more unambiguous license will make it hard for me to switch from PostgreSQL.
You can change that Warning into an Error that would make that statement fail (as you expected) by changing the server SQL_Mode.
My point is that most of the holy wars out there are based on people who
a) know one product very well, and love it
b) dislike or even hate the alternatives, but don't know enough about them as to properly criticize them.
Just as unreasonable as real world holy wars, if you ask me :)
Regards,
Another huge benefit of Postgresql is Write Ahead Logging (WAL) support. With one simple line in the Postgresql configuration file, you can specify an external program that WAL logs are sent to; backing up a database and recreating a database is as simple as pointing Postgresql at the WAL logs, a big deal for grid and clustered environments.
One word: Oracle.
Hi there,
I read your other post in regards of ASP.NET vs RoR. You said several weakness/limitation of ASP.NET which I think are not true (or necessary).
Your Claim: Specifically one thing ASP.NET does not have good support for is nice URLs. I'm sure there are ways to do it but I never found a good solution to removing those ugly .aspx page extensions.
Reply: I think only blogger, Rails fans, and some technical people that always pick on these things. I'm sure the user doesn't care of your URL. I'm sure those Web 2.0 bookmarking website out there doesn't care either. I'm sure neither Firefox, IE6/7, Opera and Safari cares of cool URL. Sure, you have a valid point, but just not strong enough to dump ASP.NET.
Your Claim: Also, the page/form centric model that ASP.NET uses is a bad design for medium to large sites I find. No matter how hard I try I've always found my ASP.NET based sites always becoming a bit of a mess if they take off in size.
Reply: I always wonder why I haven't seen any complicated websites based on Rails. Complicated in term of layout, functionalities, interactivity, forms, etc. I'm sure most people would say "less is more", echo-ing 37signals.com.
I'm afraid in the long run, the "less is more" can be easily duplicated and even worse, your next startup can be easily beaten by a group of 2-3 college students.
One reason why Rails people always echo "less is more" is because usually these people are under budget, resource, time, and probably skill. Plus not to mention Ruby is one of the slowest language out there.
You can't deny that Rails is for urban-developer kind of type. Niche. And also for Java renegade because simply they can't take the learning curve of Java (well, arguably Java waste time as well, but let's keep that for another comparison).
Put it this way, on one side you have Java/J2EE with XML, tons of framework, plugins, and tons of design patterns. On the other corner, you have Ruby and just Rails (perhaps with Capistrano). I'm sure web-designer would love to know more about Rails in any given day. Especially with DHH and PragProg books that makes Rails development "look" very easy. Deceitful is a better word.
Yes, web-designer, you heard me right. Just like the days of PHP. Simply because web-designer can't code CGI in Perl :). That's why people despise PHP. Most of the PHP work-of-art came from web-designers. The people who can't code correctly.
Now, let me challenge you. Can you use Rails for enterprise level software where SQL can be very complex?
Please don't lecture me with how cool ActiveRecord and Migration are. Show me the most complex SQL you can construct from Rails :).
PS: I'm currently using Rails too but not to the point where I think Rails is the best out there. For a simple web-app, yep, for a complex one? I still choose J2EE/ASP.NET/PHP in any given day.
Why? quite simple, Rails limits what you can do too much. Plus Rails is too opinionated. Basically you have to follow DHH's brain.
Another thing is the lack of libraries and the half-baked plugins that got so much attention.
Case and point:
Streamlined. I just completely throw Streamlined out of my window because the documentation sucks and the product just simply not up to the level of Django's admin interface. And if you ever said that Django's admin interface is a part of Django's framework while Streamlined isn't, that means Rails is not flexible.
Capistrano. Out dated documentation.
That shows how mature Rails community is and I don't expect them to change any time soon.
I've seen Rails community prefer to do consulting, screencasts (including selling them like those peepcode people doing), training (like pragmatic studio).
I hardly see any fast progress like the one in Python community.
Thank you.
nicely put. I've used PostgreSQL for years - historically it used to be technically more reliable, eg. supporting transactions. Having used it for years, why would i want to switch to MySQL (or Firebird), even if MySQL is just as capable these days and has better GUI tools - don't need them. Having said that, now that Java and Netbeans comes bundled with JavaDB/Derby, i'll find myself starting a new project with that.
Our company got acquired about 2 years ago by a US based company. While we used a MySQL environment they were all about MSSSQL - interestingly mostly because of licence concerns (is open source legal? blah.. blah..).
The option to just by a commercial MySQL database licence saved us from a migration to MSSQL.
So sometimes "political" reasons are a dimension to consider...
This is a response to the second Anonymous comment (the long one).
First, thanks for the long and thoughtful comments. You obviously put some effort and time into them.
Just wanted to discuss two of your points:
1) On our claim that ASP.NET does not have good support for nice URL's (which you've agreed is difficult to get rid of), you stated "I'm sure the user doesn't care of your URL. I'm sure those Web 2.0 bookmarking website out there doesn't care either. I'm sure neither Firefox, IE6/7, Opera and Safari cares of cool URL."
Our Reponse: That's actually not the point. You're absolutely right that neither the user nor bookmarking sites, not browsers care about nice URL's. The issue is that Google (and Yahoo!) care. Not having nice URL's has a huge affect on how Google indexes your site and therefore, what position in the search results your pages appear. Still, Search Engine Journal has a good post about doing the remapping for ASP.NET sites so maybe it's not as big of a deal now. Still, to get nice URLs from ASP.NET, it still takes extra steps (see the post I referred to).
2) On your challenge about "Can you use Rails for enterprise level software where SQL can be very complex?", I think you may have inferred something from our Why We Use Ruby on Rails post that does not exist. We've never claimed that Rails is good for enterprise level software with complex SQL. All we have ever stated was that Ruby on Rails works great for ProductCritic (which isn't enterprise level software nor is the SQL particularly comples). Like we stated in that post, there are lots of web development frameworks we could have used to develop ProductCritic on but we chose Ruby on Rails because it provided what we needed and because it's enjoyable to work with.
Again, we appreciate all comments. We put up the ProductCritic Blog because thought it might be interesting for some people to get an understanding of why we've made some of the decisions that we have. We hope this might be of help to others who are starting their own sites and it's great to see the discussion.
When it comes to a commercial license, that's easy: buy a commercial version from EnterpriseDB or Command Prompt.
Also, don't blindly assume that MySQL is faster; in many cases it isn't. And many folks are definitely finding that it doesn't scale as well as PostgreSQL; tweakers.net recently did a bunch of testing on this, for example.
Post a Comment
<< Home