Vacuum and Vacuum Full for VMware Tanzu Greenplum 6

Products

• VMware Tanzu Greenplum

Issue/Introduction

Until Greenplum 5, there were some scenarios where a REORGANIZE of a bloated table was preferred over VACUUM/VACUUM FULL. The reasons for this were:

• VACUUM is slow compared to REORGANIZE
• VACUUM FULL needs to run only in restricted mode

Environment

Product Version: 6.0

Resolution

With improvements provided in Greenplum 6, the current recommendation for maintenance activities is to use VACUUM FULL and VACUUM instead of REORGANIZE for objects to remove bloat or dead rows.

Key points

1. VACUUM or VACUUM FULL can be used for both a Heap and AO table.

2. If table bloat is too high VACUUM FULL should be used.

3. VACUUM FULL can be safely used for user tables when the system is online and available for users. For the current implementation for VACUUM FULL in Pivotal Greenplum 6, perform temp table creation based on an existing table, move the rows to temp table, and drop the older table in a transaction. This implementation will not have any impact to online users 

Note: Greenplum 6 Documentation related to VACUUM/VACUUM FULL will also be updated with this change.