Database Compression

Data size and database requirements

Information systems store data from purchases, social media, research, and financial transactions. As collections grow from megabytes to gigabytes, terabytes, and petabytes, their storage and processing costs grow as well. Structured data is managed by a database. The engine must also maintain indexes, metadata, and page buffers. Compression therefore applies to selected layers, and its benefit depends on where it is used.

A database stores records and provides operations for retrieval, modification, and analysis. At large volumes, three recurring challenges appear:

• Storage cost: Every byte occupies space on physical hardware such as hard disk drives (HDDs) or solid-state drives (SSDs). In a large installation, purchasing, powering, cooling, and maintaining many devices affects operating cost.
• I/O limits: A query may need to read data from storage into RAM. An I/O bottleneck becomes more likely as the amount of data to read increases, which can lengthen the response.
• Backups and maintenance: Copying a multi-terabyte database and performing maintenance can take many hours. System load and availability during that time depend on the procedure.

One way to reduce these costs is database compression. Data is stored in a compact representation, reducing storage use. Reading fewer bytes can shorten a query when decoding costs less than the saved I/O. The same mechanism can reduce backup size and allow more records to fit in a cache. Its effect should be measured for reads, writes, and updates.

Page boundaries affect overhead, statistics, and data skipping during reads.

When compression shortens data reads

Compression adds processor work because data must be decompressed before use. This cost is called CPU overhead. Total execution time depends on the relation between that cost and storage read time.

Processors and storage have different time costs. Reading a block includes device latency and transfer, while simple decoding can use fast CPU operations. The relation depends on the device, workload, and algorithm.

If a query reads 100 megabytes and the compression ratio is 4:1, the compressed stream has 25 megabytes. The decoder reconstructs the data from those 25 megabytes. The read then transfers 75% fewer bytes. When decoding time is small compared with the saved I/O, total execution can be shorter. Writes add encoding cost, while updates depend on how much data must be encoded again. A measurement should include throughput, latency, and CPU use.

Row-oriented and column-oriented storage

Compression techniques depend on the layout of data on disk. In a row store, values from one record are kept together. In a column store, values from one column form a separate sequence. The layout affects local repetition and the useful encoding. A row store supports full-record retrieval, while a column store can restrict reads to fields used by a query. Both layouts usually divide data into pages, fragments, or row groups.

Row-oriented databases (row stores)

In a row store, data for one record is stored together contiguously. Consider a simple employee table:

A row-oriented database would store this data on disk like this:[1, John, Smith, Sales], [2, Jane, Doe, Marketing], ...

Use: This layout supports Online Transaction Processing (OLTP). When a record fits on one data page, reading that page retrieves its fields together for retrieval or update. Inserting a row also places its fields together, preserving this locality for later access.

Column-oriented databases (column stores)

In a column store, values from one column are stored together contiguously. This layout is used in data warehousing and analytics. The same employee table could be stored like this:

Use: This layout supports Online Analytical Processing (OLAP). An aggregation over millions of rows can read only the columns required by the query, such as 'Department' and 'Salary', while skipping 'ID', 'FirstName', and 'LastName'. This reduces the amount of data transferred from storage.

A columnar layout often helps compression because values in one column have the same type and may repeat within similar ranges. Such a sequence is easier to model than the mixture of types in one row. A column format may apply dictionary, run-length, or delta encoding before a byte compressor processes the page. The order of these stages affects the result.

Compression techniques in row-oriented databases

Row stores group different data types in each record, so their compression strategies are often general-purpose.

Page-level compression

Many OLTP systems move data in chunks called data pages. Page compression processes multiple rows before writing the page. When the page is read, it is fetched in compressed form and decompressed in memory. The exact behavior depends on the engine. Some engines store large text or binary values outside the main row page and compress those values separately. Additional references to those fragments then affect read cost.

An engine may use a fast general-purpose codec such as zlib, based on DEFLATE, or LZ4. These methods search for repeated byte sequences within a page.

Other row-based techniques

• Type-specific compression: An engine can use the range of a data type. Integers and dates may occupy fewer bytes when their values fit a smaller range than the default representation.
• Prefix compression: Some text indexes use a common prefix shared by consecutive entries. For URLs starting with $https://www.example.com/$, the prefix can be stored once and subsequent entries can store their differing suffixes.

Compression techniques in column-oriented databases

Column stores can achieve high reduction by encoding homogeneous sequences separately for each column. The result depends on value distribution, ordering, and format. In practical formats, a column is split into fragments so a decoder can read a selected range. A dictionary can be abandoned in favor of plain encoding when the number of distinct values is too high.

Dictionary encoding

Dictionary encoding suits columns with low cardinality. For a selected page or column fragment, the engine builds a dictionary and assigns small integer codes to its values. Instead of repeating a long string such as "United States of America", it stores a code and keeps the dictionary once within that fragment. Code width depends on the number of entries, and the dictionary plus its metadata also have to be stored.

Run-length encoding (RLE)

RLE replaces a consecutive run of identical values with a pair containing the value and its repeat count. Instead of storing [HR, HR, HR, HR, Sales, Sales], it can store [(HR, 4), (Sales, 2)]. Sorting by date or another key increases the length of such runs in column data.

Delta encoding

Delta encoding is used for numeric columns with slow or predictable changes, such as sequential IDs, timestamps, or sensor readings. The representation stores a base value followed by differences from the previous entry. The sequence $1005, 1006, 1007$ can become $1005, +1, +1$, after which the small differences can receive another encoding. Signed differences require a representation for negative values. Time ordering often improves the result, while the benefit depends on row order.

Bit-packing

A fixed data type can use more space than the value range requires. A 64-bit integer can store values that never exceed 100. Bit-packing uses the minimum number of bits needed for a code. It can be combined with dictionary and delta encoding to store small codes and differences. A range from 0 through 100 needs 7 bits per value when the full range is encoded. Metadata must record the field width used by the page.

With favorable data, combining these techniques can give a ratio of 10:1 or higher. The result depends on cardinality, value order, and metadata overhead, so this ratio is an example measurement that varies with column data. In a practical column format, encoding usually applies to a page, fragment, or row group. A scan can skip other fragments, but each fragment carries its own header and statistics. A group that is too small increases overhead, while a group that is too large makes selective reads harder. A benchmark should therefore record the total dataset size, query time, selected-column reads, CPU work, and cache impact. In Parquet, dictionary indices can use a hybrid of RLE and bit-packing, and a dictionary that exceeds its useful size can fall back to plain encoding. Results should be separated for full scans and selective reads. Page and row-group boundaries can change the result even for identical values.