Why does Amazon Redshift only allow one sort key per table?

Question

In Redshift, only one column can be designated as a sort key. I was wondering why a column-oriented DBMS would have a restriction like this.

ex. Let's say I have a table like this:

rowid   name       age

1       Kevin      20
2       Jill       35
3       Billy Bob  19

Internally the DB would store each column separately, perhaps like this:

Kevin:1,Jill:2,Billy Bob:3

20:1,35:2,19:3

I would think it would be possible to sort these separately and with their own ordering etc.

because interleaved sort keys handle this intrinsically. or compound depending on your circumstance. "A table with interleaved keys arranges your data so each sort key column has equal importance." aws.amazon.com/blogs/aws/… — DaFi4
so if you interleave sort key on many columns: " Interleaved sorting provides fast filtering, no matter which sort key column(s) you specify in your WHERE clause. " — DaFi4

Guy Guy · Accepted Answer · 2016-09-10T21:22:09

Redshift is designed to work on massive number of records, and to calculate analytics on it quickly. Many of the design patterns of smaller DB that are tuned into transactional workloads, are not going to work in that scale. For example, sort keys in OLTP are implemented with index that is duplicating the data. On small scale of data (GBs), it is not a big issue, but with large amount of data (TBs and PBs), it is.

The main usage of sort keys in Redshift is to allow the DB to minimize the number of disk IO reads, which is very slow. This is another example of a difference between small scale DBs and large ones. If an operation is taking 100ms for 1M records, it will take 100 seconds for 1B records or an hour for 36B records. Redshift allows queries over many billions of records, by managing a mapping of the minimum and maximum value of each column for each 1MB compressed data block. If the data of that block is sorted, most of the blocks can be ignored based on your WHERE clause filters.

This is the reason why you would like to define your sort key columns (note that you can have multiple columns), to match the columns that you use in your WHERE clauses (for example, Date).

Both Compound and Interleaved can support multiple columns, but with Compound you define the order of the sorting and with interleaved they are interleaved with no order between them.

Why does Amazon Redshift only allow one sort key per table?

1 Answers