What does it mean to vacuum a database?

As referenced by this Firefox bug, what does the act of vacuuming a database accomplish? Is t开发者_StackOverflow社区his operation supported by all modern database software, or only certain ones?

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.

It is specifically referring to SQL lite vacuum command. http://www.sqlite.org/lang_vacuum.html

It is removing space left over from DELETE statements.

Vacuum means 2 things:

1. Reclaim space
2. Defrag file [kind of]

Why use Vacuum:

1. Database file always grows in size when you drop database objects such as tables, views, indexes, and triggers or delete data from tables, Because SQLite just marks the deleted objects as free and reserves it for the future uses.

2. Indexes and tables become fragmented, with a high number of inserts, updates, and deletes.

3. Unused data block is created by insert, update, & delete operations.

Precautions: Different databases may handle this differently: eg in SQLite if you use unaliased rowid, the VACUUM command will reset the rowid values. but if you use INTEGER PRIMARY KEY column, the VACUUM does not change the values of that column.

Vacuum requires space to copy the database and run the operations.

'vacuumdb' is in MySQL, sqlite and PostgreSQL. In Postgres, vacuumdb identifies space that's occupied by deleted rows and catalogues it for future use. 'vacuum full' does a more comprehensive examination and moves records into the newly created space.

It's very similiar to defragging a filesystem. More info on the PGSQL docs.