Recently, I had this task of transferring our tests from using in-memory SQLite instances to Postgres containers. It seemed a like a pretty straightforward set of changes, until I realized it’s not. I had to fix some random unexpected issues down the road. But the biggest (and to be fair, the most expected) one was the poor performance of the tests after fully migrating to Postgres.
Long story short, after some Internet search and reading documentations, I ended up getting a good performance (very close to what we had with SQLite) using the
docker-compose.yaml configuration below:
# Remove the `tmpfs` key below, either:
# - If you don't want to lose the data after the container stops/restarts.
# - If the data can be too large for the memory.
# Since it's mainly used for testing purposes, it's okay to set fsync=off for
# improved performance.
command: -c fsync=off -c full_page_writes=off
The key points here are:
tmpfsmounts, you can utilize memory (RAM) as the underlying filesystem storage for specific paths. Here I’ve used it to store Postgres data. This, of course, means there’s no real persistence in-place, therefore you will lose the data if the container stops/restarts. Also, you have to be careful with the size of the data, in case it could get close to your memory limits.
-c fsync=off -c full_page_writes=off: If you don’t disable the
fsyncoption, Postgres makes sure that updates are physically written to disk, which could be a great performance hit. As of official documentations, if you turn
fsyncoff, you should also consider disabling
About Regular Encounters
I’ve decided to record my daily encounters with professional issues on a somewhat regular basis. Not all of them are equally important/unique/intricate, but are indeed practical, real, and of course, textually minimal.