Remember to vacuum your databases!

Captain's log, stardate d223.y40/AB

There's a way to declutter PostgreSQL databases that will impact the performance of your projects. Learn about vacuum in this blog post!

Vacuuming robot - Photo by Denny Müller

You have to do VACUUM in your PostgreSQL Docker databases from time to time.

VACUUM is an operation in PostgreSQL to optimize disk space in databases, but also to improve performance of queries.

Let's see a real example from one of our current projects.

I have seen than in my local development Database of a large media platform we develop, after doing some data migrations, I was having a very slow performance on a 500k rows table (not that big). I copied the production database to a local environment to run the testing and what I'm about to describe.

Counting the items was taking between 12 and 30 seconds. So I opened a DB console and launched:

    vacuum analyze;

You'll see that it takes some time to complete on big databases, but the result is well worth it!

Now the count - and the rest of the queries - are completed in under a second.

I imagine that having the DB inside Docker doesn't execute the VACUUMs and over time and changes the Database gets less and less performant.

In my case, this issue started after adding some database fields on large tables. Maybe this is not the case, but it's worth executing it from time to time to avoid slowdowns on development.

Xavier Redó

Xavier Redó

Responsible for architecture and development. He's the one to blame for our techstack (Ruby on Rails, Angular, React, Node.js) and the one to be thanked as well. One of the very few developers with social skills we know.

comments powered by Disqus

You're one step away from meeting your best partner in business.

Hire Us