Making PostgreSQL run faster

I use Rails and Postgresql at work. We have a dataset that's growing fast, large and frequently transformed.

This becomes a pain when there's a need to pull the data from remote and work on it locally. After a while, restoring on the local machine grew to these timings using pg_restore:

real  2m29.814s
user  0m14.783s
sys   0m2.426s

After trying different ways to tweak the settings and postgresql.conf, I discovered the easiest way to speed this up:

--jobs=number-of-jobs # Try number of CPU cores for a start

# Update: Nice one from my boss
--jobs=#{cpu_cores}

def cpu_cores
  Integer(`sysctl -n hw.ncpu`) rescue 1
end

Restoring our db was slow in both IO and processing, giving it a number between 2 - 4 allows concurrency between IO and processor intensive tasks (loading data, creating indices). Since I can't do any work while waiting for the restore to complete, might as well let the fans spin =)

real  2m4.378s
user  0m17.010s
sys 0m7.348s

Saving 20 seconds a day for everyone in the team. I'm happy but just a bit more...

More tweaks?

It finally dawned on me homebrew's default postgresql.conf should be conservative. So I did tweaks that mostly involve giving pg a ludicrous amount of resources, so it has less tendency to swap to disk during operations. The net effect should be a postres that loads slightly faster with warmer caches.

Comes with the following assumptions (WARNING):

• Your postgresql installation is just for development
• You are the sole user of the machine
• At least 4gb ram and letting pg hog ram

Fire up postgresql.conf, usually in /usr/local/var/postgres for homebrew users.

Add/append the following lines:

shared_buffers = 968MB    # Ram "dedicated" to pg. Should be < 1/4 system ram
temp_buffers = 128MB      # Only if you use a lot of temp tables
work_mem = 16MB           # Non-shared ram per operation. Beware this is non-shared and in binary steps
checkpoint_segments = 64  # Size of logfile segments

WAIT... on nix based system, increasing the shared buffer requires changing SHMMAX and SHMALL. For mac/typical linux, run for immediate effect(otherwise pg can't start)

sudo sysctl -w kern.sysv.shmmax=1073741824
sudo sysctl -w kern.sysv.shmall=1073741824

To commit it permanently, add these to lines to /etc/sysctl.conf

kern.sysv.shmmax=1073741824
kern.sysv.shmall=1073741824

Checkpoint Segments

Before postgresql writes, it writes a logfile of the intended operations before committing. If there's a crash etc, this log helps recovery. On a high transaction volume operation (e.g. pg_restore), pg will moan about checkpoint_segments being too close. On a development box, it's okay to set it to a high value (64 * 16mb/segment), so pg doesn't have to slow down to segment the logfiles.

Results?

real  1m1.690s
user  0m17.114s
sys   0m7.386s

If you're trying my tweaks, I hope it speeds up your postgresql daily use / postgresql mass operations.

Shout out if you have any tips for me or if the tweaks wasted your time =)