DWHPro Insights

Teradata vs. Redshift: A Comparison of Join Strategies and Architecture

By Roland Wenzlofsky ·

Teradata vs. Redshift: A Comparison of Join Strategies and Architecture

Teradata and Redshift share similar architectures and data distribution methods. Teradata’s AMPs store portions of table data, while Redshift utilizes slices.

There are notable differences in the way data is stored on file systems.

Row/column storage and join-maintenance trade-offs are workload- and deployment-dependent. This article does not establish universal row-store superiority or lower operating effort.

We will not delve into Teradata Columnar Storage in this comparison of join strategies, as Teradata converts the columns of a columnar table into rows before processing the data (early materialization). As a result, there is no difference in the processing of the joins, even if only the necessary columns are chosen for columnar tables.

Merge Join Preparation: Co-Locating Rows

Hashing can distribute data, but repeated keys and workload patterns can cause skew. Validate actual row and work distribution.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Teradata dictates that rows must be on the same AMP to be joined.

Redshift also requires data to be on the same slice, resulting in little disparity.

Preparation in Redshift resembles that of Teradata. Broadcasting in Redshift is the equivalent of copying the entire table to all AMPs in Teradata.

Redshift can redistribute intermediate rows for a join without altering the persistent table DISTKEY. Query redistribution is distinct from a table-design change.

In Teradata, it is possible that table redistribution may not be required if both tables share the same DISTKEY. Alternatively, both tables may require redistribution.

When copying or distributing tables, Redshift operates similarly to Teradata.

Redshift and Teradata vary in their approach to storing table copies on slices. While Redshift has the capability to do so permanently, it is only suitable for smaller tables. Thus, I do not believe this feature provides a significant performance advantage over Teradata.

Comparison Of Join Methods

The Teradata Merge Join

A merge join needs appropriately colocated and ordered inputs; redistribution and sorting can prepare spools even when base tables do not share a PI or distribution key.

For non-row-partitioned tables, rows are sorted by hash, enabling a binary search to occur from a hash value of the left table in the right table and vice versa. The joining of rows differs slightly depending on whether slow-path or fast-path merge joins are performed, but the method remains the same.

To compare partitions directly in Row-Partitioned Tables, it is necessary to join the partition columns, as the rows within each partition are sorted by hash value.

The Redshift Merge Join

Redshift requires both tables to have identical SORTKEYs. However, data sorting in Redshift is less sophisticated than in Teradata.

Redshift supports automatic sorting and vacuum maintenance. Manual work may still be needed for particular cases, but periodic manual sorting is not a universal requirement.

Redshift doesn’t employ a merge join if over 20% of the data is unsorted since sorting large amounts is costly.

Both systems utilize statistics for creating join plans. However, Redshift requires more maintenance due to the need to consider additional factors compared to Teradata.

Summary

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam