DEV Community

jess unrein
jess unrein

Posted on • Updated on

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

Top comments (3)

coreyja profile image
Corey Alexander

This is a cool truck!

I dug in to see if I could utilize this in CircleCI, and found out that CircleCI already adds this for their Postgres image!
(Or at least has it for the version of Postgres that I use, but I assume its in others as well)

So if you are using CircleCI for your tests, this is likely already enabled for you!

shalvah profile image

This is kinda old now, but it seems the newer CircleCI images (cimg/postgres) don't come with this anymore e.g.

But thanks for the tip. I think it's still valid for the older circleci/postgres images.

ayedo profile image
Alon Dolev • Edited

I believe that one of the main problems with slow tests is not the performance of the database itself, but the time it takes to set it up for each test.

I wrote a little library to help you quickly restore a Postgres database to a 'clean' state. This way you only need to run costly database migrations once, and then you can quickly restore the database for each test.

We used it in a productive system to get a 4x speedup in our integration tests. If you're working on a JVM system it may help you too. Check it out here: