PostgreSQL 9.2 Range Columns and Rails 4

Pushpin Map of USA I recently had an application where I needed to find the location for an incoming IP address. I used the MaxMind GeoLite City database to provide the raw data, and I wanted this data inside my own database so I could link to the data records as needed.

The GeoLite data has two tables, the Location data that has the City, Latitude and Longitude, and a Blocks table that has a start/end IP address and a location_id from the Location table.

My initial migration looked like this, with a start_ip and end_ip as a bigint (to avoid problems with signed/unsigned conversions):

create_table :blocks do |t|
  t.column :start_ip, 'bigint', :null => false
  t.column :end_ip, "bigint", :null => false
  t.column :location_id, :integer, :null => false
add_index :blocks, [ :start_ip, :end_ip ], :unique => true

This worked well enough to find a location based on IP address, but on my MacBook Pro with PostgreSQL, with 1M block records, each lookup was taking over 230ms.

Since I know that PostgreSQL 9.2 has added a Range column type, and Rails 4 is just around the corner with support for the built-in range types, I thought I’d give them a try for this problem.

Here’s my updated migration, using a int8range for my start/end IP, and a GIST index to make it speedy:

create_table :blocks do |t|
  t.column :ip_range, "int8range", :null => false
  t.column :location_id, :integer, :null => false
execute "CREATE INDEX blocks_gist_data ON blocks USING gist(ip_range);"

The same lookup, with the same data, now takes about 30ms to perform. This seems to be a useful speedup for the slightly more verbose code I had to write to access the ip_range with Rails 3.2 (this should be a little easier with the Rails 4 support)

The data size for the int8range data is a little bigger than the original form, PostgreSQL reports to me that the original data is 24 bytes per row, and the ip_range form is 40 bytes per row, but for my application, this trade-off is very worthwhile.

Take a look at your applications to see if you can use a Range Type to improve the performance of some of your important queries.