Amazon Redshift VACUUM Command Syntax, Usage and Examples

  • Post author:
  • Post last modified:February 26, 2018
  • Post category:Redshift
  • Reading time:3 mins read

Redshift VACUUM command is used to reclaim disk space and resorts the data within specified tables or within all tables in Redshift database.

When you delete or update data from the table, Redshift logically deletes those records by marking it for delete. Vacuum command is used to reclaim disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations. This command also sorts the data within the tables when specified. 

Read:

Redshift VACUUM command Syntax

Below is the vacuum command syntax:

VACUUM [ FULL | SORT ONLY | DELETE ONLY | REINDEX ]
[ [ table_name ] [ TO threshold PERCENT ] ];

Where;

  • FULL – Sorts the specified table or all tables in the current database and reclaims disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations. This is the default vacuum operation.

VACUUM full slips the table sort it is already 95% sorted.

  • SORT ONLY – Sorts the specified table or all tables in the current database without reclaiming disk spaces.
  • DELETE ONLY – Reclaims disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations without sorting records.
  • REINDEX – Analyze table distribution sort keys and perform full vacuum operation. VACUUM REINDEX comparatively tables long time and is not supported with TO threshold PERCENT parameter.
  • TO threshold PERCENT– A clause that specifies the threshold above which VACUUM skips sorting of data and target threshold for reclaiming space in the delete phase.

Redshift VACUUM command Examples

Below are the some of the usage examples of redshift VACUUM command:

Reclaim disk space and sort records of all table in databases:

vacuum;

training=# vacuum;
VACUUM
training=#

Reclaim disk space and sort records of specific table:

vacuum table_name;

training=# vacuum tr_test;
VACUUM
training=#

Resort rows in the table only if fewer than 75 percent of rows are already sorted:

training=# vacuum tr_test TO 75 PERCENT;
VACUUM
training=#
Tags: