> ## Content Index > Fetch the complete content index at: https://www.mekeywhydoesmatter.com/llms.txt > Use this file to discover other available public pages before exploring further. # [DATA][ENG.] 3. On Column Data Sorting Speed in AWS Redshift and Snowflake - URL: https://www.mekeywhydoesmatter.com/data-eng-3-on-column-data-sorting-speed-in-aws-redshift-and-snowflake/ - Published: 2026-09-02T14:29:01.000Z - Updated: 2026-09-02T14:29:01.000Z - Author: mekey - Tags: Data To start with the conclusion, sorting by a specific column using `ORDER BY` in Amazon Redshift or Snowflake is often at a disadvantage compared to traditional OLTP DBs (such as PostgreSQL, Aurora). However, with specific configurations in Redshift or Snowflake, column value sorting speeds can actually be extremely fast. ### 1\. Why AWS Redshift is considered disadvantageous for sorting The core reason is that Redshift is a "Columnar Data Warehouse."Redshift has a Columnar + MPP (Massively Parallel Processing) architecture.In other words, data is stored as follows: ``` Node1 block1 block2 Node2 block3 block4 ``` And each node processes data in parallel. The problem is `ORDER BY`. ``` SELECT * FROM sales ORDER BY purchase_date ``` In this case, the following internal process is required: 1. Partial sorting at each node 2. Data shuffle between nodes 3. Final merge sort Namely: ``` node1 sort node2 sort node3 sort ↓ network shuffle ↓ merge sort ``` Therefore, sorting the entire dataset is highly expensive. ### 2\. Conversely, why are OLTP DBs fast? Take, for example: - PostgreSQL - Amazon Aurora These databases typically have the following structure: - single node - row store - b-tree index If a query has an `ORDER BY purchase_date` and an index exists:`index scan → already in a sorted state`So there is almost no sorting cost. ### 3\. Cases where sorting is fast in Redshift Redshift is powerful when a SORT KEY is present. *Example:* ``` CREATE TABLE sales ( user_id INT, purchase_date DATE, amount INT ) SORTKEY(purchase_date); ``` In this case, data is stored like this: ``` block1 purchase_date 2023-01 block2 purchase_date 2023-02 block3 purchase_date 2023-03 ``` So, if you run: ``` SELECT * FROM sales ORDER BY purchase_date LIMIT 100 ``` It only reads the first block.Meaning, it is **extremely fast**. ### 4\. Common mistakes made in Redshift - **ORDER BY without a SORTKEY** - `ORDER BY created_at` → Sorts the entire dataset → Very slow - **ORDER BY without LIMIT** - `ORDER BY created_at` → Sorts the entire dataset - **SELECT \*** - In a columnar DB, `SELECT *` is also highly expensive. ### 5\. How to speed up sorting in Redshift **(1) SORT KEY Design** (Most important) - `SORTKEY(event_time)` - `SORTKEY(user_id)` **(2) Use LIMIT** - `ORDER BY event_time LIMIT 100` **(3) Consider DISTKEY** - Reduces node-to-node shuffling. **(4) Pre-aggregation table** - In a DW, it is common to move from a `raw table` → `aggregated table`. ### 6\. Real-world Redshift sorting strategies Usually, it is done like this: - Fact table: `SORTKEY(event_time)` - Query: `ORDER BY event_time LIMIT N` - Or use a materialized view (already sorted). To summarize, performance typically looks like this: | **Task** | **Aurora** | **Redshift** | | --------------------------- | ---------------- | ------------------ | | **Querying a specific row** | Aurora dominates | | | **Large-scale aggregation** | | Redshift dominates | | **ORDER BY (no index)** | Aurora | | | **ORDER BY (sortkey)** | | Redshift | Now, let's look at **Snowflake**. `ORDER BY` performance in Snowflake shares some similarities with Amazon Redshift, but it is structurally a bit different. To start with the conclusion: While Snowflake is also fundamentally at a disadvantage compared to OLTP DBs for sorting queries, it is more flexible than Redshift depending on the situation thanks to **micro-partition pruning** and **clustering**. I will explain this structurally below. ### 7\. Snowflake's storage structure Snowflake is a columnar DW like Redshift, but its storage method differs. **Redshift:** ``` node └ block ``` **Snowflake:** ``` micro-partition (approx. 50~500MB) ``` *Example:* ``` partition 1 purchase_date 2023-01 partition 2 purchase_date 2023-02 partition 3 purchase_date 2023-03 ``` Each micro-partition contains min/max metadata: - `min_date` - `max_date` - `distinct values` Thus, during queries, **partition pruning** occurs. ### 8\. How ORDER BY is executed in Snowflake *Example:* ``` SELECT * FROM sales ORDER BY purchase_date ``` Execution process: 1. scan micro-partitions 2. distributed partial sort 3. final merge sort Structurally, it looks like this: ``` worker sort worker sort worker sort ↓ merge sort ``` This is similar to Redshift. Therefore, doing an `ORDER BY` on an entire table is also highly expensive in Snowflake. ### 9\. However, the big difference from Redshift - **Redshift:** Physical sorting based on a SORT KEY - **Snowflake:** Automatic clustering + pruning | **Feature** | **Redshift** | **Snowflake** | | ---------------------- | --------------- | ------------------ | | **Data Sorting** | SORTKEY | Clustering | | **Auto Sorting** | Requires vacuum | Automatic | | **Partition Metadata** | Limited | Extremely powerful | ### 10\. Cases where ORDER BY is faster in Snowflake **(1) Presence of LIMIT** ``` SELECT * FROM sales ORDER BY purchase_date LIMIT 100 ``` In this case, **top-k optimization** activates, so a full sort is not required. **(2) Presence of a clustering key** ``` ALTER TABLE sales CLUSTER BY (purchase_date); ``` Then the micro-partitions will be sorted somewhat like `2023-01`, `2023-02`, `2023-03`. **(3) WHERE + ORDER BY** ``` SELECT * FROM sales WHERE purchase_date >= '2024-01-01' ORDER BY purchase_date ``` In this case, **partition pruning** reduces the amount of data subject to sorting. ### 11\. Real performance differences (Snowflake vs. Redshift) General trends: | **Query Type** | **Snowflake** | **Redshift** | | ----------------------- | ------------- | -------------- | | **Full table ORDER BY** | Slow | Slow | | **ORDER BY + LIMIT** | Fast | Fast | | **ORDER BY + Filter** | Fast | Medium | | **SORTKEY Sorting** | \- | Extremely Fast | ### 12\. Snowflake's advantages over Redshift Thanks to its micro-partition metadata, Snowflake is especially strong at `WHERE + ORDER BY`. *Example:* ``` SELECT * FROM logs WHERE event_date >= '2025-01-01' ORDER BY event_date LIMIT 100 ``` Snowflake can utilize **partition pruning** to skip \~90% of partitions. ### 13\. Overall sorting performance comparison | **System** | **Key Feature** | | -------------- | ----------------------- | | **PostgreSQL** | Index-based sorting | | **Aurora** | Index scan | | **Redshift** | SORTKEY | | **Snowflake** | Micro-partition pruning | ### 14\. Real-world Data Platform Architecture Perspective In Data Warehouse systems, they are typically used like this: - **Aurora** → OLTP lookups - **Redshift / Snowflake** → Analytics Therefore, queries that require a full table `ORDER BY` in a DW are usually handled via: - BI tools - Top N selection - Aggregation **Note:** The top 3 most expensive queries in large DWs are typically: 1. `ORDER BY` 2. `DISTINCT` 3. Global join shuffle