Amazon Redshift中CustomerTransaction表排序键选型：复合还是交错？

We're planning to import OLTP relational tables into AWS Redshift. The CustomerTransaction table is associated with multiple lookup tables—here we only show 3, but there are more in reality.

Questions:

1. What sort key should we set for the CustomerTransaction table?
2. In SQL Server, we created non-clustered indexes on the foreign keys of CustomerTransaction. In AWS Redshift, should we use compound sort keys or interleaved sort keys for the foreign key columns of this table?
3. What's the best indexing strategy for this table?

Here's the table schema (note: corrected a typo in ProductType's primary key column):

CREATE TABLE dbo.CustomerTransaction (
    CustomerTransactionId bigint PRIMARY KEY IDENTITY(1,1),
    ProductTypeId bigint, -- Foreign key to ProductType Table
    StatusTypeID bigint, -- Foreign key to StatusTypeTable
    DateOfPurchase date,
    PurchaseAmount float,
    ....
)

CREATE TABLE dbo.ProductType (
    ProductTypeId bigint PRIMARY KEY IDENTITY(1,1),
    ProductName varchar(255),
    ProductDescription varchar(255),
    .....
)

CREATE TABLE dbo.StatusType (
    StatusTypeId bigint PRIMARY KEY IDENTITY(1,1),
    StatusTypeName varchar(255),
    StatusDescription varchar(255),
    .....
)

Great question! Let's break this down based on Redshift's columnar OLAP architecture, which works very differently from SQL Server's OLTP engine:

Choosing Compound vs. Interleaved Sort Keys for CustomerTransaction #

First, let's clarify the core tradeoffs between the two sort key types, then apply them to your scenario:

• Compound Sort Keys: These sort data by the first column, then the second, and so on. They’re ideal when your queries consistently filter or sort by a "leading" column first, followed by other columns. For fact tables like CustomerTransaction (which I assume is your largest table), this is almost always the right choice for typical OLAP workloads.

  In most transactional analytics queries, you’ll start with a date filter (e.g., WHERE DateOfPurchase BETWEEN '2023-01-01' AND '2023-12-31'), then slice by product type, status, or other dimensions. A compound sort key like (DateOfPurchase, ProductTypeId, StatusTypeId) would optimize this perfectly:

  • Queries filtering on DateOfPurchase can quickly skip entire blocks of irrelevant data using Redshift’s zone maps, drastically reducing the amount of data scanned.
  • The subsequent foreign key columns are sorted within each date range, which speeds up joins with your lookup tables and further filtering operations.

• Interleaved Sort Keys: These treat all columns equally, so filtering on any single column has similar performance. However, they come with higher maintenance overhead (slower vacuum operations, more storage for sort metadata) and are only useful if your queries frequently filter on any of the sort key columns without a clear leading column. For example, if you have just as many queries filtering only on ProductTypeId as you do on DateOfPurchase, interleaved might make sense—but this is rare for transaction fact tables.

Best Optimization Strategy for CustomerTransaction in Redshift #

Redshift doesn’t use traditional B-tree indexes like SQL Server. Instead, performance relies on sort keys, distribution keys, and columnar optimizations. Here’s what you should focus on:

1. Sort Key (as discussed above):

   • Stick with a compound sort key starting with DateOfPurchase, followed by your most frequently joined/filtered foreign keys (ProductTypeId, StatusTypeId, and any others you use heavily). This aligns perfectly with common OLAP query patterns for transaction data.

2. Distribution Key:

   • For CustomerTransaction (a large fact table), you have two strong options:
     • Auto Distribution: Let Redshift automatically manage distribution (this is the default for new tables). Redshift will analyze your data and query patterns to choose the optimal distribution strategy, which is great if you’re unsure or have mixed query patterns.
     • Key Distribution: If you frequently join CustomerTransaction with a specific large lookup table (unlikely here, since your lookup tables seem small), you could set the distribution key to match the lookup table’s primary key (e.g., ProductTypeId). But for small lookup tables, a better approach is to use ALL Distribution on the lookup tables themselves—this copies the entire lookup table to every Redshift node, so joins with the fact table don’t require cross-node data transfer.

3. Forget About Foreign Key "Indexes":

   • Unlike SQL Server, Redshift doesn’t create indexes for foreign keys. Foreign key constraints in Redshift are mostly logical (they don’t enforce referential integrity by default, unless you explicitly set VALIDATE), and they don’t improve query performance. Instead, your sort key and distribution strategy will handle join efficiency.

4. Column Encoding:

   • Apply appropriate encoding to reduce storage and speed up queries:
     • For integer columns with low cardinality (like ProductTypeId, StatusTypeId), use BYTE encoding to compress data significantly.
     • For DateOfPurchase, Redshift’s default DATE encoding is already optimal.
     • For PurchaseAmount, LZO or ZSTD encoding works well for numeric values with high cardinality.

5. Bonus Optimizations:

   • If you have recurring complex queries (e.g., monthly sales by product type and status), consider creating materialized views to precompute results. Just note that materialized views require maintenance when the underlying data changes.
   • Avoid overloading the sort key with too many columns—stick to the top 3-4 most frequently used filter/join columns to keep sort operations efficient.

内容的提问来源于stack exchange，提问作者user8280126