Why is it a vacuum not needed with Mysql compared to the PostgreSQL?

Question

I am more familiar with PostgreSQL than MySQL. Have encountered wraparound Id failure once with the PostgreSQL db and then understood the importance of vacuuming in the db. Actually, that was such a massive overhead work to deal with(and it was with an old version 7.4.3 which is updated a few months back to have the autovacuum). When comparing MySQL with PostgreSQL, assume that MySQL does not have to deal with such overheads like vacuum in PostgreSQL. Is this assumption right?

Also why is it a vacuum not needed with MySQL Dbs compared to PostgreSQL? Is there any other optimization alternatives similar to vacuum exist for MySQL dbs?

Solution 1

Robert Haas wrote on this topic.

The short version is that InnoDB uses rollback logs, more like Oracle's design. Only the most recent version of a row is kept on the main table. It must manage log purging, an asynchronous/delayed operation with a related function to PostgreSQL's VACUUM.

This means more writes to do on updates and makes access to old row versions quite a lot slower, but gets rid of the need for asynchronous vacuum and means you don't have table bloat issues. Instead you can have huge rollback segments or run out of space for rollback.

So it's a trade-off, a design with a different set of advantages and problems.

If you're talking about MyISAM tables, then it's totally different. PostgreSQL's tables won't eat your data. MyISAM will. PostgreSQL's tables are transactional. MyISAM isn't. A flat file doesn't require VACUUM either, that doesn't make it a good idea.

Solution 2

The MySQL approximation of PostgreSQL's vacuum is OPTIMIZE TABLE tablename (MySQL docs). It performs a similar function in MySQL as PostgreSQL in that, depending on the storage engine used, it reclaims unused space, reorganizes indexes and tables, and defragments data files. You should definitely run it periodically just like vacuum in PostgreSQL.