DWHPro Insights

Teradata Compression and Space Management

By Roland Wenzlofsky ·

Learning path: Compression and space. View the suggested reading order. This reference includes release-specific examples; check the version context before applying a technique.

Teradata gives you more than one way to store the same data in less space, and they behave very differently once queries start reading it back. This guide brings together the compression material on this site: what multi-value and block level compression each do, how to find the columns worth compressing, and the trade-off you accept in CPU.

Multi-Value and Block Level Compression Compared

Introduction to Teradata Compression

Block-level compression availability, defaults and controls depend on the deployment and release. Check the applicable documentation and actual table/block state before describing BLC as enabled or disabled.

With Teradata’s introduction of block-level compression, the utilization of multi-value compression at various Teradata sites has decreased. At first glance, disregarding the use of MVC appears logical. However, identifying the most appropriate prospects for MVC involves considerable effort, which is frequently not reasonable in the current era where the storage cost is decreasing, but consultation is costly.

From the start, I want to clarify one thing: MVC and BLC serve distinct application purposes.

MVC aims to enhance the performance of queries by compressing data into blocks for processing. Additionally, it naturally reduces disk space requirements.

BLC can improve query performance by reducing physical I/O, although compression and decompression consume resources. The net effect depends on the workload, algorithm and hardware. It is not exclusively a cold-data feature.

Interaction of BLC and MVC

The historical experiment compared four intended configurations: neither compression method, MVC only, BLC only and both. These controls are deployment-specific. A successful SET QUERY_BAND does not prove the resulting blocks have the intended compression state; verify that state before comparing measurements.

In MVC, the columns and compression were carefully selected to ensure that nearly everything is compressed.

Our test setup comprises four tables, as depicted below.

SET QUERY_BAND = 'BLOCKCOMPRESSION=NO;' FOR SESSION;
CREATE SET TABLE DWHPRO.Customer_MVC
(
 CustomerId BIGINT NOT NULL,
 CategoryCd CHAR(100) COMPRESS ('PLATINUM','GOLD','SILVER','BRONZE')
)
PRIMARY INDEX ( CustomerId );
SET QUERY_BAND = 'BLOCKCOMPRESSION=NO;' FOR SESSION;
CREATE SET TABLE DWHPRO.Customer_NONE
(
 CustomerId BIGINT NOT NULL,
 CategoryCd CHAR(100)
)
PRIMARY INDEX ( CustomerId );
SET QUERY_BAND = 'BLOCKCOMPRESSION=YES;' FOR SESSION;
CREATE SET TABLE DWHPRO.Customer_BLC_MVC
(
 CustomerId BIGINT NOT NULL,
 CategoryCd CHAR(100) COMPRESS ('PLATINUM','GOLD','SILVER','BRONZE') 
)
PRIMARY INDEX ( CustomerId );
SET QUERY_BAND = 'BLOCKCOMPRESSION=YES;' FOR SESSION;
CREATE SET TABLE DWHPRO.Customer_BLC
(
 CustomerId BIGINT NOT NULL,
 CategoryCd CHAR(100)
)
PRIMARY INDEX ( CustomerId );

To be concise, we added 10 million rows to each table for greater significance. The CategoryCd column only includes four values: ‘PLATINUM’, ‘GOLD’, ‘SILVER’, and ‘BRONZE’.

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

Comparing the Disk IOs

The disk input/output operations result from performing a complete table scan without using indexes.

Teradata BLC
IOs needed for each combination of compression

By solely analyzing the disk IOs, we can deduce the following from the outcome:

  • Compression should be chosen from measured space savings and workload costs; this example does not establish that every uncompressed table is incorrectly designed.
  • We should combine block-level compression with MVC

Our test optimized the MVC setup to allow for the compression of nearly everything. However, in actual environments, such optimization may not be feasible.

Comparing the CPU Seconds

We will now investigate how varying compression techniques impact CPU consumption.

teradata MVC
Adding BLC to the optimized MVC does not benefit IOs but increases the used CPU seconds.

Here is the emerging picture:

  • Compression should be chosen from measured space savings and workload costs; this example does not establish that every uncompressed table is incorrectly designed.
  • BLC adds compression/decompression work but may reduce I/O enough to improve elapsed time. Measure the trade-off rather than restricting BLC categorically to cold data.

Comparing the Space Usage

space

If we focus solely on permanent space savings, the following image arises:

  • Compression should be chosen from measured space savings and workload costs; this example does not establish that every uncompressed table is incorrectly designed.
  • MVC and BLC together have about the same space savings as BLC alone.
  • Even the highly optimized MVC setup we chose does not achieve space savings on its own as BLC alone or a combination of both.

Summary

Although the results may appear straightforward, it’s important to approach them with caution. Our scenario was designed to optimize MVC, which may not always be feasible in real-world situations. The benefits of implementing additional MVC are also limited if BLC is already enabled on most systems.

Additionally, there are few automated tools available for MVC, and the cost of hiring a consultant is no longer proportional to the price of mass storage. The days of Teradata consultants spending weeks on MVC to save costly disk space seem behind us.

It looks different here if you examine the whole thing from the CPU side. If we deal with a CPU-bound system, MVC can make sense to bring load off the machine.

The incremental benefit of MVC with BLC depends on the data and workload; the historical I/O result does not establish that MVC has no value elsewhere.

Evaluate MVC when its measured storage, CPU or I/O benefit justifies implementation and maintenance. High CPU usage is one possible reason, not the only valid reason.

Finding Candidate Columns for MVC

Analyzing a large Teradata table with Multivalue compression incurs high workload costs.

Unfortunately, Teradata does not provide any mechanism to automate this undertaking.

Analysis typically involves tallying unique values in each table column, a time-consuming and resource-intensive process for large tables.

Fortunately, Teradata 14.10 offers a more cost-effective solution.

SHOW STATISTICS VALUES COLUMN <column> ON <table>;

This statement displays biased column values and other useful statistics. Biased values are often ideal for Teradata Multivalue compression.

This approach’s main benefit is the absence of a costly table analysis, which contrasts with the method of counting distinct column values for each column of a large table. Despite this, it still provides exceptional compression recommendations.

Considering the count of NULL values in each column is an effective starting point to reduce space usage swiftly.

While effective in producing swift outcomes, this approach is not without its downsides:

  1. It only works for columns with collected and correct statistics.
  2. Unfortunately, the information used in the SHOW STATISTICS statement is taken from a binary object (“FieldStatistics”). There is no easy way to extract this information. As a substitute, we have to use the text output of the statement and parse it, to be able to automate the process of creating compression statements.

Saving Space with Block Level Compression

BLC defaults vary by deployment. Additional MVC savings depend on the value distribution and physical storage configuration; do not assume a fixed compression factor.

How Block Level compression and MultiValue compression relate to each other is shown in detail in the article below:

In this article, we will show you a trick on how to use Block Level compression together with Teradata Row Level Partitioning to save space.

To comprehend this, it is crucial to grasp the functioning of Block Level Compression and Row Partitioning.

BLC packs whole data blocks using different algorithms. These include ZLIB (software compression) and ELZS_H (hardware compression), used in 9 different compression levels.

This section focuses on space savings. BLC can also affect performance through both reduced I/O and compression/decompression costs.

BLC achieves better compression when equal values are grouped together in a data block. Consider a table with a date field and a unique primary index (UPI).

Primary-index hashing and the applicable map place rows on AMPs. A UPI prevents duplicate index values but does not guarantee perfectly even AMP distribution or a particular physical ordering of dates.

Let’s discuss how data in a Row Partitioned Table is distributed. Initially, the data is distributed to the AMPs based on the UPI. Following that, the rows are sorted into the designated partitions according to the partition expression.

For our example, we can base the partition expression on the date. The aim of Row Partitioning is to store rows with identical dates in the same partition. This ensures they are physically adjacent on the disk, enabling efficient access.

This means that better compression and space savings can be achieved simultaneously. The compression algorithms achieve higher compression rates because the date values are stored close together within the same data blocks.

In practice, ideal conditions can result in up to 50% smaller tables.

It is important to consider the overall design when utilizing row partitioning. While it may be appropriate for temporary tables, it should not be used solely for space-saving purposes on production tables.

In the future, should you encounter insufficient space again, it would be worthwhile to contemplate this alternative.

Compression only pays off relative to what the row costs uncompressed: how the data type decides the stored width.

What compression is competing against, and where else it shows up: the pattern that shrank an already-compressed table to a tenth, run-length, dictionary and delta compression in Teradata Columnar and how columnar storage behaves in the cloud.

Related storage reading: block-level compression and what it saves, multi-value compression in practice and how the data type sets the uncompressed width.

Reproducibility note

The charts in this article are historical observations from the stated data setup. Current checks confirmed the corrected table definitions and row retrieval, not the ten-million-row storage/CPU benchmark. Treat its percentages as case-specific rather than current platform guarantees.

Articles in this guide

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

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.

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