Improve Amazon Redshift table performance fast & easy.

Assuming Redshift tables are un-compressed because most people don’t do it.

  1. list all the big tables by size, here’s a script
  2. run analyze compression
    analyze compression public.report_table_name;

    Example results:

    table column encoding est_reduction_pct
    report_table_name rowid raw 0
    report_table_name create_date zstd 9.49
    report_table_name create_day delta 38.72
    report_table_name create_date_key zstd 28.98
    report_table_name type zstd 67.59
    report_table_name type_desc zstd 58.25
    report_table_name created_by zstd 35.17
  3. recreate table with new compression encoding and see performance gains.

DROP TABLE IF EXISTS public.report_table_name cascade;

CREATE TABLE public.report_table_name
( rowid INT IDENTITY(1,1),
create_date timestamp encode zstd,
create_day date encode zstd,
type integer encode zstd,
type_desc varchar(28) encode zstd,
created_by varchar(30) encode zstd,
data_refresh_date date encode raw
) ;

Amazon manual regarding compression command:

This entry was posted in big data and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s