Rails on PostgreSQL

Here's a video from a few months ago - a talk given by Gleb Arshinov (CEO of Pluron) on June 9 2009 at the San Francisco PostgreSQL User's Group entitled "PostgreSQL as a Secret Weapon for High-Performance Ruby on Rails Applications". Here's the link to the mov file, and here are all the SFPUG videos. Note that the video file is 409 MB; I'm asking around to see if there's a version uploaded to YouTube or something.

Some notes on the presentation:

• Pluron makes Acunote, an Agile project management app. They have 4K customers (some at EngineYard, some internal). They use nginx+mongrel.
• Have used PostgreSQL from day one for their app.
• PostgreSQL has good SQL standard compliance, good documentation.
• Noted that PostgreSQL doesn't support bulk update (e.g., ON DUPLICATE KEY UPDATE). A good discussion here around the 21:00 about possible solutions and problems with race conditions and such. The slide has some text about replication but unfortunately he doesn't talk about that.
• Mentions Acunote's virtual attributes plugin
• Discusses their treatment of tree structures around 33:40.
• Talks about pagination at 37:00.
• Talks about their search language at 42:00. As far as I could tell they're not using Sphinx or any external search tool... not sure if they're using PostgreSQL full text search or not.
• Around 46:00 discusses using any(array()) vs in(). Interesting if you don't mind going database-specific in your Rails app.
• At 1:03:00 suggests using SQL DDL via Rails DDL DSL (e.g., CREATE TABLE vs create_table). Same for using SQL to do data changes in migrations. I've found that this is a good idea as well, especially when dealing with larger data sets.
• Recommends using foreign key constraints - but using them to protect data integrity, not to implement business logic.

A great presentation with some good technical content... highly recommended!

I recently came across a video presentation from the San Diego Ruby User's Group from Oct 2008. This video features Guyren Howe extolling the virtues of using Rails with PostgreSQL vs MySQL. He hits on a bunch of different topics, including:

• PostgreSQL has a bunch of features MySQL doesn't have - advanced rules/stored procedures/rules, a genetic query optimizer, excellent Oracle compatibility.
• PostgreSQL tends to outperform MySQL. He cites a variety of benchmarks including some that were run on 12-way boxes at Sun.
• PostgreSQL has a clear and liberal license, MySQL's license has all sorts of GPL mixed in it. If he were to give this presentation now I daresay he'd add the fact that Oracle now owns MySQL AB (via Sun) to the mix. Lively times there.

The presentation is a little out of date - he's comparing PostgreSQL 8.2 with MySQL 5 - but the main points of the presentation are right on target. Also, it would have been nice if there had been more Rails-specific content, but as he said, Rails tends to abstract away the database, so there's not much room for maneuvering there.

If you just want to see the slides, they're here. Enjoy!

I came across a Rails+PostgreSQL interview from last year - this is Bob Zurek of EnterpriseDB talking to Robby Russell about Planet Argon, Rails, PostgreSQL, and all that. There's a transcript there as well if you're in a hurry.

Generally speaking, Robby Russell is a big name in the Rails+PostgreSQL space. As Bob Zurek notes in the interview, if you Google up rails postgresql the first half dozen links or so point over to Robby's blog, Robby on Rails. You can see all his PostgreSQL related posts via the 'postgresql' tag; lots of good stuff there.

If you're running the Typo blog engine on PostgreSQL (like we're doing with this site) you'll hit this problem when you try to delete all spam comments that have been posted to your blog. This bug is fixed in Git with this commit:

+++ app/controllers/admin/feedback_controller.rb	2009-08-17 16:15:56.000000000 -0500
@@ -142,7 +142,7 @@
 
   def delete_all_spam
     if request.post?
-      Feedback.delete_all('state in ("presumed_spam", "spam")')
+      Feedback.delete_all(['state in (?,?)', "presumed_spam", "spam"])
       flash[:notice] = _("All spam have been deleted")
     end
   end

The problem is that PostgreSQL uses single quotes to delimit strings, and the solution is to use ActiveRecord's interpolation rather than manually building the IN clause. There's a potential performance gain as well since the call stack winds its way through the ActiveRecord code until it gets to the ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.quote_string method, which calls the native method PQescapeStringConn provided by the underlying PostgreSQL driver. Fast!

So, note to application developers - if you want your Rails app to be portable to different database engines, use the ActiveRecord helpers so that the individual database adapters can properly build the raw SQL statement.

More generally regarding Typo, I've run a couple of blogs on Typo+PostgreSQL and things are working well... it's a good combination.

Oh, and also, first post!