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 … Read more
A REINDEX immediately after a VACUUM FULL is useless because VACUUM FULL itself rebuilds the indexes. This is mentioned in the 9.4 documentation in Recovering Disk Space : …to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. … Read more
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 … Read more
You don’t to specify the table name in the syntax. Only VACUUM works. Also, it will clean the main database only and not any attached database files. For more info, refer to the SQLite documentation.
After more searching I have discovered the isolation_level property of the psycopg2 connection object. It turns out that changing this to 0 will move you out of a transaction block. Changing the vacuum method of the above class to the following solves it. Note that I also set the isolation level back to what it … Read more