How to Create an Index in Redshift

Being a columnar database specifically made for data warehousing, Redshift has a different treatment when it comes to indexes. It does not support regular indexes usually used in other databases to make queries perform better. Instead, you choose distribution styles and sort keys when you follow recommended practices in How to Use DISTKEY, SORTKEY and Define Column Compression Encoding in Redshift.

Redshift does support creation of unique, primary key and foreign key indexes (referred to in the Redshift Documentation as constraints). However, the same documentation states that these are informational only and are not enforced. It is recommended to use them if your data loading process ensures their integrity, as they are used as planning hints to optimize query execution. However, you should not define them if you doubt their validity. For example, if you are not sure if the product name is really unique for a table, do not create the unique key constraint for it. But if you are certain, then do create it so the database engine can use it when executing your queries.

To add a constraint to a table, use the ALTER TABLE command:

-- Add primary key to a table
ALTER TABLE table_1 ADD PRIMARY KEY (col1);

-- It is recommended to specify a constraint name using the CONSTRAINT keyword
-- Here we also show you can have more than one column in the constraint definition
ALTER TABLE table_2 ADD CONSTRAINT table_2_pk PRIMARY KEY (col1, col2);

-- Add a unique key
ALTER TABLE table_3 ADD CONSTRAINT table_3_uq UNIQUE (col1);

-- Add a foreign key
ALTER TABLE products ADD CONSTRAINT products_categories_fk
FOREIGN KEY (category_id) REFERENCES categories (category_id);