The article skimmed over MVCC like it was no big deal. But that is HUGE. On MySQL or MSSQL, if you try "update x where y" it must LOCK the entire table while it does the calculation. Nobody else can read or write to the table until it's done, so it looks like your entire database is down.
Also, the Postgres Optimizer is light-years ahead of MySQL. The MySQL docs even say it cannot use indexes in a sub query. And Postgres can build partial indexes and indexes on a function. For example, you can index only the open orders, so you can pull up open orders quickly, but don't have the overhead of indexing a billion closed orders.
nirvdrum
· 3 months ago
Worse, in my opinion, is that adding an index causes a table lock in MySQL.
Saturn Flyer
· 4 months ago
Brilliant. Another big plus in the Rails world is being able to setup different but related apps in separate schemas and set the user's search path. As far as I know, MySQL has no way to do this. People seem to be searching for a way to pluginize apps to drop them into others, but multiple schemas allows you to keep separate apps separate and only connect them where you need: the records.
There's some overlap with ActiveRecord models, but it's a manageable problem and in my opinion, better than combining sources or using ActiveResource. I have a client running a forum, cms, and custom app with shared tables and the concerns for the applications are kept separate.
nirvdrum
· 4 months ago
One thing to watch out for is that in Rails < 2.3.3, composite indices are broken in PostgreSQL. For more info, you can read my account of it at http://nirvdrum.com/2009/04/26/composite-index-.... The short of it is you either need to be using single column indices or a more recent version of Rails.
Nikolay Kolev
· 4 months ago
I initially hated when Sun acquired MySQL as they were in supporting PostgreSQL before, but now knowing what's happened a couple of years later, I think it was actually good for PostgreSQL. It is the (much) better database and is only lacking the eye candy of MySQL's GUI tools.
Andrew France
· 4 months ago
I use PostgreSQL for all my Rails projects and recommend the excellent RedHillOnRails Core plugin to add support for defining foreign keys and views in migrations. Unfortunately the original RedHill site is now defunct but you can get the plugin from http://agilewebdevelopment.com/plugins/redhillo....
Harold Giménez
· 4 months ago
+1. I use this plugin on all my Postgres projects.
David Turnbull
· 4 months ago
When contemplating the choice between MySQL and PostgreSQL recently for a new Rails project, the major feature lacking in MySQL is DDL transactions. In fact, looking at the docs on what statements (and how many) will autocommit an existing transaction is pretty shocking.
elitwin
· 4 months ago
Agreed - the fact that the system tables are all stored in MyISAM (non-transactional) is a big problem in my opinion.
Robspierre
· 4 months ago
One "challenge" I currently have with MySQL is the ability to use an index when multiple range operations are performed, eg. [1]:
SELECT * FROM parts WHERE ordered_at > '2009-01-01 00:00:00' AND delivered_at < '2009-01-15 10:00:00'
Given this, and an index on (ordered_at, delivered_at) MySQL will only be able to use the first column of the index. Any tips on whether PostgreSQL handles this better?
For me, that is the biggest knock against MySQL - the atrocious query optimizer. The fact that a query can only use one index is ridiculous.
Harold Giménez
· 4 months ago
I am not sure what the query plan would look like in MySQL, but in PostgreSQL it would definitely use both columns if you define an index on the ordered_at and delivered_at columns. Here's a quick example: http://gist.github.com/160963
Harold Giménez
· 4 months ago
Here's some info on how Postgres combines indexes and some considerations when deciding whether to use two separate indexes or one index with both columns: http://www.postgresql.org/docs/8.4/interactive/...
JT
· 4 months ago
In this case use BETWEEN and two separate indexes.
I personally don't think one is better than the other, they both have their strengths and weaknesses, what I've found is that if you're really familiar with one over the other you're probably going to build a better, more efficient system due to the knowledge in the other RDBMS not being as up to par.
Pistos
· 4 months ago
I'm certainly a PostgreSQL user (and MySQL avoider), but I find it curious that you mention Rails at all in this post. I thought ActiveRecord does most (everything?) on its own, and uses only the bare bones of the database simply to persist. Meaning to say, many of the advantages you cite, some of which are absent in MySQL, won't even be used by ActiveRecord. For example, views, triggers, rules, and plpgsql functions. As such, a Rails user switching DBs from MySQL to PostgreSQL won't see much difference.
A _Ruby_ user on the other hand...
Christoph Olszowka
· 4 months ago
You can actually use views with ActiveRecord, they'll act just like regular tables. Table inheritance, triggers, rules and functions are things that work on the database level and (i.e.) can help you to preserve referential integrity - things like uniqe indices and friends can be added via your migrations and enforce your data's correctness (as opposed to validates_uniqueness).
Check out Enterprise Rails (2008, O'Reilly) by Dan Chak to learn more about leveraging "advanced" database features. Dan is obviously very opinionated, and while it's arguable everything he writes should be accepted without further questioning (i.e. he refuses to use Rails migrations, which imho are a great tool), it's still an intriguing read, making you ask yourself if everything Rails does is really the best approach to handle things.
Harold Giménez
· 4 months ago
@Pistos,
you definitely have a good point regarding ActiveRecord: it will abstract out and move most functionality to the ORM layer as opposed to living at the database layer, which is A Good Thing (TM). In most Rails app this will suffice, especially on the small to medium sized ones.
I have however used some of Postgres' features in a Rails project. For instance, I've used views and rules in a Rails project, and ActiveRecord happily thought it was just a table. Some may argue that triggers are a safer way to do things like database auditing, especially when the Rails app is not the only system that is inserting/updating the database. As an aside, we implemented a very data heavy system that required ETL'ing data from numerous sources and that's where something like Pl/Ruby or Pl/PGSql came in handy.
In any case, this is aside from my main point: it's all about PostgreSQL's license and community. The slew of features and awesomeness of the DBMS are just there to back up my argument.
kedare
· 4 months ago
Postgresql is great, but is it really useful when used with Rails ? Because rails don't use any "great features" of postgresql likes RULES, TRIGGERS, VIEWS, FK, PostGIS or FullText Search (easy implementable I think if it's likes in Django), and I don't think we can use RULES, FK or other "exotics things" with rails without brake it... Mysql is also great for some features that boot performances likes the Query Cache (but useless if you have a good caching strategy on the rails app), and easy master/slave replication... I prefer Postgresql (I use it with Django), but i'm not sure if it work great with Rails ? (I plan to migrate from Django to Rails)
Harold Giménez
· 4 months ago
All good points.
Rails will not "use" any of these features, but you can certainly implement them and have Rails' ActiveRecord just keep running queries/inserts/updates as any other ORM would. I have certainly used views and rules in a Rails app. I can imagine using triggers for some stuff (maybe auditing?) without Rails even knowing about it. I use FKs on all my Rails app, helped by the redhillonrails plugin mentioned on other comments above. I've never done PostGIS, but there are two plugins for using tsearch that I want to explore shortly: acts_as_tsearch and tsearchable - google's your friend.
The one plus for MySQL has got to be replication. Slony - probably the most popular package - works well, but it is definitely not easy to set up and does it's work via SQL triggers as opposed to binary log shipping. As mentioned on the post, this is an area that is being worked on, and will be part of the PostgreSQL core soon.
I can tell you, PostgreSQL DOES work great with Rails.
Thanks for stopping by, and welcome to Rails? Hope you enjoy the ride.
kedare
· 4 months ago
The big problem with Slony is the required network bandwidth : A simple master-slave = 8x more data travel on the network (2x on MySQL) Replication with 4 servers = 32x more data (4x on MySQL) Replication with 12 servers = 288x more data (12x on MySQL) It's not useable on huge database cluster.... Source: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
And you say : "it's work via SQL triggers", All Foreign Key on Postgresql are managed with hidden Triggers (I discovered that many days ago...) The triggers are on pg_catalog (by example: pg_catalog.RI_FKey_cascade_del() is called when you delete an "ON DELETE CASCADE" foreign key entry) (I didn't found any article on that, but i don't know what can be the other usage of this triggers, the description said: "referential integrity ON DELETE CASCADE", so I think it's that)
Harold Giménez
· 4 months ago
"It's not useable on huge database cluster...." - I'm sorry, but there are numerous big deployments that have managed and perform beautifully. Where this site is hosted (Heroku) comes to mind: http://heroku.com/how/architecture. You do need a skilled admin/DBA.
Slony's bandwidth overhead is a direct consequence of the fact that it's not shipping binary logs, but SQL statements over to the slaves. Slony is not the only replication solution for PostgreSQL...
Cory
· 1 month ago
This is exactly the well reasoned information I have been looking for while considering a switch to PG. Thank you!
All Comments
Also, the Postgres Optimizer is light-years ahead of MySQL. The MySQL docs even say it cannot use indexes in a sub query. And Postgres can build partial indexes and indexes on a function. For example, you can index only the open orders, so you can pull up open orders quickly, but don't have the overhead of indexing a billion closed orders.
There's some overlap with ActiveRecord models, but it's a manageable problem and in my opinion, better than combining sources or using ActiveResource. I have a client running a forum, cms, and custom app with shared tables and the concerns for the applications are kept separate.
SELECT * FROM parts WHERE ordered_at > '2009-01-01 00:00:00' AND delivered_at < '2009-01-15 10:00:00'
Given this, and an index on (ordered_at, delivered_at) MySQL will only be able to use the first column of the index. Any tips on whether PostgreSQL handles this better?
[1]: http://dev.mysql.com/doc/refman/5.0/en/range-ac...
PostgreSQL it would definitely use both columns if you define an index on the
ordered_at and delivered_at columns. Here's a quick example:
http://gist.github.com/160963
when deciding whether to use two separate indexes or one index with both
columns:
http://www.postgresql.org/docs/8.4/interactive/...
I personally don't think one is better than the other, they both have their strengths and weaknesses, what I've found is that if you're really familiar with one over the other you're probably going to build a better, more efficient system due to the knowledge in the other RDBMS not being as up to par.
A _Ruby_ user on the other hand...
Check out Enterprise Rails (2008, O'Reilly) by Dan Chak to learn more about leveraging "advanced" database features. Dan is obviously very opinionated, and while it's arguable everything he writes should be accepted without further questioning (i.e. he refuses to use Rails migrations, which imho are a great tool), it's still an intriguing read, making you ask yourself if everything Rails does is really the best approach to handle things.
you definitely have a good point regarding ActiveRecord: it will abstract out and move most functionality to the ORM layer as opposed to living at the database layer, which is A Good Thing (TM). In most Rails app this will suffice, especially on the small to medium sized ones.
I have however used some of Postgres' features in a Rails project. For instance, I've used views and rules in a Rails project, and ActiveRecord happily thought it was just a table. Some may argue that triggers are a safer way to do things like database auditing, especially when the Rails app is not the only system that is inserting/updating the database. As an aside, we implemented a very data heavy system that required ETL'ing data from numerous sources and that's where something like Pl/Ruby or Pl/PGSql came in handy.
In any case, this is aside from my main point: it's all about PostgreSQL's license and community. The slew of features and awesomeness of the DBMS are just there to back up my argument.
Because rails don't use any "great features" of postgresql likes RULES, TRIGGERS, VIEWS, FK, PostGIS or FullText Search (easy implementable I think if it's likes in Django), and I don't think we can use RULES, FK or other "exotics things" with rails without brake it...
Mysql is also great for some features that boot performances likes the Query Cache (but useless if you have a good caching strategy on the rails app), and easy master/slave replication...
I prefer Postgresql (I use it with Django), but i'm not sure if it work great with Rails ? (I plan to migrate from Django to Rails)
Rails will not "use" any of these features, but you can certainly implement them and have Rails' ActiveRecord just keep running queries/inserts/updates as any other ORM would. I have certainly used views and rules in a Rails app. I can imagine using triggers for some stuff (maybe auditing?) without Rails even knowing about it. I use FKs on all my Rails app, helped by the redhillonrails plugin mentioned on other comments above. I've never done PostGIS, but there are two plugins for using tsearch that I want to explore shortly: acts_as_tsearch and tsearchable - google's your friend.
The one plus for MySQL has got to be replication. Slony - probably the most popular package - works well, but it is definitely not easy to set up and does it's work via SQL triggers as opposed to binary log shipping. As mentioned on the post, this is an area that is being worked on, and will be part of the PostgreSQL core soon.
I can tell you, PostgreSQL DOES work great with Rails.
Thanks for stopping by, and welcome to Rails? Hope you enjoy the ride.
A simple master-slave = 8x more data travel on the network (2x on MySQL)
Replication with 4 servers = 32x more data (4x on MySQL)
Replication with 12 servers = 288x more data (12x on MySQL)
It's not useable on huge database cluster....
Source: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
And you say : "it's work via SQL triggers", All Foreign Key on Postgresql are managed with hidden Triggers (I discovered that many days ago...) The triggers are on pg_catalog (by example: pg_catalog.RI_FKey_cascade_del() is called when you delete an "ON DELETE CASCADE" foreign key entry)
(I didn't found any article on that, but i don't know what can be the other usage of this triggers, the description said: "referential integrity ON DELETE CASCADE", so I think it's that)
Slony's bandwidth overhead is a direct consequence of the fact that it's not shipping binary logs, but SQL statements over to the slaves. Slony is not the only replication solution for PostgreSQL...