PostgreSQL, Unit Testing April 6, 2016 jess No comments

Speed up your (postgres) unit tests with one weird trick!

My coworker was lamenting to her database administrator friend about our slow-as-tar unit tests. They unearthed a postgres setting that halved our Jenkins build time when disabled. This was the single biggest performance boost to our unit tests after several attempts at optimization.


fsync is a PostgreSQL configuration setting that helps with reliability and disaster recovery. It’s a boolean flag that changes postgres’s write settings. When enabled, it tries to ensure that updates are physically written to disk. This is a great safety measure if you want to protect your data against hardware crashes or operating system failures. However, it results in a significant performance hit.

Why enable fsync?

fsync can be invaluable for recovery if you find yourself with a corrupted database. It’s enabled by default, and for good reason. If your production performance needs improvement, there are probably safer optimizations to make. It could save you from unrecoverable corrupt data should the worst happen. Unless you have a surefire way of recovering data from an external source, turning off fsync in production is dangerous.

Why disable fsync?

There’s no need for fsync in most testing environments. When unit testing, you probably expect to set up and tear down your database at least once. Writing to disk is an expensive operation for something you plan to throw away anyhow.

How to change your PostgreSQL config

If you’ve never changed your postgres settings before, start by figuring out where the config file lives. If you don’t know you can find it by running

SHOW config_file;

in your psql interactive terminal. This will print out the path to the postgres config file.

Next, find the fsync option. It’s generally located near other Write Ahead Log options. Make sure the line is not commented out, as it will default to on. Switch off the boolean flag and save your changes. Note that changes to the config require postgres to restart before taking effect.

Lastly, sit back and enjoy all the time you’ve won back now that updates to your test database aren’t by default writing to disk.