Phase 2: Data Storage

Redshift: distribution styles & sort keys

Intermediate ~3 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine a super giant library, bigger than any you've ever seen, with millions and millions of books! When you need to find a book, or even better, find all the books related to a certain topic or by a certain author, it could take ages if they're scattered everywhere. Your computer system, Redshift, is like a super smart librarian trying to organize all this information (which we call "data," like books) so that when you ask it a question ("Find me all the books about space travel written in the last 10 years!"), it can give you the answer super, super fast. One of the biggest tricks the librarian uses is deciding how to place the books across different rooms or sections of the library. This is called "distribution style."

Let's say our giant library has a few main rooms, and each room has its own librarian ready to help. If the head librarian uses the "Even" style, they just put books away one by one into Room 1, then Room 2, then Room 3, and so on, until all the rooms have roughly the same number of books. It’s fair, but if you want all the books by "Dr. Seuss," you might have to send a message to all the rooms because his books could be anywhere! For tiny, very popular books, like "The Cat in the Hat," the "All" style means the librarian makes a copy and puts that book in every single room. So, no matter which room you’re in, you can always find a copy right there. This is great for small, popular books, but you wouldn't do it for huge encyclopedias because you'd need too many copies and too much space. The cleverest style is "Key". Here, the head librarian picks a special rule, like "all books by the same author go into the same room." So, if you want all Dr. Seuss books, you know exactly which room to go to, and they'll all be there. Or, if you have books from a "series," all books from "The Magic Treehouse Series" go into one specific room. This way, when you need to find all related books, they are already together, which is super fast!

The "Key" style is often the best for making Redshift super speedy, especially when you often need to find groups of related information, like all the parts of one big order from an online store. If all the information about "Order #12345" is in the same room, it's much faster to gather than if parts of it were in Room 1, Room 2, and Room 3. Redshift also has an "Auto" setting where it tries to guess the best way to organize your books, which is helpful too. So, when you're helping design how information is stored in Redshift, choosing the right "distribution style" is like telling the super librarian the smartest way to arrange the books in the different rooms. This means when you or someone else asks a question, Redshift can zoom through the data and give you answers in a blink, instead of making the librarians run all over the giant library!

When designing tables in Redshift, choosing the right distribution style is crucial for query performance. Distribution styles dictate how data rows are spread across the compute nodes (and their slices) in your cluster. An optimal distribution minimizes data movement between nodes during query execution, especially for joins. - AUTO allows Redshift to manage distribution, often defaulting to EVEN or KEY based on data patterns. - EVEN distributes rows round-robin, ensuring even data spread but potentially requiring data movement for joins. - ALL copies the entire table to every node, beneficial for small dimension tables frequently joined with large fact tables, but consumes more storage and increases load times. - KEY distributes rows based on the hash of values in a chosen column. This is often the most performant for large fact tables, as it colocates matching rows from joined tables on the same node, drastically reducing data transfer over the network during joins. For instance, if orders and order_items tables are both distributed by order_id, then all data for a specific order_id will reside on the same node, making joins extremely efficient.

Complementing distribution styles are sort keys, which determine the physical order in which data is stored within each slice on disk. While distribution handles where data lives across nodes, sort keys manage how it's organized once it's there. Properly chosen sort keys significantly accelerate queries that filter on a specific range, perform aggregations, or involve joins where data needs to be merged. - COMPOUND sort keys sort data based on the order of specified columns. This is ideal when your queries frequently filter or join on the first one or two columns in the key. For example, sorting by (date, product_id) is excellent for queries filtering by date or date and product_id. - INTERLEAVED sort keys give equal weight to all columns in the sort key, which can be beneficial when queries filter on different combinations of columns within the key, or use columns out of order. However, INTERLEAVED keys are more complex to maintain (VACUUM and ANALYZE operations take longer) and consume more disk space, so COMPOUND is generally recommended first.

The synergy between distribution styles and sort keys is key to maximizing Redshift's performance. For large fact tables, a common strategy is to use KEY distribution on a column frequently used in joins (like customer_id or order_id) to ensure related data is on the same node. Then, apply a COMPOUND sort key on columns frequently used for filtering or time-series analysis (e.g., event_timestamp, status). Always analyze your most critical queries to identify the columns that are most often filtered, joined, or grouped by. Use the EXPLAIN command to see the query plan and identify potential bottlenecks, especially data distribution warnings or large DS_BCAST_GATHER steps, which often point to sub-optimal distribution. Regularly monitoring STL_QUERY and STV_WLM_QUERY_INFO can further inform your tuning efforts.

Key Takeaways

  • DISTKEY determines how data is spread across compute nodes, crucial for efficient joins.
  • SORTKEY defines the physical order of data within nodes, speeding up filtering and range scans.
  • Use KEY distribution on common join columns to colocate related data and minimize network I/O.
  • COMPOUND sort keys are generally preferred for speeding up queries filtering on leading sort key columns.
  • Analyze query patterns and use EXPLAIN to identify and optimize distribution and sort key choices.

Code Example

sql
CREATE TABLE fact_sales (
    sale_id           BIGINT NOT NULL,
    product_id        INTEGER NOT NULL,
    customer_id       INTEGER NOT NULL,
    sale_date         DATE NOT NULL,
    quantity          INTEGER,
    price             DECIMAL(10, 2)
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (sale_date, product_id);

How this code works

This code establishes a fact_sales table within Redshift, meticulously structured to optimize query performance by strategically distributing and sorting data. The DISTSTYLE KEY clause, combined with DISTKEY (customer_id), is crucial here. It instructs Redshift to distribute rows across its compute nodes based on the customer_id value. This ensures that all sales records for a specific customer reside on the same node. This design choice is particularly effective for queries that filter by customer or join fact_sales with a customer dimension table, as it minimizes the need for data to travel between different nodes, significantly speeding up those operations.

Beyond distribution, SORTKEY (sale_date, product_id) dictates how the data is ordered within each node. Rows are first sorted by sale_date, and then by product_id for records sharing the same date. This physical ordering is a powerful optimization: when queries filter by date ranges or group by product_id, Redshift can quickly locate relevant data blocks and skip over irrelevant ones, reducing the amount of data it needs to scan. The other columns like sale_id, quantity, and price simply define the attributes stored for each sales transaction.

Related Articles

Hand-picked reads to go deeper on cloud data warehouses.

Back to Data Engineer Roadmap