Remember to vacuum your databases!

Diario del capitán, fecha estelar 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ó

Responsable de arquitectura y desarrollo. Es el culpable de que trabajemos con Ruby on Rails, Node.js, React y Angular, entre otros. Pocos desarrolladores que conozcamos tienen sus habilidades sociales.

comments powered by Disqus

Estás a un paso de conocer a tu mejor socio.