Vacuum and Vacuum Full forUp Tanzu Greenplum 6.x and above

Products

• VMware Tanzu Greenplum
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

Up to Greenplum 5.x, there were some scenarios where ALTER TABLE X.Y SET WITH (REORGANIZE=true) on 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

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.

Key points

1. VACUUM or VACUUM FULL can be used for both Heap and AO tables.
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.
4. The current implementation for VACUUM FULL in Greenplum 6 performs temp table creation based on an existing table, moves the valid rows to temp table and drops the older table in a single transaction. This implementation will not have any impact to online users.