If you’re like me, you installed PostgreSQL (or MySQL or whatever) locally for development purposes. Then you copied data into your instance, and along you went.

Eventually, you wrote a migration that touched nearly all rows in your database, and you waited, and waited, and waited some more (grab some coffee!) for the migration to finish.

In my case, I had a migration using pure Ruby which took 30 minutes. The migration ran along the lines of:

 1 def self.up
 2   add_column :segments, :pathname, :string
 3 
 4   # There are 200,000 segments in the DB
 5   Segment.find_each do |segment|
 6     segment.pathname = segment.full_pathname
 7   end
 8 
 9   change_column :segments, :pathname, :string, :null => false
10 end
11 
12 class Segment < ActiveRecord::Base
13   belongs_to :parent, :class_name => "Segment"
14 
15   def full_pathname
16     full_name = []
17     m = self
18     while m
19       full_name << m.name
20       m = m.parent
21     end
22 
23     full_name.reverse.join(" / ")
24   end
25 end

The intent being that we’d cache the segment’s pathname in each record (think a directory tree). Since we had 200,000 segments, and each of those would walk up the chain, it took around 30 minutes to run that migration. I rewrote the migration to use CTE, which PostgreSQL supports out-of-the-box. My migration now ran in 17 minutes or so. I warned my teammates about this migration, and let it be. Eventually, a colleague took my branch and ran the migration. When he reported the migration took 30 seconds for him, at first I thought his SSD was the major difference between our machines. It turns out it wasn’t…

1 # PostgreSQL installed through Homebrew
2 # /usr/local/var/postgres/postgresql.conf
3 # Your file location will vary
4 # ...
5 shared_buffers = 2400kB
6 # ...

Changing that single line from 2400kB to 96MB let me run the migration in 30 seconds as well… I was very impressed at my stupidity. Let it be a lesson learned!

Search

Your Host

A picture of me

I am François Beausoleil, a Ruby on Rails and Scala developer. During the day, I work on Seevibes, a platform to measure social interactions related to TV shows. At night, I am interested many things. Read my biography.

Top Tags

Books I read and recommend

Links

Projects I work on

Projects I worked on