Table Compaction

After a table has been extensively modified, its disk file(s) may accumulate a certain amount of unused free space, especially if a large number of rows have been deleted. This free space will be re-used as much as possible whenever new rows are inserted or updated, to try to avoid expanding the table's disk footprint. However, if the table is no longer to be modified in the future - e.g. it is now a search-only archive - this free space will never be reclaimed. It is now wasted disk space, as well as a potential performance impairment, as larger seeks may be needed by the operating system to access actual payload data.

Free space in a table may be reclaimed by compacting the table (retaining all payload data), with the following SQL:

ALTER TABLE name COMPACT;

This will compact the table name to eliminate its free space. The process may take some time for a large table, or where there are many indexes on it. Also, while the end result will generally be less disk usage for the table, during the compaction disk usage will temporarily increase, as copies of the table and most of its index files are created. Therefore, before starting, ensure that there is free disk space (in the database's partition) at least equal to the combined size of the table and its indexes.

Because extensive modifications are needed, the table will not be modifiable during compaction: attempts to insert, delete or update rows will block until compaction is finished. The table is readable during compaction, however, so SELECTs are possible. Progress meters may be printed during compaction by setting the SQL property meter to 'compact'. The ALTER TABLE name COMPACT syntax was added in version 6.00.1291080000 20101129. NOTE: Versions prior to version 6.00.1291080000 20101129 should not attempt to access the table during compaction, or corruption may result.

Note that compacting a table is generally only useful when the table will no longer be modified, or has undergone a large amount of deletions that will not be replaced by inserts. Conversely, a "steady-state" continuously-modified table rarely benefits from compaction, because it will merely accumulate free space again: the short-term gains of compaction are outweighed by the significant cost and delay of repeatedly runnning the compaction.