Redshift Column Compression Settings
This is part 4 of a series on Amazon Redshift maintenance:
- Vacuuming for Table Performance
- Monitoring Disk Space
- Monitoring Query Performance
- 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:
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.
No spam, ever! Unsubscribe any time. Learn more about the product.