Performance degradation during ALTER TABLE ... convert_quicklz_to_zstd in VMware Tanzu SQL

Products

• VMware Tanzu Data Intelligence
• VMware Tanzu Greenplum / Gemfire
• VMware Tanzu Greenplum

Issue/Introduction

When performing a table compression conversion from QuickLZ to Zstandard (zstd) using the ALTER TABLE <table_name> SET WITH (REORGANIZE=true, convert_quicklz_to_zstd) command, users may experience significantly longer execution times than expected. In some environments, converting large partitioned tables (e.g., 5 TB+) can take several hours, whereas manual data migration scripts (Create Table As Select - CTAS) may perform the same task in half the time.

Cause

The performance bottleneck is attributed to two primary factors:

1. Auto-Stats Overhead: When gp_autostats_mode is set to on_change or on_no_stats, the system automatically triggers an ANALYZE operation on the internal temporary tables created during the reorganization process. This adds significant metadata overhead to the conversion.

2. Sequential Processing: The ALTER TABLE command on a partitioned root table iterates over leaf partitions sequentially. If the table has a high partition count, it utilizes only a fraction of the available CPU resources (typically one core per primary segment). In contrast, parallel scripts utilize multiple workers to process leaf partitions simultaneously, resulting in higher hardware utilization and faster completion times.

Resolution

To optimize the conversion performance and minimize execution time, apply the following recommendations:

1. Disable Automatic Statistics: Before running the ALTER command, disable automatic statistics collection for the session to prevent the system from analyzing internal temporary tables:

SET gp_autostats_mode = 'none';
ALTER TABLE <table_name> SET WITH (REORGANIZE=true, convert_quicklz_to_zstd);

 

2. Leverage Parallelism for Database Conversions: To convert multiple tables across the database, execute ALTER TABLE commands in parallel across multiple database sessions. This ensures that the host's CPU cores (decompress/compress operations) are fully utilized.

 

3. Alternative for Large Partition Hierarchies: For individual, very large partitioned tables where speed is the highest priority, a manual CTAS method (creating a new table with Zstandard partitions and inserting data from the old table) remains the most performant option. This is because a manual script can target leaf partitions in parallel, whereas a single ALTER TABLE on a root partition processes leaf partitions sequentially.