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.
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.
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
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.