Rails, PostgreSQL, and database drivers 5

Posted by Tom Copeland Fri, 04 Sep 2009 14:19:00 GMT

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!

Comments

Leave a comment

  1. alex@alexkane.net about 8 hours later:

    Great website! Is the new pg driver much faster?

  2. Tom Copeland about 8 hours later:

    @alex, Thanks! I haven’t seen any benchmarks… but it’s definitely the way to go.

  3. Darren Boyd 13 days later:

    On OSX (Leopard), this is the process I used to get the native gem to build…

    sudo su -
export ARCHFLAGS='-arch i386'
export PATH=/opt/local/lib/postgresql83/bin:$PATH
gem install pg
<control + d to log out of super user>

    Note that the postgres bin path is specific to my installation.

  4. Wayne E. Seguin 25 days later:

    You do not need to install gems as root. If the reason is “because then the command shows up” then add the user’s gem directory to your path:

    PATH=$PATH:~/.gem/ruby/1.8/bin

    You can even set this exclusively:

    GEM_HOME=~/.gem/ruby/1.8

    For the purposes of pg gem, in order to cover both bases (both 32 & 64 bit compiled postgres installs) on OSX:

    ARCHFLAGS="-arch i386 -arch x86_64"

    Ensure that your path has psql in it as noted above. Change /opt/local/lib/postgresql83/bin to wherever you have pg_config located. For me this is:

    PATH=$PATH:/usr/local/postgresql/bin ; export PATH

    Then install the gem.

    gem install pg

    This non-root technique is especially useful when you have multiple ruby versions installed and are actively switching/using all of them ( I use rvm for this http://rvm.beginrescueend.com/ )

    w00t for Postgres!

  5. Tom Copeland 25 days later:

    @wayne, true… I guess that most of the time when I install a gem, though, I want it to be available to all user accounts on the box. But you’re right, another possibility is to just install it to your home directory.

Comments