Reduce disk space by compressing tables and fragments

You can reduce disk space by compressing data in tables and table fragments. After compressing data, you can repack the data to consolidate the free space in a table or fragment, and shrink the space for the data to return the free space to the dbspace.

Compression is advantageous for applications with a lot of I/O activity and for applications in which the reduction of disk space usage is critical. However, if your applications run with high buffer cache hit ratios and high performance is more important than space usage, you might not want to compress data, because compression might slightly decrease performance.

Compressing data, consolidating data, and returning free space have the following benefits:

• Significant savings in disk storage space
• Reduced disk usage for compressed fragments
• Significant saving of logical log usage, which saves additional space and can prevent bottlenecks for high-throughput OLTP after the compression operation is completed.
• Fewer page reads, because more rows can fit on a page
• Smaller buffer pools, because more data fits in the same size pool
• Reduced I/O activity, because:
  • More compressed rows than uncompressed rows fit on a page
  • Log records for insert, update, and delete operations of compressed rows are smaller
• Ability to compress older fragments of time-fragmented data that are not often accessed, while leaving more recent data that is frequently accessed in uncompressed form
• Ability to free space no longer needed for a table
• Faster backup and restore

Because compressed data covers fewer pages and has more rows per page than uncompressed data, the query optimizer might choose different plans after compression.

You can speed up compression and repacking by running the operations in parallel.