What does it mean to vacuum a database?

database terminology vacuum
An̲̳̳drew picture An̲̳̳drew · Jan 24, 2010 · Viewed 30k times · Source

As referenced by this Firefox bug, what does the act of vacuuming a database accomplish? Is this operation supported by all modern database software, or only certain ones?

Answer

edgar.holleis picture edgar.holleis · Jan 24, 2010

Databases that use MVCC to isolate transactions from each other need to periodically scan the tables to delete outdated copies of rows. In MVCC, when a row is updated or deleted, it cannot be immediately recycled because there might be active transactions that can still see the old version of the row. Instead of checking if that is the case, which could be quite costly, old rows are assumed to stay relevant. The process of reclaiming the space is deferred until the table is vacuumed which, depending on the database, can be initiated automatically or explicitly.

Related questions

What is sharding and why is it important?

I think I understand sharding to be putting back your sliced up data (the shards) into an easy to deal with aggregate that makes sense in the context. Is this correct? Update: I guess I am struggling here. In my …

Read More
Explanation of BASE terminology

The BASE acronym is used to describe the properties of certain databases, usually NoSQL databases. It's often referred to as the opposite of ACID. There are only few articles that touch upon the details of BASE, whereas ACID has plenty …

Read More
What does "bulk load" mean?

Jumping from article to article, I can see everywhere the expression "bulk loading". What does it really (technically) mean? What does it imply? Explanation based on use-cases is welcome.

Read More