DWHPro Insights

The Importance of PDM in Database Compression and Resource Usage Optimization

By Roland Wenzlofsky ·

The Importance of PDM in Database Compression and Resource Usage Optimization

The importance of the PDM

Compression is a widely used method for reducing IOs by enabling the accommodation of more rows in each data block. Data blocks are the smallest unit exchanged between hard drives and main memory, which is the most costly operation in any database system.

Compression can be automated through block-level compression or executed manually through the MVC approach. Additionally, compression software, like the one provided by Prise Ltd. (accessible at https://www.prisetools.com/products/compress-wizard), is also an alternative.

Developers frequently overlook various resource-saving techniques besides Multivalue Compression (MVC). The proper implementation of data types and character sets, known as PDM, is frequently neglected.

First, the PDM, then Compression

Compression relies on the specific data types utilized. Hence, before commencing any compression process, confirming that the correct data types and character sets are used in the PDM is essential. Otherwise, any modifications made to the PDM will require recompressing.

Choosing the appropriate data types and character sets can effectively reduce resource usage and offer benefits that Compression cannot provide.

MVC is most useful for repeated values; other algorithms can compress structure even when values are distinct. Do not generalize MVC limitations to all compression.

Choosing appropriate data types can reduce resource usage by allowing more rows to be packed into each data block.

Choosing appropriate data types and character sets is integral to the process of physical data modeling. Regrettably, this crucial step is frequently overlooked due to the upfront expenses incurred during implementation.

Data types are commonly transferred from the staging area to the PDM of the core model and subsequently to the data marts.

Although this method initially reduces expenses during the implementation process, we advise against it. The expenses will merely be deferred to a later phase, when modifying the PDM becomes significantly more complex.

DECIMAL storage depends on precision: DECIMAL(38,n) uses 16 bytes, not eight. Choose a smaller precision/scale only after proving it preserves the complete required numeric range and precision.

When implemented as suggested above, an optimized decimal requires 4 fewer bytes per row in a table. Imagine the amount of space saved in a table with billions of rows, and remember that this enhancement occurs before compression techniques are applied.

LATIN can reduce character storage when the complete required domain is representable. Retain UNICODE where needed; a size optimization must not lose characters.

As a Teradata Consultant, omitting this vital optimization step is not advisable. Although creating the PDM is time-consuming, it will yield significant benefits in the long run.

I believe I have presented sufficient justifications to prioritize these activities. Please keep them in mind for your upcoming project.

Feel free to ask any questions in the comments. I will be monitoring and responding to as many as possible. Thank you for taking the time to read this.

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

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

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

1 thought on “The Importance of PDM in Database Compression and Resource Usage Optimization”

Leave a Comment

Get more practical data engineering advice

Join the DWHPro newsletter for practical data engineering tips, troubleshooting examples and data platform insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam