35

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?

RunningAdithya
  • 1,656
  • 2
  • 16
  • 22
  • 1
    `Actually, that was such a massive overhead work to deal with.` Are you aware of [autovacuum](http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html)? In modern Postgres, you are covered automatically by default, except for special needs. – Erwin Brandstetter Aug 06 '14 at 07:54
  • @ErwinBrandstetter: This was happened while working with a old system which had postgresql version 7.4.3 where autovacuum feature was not available. The db was updated to the latest version a few months ago and there is no more overheads now. Actually I forgot to mention that it was happened a long time back – RunningAdithya Aug 06 '14 at 10:49
  • 1
    That explains it. Yes, quite worth mentioning. – Erwin Brandstetter Aug 06 '14 at 14:33

2 Answers2

30

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.

j.w.r
  • 4,136
  • 2
  • 27
  • 29
30

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778