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!