5 ms·

Not really, the innodb_file_per_table variable has been set to 1 for a long time. Running OPTIMIZE TABLE frees up the disk space in this case.

• sudhirj 2y ago
  Is this process materially different from a vacuum? Does it manage to optimise without a write lock?
  • benoitg 2y ago
    I don't know how VACUUM works, I couldn't tell you about the differences. The OPTIMIZE works almost exclusively with online DDL statements. There's only a brief table lock held during table metadata operations, but I haven't found that to be a problem in practice. (https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html#optimize-table-innodb-details https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html#...)
  • homebrewer 2y ago
    http://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-2-vacuum-vs.html http://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-2...
    • sudhirj 2y ago
      That helps a lot thanks. Will summarize it quickly for those who come later: MySQL (InnoDB really) and Postgres both use MVCC, so they write a new row on update. InnoDB however also additionally writes a record marking the old row for deletion. To do a cleanup, InnoDB uses the records it kept to delete old data, while Postgres must do a scan. So InnoDB pays a record-keeping price as part of the update that makes it easier to clear data, while Postgres decides to pay this price of occasional scanning.