Remember to vacuum your databases!

Captain's log, stardate d223.y40/AB

Databases PostgreSQL DevOps
Xavier Redó
Founder & CTO
Remember to vacuum your databases!

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

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.

Share this post

Related Articles


Introduction to Kubernetes

We've recently gotten our hands on Kubernetes, and we've compiled a quick guide on how to get started.

Read full article

MySQL deferred constraints and unique checks

MySQL and PostgreSQL are very similar but not exact. Take a look at this scenario that works with PostgreSQL but not with MySQL.

Read full article

Query data from PostgreSQL to represent it in a time series graph

In this blog post, our CTO, Xavi, will show us how to query data from PostgreSQL to represent it in a time series graph.

Read full article