Is Teradata Compression Worth Your Time and Effort?
Teradata DBMS offers various compression options to save disk space and improve performance. But, the cost of implementing the compression takes man-time. This article discusses the cost vs. benefit of compression and suggests carefully considering the compression setting when designing new or modif
Teradata DBMS has consistently supported compression in various forms.
Multi-Value Compression (MVC) was introduced in version 5.0, Algorithmic (ALC) and Block Level (BLC) compression in version 13.10, and version 14.0 introduced various automatic compression options for Columnar tables.
Implementing Teradata compression with MVC or ALC can lead to savings in disk space and improved performance. However, it requires a significant investment in manpower, which can be expensive.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
It is important to remember that data demographics change over time, necessitating regular revisiting of compression analysis, which may occur annually.
Get the next issue by email.
Are the benefits of dedicating time to compression worth the costs? While certain tools may assist with compression, the purpose of this article is to raise this question. As with most matters, it boils down to a "cost versus benefit" analysis.
- They have about 98000 tables.
- About 26000 tables have MVC implemented.
- On their system, only 4000 tables are big enough that a full table scan is estimated to take longer than 1 second – even allowing for a reduced scan rate because of concurrent queries.
It is unnecessary to remove the current MVC compression for small tables. MVC compression does not have any CPU overhead, and the negligible benefits of eliminating compression on multiple tables are not worth the effort. However, some savings may be achievable if ALC is used on a small table.
When designing or modifying tables, carefully considering the compression setting is advisable.
If the table is small, compression may not be needed. This is a matter of process alteration rather than remediation.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.