1 - Indexing Support
MySQL cannot utilize more than one index per query. I believe this is worth repeating: MySQL CANNOT UTILIZE MORE THAN ONE INDEX PER QUERY. Wait till your tables get large enough and this will surely hit you. OTOH PostgreSQL can use multiple indices per query which come real handy.
2 - Full Text Indexing Support
MySQL can do full text indexing on MyISAM tables only, those working with InnoDB tables are out if luck. PostgreSQL has very advanced full text indexing capabilities wich enable you to control the tiniest details down to the stemming strategy.
3 - Asynchronous Interface
MySQL drivers are very unfriendly to the Ruby interpreter. Once a command is issued they take over until they come back with results. PostgreSQL sports a completely asynchronous interface where you can send queries to the database and then tend to other matters while the query is being processed by the server. The good news is that an Async ActiveRecord adapter for MySQL is being developed right now, as part of the rapidly growing NeverBlock library.
4 - Ruby Threading Aware
PostgreSQL dirvers enable the Ruby thread scheduler while IO requests are being processed (a nice side effect of the async interface). Which makes it much better suited for multithreaded Rails apps.
5 - Multistatements Per Query
Both MySQL and PostgreSQL support sending multiple statements separated by semi colons at once. But the returning result will be that of the last statement in the group. Now did you know that by using the async interface you can send multiple queries at once and then get back the results, one by one? One of the coolest features of the coming ActiveRecord (and Sequel btw) adapter is it's support for queuing queries to be consumed by a pool of connections. A trick we are contemplating working on is to group consequent selects together and send them in a single request to PostgreSQL and then later extract the results associated with each one of them. This is still very theoretical but should be verified soon.
Now that the 0b101 reasons are told I rest my case.
MySQL cannot utilize more than one index per query. I believe this is worth repeating: MySQL CANNOT UTILIZE MORE THAN ONE INDEX PER QUERY. Wait till your tables get large enough and this will surely hit you. OTOH PostgreSQL can use multiple indices per query which come real handy.
2 - Full Text Indexing Support
MySQL can do full text indexing on MyISAM tables only, those working with InnoDB tables are out if luck. PostgreSQL has very advanced full text indexing capabilities wich enable you to control the tiniest details down to the stemming strategy.
3 - Asynchronous Interface
MySQL drivers are very unfriendly to the Ruby interpreter. Once a command is issued they take over until they come back with results. PostgreSQL sports a completely asynchronous interface where you can send queries to the database and then tend to other matters while the query is being processed by the server. The good news is that an Async ActiveRecord adapter for MySQL is being developed right now, as part of the rapidly growing NeverBlock library.
4 - Ruby Threading Aware
PostgreSQL dirvers enable the Ruby thread scheduler while IO requests are being processed (a nice side effect of the async interface). Which makes it much better suited for multithreaded Rails apps.
5 - Multistatements Per Query
Both MySQL and PostgreSQL support sending multiple statements separated by semi colons at once. But the returning result will be that of the last statement in the group. Now did you know that by using the async interface you can send multiple queries at once and then get back the results, one by one? One of the coolest features of the coming ActiveRecord (and Sequel btw) adapter is it's support for queuing queries to be consumed by a pool of connections. A trick we are contemplating working on is to group consequent selects together and send them in a single request to PostgreSQL and then later extract the results associated with each one of them. This is still very theoretical but should be verified soon.
Now that the 0b101 reasons are told I rest my case.
Regarding this--I'm pretty sure that it's a complicated question with no easy answer.
I look forward to hearing of mysql for NeverBlock [asymy? [2]].
I think you may be right--postgres users will benefit more from the upcoming multi threaded rails [3], and, hopefully, with Asynchronous MySql [neverblock or what not] then MySql users can similarly benefit.
Thanks for your work on this.
Take care.
-=R
[2] http://github.com/tqbf/asymy/tree/master
[3] http://weblog.rubyonrails.org/2008/8/16/josh-peek-officially-joins-the-rails-core
Except active record is designed around MySQL in ways which might be hard to unpick: "Active Record's find family of functions. It's a horrible hack, for instance, they support the :group clause, which has semantics ("return a collection
of groups of things") incompatible with find's base semantics ("return a collection of things"). Rails answer? It implicitly relies on MySQL's retarded interpretation of SQL and the fact that given a table with two columns, id and colour, it will silently interpret "SELECT * FROM table GROUP BY colour" as "SELECT FIRST(id), colour FROM table GROUP BY colour". End result? A valid combination of clauses in AR will generate incorrect SQL Postgres will (correctly) choke on."
( from http://groups.google.com/group/comp.lang.lisp/msg/f2c33661b80ba302 )
BTW, for 2), you may want to check out the Sphinx storage engine - http://www.sphinxsearch.com/ - it might help you out.
How are you supposed to be able to program if you can't even count? Those were not 101 reasons, there were five of them.
5 != 101
Anonymous:
Read the last line of his post -- he was making a joke. It is 101, just not in base-10.
101 in binary == 5.
You really need to work on the wording of: "MySQL CANNOT UTILIZE MORE THAN ONE INDEX PER QUERY." MySQL certainly can use one index per JOIN, plus one for the WHERE clause. With your delivery of that and its prominence as the #1 reason, I almost skipped your post entirely. I try not to waste time on stupid assholes.
@roger, sure once Asymy matures it will be supported, I actually have a working prototype.
@anonymous_with_no_time_to_waste, sorry I pissed you off, not intended, now you really need to work on the words you use generally
well written !
Wow. That Anonymous guy who's time must be more precious than gold its self, and who takes great offense when his precious time is "wasted", sure is a the rear end of a donkey. My mother always told me "If you can't say something nice, don't say anything at all." I am glad you wrote this post as I have been torn for reasons to give Postgres a try with my Rails projects. I have been so comfortable with MySQL for years now, but I do need to break out of my comfort zone every once in a while.
We've been using Postgresql for many years now. From big 400 GB databases to social networks with millions of unique users per month.
I never understood why people were so consumed with using MySQL when there are such an embarrassment of riches with Postgresql.
MySQL 5 added support for index merging, allowing it to use multiple indexes per table.
well you got hacker-news'd and i left a commennt on tsearch (#2) there
oops forgot the all-important URL. Very sloppy
http://news.ycombinator.com/item?id=281676
Douchebag counting scheme not appreciated.
PostgreSQL has one disadvantage compared to MySQL: it doesn't support case-insensitive varchar columns. This means I'll have to manually keep a lower-case index of usernames. If I use functions such as LOWER() then it will skip the index.
Hongli -
If you declare your index using a function, then postgres will pick it up. That is, build an index on lower(username).
cheers
actually postgresql has a pretty extensable type system, so if you need a specific type, you can generally make your own. case in point, the citext project implements a case insensitive text type. the original code is available at http://pgfoundry.org/projects/citext/, though you can get a more enhanced/updated version from https://svn.kineticode.com/citext/trunk/ (that code is the basis for what will be an include module shipped with 8.4)
I think this could be titled "101 reasons why it is better for large rails projects' than for rails in general. I prefer sqlite for small projects, because of the setup simplicity. And others may end up preferring oracle for the very high end. So I guess postgres fits well for 'large' rails apps.
-=R
I totally agree, I myself use MySQL on many projects some of which are of considerable size.
I just needed to highlight the short comings given the hype that is surrounding Rails being threadsafe. People need to know that mysql (and sqlite for that matter) wont help them in that area unless they're using jRuby.
Good to know!
Take a close look in PostgreSQL within Ruby is one of next tasks for sure!
I'm subscribing to your feed, hoping that I will read more news (including Asynchronous MySql news) soon! =)
Rails may not be multithreaded yet, but Merb currently is. Not that Ruby (or Rubinius) are especially great at multithreading, but JRuby is 'ready' enough now that these things start to matter.
A lot of the quirks of PostgreSQL support in Rails are due to DHH preferring MySQL, maybe because he sees the DB as a dumb datastore ( http://www.robbyonrails.com/articles/2005/08/18/active-record-i-3-u-but-i-still-trust-my-database-server-a-tiny-bit-more ) ?
Switch to a decent ORM layer (Datamapper or Sequel) and you no longer have the bias.
Looks like Mohammed has attacked numbers 3 and 4 himself with his release of the mysqlplus driver for Ruby.
Nice work!
-=R
Good Job! :)
After finding out that MySQL apparently doesn't support nested transactions (!!!!) we decided to switch to PostgreSQL. It seems to be a bit slower but hopefully the other benefits will outweigh this performance penalty in the future.
Also, PostgreSQL has many little annoyances compared to MySQL. For example, its primary key sequences can get out of sync after restoring a database from a dump (including from the Rails Rake tasks) and I'll have to manually fix them.
@Hongli Lai in Postgres there are a couple of ways you could manage case-insensitive text:
(1) Convert all parameters to be inserted or matched against this field to lower case. If you truly don't care about case this may be the best route.
(2) Index on lower cased versions. If you want to preserve case in storage but look up without case you could use Postgres' ability to create indexes on arbitrary expressions:
CREATE INDEX my_field_case_insensitive_index ON my_table(LOWER(my_field));
Then whenever you do a comparison like value = LOWER(my_field) it will see the use of the indexed expression and work out that it can use the index.
If you want to enforce uniqueness disregarding case you could make that a unique index (CREATE UNIQUE INDEX).
I think the number one beef I have with MySQL is the way it handles schema changes. That is, if you add a column, it blocks access to the table while it copies everything across to a new table with the extra column, then unlocks it.
This can really blow chunks if you've got several million rows of data.
Then there's the priority inversion traps that row level locking can get you into. I'm not sure my brain is up to describing that, but let's just say that long running transactions in MySQL cannot be trusted.