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:
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
Integer(`sysctl -n hw.ncpu`) rescue 1
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 =)
Saving 20 seconds a day for everyone in the team. I'm happy but just a bit more...
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
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
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
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.
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 =)