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!

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.