Speed up your PostgreSQL unit tests with one weird trick!

<Note: I know the title is hokey, but I couldn't think of what else to call it! Would love feedback on how to title articles effectively, since that's something I struggle with>

About few years ago I worked on a product where our unit tests moved about as quickly as tar. My coworker was lamenting to her DBA friend about the tests, and after poking around together, they unearthed a PostgreSQL setting that halved our Jenkins build time when disabled. This was the single biggest performance boost to our unit tests after several months of attempted optimizations.

fsync

fsync is a PostgreSQL configuration setting that helps with reliability and disaster recovery. It’s a boolean flag that changes PostgreSQL'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 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 running unit tests, 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 PostgreSQL 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 PostgreSQL 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 defaults to on. Switch the boolean flag for fsync to off. Note that changes to the config require PostgreSQL to restart before taking effect.

Note: PosgreSQL is very flexible when assigning boolean values for server configuration settings. TRUE values are on, true, yes, and 1. FALSE values are off, false, no, and 0. All of these values are case-insensitive, and any of these is fine. Just make sure to pick a single style and stay consistent.

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