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

mysql postgresql vacuum
RunningAdithya picture RunningAdithya · Aug 6, 2014 · Viewed 21.7k times · Source

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?

Answer

j.w.r picture j.w.r · Aug 6, 2014

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.

Related questions

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
Best practices for SQL varchar column length

Every time is set up a new SQL table or add a new varchar column to an existing table, I am wondering one thing: what is the best value for the length. So, lets say, you have a column called …

Read More
CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)? SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 Is there anything like this: SELECT CURRENT_MILLISEC --> 1331255526000 or the …

Read More