This post will contrast the architecture of two widely-used database systems.
The similarities between Teradata and Amazon Redshift are notable, as much of your knowledge about Teradata can be applied to Amazon Redshift.
Although Amazon Redshift stores data in columns permanently, the similarities remain significant.
Teradata can store data in columns, though it was not originally intended for this and has some inherent design drawbacks.
How does the high-level architecture of Amazon Redshift compare to Teradata?
Redshift slices are subdivisions of compute-node resources, not independent computers with dedicated physical disks. Managed storage and serverless deployments change the compute/storage relationship. Compare the actual deployment rather than assuming a one-to-one equivalence to Teradata nodes, AMPs, PEs and BYNET.
How is the data of a table distributed in Amazon Redshift?
Redshift KEY distribution hashes the DISTKEY to place rows; EVEN and ALL are other distribution styles, with AUTO choices available. A DISTKEY is a distribution key, not a Teradata-style primary-index lookup structure.
What makes Teradata Columnar different from Redshift Columnar?
Amazon Redshift distributes the data across all slices and divides them into columns. The columns of a row are assigned to blocks so they can easily be found together again. Each block contains metadata that stores the value range of the block. This helps not to read blocks if they do not include the values you are looking for.
Teradata offers columnar table storage in different ways, but it retrieves the data from the column containers, decompresses it, and reconstructs rows. In a subsequent step, the row-based traditional database engine works but operates on the already decompressed data. This shows that Teradata was not initially designed as a columnar database.
Is there an equivalent to Teradata Partitioned Primary Index Tables?
A Redshift sort key organizes data and can support block pruning. It is not a Teradata row partition, and the two designs are not interchangeable.
Are there secondary indexes in Amazon Redshift?
No, but let’s be honest:
How often do you use a NUSI or USI in Teradata? And if so, isn’t it always hard to design it to be used? Statistics must be correct; selectivity must be suitable, etc.
Amazon Redshift uses an ingenious method for performance tuning: The value range is saved in metadata for each data block. This allows Amazon Redshift to restrict the search to blocks that match the WHERE condition.
What do Amazon Redshift data blocks look like?
Redshift uses 1 MB columnar blocks and metadata for pruning. The former claim that blocks grow and split into two equal halves like general dynamic Teradata blocks was unsupported.
How do joins work in Amazon Redshift?
Matching rows must be brought together for join processing. Compatible distribution can avoid redistribution; DISTSTYLE ALL stores a copy per compute node, not per slice. Replication adds storage and load/maintenance cost and is workload-dependent.
Is there an equivalent to Teradata NOPI Tables in Amazon Redshift?
Yes, we can evenly and randomly distribute data across all slices without distkey hashing:
CREATE TABLE Customer
(
CustomerId INTEGER NOT NULL,
Customer_Name CHAR(100),
OpenDate DATE NULL sortkey
) DISTSTYLE EVEN;
Just as Teradata must instruct all AMPs in NOPI tables to search for data, it is necessary to search on all slices when using DISTSTYLE.
How does workload management differ between Teradata and Redshift?
Redshift supports automatic WLM with priorities and automatic memory/concurrency decisions, as well as manual WLM. Concurrency scaling supports eligible read and write workloads under documented restrictions. Managed storage and serverless mean compute and storage are not universally coupled. Measure queueing and cost for the actual workload.
Space Management on Redshift?
Redshift performs automatic vacuum deletion and automatic sorting. Manual VACUUM can still be useful for particular workloads, but cleanup is not universally manual-only.
As you have seen, the similarities between Teradata and Amazon Redshift are remarkable. This does not mean that there are no significant differences in the implementation.
But for you as a user, it will not be too difficult to switch to Amazon Redshift and understand how the system works.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
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
Excellent . Same thinking . U r a genius . ppl like me get satisfaction when thoughts r matching with people like u .
One Q . If oracle removes GCS for data warehouse appliance , applies similar exclusive lock then it is the Best of the best . What is ur opinion.
I was talking abt Exadata or oracle autonomous in cloud..