PostgreSQL and Ruby on Rails

The current state state of PostgreSQL on Rails (early 2009) is a bit of a muddy mess. There are three different gems you can use to connect ActiveRecord to PostgreSQL and no guidance I can find about which of the two native adapters to use. I’m going to try and clear this up.

postgres-pr is a pure Ruby adapter. It doesn’t require native libraries and should work in most situations. However, because it isn’t native, it’s the low performer and likely won’t offer access to all of PostgreSQL’s features.

postgres is the old adapter. It appears to be maintained now by Jeff Davis, who forked it from Dave Lee. If you are compiling against PostgreSQL 8.3, you must use Jeff’s version (currently 0.7.9.2008.01.28), which includes build fixes for 8.3.

pg is the new adapter, also maintained by Jeff Davis. He says this one has a better design than postgres and offers more features. It does not work prior to Rails 2.1, however. Rails 2.1 and later will attempt to load this driver first and fall back to postgres. If you use any plug-ins that monkeypatch the database driver, you might have problems with pg (my own sql_logging, for one, is broken — I’ll fix this shortly).

So:

• If you can build native extensions, are on Rails 2.1 or later, use pg.
• If you can build native extensions, but are on Rails 2.0 or earlier, use postgres.
• If you cannot build native extensions, use postgres-pr.

Building native extensions on OS X can be tricky, though. Here’s what I use on an Intel Mac, using the Ruby and Rubygems that ship with OS X and PostgreSQL 8.3 from MacPorts. (Unlike Robby’s guide, I do not advocate moving the system’s Ruby out of the way. You’re likely to break other stuff if you do so.)

sudo env ARCHFLAGS='-arch i386' gem install pg --remote -- --with-pgsql-include=/opt/local/include/postgresql83 --with-pgsql-lib=/opt/local/lib/postgresql83


If you use a different version of PostgreSQL than 8.3, make the appropriate substitution. If you’re still on PowerPC, change the ARCHFLAGS to -arch ppc. If you want to use postgres instead of pg:

sudo env ARCHFLAGS='-arch i386' gem install postgres --remote -- --with-pgsql-include=/opt/local/include/postgresql83 --with-pgsql-lib=/opt/local/lib/postgresql83