Data Compression in SQL

How to Store More and Query Faster in SQL

Mar 14, 2025

In modern database systems, data volumes are growing rapidly, and managing storage efficiently has become a critical challenge. Data compression plays a key role in reducing storage costs, improving query performance, and optimising resource usage. By minimising the physical size of stored data, compression helps reduce disk I/O, which can significantly speed up queries (especially for large datasets).

SQL databases offer various built-in compression techniques, designed to handle different types of workloads. Some methods are optimised for transactional databases (OLTP), while others are better suited for analytical processing (OLAP). Choosing the right compression strategy depends on factors such as data structure, read vs. write frequency, and performance requirements.

In this post, we’ll explore:

What is Data Compression?
Benefits of Data Compression in SQL.
Types of Compression in SQL.
Comparison of SQL Compression Techniques.
When to Use Compression and When to Avoid.

By the end, you’ll have a solid understanding of how SQL databases handle compression and how you can apply it effectively to optimise your data storage and performance.

What is Data Compression?

Data compression is the process of reducing the physical storage space required to store data in a database. It achieves this by removing redundancy, encoding repeating values more efficiently, or restructuring data storage formats to optimise space usage. In relational databases, compression plays a crucial role in improving storage efficiency, query performance, and overall system resource utilisation.

How Compression Works in SQL Databases

Relational databases store data in tables, where each row has multiple columns. These tables are structured into pages (blocks of data read and written together), and when a query runs, the database engine retrieves the required pages from the disk.

Compression reduces the number of pages needed to store the same amount of information, which leads to:

Faster queries (less data to scan and transfer).
Lower storage costs.
Improved memory utilisation.

Compression in OLTP vs. OLAP Workloads

SQL databases typically serve two main types of workloads, and compression behaves differently in each:

OLTP (Online Transaction Processing)
- Used in applications with frequent inserts, updates, and deletes (e.g., banking, e-commerce).
- Compression must balance space-saving with write performance (too much compression can slow down transactions).
- Row-level and page-level compression are commonly used.
OLAP (Online Analytical Processing)
- Used for large-scale data analysis and reporting (e.g., dashboards, business intelligence).
- Compression is highly beneficial because data is read more often than written.
- Columnar compression (like Columnstore indexes) provides significant performance gains.

For more details about row-based and columnar storage, you can check out the below post.

11 Storage Formats for Data Engineers

Pipeline to Insights

Jan 2

Read full story

How SQL Databases Handle Compression

Different relational databases have built-in compression mechanisms, often optimised for specific use cases:

SQL Server: Supports row, page, and column store compression.
PostgreSQL: Uses TOAST (The Oversized-Attribute Storage Technique) for large data types.
MySQL: Supports compressed InnoDB tables using zlib compression.
Oracle: Implements Hybrid Columnar Compression (HCC) for analytical workloads.

Benefits of Data Compression in SQL

Data compression is more than just a way to save disk space. It plays a vital role in improving query performance, reducing costs, and optimising resource usage in relational databases.

1. Reduced Storage Costs

Compressed data takes up less space, leading to lower disk storage requirements. This is especially valuable for:

Cloud databases (where storage costs scale with usage)
On-premise databases with limited disk capacity
Data warehouses that store large historical datasets

2. Faster Query Performance

Compression reduces I/O overhead, meaning fewer data pages need to be read from disk. This results in:

Faster SELECT queries
Reduced memory usage, allowing more data to fit into the cache
Improved backup and restore speeds

3. Optimised Disk and Network Usage

Compressed data not only takes up less disk space but also reduces network transfer times, which is very useful when:

Replicating databases across servers
Running distributed queries on cloud platforms
Performing backups and migrations

4. Improved Performance in OLAP

For data warehouses and analytics, compression is often a must-have.

Columnstore compression in SQL Server and PostgreSQL allows databases to store columnar data in a more compact format.
Run-length encoding (RLE) and dictionary compression can drastically reduce storage needs for repetitive values.

Types of Data Compression in SQL Databases

SQL databases provide several built-in compression techniques, each designed to optimise storage and query performance in different ways. The best choice depends on the data structure, workload type (OLTP vs. OLAP), and compression trade-offs.

1. Row-Level Compression

Row compression reduces space used by each row by eliminating extra padding and storing fixed-length data types in a variable-length format.

In a regular SQL table, a column defined as CHAR(50) always takes up 50 bytes, even if the actual text is much shorter.
With row compression, SQL stores only the required number of bytes, just like a VARCHAR field.
It also optimises numeric and NULL values, reducing unnecessary storage for zeroes and blank spaces.

Best for: Transactional databases (OLTP) where frequent inserts and updates occur.

Storage savings: Moderate (Apprx ~15%).

Real-World Use Case: Imagine an e-commerce database storing millions of orders. Row compression helps optimise space for frequently updated order details without significantly increasing CPU usage.

Trade-offs:

Compression gains are lower than other methods.
Minimal impact on read performance, but some CPU overhead for decompression.

2. Page-Level Compression

Page compression works at the storage page level (8KB blocks of data). It applies multiple layers of compression:

Row compression (removes unnecessary storage for data types).
Prefix compression (stores common values once and references them).
Dictionary compression (replaces repeating values with shorter codes).

This technique is more effective than row compression because it eliminates redundant values across multiple rows.

Best for: Large tables where data is frequently read but occasionally updated.

Storage savings: High.

Real-World Use Case: A financial transactions table with millions of similar records (e.g., payments, deposits) benefits from page compression since many values repeat across multiple rows.

Trade-offs:

Higher CPU overhead for inserts/updates.
Not ideal for frequently updated tables.

3. Columnstore Compression (Columnar Storage)

Traditional SQL databases store data in rows, but columnar storage reorganises data by columns instead of rows.

This groups similar values together, allowing better compression for repetitive data.
Since analytical queries often scan entire columns, compressed columnar storage significantly reduces the amount of data read from the disk.
It supports batch processing, meaning queries run much faster.

Best for: Analytical workloads (OLAP), data warehouses, and reporting queries.

Storage savings: Possibly Very High.

Real-World Use Case: A business intelligence dashboard that queries sales trends over five years benefits from columnstore compression because it reads large volumes of data efficiently.

Trade-offs:

Not suitable for OLTP workloads (slow updates/inserts).
High CPU cost when writing data.

4. Dictionary Encoding

Dictionary encoding replaces repeating values with shorter, predefined codes.

The database builds a lookup table (dictionary) containing unique values.
Instead of storing the full value, it stores a shorter reference key.

Best for: Text-heavy data (logs, product descriptions, categorical data).

Storage savings: High.

Real-World Use Case: A customer support chat database storing thousands of similar responses benefits from dictionary encoding by reducing duplicate message storage.

Trade-offs:

Additional processing is required to decode values.
Less effective for unique or random data.

5. Run-Length Encoding (RLE)

Instead of storing each occurrence of a value, RLE stores the value once, along with the number of times it appears consecutively.

Example: Instead of storing AAAAAABBBCCC, RLE stores A6B3C3.

Best for: Data with consecutive repeating values (logs, time-series data, categorical data).

Storage savings: Very High (Possibly ~80% reduction).

Real-World Use Case: A weather monitoring system storing hourly temperature readings benefits from RLE compression, as consecutive values remain the same for long periods.

Trade-offs:

Less effective if values change frequently.
May require sorting data before compression.

6. Huffman Coding

Huffman coding assigns shorter binary codes to more frequent values and longer codes to less frequent values.

This is similar to how ZIP files compress text.
More effective when certain values appear much more often than others.

Best for: Text-heavy datasets with highly skewed value distributions.

Storage savings: Medium.

Real-World Use Case: A news website storing article headlines benefits from Huffman encoding since certain words like "Breaking" or "Exclusive" appear frequently.

Trade-offs:

High CPU overhead for encoding/decoding.
Not widely used in traditional SQL databases.

Implementing Huffman Encoding for Lossless Compression - PyImageSearch — Source

Comparison of SQL Compression Techniques

When to Use Compression and When to Avoid

Data compression can significantly improve database efficiency, but it is not always the right solution for every workload. While compression reduces storage costs and disk I/O, it adds CPU overhead during data writes, which can impact performance in high-transaction environments. Below are the key scenarios where compression should be used and cases where it might be better to avoid it.

When to Use

Compression is beneficial in read-heavy workloads, large datasets, and environments where storage efficiency is a priority. Consider using compression in the following situations:

1. Large Tables with Repetitive or Redundant Data

If a table contains many repeating values (e.g., product categories, status fields, transaction logs), dictionary encoding or page compression can significantly reduce storage.
Columnstore compression is highly effective for storing categorical data in analytical workloads.

Example: A customer order history table that contains millions of rows with repeated customer IDs, order statuses, and product categories will benefit from page-level compression or columnstore compression.

2. Read-Heavy Workloads (OLAP, Data Warehouses, Reporting Systems)

Compression is ideal for query-intensive environments where data is retrieved more often than written.
Columnstore compression drastically reduces the amount of data scanned during analytical queries.

Example: A business intelligence dashboard running complex reports over a 10-year sales dataset will experience faster queries and reduced storage costs with columnstore compression.

3. Tables with Historical or Archive Data

Older data that is rarely updated but needs to be queried occasionally benefits from high compression levels.
Compressed data uses less storage and speeds up historical trend analysis.

Example: A log table storing application events from the past five years can use run-length encoding (RLE) or page compression to significantly reduce storage footprint.

4. Large Text, JSON, or XML Fields

TOAST (PostgreSQL), COMPRESSED InnoDB (MySQL), and Hybrid Columnar Compression (Oracle) reduce the storage overhead of large text-based data.
Dictionary encoding can compress redundant text strings efficiently.

Example: A customer support system storing chat history in JSON format benefits from TOAST compression in PostgreSQL, which automatically offloads large text values to external storage.

5. Backup and Replication Optimization

Compressed data takes less space in backups, reducing storage costs and speeding up data replication across servers.
Backing up a compressed table results in smaller files and faster transfer times.

Example: A high-availability database replicating data across multiple regions benefits from compression to reduce network bandwidth usage.

When to Avoid

Compression is not always beneficial, particularly in high-transaction environments where write performance is critical. Avoid compression in these scenarios:

1. Write-Heavy Workloads (OLTP, Real-Time Transaction Systems)

Compression adds CPU overhead for every insert, update, or delete operation.
Frequently modified data requires constant recompression, slowing down transactions.

Example: A banking application processing real-time transactions should avoid compression because decompression and recompression overhead will impact write latency.

2. Tables with Highly Unique or Random Data

Compression works best with repeated values.
If most values in a table are unique, compression won't provide significant savings.

Example: A cryptographic key storage table, where every record is unique, will see minimal benefits from compression.

3. Small Tables with Minimal Data

Compression introduces CPU overhead that may not justify the small storage savings.
Small tables can be efficiently managed with indexing and memory caching instead.

Example: A lookup table storing country codes (e.g., US, UK, AU, etc.) does not need compression, as the storage savings would be negligible.

4. If Storage Isn’t a Concern

If the database is running on high-speed SSDs with ample disk space, compression might not be necessary.
Focus on indexing and query tuning before considering compression.

Example: A real-time analytics system with access to high-performance cloud storage might prioritise speed over storage efficiency.

Conclusion

Data compression in SQL databases is a powerful optimisation technique that can reduce storage costs, improve query performance, and optimise resource usage. By selecting the right compression method based on your workload you can achieve significant efficiency gains.

However, compression is not a one-size-fits-all solution. While OLAP workloads benefit from aggressive compression, transactional systems (OLTP) must balance storage savings with write performance. In some cases, indexing, partitioning, or data type optimisation may be better alternatives to compression.