How to index data in Redshift
Redshift

How to index data in Redshift

How to create an equivalent of an index in Redshift
Bartosz Mikulski
Bartosz Mikulski – 1 min read

There are no indexes in Redshift. At least not the ones we use in relational databases. It is so because Redshift uses columnar storage, standard row indexing techniques are not applicable.

Instead of that, we can define a sort key. There are, however, a few issues related to sort keys. First of all, we can have only one sort key per table. Such a limitation exists because Redshift will use that sort key to order the data stored in the underlying files, and, of course, there can be only one way to order it.

The second issue is the fact that we have to define the sort key at the time when we create the table. Because the sort key is a technical concept that influences even the order of data on the hard drives, we provide it as a part of the table definition.

To determine which column(s) will be the best to use as the sort key, take a look at the Redshift best practices: https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-sort-key.html.

Note that it is possible to change the SORT KEY later. Of course, that requires reorganizing the underlying data files, but that technical detail is handled automatically by AWS. For us, the essential information is that:

  • The sort key can change.
  • There is always only one sort key (however, we can use a compound key with multiple columns).
  • It is used to order the data when storing it on the hard drives, so it has a tremendous influence on query performance.
Older post

How to generate a sequence of dates in Redshift

How to use the generate_series function to generate a sequence of dates

Newer post

How to count the number of rows that match a condition in Redshift

How to count the rows by multiple conditions at the same time in SQL

Related Posts

Redshift

Get the last day of the month in Redshift

How to use the last_day function in Redshift
Published on: 18 Dec 2020

Redshift

How to make an unconditional join in Redshift

LEFT OUTER JOIN ON 1=1 in Redshift
Published on: 17 Dec 2020

Redshift SQL

How to count the number of rows that match a condition in Redshift

How to count the rows by multiple conditions at the same time in SQL
Published on: 16 Dec 2020

Are you looking for an experienced AI consultant? Do you need assistance with your RAG or Agentic Workflow?
Schedule a call, send me a message on LinkedIn. Schedule a call or send me a message on LinkedIn

>