Rebuilding Tables

You can reclaim disk space on a large scale by rebuilding tables, as follows:

  1. Create a table with the same (or similar) definition as the table to rebuild.
  2. Create projections for the new table.
  3. Copy data from the target table into the new one with INSERT…SELECT.

  4. Drop the old table and its projections.

    Rather than dropping the old table, you can rename it and use it as a backup copy. Before doing so, verify that you have sufficient disk space for both the new and old tables.

  5. Rename the new table with ALTER TABLE…RENAME, using the name of the old table.

When you rebuild a table, Vertica purges the table of all delete vectors that precede the AHM. This prevents historical queries on any older epoch.

Projection Considerations

  • You must have enough disk space to contain the old and new projections at the same time. If necessary, you can drop some of the old projections before loading the new table. You must, however, retain at least one superprojection of the old table (or two buddy superprojections to maintain K-safety) until the new table is loaded. (See Prepare Disk Storage Locations in Installing Vertica for disk space requirements.)
  • You can specify different names for the new projections or use ALTER TABLE…RENAME to change the names of the old projections.
  • The relationship between tables and projections does not depend on object names. Instead, it depends on object identifiers that are not affected by rename operations. Thus, if you rename a table, its projections continue to work normally.