What is CompressionLevel?

The best minds from Teradata, our partners, and customers blog about relevant topics and features.
Teradata Employee

If you are using block-level compression (BLC) you may have glanced over the list of 40+ DBS Control parameters that influence how this feature is behaving on your platform. Be aware that most of these parameters have been filled in with reasonable defaults for your system, and with some exceptions, you are not encouraged to experiment with them.


One parameter that might be particularly tempting to change is called CompressionLevel. This posting describes what the compression level parameter is and why you do not want to casually start increasing it.


This posting applies to Teradata Database 16.10 and greater releases.


Before jumping into a discussion on compression level, be aware that compression parameters take effect without having to perform a database restart. If you change compression parameters and want them to be applied to a table that has already been compressed, use the Ferret COMPRESS command on the table. After doing so, the whole table is recompressed using the new tunable values. Otherwise, changes to the parameters only take effect when compressed blocks are updated, and then only on the particular data blocks being updated.


Compression Level

CompressionLevel, which can be expressed in a range of '1' to' 9', directs the compression operation how to balance the speed of the operation and the degree of compression that results. If CompressionLevel is set to '1' compression algorithms will execute in way to favor speed, by minimizing the CPU applied to do the compression. As a result of favoring speed, the degree of compression is expected be less with the '1' setting because less effort is being applied.


As you increase the CompressionLevel parameter value, more time is spent on the compression operation so greater compression may be achieved, but at the expense of more CPU and a longer elapsed time. The maximum value for the CompressionLevel parameter is '9'.


As you increase the parameter from the default of '1', the CPU used for compression gradually grows. More CPU used during the compression operations on data blocks is likely to lengthen the time to load data into the database.


There are a couple of things to note about this parameter:


  • Previous to 16.10, the default for CompressionLevel was '6'.  The default setting was changed starting in the 16.10 from '6' to '1', as '1' has been found to provide a better point of balance between CPU usage for the compression operation and degree of compression achieved.


  • This setting is ignored if CompressionAlgorithm, another parameter in DBS Control, is not set to ZLIB.


What to Expect Increasing Compression Level

Increasing the compression level across different test and production systems has not led to favorable results. In cases where Teradata sites have increased CompressionLevel from '1' to '6' only a mere 5% improvement in space savings has been achieved. However, with that change the CPU used increased by about 30%.


With smaller adjustments upward, for example from '1' to '3', the compression level parameter hasn't shown much change either way. In one test case, space savings improved only 0.8% while the CPU overhead for compression went up 0.9%. That's not a significant enough difference to make it worthwhile to deviate from the default of '1'.


The impact of changing the compression level is very dependent on the data and its characteristics. You could see more, or less change on either the CPU overhead side or the space savings side, than reported in the cases above. There is no way to predict ahead of time, which is a reason to be cautious.


The impact of this setting is not just on the compression side when data blocks are being written. In order to read data that has been compressed with BLC, the data blocks have to be decompressed. Decompression of BLC-compressed data comes with some CPU overhead, although it is generally a fraction of the CPU required for compressing the same data. Increasing CompressionLevel is likely to increase the CPU cost of decompression when reading BLC-compressed data.



If you are on a platform where all user tables are universally compressed, it is even more important to be understand the tradeoffs and be cautious of increasing the CompressionLevel parameter. In such cases, the side-effects of the change will have a wider impact.


Because the increase in CPU time involved in compression is disproportionately higher than the advantage realized in space savings when this setting is increased, it is recommended that the default setting for CompressionLevel remain unchanged at '1' if you are on 16.10 or above. I advise anyone considering a modification to this setting to speak with the support center as a first step.    


Thanks Carrie !!


My question is : If we are on older version where our data compress with level 6 and later we are going to TD 16.10. In that case, One update statement ( for expample) may increase the size of old compress table in 16.10 & recreated table from old table will also have greater in size ?  Is it applicaple for Temprature base BLC ?



Teradata Employee



If you have changed the CompressionLevel setting in DBS Control prior to moving to 16.10, then that change will be honored and carried forward to 16.10.  If you have stuck with the default setting pre-16.10 (which is 6), then you will get the new default setting (which is 1) in 16.10.  This is how most DBS Control parameters work.


So assuming you have been using the default setting, when you get onto 16.10 you will be using a lower CompressionLevel value. However, you will not have any change in your established BLC tables until such time as individual data blocks are actually updated. Only data blocks that have been changed (and therefore have to be rewritten) will use the new CompressionLevel value.  At that time you may see some very slight increase in data block size when the data block is re-compressed.  As I said in the posting, it will depend on the patterns in your data, the data block size, etc.  However, we do not expect very much of an increase, and if it happens very gradually you are not likely to notice much of a change. 


If you were to use the Ferret COMPRESS command on the table, then the new compression level will be applied to all data blocks.  In that case the whole table could take a little more space, like 5% give or take.  But the good news is that compression (and decompression) will use less CPU and happen faster.


Yes, compression level changes will impact data blocks compressed by TBBLC.  But with TBBLC, only the data blocks that are compressed will be impacted.


Thanks, -Carrie