Rails on PostgreSQL

If you are planning on building a Rails application that uses spatial data in any way, then you owe it to yourself to take the time to investigate Postgis. Out of the box you’ll be able to perform an array of powerful functions on your spatial data: from bounding boxes and distance queries to polygon area calculations.

Installation

I won’t go into setup in too much detail, under most linux distributions postgis is a simple package install, it’s in macports if you’re a mac user, so either way installation shouldn’t be too hard. For integration with rails I recommend the GeoRuby gem and spatial_adapter. From the docs: “GeoRuby provides data types intended to hold data returned from PostGIS…”. spatial adapter is a rails plugin that extends ActiveRecord so that it understands geometric columns, transparently converting them to subclasses of the GeoRuby::Geometry class, supporting geometry columns in migrations, etc. spatial_adapter is hosted on github: http://github.com/fragility/spatial_adapter.

Data Storage

Postgis supports lots of datatypes. For the purpose of this blog post we’ll focus on just using points, all other datatypes are composed of points and most of the concepts are easily applied to the other datatypes. Points are made up of at least three points of data: x, y, and Spatial Reference System Identifier (SRID). SRIDs are used to describe the coordinate system used by the point. My gis background is poor so I’ll just suggest that if you are planning on working in lat/long values, SRID 4269 is probably the right choice for you (or maybe 4326, see the comments). The one downside of using lat/long data is it makes distance queries more difficult, 1 degree isn’t a fixed number of meters so you can’t directly ask the database for all rows within 100 meters of a given point, or at least not without having the database run a sequential scan of all rows. If people are interested in distance queries I’m happy to write about the subject.

Queries

The most basic query is the bounding box - which geometries lay within the given box? It is defined in postgis as && and is able to use spatial indexes directly so is very fast. Beyond that most queries take the form of a function and are well documented in the PostGIS documentation, but enough talk - let’s start an example.

Example

Let’s assume we have a table of restaurants that we want to display on a map. First let’s add a geometry column to the restaurants table, with spatial_adapter it is a simple migration:

add_column :restaurants, :the_geom, :point, :srid => 4269
add_index :restaurants, :the_geom, :spatial => true

If you open up a psql console and look at the table definition you will see that this adds the column the_geom with the type the_geom as well as adding three new table constraints: srid=4269, number of dimensions is 2 (postgis supports more), and the geometry type is a point. Handy! You can see we’ve also added an index, spatial_adapter adds the :spatial option to indexes to specify simplify the creation of spatial indexes.

Now to add points let’s open up a console and add geometry data to our restaurants:

r = Resaurant.first r.the_geom = Point.from_x_y(-122.39, 47.5123, 4269) r.save!

Of course in real life you aren’t going to just make up data, you’ll likely want to use a geocoding service like google’s geocoding api to determine the correct lat/long information. You’ll probably also want to store your chosen SRID in a constant somewhere, but now I’m nitpicking - let’s looks query our new data.

Restaurant.first(:conditions => ["the_geom && ?", Polygon.from_coordinates([[[x_min, y_min], [x_min, y_max], [x_max, y_max], [x_max, y_min], [x_min, y_min]]], 4269)])

Simple! Hope this helps show the basics of using PostGIS with rails. This post just barely scrapes the surface, luckily PostGIS and GeoRuby have excellent documentation, but if you have any more questions don’t hesitate to ask in the comments and I’ll try and help.

Doug Cole is the CTO of www.estately.com a real estate search website. Interested in working with Estately? Let us know!

Saw this advertisement for a Ruby/Rails/PostgreSQL job in Honolulu today. It's for eggup.com, which unfortunately is completely protected by a HTTP basic authentication challenge. First thing to do if you get that job - put up a nice "coming soon" page!

Thanks to Robby on Rails I heard about Alexander Tretyakov's interesting RailsOnPg plugin. This plugin makes it a bit easier to create PostgreSQL functions, triggers, views, and foreign keys by providing a nicer front end to calls to ActiveRecord::Base.connection.execute.

For example, here's a migration to add a foreign key to a Comment model that belongs to a User:

class AddForeignKeyFromCommentsToUsers < ActiveRecord::Migration 
  def self.up
    add_foreign_key :comments, :user_id, :users
  end
  def self.down
    remove_foreign_key :comments, :user_id, :users
  end
end

This results in the following SQL:

ALTER TABLE comments
                 ADD CONSTRAINT fk_comments_user_id
                 FOREIGN KEY (user_id)
                 REFERENCES users(id) 
                 ON UPDATE NO ACTION 
                 ON DELETE NO ACTION 

As you can see, this provides some sensible defaults and a consistent naming scheme so that you can reliably roll back a migration that created a foreign key.

I ran into some problems when creating a function; my migration failed with a PGError. Turns out that the plugin attempts to execute CREATE LANGUAGE plpgsql before it creates a function; in my case that language was already in place. I commented out line 16 of railsonpg/lib/functions.rb (the call to setlang) and everything worked fine. It looks like this need for a CREATE LANGUAGE IF NOT EXISTS (or something) has come up before, but I'm not sure what the status is. I'm using PostgreSQL 8.4.1 and that statement doesn't seem to be supported.

At any rate, this looks like a handy plugin that could remove a lot of raw SQL from your migrations. Good stuff!

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!

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.