Occasionally when developing you will encounter a database query that is causing soul crushing load times.  A first attempt at optimization might include looking at the query being used and trying to improve upon it.  Most of the time, if database bottleneck issues have not previously been a problem, this will solve the issue.  If, after fiddling with the queries, the issue persists, a next step might be looking into changing your configuration files to make sure the server’s specs are being properly utilized.

If you’re using PostreSQL, this is where a handy utility called pgtune can come in handy.  It isn’t a panacea for postgres configuration, but it is a handy shortcut to point you towards the need for optimizations in your postgres config.  If you simply want to check that your configuration is/isn’t the source of the main problem without putting too much time into fine-tuning postgres, pgtune is an extremely quick and easy solution.

To install run:

sudo yum install pgtune

​or

sudo apt-get install pgtune

After installation, you can simply run:

pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune

Note: If the PGDATA var isn’t set, you can find your data directory by going into postgres and running the query: show data_directory;

The command will output a new configuration file with settings roughly optimized for the server’s hardware.  All that’s left is to backup the original config file and install the new one:

sudo mv $PGDATA/postgresql.conf $PGDATA/postgresql.conf.backup

sudo mv $PGDATA/postgresql.conf.pgtune $PGDATA/postgresql.conf

sudo service postgresql restart

Postgres is now running with the new configuration and you can check to see if your latency issue is solved, if it isn’t, it’s likely, but not definite, that the problem is less hardware related and more to do with database structure or querying issues.

The postgres community has a pretty extensive wiki with great information on tuning for hardware, general postgres tuning using VACCUUM and ANALYZE, and much, much more.  Here are a few good links that I keep in my bookmarks permenently.

• https://wiki.postgresql.org/wiki/Performance_Optimization
• https://wiki.postgresql.org/wiki/Using_EXPLAIN
• https://wiki.postgresql.org/wiki/Logging_Difficult_Queries

Also, there is an online interface that can be used instead of installing the server tool, so long as you have the hardware specs at hand: http://pgtune.leopard.in.ua/