CODEWITHSUNDEEP
amazon-redshift
Roshan Nagpure
Roshan Nagpure

Reputation: 124

Which option is best for selecting column compression encoding [ COPY VS ANALYZE COMPRESSION ]

Scenario : I have to change existing table's column encoding

a) If I execute ANALYZE COMPRESSION table_name ---this approach is suggested to use ZSTD compression for all columns including SORT-KEY column.

b) I have created new table using existing table’s DDL and used copy command in order to get column compression encoding (Copy select column compression encoding when load data into an empty table) ---COPY command suggested LZO for all columns including SORT-KEY column.

Question :

Which approach is correct or optimised ? SORT-KEY column compression is bad so will ZSTD for SORT-KEY column improve performance ?

Upvotes: 1

Views: 1574

Answers (2)

Tony Gibbs
Tony Gibbs

Reputation: 2489

ANALYZE COMPRESSION only looks at the effectiveness of the compression based on storage and does not consider other factors.

In many cases the first column of the SORT KEY compresses well and is typically filtered on (predicate in the where clause). If for some reason you never filtered on the column (maybe a merge join) it would be okay to compress the SORT KEY.

The reason we recommend decompressing the first column of the SORT KEY is because when you filter with a range restricted scan on a column that is highly compressed compared to the other columns you are scanning it can result in a slight decrease in performance.

Upvotes: 1

Mukund
Mukund

Reputation: 946

https://forums.aws.amazon.com/thread.jspa?threadID=252583 https://discourse.snowplowanalytics.com/t/make-big-data-small-again-with-redshift-zstd-compression/1280

Probably the above threads helps a it.

Upvotes: 1

Related Questions