[DATA][ENG.] 3. On Column Data Sorting Speed in AWS Redshift and Snowflake

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 stateSo 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:

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
  

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:

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

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