Unable to Alter Compression Type or Level on Append-Only Tables in Greenplum

Products

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

Issue/Introduction

When attempting to change the compression type (compresstype) or compression level (compresslevel) of an existing append-only table in Greenplum Database, users encounter an error.

ERROR:  cannot alter compresstype or compresslevel for an existing append-only table

Environment

GPDB 6.X

Cause

Append-only (AO) tables in Greenplum do not allow modification of compression settings after the table has been created. This is due to the immutable storage format used by AO tables, where compression configuration is fixed at creation time.

Resolution

For append-only tables :

1. Create a New Table with Desired Compression

CREATE TABLE my_append_table_new (
    col1 INT,
    col2 TEXT
)
WITH (
    appendonly=true,
    orientation=column,
    compresstype=ZSTD,
    compresslevel=5
)
DISTRIBUTED BY (col1);

 

2. Copy Data from Old Table to New Table

INSERT INTO my_append_table_new
SELECT * FROM my_append_table;

3. (Optional) Rename Table

ALTER TABLE my_append_table RENAME TO my_append_table_old;
ALTER TABLE my_append_table_new RENAME TO my_append_table;

 

4. (Optional)  Drop Old Table

DROP TABLE my_append_table_old;

Additional Information

Reference documentation