How to create an Index in Amazon Redshift

I'm trying to create indexes in Amazon Redshift but I received an error

create index on session_log(UserId);

UserId is an integer field.

Answers 1

  • If you try and create an index (with a name) on a Redshift table:

    create index IX1 on "SomeTable"("UserId");
    

    You'll receive the error

    An error occurred when executing the SQL command: create index IX1 on "SomeTable"("UserId") ERROR: SQL command "create index IX1 on "SomeTable"("UserId")" not supported on Redshift tables.

    This is because, like other data warehouses, Redshift uses columnar storage, and as a result, many of the indexing techniques (like adding non-clustered indexes) used in other RDBMS aren't applicable.

    You do however have the option of providing a single sort key per table, and you can also influence performance with a distribution key for sharding your data, and selecting appropriate compression encodings for each column to minimize storage and I/O overheads.

    For example, in your case, you may elect to use UserId as a sort key:

    create table if not exists "SomeTable"
    (
        "UserId" int,
        "Name" text
    )
    sortkey("UserId");
    

    You might want to read a few primers like these


Related Articles