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