Rails on PostgreSQL

This is kind of a blast from the past - it's a talk by Bricolage lead developer David Wheeler at PostgreSQL Conference West 2007. It's mainly an introduction to Rails, but David's a real PostgreSQL guru (and had a Rails app that was bought by Twitter) and thus brings out some interesting points. Here are some highlights from an initial listen:

• Some basics on Rails competitors and philosophy. Someone in the audience mentions Grails.


• ActiveRecord validations and callbacks. A few minutes on ActionView and ActionController


• Migrations. This was prior to timestamped migrations, so he talks about numbered migrations. Discusses creating indexes, the lack of built support for views. Suggests using ActiveRecord::Base.connection.execute to just run raw SQL as necessary. Shows an example that uses SET DEFAULT CURRENT TIMESTAMP.


• Talks about the araddconstraint plugin. Probably foreigner (as mentioned on Ruby5) is the current leader for adding foreign key constraints, although I haven't used it. Looks good though.


• Demonstrates a class-level finder that uses PostgreSQL-specific SQL - specifically, the LOWER function. This talk predates named_scope, so, there ya go.


• At around 28:00 he notes problem with using Slony and migrations - e.g., how do you get to the SQL so you can send it off to your Slony instance? In his case they just stopped using Slony and went to a warm standby, probably with WAL shipping.


• Talks about loading large data sets using COPY. I've found that this is the right way to get lots of data in a PostgreSQL database as well. Don't waste time using models for stuff like that.


• Discusses skinny controllers and fat models.


• At 32:30 talks about associations and some ActiveRecord conventions. has_many :through was new stuff then, I think; he uses has_and_belongs_to_many in the example.


• At 40:00 he talks about created_at and updated_at and time zones. He suggests that you always stores times in UTC, which is pretty standard. I think some of the possible complexities here are now built in to Rails, but I'm not sure.


• At 44:30 he talks about reopening classes. This seems to be a new topic for the audience and he gets some pushback. Someone refers to it as "Ruby's GOTO." The class he reopens is the PostgreSQLAdapter; he plugs in his own version of quoted_date.


• Around 50:00 he asks if someone could please update the PostgreSQL driver. Jeff Davis is in the audience and responds to him about the updates he's doing. Today this has all been taken care of as I noted in a previous post on Ruby PostgreSQL drivers.

It's a nice presentation in front of a small group, with a nice feel to it. The audio quality is decent, although the slides are a little hard to read. Also, thanks to David for the nice email regarding this post. Enjoy!

Just noticed this job posting about an opening at Zerista. It asks for Rails and PostgreSQL experience, thus the mention here. The person to contact is Charlie Savage, who's done great work on improving the libxml-ruby gem. So you'd be working with smart folks.

I googled around and didn't find any tech interviews or videos or whatever... if someone from Zerista is reading this and wants to share information about how you're using Rails and PostgreSQL in interesting ways, please post a comment or contact me!

Occasionally I see a job description or interview where someone will mention that they're using PostgreSQL + Rails in production for some big application. I'd like to do more detailed writeups on these... but here are some that I've seen:

• Ryan Heneise writes in to say that Donor Tools runs on PostgreSQL.


• Doug Cole wrote in to confirm that the nifty real estate service estately.com runs on Rails + PostgreSQL. They use PostgreSQL full text search and PostGIS. Doug also noted that (as of 9/21/09) they're hiring developers, so, give them a holler if you're looking. You can also see a nifty PostGIS presentation they did at Seattle Tech Startups.


• Mark Tremblay adds screenlight.tv to the list.


• Nathen Harvey writes in that VisualCV is a Rails + PostgreSQL app.


• Eric Hodel says that he's using Rails + PostgreSQL on rubypan.org. He uses PostgreSQL FTE via the texticle gem.


• This job advertisement indicates that Yammer is using Rails with PostgreSQL. From that advertisement, they're also using PostgreSQL's full text search.


• Heroku provides each Rails app with a PostgreSQL database. I've googled all over for more information but haven't dug up any other interesting details, although I bet there's some neat stuff going on there.

That's all that come to mind at the moment. If anyone has more details on any of these, or more examples, please let me know - would be great to have some more detailed information!

There are a variety of database drivers available for getting a Rails app to talk to PostgreSQL. The Rails wiki has an excellent overview of the various drivers and their freshness. Here's a summary:

• gem install postgres-pr: This gets you the pure Ruby driver - e.g., no native code. Thus it is slow. It's useful if you don't have a native PostgreSQL client, though. I actually use this on RubyForge - I didn't know any better when I first wrote code using it and now I need to go back and remove it.


• gem install postgres: This gets you a native driver, but it's over 18 months old. Don't use it. If you are already using it, however, it looks like PostgreSQL guru Jeff Davis is doing bugfixes as necessary. So, you could do worse.


• gem install pg: This is the one! This gets you a native driver that's actively being maintained. It's a complete rewrite of the postgres driver and is the way to go.


• gem install activerecord-jdbcpostgresql-adapter: I haven't used this, but Simon Tokumine recommends using this gem if you're using JRuby with Rails and PostgreSQL. From the ActiveRecord-JDBC project site, this gem "[...] allows use of virtually any JDBC-compliant database with your JRuby on Rails application". Sounds like a winner.

Here's an edge case I ran into recently. I had a Rails 2.0.2 application in production, and ActiveRecord 2.0.2 does a require _library_or_gem 'postgres' in activerecord-2.0.2/lib/active_record/connection_adapters/postgresql_adapter.rb, so it won't work with the pg gem. If you're stuck on Rails 2.0.2 for some reason you'll need to go with either postgres or postgres-pr. You could try hacking the adapter to work with pg, but I went down that road, made three or four changes, kept getting errors, and decided it was just simpler to use one of the older gems. I'd be interested in hearing from anyone who's modified that version of the adapter to use the new driver... although the better path is probably just to upgrade the application to use a newer version of Rails.

Also, for folks upgrading to Snow Leopard, here's a helpful post on the pg gem and ARCHFLAGS from Jan.

To underscore my recommendation to use the pg gem, here's a quote from that project's README:

The 'pg' module is the newer module, that has been greatly improved, and is almost a complete rewrite. It is not backwards compatible. Use this module for newly written code. It should be more stable, less buggy, and has more features.

Indeed.

I poked around the code of pg and postgres to see the differences. It looks like a lot of cleaning up has happened. For one example, the PQfreemem function that libpq provides isn't being used in pg since, per line 27 of pg.c, "[it's] unnecessary: copied to ruby object, then freed. Ruby object's memory is freed when it is garbage collected." A quick grep around the postgres gem shows a bunch of occurrences of that function - it was even embedded in a preprocessor directive to make it easier to use.

So, to sum up, gem install pg. Thanks to Jeff for his work on this!