Deleting rows in Postgres

Today I encountered an issue where the Postgres DB server was running out of disk space. We had to follow a few steps to mitigate the issue. Here’s what we did:

1. First increase disk space
2. Let’s check the space taken up by the dead tuples
3. Let’s remove the bloat
4. As a long term solution, we need to start archiving these records if they are no longer used.

I honestly didn’t understand a single thing!!! Dead tuple? Bloat? Did Postgres eat something? So, I obviously looked it up and found a few blogs of interest. Sharing them here:

Blogs Read:

1. https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/
2. https://www.shubhamdipt.com/blog/how-to-clean-dead-tuples-and-monitor-postgresql-using-vacuum/

My notes:

What happens when Postgres deletes data in the table?

It doesn’t free up space. Instead, Postgres marks these records to be deleted as a dead tuple, to preserve them incase a previous transaction is using it. This behavior references the C in ACID compliance of Postgres! This extra space is consumed by those dead tuples is called a bloat.

If it doesn’t get deleted, what happens?

So the data is first preserved until Postgres knows that these records no longer belong to a transaction. Once all dependencies are removed, it proceeds to vacuum the records.

What is Postgres Vacuum?

Postgres vacuum is the process of seemingly deleting the dead tuples. I say seemingly, because it doesn’t free up space. What it does is marks these tuples are records that can be converted to new “inserts”. Think of it as a soft delete, but one that can be overwritten.

The vacuum is a configurable setting. It can either be turned on manually or be automatic.

It is also important to note why Postgres doesn’t necessarily delete the records. This is because Vacuuming is a non blocking operation. It will not create locks on the table. So that’s why it is important that Postgres doesn’t delete it, and rather, preserves the data incase it violates a transaction.

These are just a few of my learnings from reading those blogs today.