How can I design column encoding in Amazon Redshift?

Short description

To configure column encoding in Amazon Redshift, use ENCODE AUTO and ANALYZE COMPRESSION commands. For more information, see Compression encodings.

If you apply compression to a sort key column, then query performance can degrade because it adds block scans for certain query types. If you manually specify encoding types for a table, then set RAW encoding for the sort key column.

For more information, see Amazon Redshift best practices for designing tables.

Resolution

Use ENCODE AUTO to maximize column compression

Use ENCODE AUTO to automatically optimize column compression in your Amazon Redshift tables. By default, ENCODE AUTO continuously analyzes your cluster's workload patterns to help maximize query performance and reduce the need for manual compression management.

If you specify an encoding type on any column in the table, then you turn off ENCODE AUTO.

To check whether you have ENCODE AUTO turned on for a table, use the following query:

SELECT "table", encoded FROM svv_table_info WHERE "table" = 'table_name';

Note: Replace table_name with the name of your table.

Run the ANALYZE COMPRESSION command to review an analysis of your table

To perform compression analysis and produce a report that suggests compression encoding for your table, run the ANALYZE COMPRESSION command:

ANALYZE COMPRESSION lineitem;

Note: Replace lineitem with your table name.

Example output:

Table   |     Column      | Encoding | Est_reduction_pct 
----------+-----------------+----------+-------------------
 lineitem | l_orderkey      | raw      | 0.00
 lineitem | l_partkey       | az64     | 33.34
 lineitem | l_suppkey       | az64     | 45.46
 lineitem | l_linenumber    | az64     | 87.88
 lineitem | l_quantity      | az64     | 80.00
 lineitem | l_extendedprice | az64     | 60.32
 lineitem | l_discount      | az64     | 90.77
 lineitem | l_tax           | az64     | 90.77
 lineitem | l_returnflag    | zstd     | 80.99
 lineitem | l_linestatus    | zstd     | 86.86
 lineitem | l_shipdate      | az64     | 61.15
 lineitem | l_commitdate    | az64     | 61.26
 lineitem | l_receiptdate   | az64     | 61.15
 lineitem | l_shipinstruct  | zstd     | 96.50
 lineitem | l_shipmode      | bytedict | 88.89
 lineitem | l_comment       | zstd     | 70.36
 lineitem | l_dummy         | zstd     | 99.97
(17 rows)

Note: For each column, the report includes an estimate of potential disk space reduction compared to RAW encoding.

(Optional) To help you understand the output from the ANALYZE COMPRESSION command, you can view your table definition. Run the following command:

SHOW TABLE lineitem;

Note: Replace lineitem with your table name.

Example output:

Show Table DDL statement             
-------------------------------------------------
 CREATE TABLE public.lineitem (                 
     l_orderkey integer ENCODE raw,             
     l_partkey integer ENCODE raw,              
     l_suppkey integer ENCODE raw,              
     l_linenumber integer ENCODE raw,           
     l_quantity numeric(15,2) ENCODE raw,       
     l_extendedprice numeric(15,2) ENCODE raw,  
     l_discount numeric(15,2) ENCODE raw,       
     l_tax numeric(15,2) ENCODE raw,            
     l_returnflag character(1) ENCODE raw,      
     l_linestatus character(1) ENCODE raw,      
     l_shipdate date ENCODE raw,                
     l_commitdate date ENCODE raw,              
     l_receiptdate date ENCODE raw,             
     l_shipinstruct character(25) ENCODE raw,   
     l_shipmode character(10) ENCODE raw,       
     l_comment character varying(44) ENCODE raw,
     l_dummy character varying(10) ENCODE raw   
 )                                              
 DISTSTYLE AUTO                                 
 SORTKEY ( l_orderkey );
(1 row)

ANALYZE COMPRESSION acts as an advisory tool and doesn't modify the table's column encodings. To apply the suggested encodings, recreate the table, or run the ALTER TABLE command:

ALTER TABLE lineitem ALTER COLUMN l_dummy ENCODE example_compression;

Note: Replace lineitem with your table name, l_dummy with your column name, and example_compression with your compression encoding.

Related information

How do I detect and release locks in Amazon Redshift?

Column compression to reduce the size of stored data