Redshift Column Compression Settings

This is part 4 of a series on Amazon Redshift maintenance:

1. Vacuuming for Table Performance
2. Monitoring Disk Space
3. Monitoring Query Performance
4. Column Compression

What is column compression

Amazon Redshift being a columnar database, is able to pick a more optimal compression scheme for columns compared to a regular row store database. This is because contiguous blocks of columns have similar patterns and distributions of data stored.

Why column compression is important

Compression of columns can significantly impact the performance of queries. The main reason being the compressed columns take up less space on disk and therefore require less disk I/O operations while executing queries involving that compressed column.

Redshift picks a column compression encoding when you first copy data into an empty table. As data is added and deleted from that table, the optimum compression algorithm might change.

SQL to see current column encoding

select "column", type, encoding
from pg_table_def
where table_name = table_name_here;

What Redshift recommends

If you suspect that the right column compression ecoding might be different from what's currenlty being used – you can ask Redshift to analyze the column and report a suggestion.

analyze compression table_name_here;

which will output:

The analyze compression command does not change the compression method, it is simply a reporting tool. To manually change the column compression method being used, you'll have to recreate a table with the same schema and deep-copy the contents of the existing table.

Next Generation SQL Data Analysis Write SQL, visualize data, and share your results. Onboarding forward-thinking teams now:

No spam, ever! Unsubscribe any time. Learn more about the product.