Database Compression Techniques Explained

Modern databases can store billions or even trillions of records. As data volumes grow, storage costs, memory usage, network transfers, and query performance become increasingly important.

One way databases manage these challenges is through data compression.

Database compression reduces the amount of physical storage required to represent data. In analytical systems, it can also improve query performance because the database may need to read less data from disk.

Compression is especially powerful in columnar databases, where similar values are stored together. Repeated values, ordered data, and predictable patterns can often be represented much more efficiently than their original form.

Database compression reduces the physical size of stored data by representing information more efficiently. Common techniques include dictionary encoding, run-length encoding, delta encoding, bit packing, prefix encoding, and general-purpose algorithms such as LZ4 and Zstandard.

Columnar databases often achieve particularly high compression ratios because values from the same column tend to have similar data types and patterns.

In this guide, we’ll explore how database compression works, the major compression techniques, their advantages and disadvantages, and how compression affects analytical query performance.

Why Do Databases Compress Data?

Suppose a table contains 100 million rows.

Without compression, the database may need to store a large amount of raw data.

With compression:

Raw Data
   ↓
Compression
   ↓
Smaller Representation
   ↓
Storage

This can provide several benefits:

• Lower storage requirements
• Lower storage costs
• Less disk I/O
• Reduced network transfer
• Better cache utilization
• Potentially faster analytical queries

Compression is therefore not only a storage optimization.

It can also become a query performance optimization.

Row-Oriented vs Columnar Compression

Compression behaves differently depending on how a database stores data.

Row-Oriented Storage

A row-oriented database stores values belonging to the same record together.

For example:

1, Michael, Lagos, 25
2, Sarah, Abuja, 31
3, David, Lagos, 28

This structure is convenient for transactional workloads.

Columnar Storage

A columnar database stores values from the same column together:

ID:
1, 2, 3

Name:
Michael, Sarah, David

City:
Lagos, Abuja, Lagos

Age:
25, 31, 28

The columnar representation makes compression easier because similar values are grouped together.

For example:

Lagos
Lagos
Lagos
Lagos
Lagos

can be represented much more efficiently than storing the complete text repeatedly.

1. Dictionary Encoding

Dictionary encoding replaces repeated values with compact numerical identifiers.

Suppose a column contains:

Lagos
Lagos
Abuja
Lagos
Ibadan
Abuja

A dictionary could be:

1 → Lagos
2 → Abuja
3 → Ibadan

The original column can then become:

1
1
2
1
3
2

This can significantly reduce storage when a column contains relatively few distinct values.

Best Use Cases

Dictionary encoding works particularly well for:

• Country
• City
• Product category
• Status
• Department
• Boolean-like values

Limitation

It becomes less effective when nearly every value is unique.

For example, a column containing unique transaction IDs has little opportunity for dictionary compression.

2. Run-Length Encoding

Run-Length Encoding (RLE) compresses repeated consecutive values.

For example:

A
A
A
A
B
B
C
C
C

can become:

A × 4
B × 2
C × 3

Instead of storing each value individually, the database stores the value and the number of times it appears consecutively.

RLE is particularly effective when data is sorted or naturally contains long runs of identical values.

Example

A status column might contain:

Active
Active
Active
Active
Inactive
Inactive
Inactive

RLE can represent this very efficiently.

Limitation

Randomly ordered data produces shorter runs and therefore lower compression.

3. Delta Encoding

Delta encoding stores the difference between consecutive values instead of storing every value in full.

Consider:

1000
1010
1020
1030
1040

Instead of storing all values directly:

1000
1010
1020
1030
1040

the database can store:

1000
+10
+10
+10
+10

The differences require fewer bits to represent.

Delta encoding is useful for:

• Timestamps
• Sequential IDs
• Measurements
• Sensor data
• Ordered numerical values

4. Frame-of-Reference Encoding

Frame-of-reference encoding stores values relative to a base value.

Suppose a column contains:

100000
100005
100012
100018
100023

Instead of storing the full values, the database can select a reference:

Reference = 100000

and store:

0
5
12
18
23

The smaller offsets can require significantly less storage.

This technique works particularly well when values within a block are close together.

5. Bit Packing

Bit packing stores values using only the number of bits they actually require.

Suppose a column contains only:

0
1
2
3

There is no need to use a full 32-bit integer for every value.

Four possible values require only two bits:

0 → 00
1 → 01
2 → 10
3 → 11

Bit packing can therefore reduce storage substantially for columns with small integer ranges.

It is useful for:

• Boolean values
• Small categorical codes
• Status indicators
• Small integer measurements

6. Frame-of-Reference + Bit Packing

Database compression techniques can also be combined.

For example:

Original Values
      ↓
Choose Reference
      ↓
Calculate Offsets
      ↓
Bit Pack Offsets
      ↓
Compressed Block

This can be highly effective for numerical columns where values within a block are close together.

7. Prefix Encoding

Prefix encoding takes advantage of shared beginnings between strings.

Consider:

customer_001
customer_002
customer_003
customer_004

Instead of storing the repeated prefix:

customer_

for every value, the database can store the shared prefix once and encode the changing portion separately.

This can help compress structured string values.

8. Frame-of-Reference for Timestamps

Timestamps often contain significant redundancy.

For example:

2026-08-17 10:00:01
2026-08-17 10:00:03
2026-08-17 10:00:05
2026-08-17 10:00:07

Rather than storing every complete timestamp, a database can represent timestamps relative to a reference time.

This is one reason time-series and event datasets can often compress effectively.

9. General-Purpose Compression

Databases can also use general-purpose compression algorithms.

Common examples include:

• LZ4
• Zstandard (Zstd)
• Snappy
• Gzip

These algorithms identify repeated patterns in blocks of data and encode them more efficiently.

LZ4

LZ4 is designed for very fast compression and decompression.

It is useful when performance is important.

Zstandard

Zstandard provides a useful balance between compression ratio and speed.

It is widely used in modern data systems.

Snappy

Snappy is optimized for speed and moderate compression rather than achieving the smallest possible files.

Gzip

Gzip can provide strong compression but is generally slower than algorithms optimized for high-throughput analytical workloads.

Compression Ratio

A useful metric for evaluating compression is the compression ratio.

For example:

Original Size = 100 GB
Compressed Size = 20 GB

Compression Ratio = 5:1

The higher the ratio, the more effectively the data has been compressed.

Another useful measure is the percentage reduction:

100 GB → 20 GB

This represents an 80% reduction in physical storage.

Compression vs Query Performance

It may seem that compression should always make queries slower because the database needs to decompress data.

In analytical workloads, the opposite can happen.

Consider:

Uncompressed
1 TB → Read 1 TB → Process

Compressed
200 GB → Read 200 GB → Decompress → Process

If disk or network I/O is the bottleneck, reading 200 GB can be significantly faster than reading 1 TB.

Modern analytical engines can also decompress data efficiently.

Therefore:

Compression can reduce CPU work in some cases while increasing it in others.

The actual effect depends on the workload and storage architecture.

Compression and Column Pruning

Columnar databases provide another important advantage.

Suppose a table contains 100 columns but a query needs only:

SELECT customer_id, revenue
FROM sales;

A columnar engine may read only those columns.

If those columns are also compressed, the amount of data transferred from storage can become very small compared with scanning the entire table.

This combination is one reason columnar storage is so effective for analytics.

Compression and Partitioning

Partitioning can improve compression by grouping similar data together.

For example, partitioning sales data by:

Year
Month
Region

may create partitions with more consistent characteristics.

This can make compression more effective and can also reduce the amount of data scanned by queries.

However, excessive partitioning can introduce unnecessary metadata and management overhead.

Compression and Sorting

Sorting can have a major effect on compression.

Consider a city column:

Lagos
Abuja
Lagos
Ibadan
Lagos
Abuja

RLE may not work particularly well.

After sorting:

Abuja
Abuja
Ibadan
Lagos
Lagos
Lagos

longer runs appear.

This can improve RLE compression.

Sorting can also improve other encoding techniques by grouping similar values together.

Compression in Data Lakes

Compression is also important in modern data lake architectures.

Common file formats include:

• Parquet
• ORC
• Avro

Columnar formats such as Parquet and ORC are particularly effective for analytics because they combine columnar storage with encoding and compression techniques.

A simplified Parquet workflow looks like:

Raw Data
   ↓
Columnar Organization
   ↓
Encoding
   ↓
Compression
   ↓
Parquet File

Different columns can use different encoding strategies depending on their characteristics.

Compression in Data Warehouses

Cloud data warehouses commonly use internal compression automatically.

For example, analytical systems can compress:

• Integer columns
• Strings
• Dates
• Timestamps
• Repeated categorical values

Users often don’t need to manually choose the underlying encoding algorithm.

Instead, the database engine analyzes the data and applies appropriate storage techniques.

When Compression Works Best

Compression tends to work particularly well when data contains:

• Repeated values
• Low-cardinality categories
• Similar numerical values
• Ordered timestamps
• Repeated strings
• Predictable patterns
• Sorted data

For example:

Country
US
US
US
US
US

is highly compressible.

In contrast:

UUID
a91f...
73bd...
12ef...
8c21...

contains highly unique values and is generally harder to compress using simple dictionary techniques.

When Compression Works Poorly

Compression may be less effective when data is:

• Highly random
• Encrypted
• Already compressed
• Extremely high-cardinality
• Poorly organized

Encrypted data, for example, usually has very little detectable redundancy.

Compressing already compressed formats often provides little additional benefit.

Compression Trade-Offs

Compression involves trade-offs.

Factor	More Compression	Less Compression
Storage	Lower	Higher
Compression CPU	Higher	Lower
Decompression CPU	Higher	Lower
I/O	Lower	Higher
Query speed	Can improve	Can decrease
Cost	Often lower	Often higher

The goal isn’t necessarily maximum compression.

The goal is usually the best balance between storage efficiency and workload performance.

Choosing a Compression Technique

A practical decision process is:

What type of data do you have?
          ↓
Categorical?
 → Dictionary Encoding

Repeated Consecutive Values?
 → RLE

Sequential Numerical Values?
 → Delta Encoding

Small Integer Range?
 → Bit Packing

General Data Blocks?
 → LZ4 / Zstd / Similar Algorithm

Modern databases often combine several of these techniques automatically.

Best Practices

Let the Database Optimize When Possible

Modern analytical databases usually have sophisticated compression systems. Avoid manually optimizing low-level encoding unless you understand the workload.

Use Columnar Formats for Analytics

Formats such as Parquet and ORC can provide strong compression alongside efficient column pruning.

Sort Strategically

Ordering data can improve both compression and query performance.

Measure Real Workloads

Don’t assume the highest compression level produces the fastest queries.

Benchmark representative queries.

Monitor Storage and Compute Costs

Compression can reduce storage and I/O costs while potentially increasing CPU usage.

The optimal configuration depends on the workload.

Common Mistakes

Assuming Compression Always Slows Queries

Reduced I/O can make compressed queries faster.

Choosing Maximum Compression Automatically

Maximum compression may increase CPU overhead without providing meaningful benefits.

Ignoring Data Ordering

Poorly organized data may compress much less effectively.

Compressing Everything the Same Way

Different columns have different characteristics.

Ignoring Query Patterns

Storage optimization should support the workload rather than exist independently of it.

Database Compression vs File Compression

Database compression and file compression are related but not identical.

Database compression is integrated into the storage engine and can work with structures such as columns, pages, blocks, and indexes.

File compression compresses a file or data object as a whole.

For analytical workloads, database-aware compression can be more powerful because the engine understands the structure of the data.

The Future of Database Compression

As data volumes continue to grow, compression will become increasingly important for analytical systems.

Modern data platforms are combining:

• Columnar storage
• Adaptive encoding
• Compression
• Data skipping
• Partitioning
• Sorting
• Vectorized execution
• Intelligent caching

The result is an architecture where reducing the physical size of data can simultaneously reduce storage requirements and accelerate analytical queries.

Compression is therefore becoming less of a simple storage trick and more of a fundamental part of modern database architecture.

Database compression allows systems to represent data using less physical storage while potentially improving analytical performance.

Techniques such as dictionary encoding, run-length encoding, delta encoding, bit packing, frame-of-reference encoding, and general-purpose algorithms like LZ4 and Zstandard each work best under different data characteristics.

For analytical workloads, compression becomes especially powerful when combined with columnar storage, sorting, partitioning, and column pruning.

The key lesson is that the best compression strategy isn’t necessarily the one that produces the smallest dataset. The right approach balances storage savings, CPU overhead, query performance, and cost.

FAQ