Core Technology of PolarDB-X Storage Engine | Secondary Prefix Compression

By Yichang, Zanye, Lantao

Background

PolarDB-X is a distributed database built on a unified centralized-and-distributed architecture. To handle hybrid workloads flexibly, each Data Node (DN) — the data storage layer — employs multiple data structures, with a row store providing online transaction processing (OLTP) capability. As a database 100% compatible with the MySQL ecosystem, the DN builds on InnoDB’s storage structures with deep optimizations that significantly improve data access efficiency.

In real-world OLTP workloads, a single large table carrying a dozen or more secondary indexes is common — particularly in SaaS multi-tenant systems, e-commerce product/order tables, transaction ledgers, distributed tracing stores, and archive databases. It is not unusual for secondary indexes to account for over 50% of total instance storage. In some cases, the indexes significantly outweigh the primary table itself.

This index bloat not only drives up storage costs directly, but also degrades buffer pool hot-data coverage, amplifies IOPS pressure, and increases B+ tree page splits and SX-latch contention. As cloud-native databases push for maximum cost-efficiency, designing compression specifically tailored to the structural characteristics of secondary indexes is a key challenge for the PolarDB-X storage engine.

To address this, the PolarDB-X DN storage engine introduces Secondary Prefix Compression (SPC) — a lightweight prefix compression scheme that operates exclusively on secondary indexes, uses per-page local dictionaries, and triggers encoding at page-split time. On typical workloads, SPC reduces secondary index space by 30%–70%, keeps additional CPU overhead on the read/write path well controlled, and delivers significant performance gains under I/O-bound conditions.

InnoDB secondary index storage structure

B+ tree layout of secondary indexes

In InnoDB, a user-defined table is physically implemented as a set of B+ tree indexes. The clustered index — keyed by the primary key — stores full row data in its leaf nodes, while each secondary index stores only the secondary key (SK) and the primary key (PK) in its leaves. The physical record layout of a secondary index is shown below:

InnoDB currently organizes secondary index records by storing each record as an (SK, PK) tuple sequentially in B+ tree leaf pages. This design is straightforward and provides efficient point lookups and range scans. However, from a page-level storage perspective, it does not exploit several distribution characteristics that are typical of secondary index data.

These characteristics manifest in three ways:

1. Records with identical SKs appear consecutively but do not share storage.

In a non-unique index, records sharing the same SK are laid out contiguously, yet each record still stores a full copy of the SK — only the trailing PK differs.

2. Business keys often share long common prefixes.

Fields such as order IDs, SKUs, URLs, file paths, trace IDs, email domains, and business codes in production workloads typically share highly similar prefixes, with distinguishing information concentrated in the suffix.

3. Sorted order makes adjacent records naturally similar.

Because secondary indexes are sorted by SK, adjacent records within the same leaf page typically share long common prefixes. Under InnoDB’s current record format, however, these redundant prefixes are still stored separately in every record.

Functionally, this layout is perfectly correct. From a space-utilization standpoint, however, it means secondary index pages contain a large amount of redundant information that could be exploited. This is precisely the motivation behind SPC: improve storage efficiency by locally exploiting these repeated prefixes within each page, without changing the logical structure of the secondary index.

Limitations of MySQL’s built-in compression

MySQL InnoDB offers two built-in compression schemes, both of which apply general-purpose compression algorithms at the block level. They suffer from high computational cost, and in practice it is difficult to achieve both good compression ratios and acceptable access performance simultaneously. As a result, they tend to be deployed mainly in workloads that are particularly sensitive to storage costs.

	Table Compression	Transparent Page Compression
Syntax	CREATE TABLE t1 (c1 INT PRIMARYKEY)ROW_FORMAT=COMPRESSEDKEY_BLOCK_SIZE=8;	CREATE TABLE t1 (c1 INT)COMPRESSION="zlib";
Mechanism	Pages are kept in both compressed and uncompressed forms in memory. Row-level modifications must maintain both versions, with the compressed page re-compressed on write. Only the compressed page is flushed to disk.	Pages are compressed when written to disk. Blocks smaller than the OS filesystem unit can reclaim space via file hole punching.
Dependencies	Logical page compression; no OS dependency.	Physical page compression; requires filesystem hole-punch support.
Cost	Memory: buffer pool holds both compressed and uncompressed copies, cutting effective utilization in half. CPU: maintaining both versions adds 5%–20% overhead.	Memory: buffer pool holds only one copy. CPU: compression and decompression occur during I/O, adding 5%–20% overhead.

At its root, the space overhead of InnoDB’s secondary indexes comes from redundant SK data stored within each page: adjacent records share long common prefixes, and records with the same SK appear consecutively yet each stores its own full copy. This directly lowers leaf-page space utilization, which in turn dilutes buffer pool coverage of hot data, increases index-page accesses and B+ tree splits, and raises concurrency contention. The implication is clear: the key to secondary index compression is not to introduce yet another heavyweight general-purpose compressor, but to find a way to exploit these naturally occurring repeated prefixes and similarities without breaking the existing index structure or access paths.

SPC index structure

To address the storage overhead of InnoDB secondary indexes, the PolarDB-X DN storage engine introduces the SPC index structure. By maintaining per-page prefix dictionaries, SPC significantly reduces secondary index space consumption and improves the effective utilization of both the buffer pool and I/O bandwidth.

SPC overall architecture

The core idea of SPC is:

Within each secondary index page, a small metadata area — the page dictionary (Page Dict) — stores a set of common prefixes for that page. Each record in the page then stores only a reference to a dictionary entry, how many bytes of that prefix it uses, and its own remaining suffix.

The overall layout of an SPC page is shown below:

Page Dict: per-page shared prefix dictionary

A critical design choice for prefix compression is: where are the prefix entries stored, and at what granularity can they change?

• Per-record delta prefixes. Each record stores a diff relative to the preceding record. This maximizes theoretical compression, but creates a strong dependency chain: parsing a single record requires reconstructing the previous one. Any insert, delete, or update in the middle can cascade to subsequent records, and optimistic writes easily degrade into pessimistic full-page rewrites. Engineering complexity is very high.

• Cross-page shared dictionary. A dictionary maintained outside individual pages — shared across an index, a partition, or a range of pages — allows multiple pages to reuse the same high-frequency prefixes. In workloads where prefixes are highly stable, this can yield higher overall compression. The downsides, however, are significant: the dictionary becomes a new piece of global metadata, introducing complexity around versioning, concurrency control, crash recovery, page-split migration, and prefix lifecycle management. Individual page reads and writes are no longer self-contained, making access paths and reclamation heavier.

• Per-page shared dictionary. Each page maintains its own local dictionary, shared by all records within that page. A single DML operation typically modifies only the record itself without triggering a dictionary update. The dictionary is recomputed only during page reorganization, page split, or full-page re-encoding. Records naturally decompose into “dictionary reference + own suffix.” The in-page access path is simple, locality is good, and the scheme aligns well with the existing B+ tree page lifecycle.

In summary, the three approaches represent three different trade-offs: per-record deltas push compression to the theoretical maximum but also maximize write amplification and implementation complexity; cross-page dictionaries extend reuse scope but introduce global consistency and metadata management costs; per-page dictionaries are not the most aggressive in compression ratio, but they strictly confine the compression scope to a single page, achieving a well-balanced trade-off among compression benefit, read/write overhead, and engineering tractability. SPC chooses the per-page shared dictionary, which consists of three components:

The number of dictionary entries.
An offset directory for each prefix within the dictionary area.
The actual prefix byte strings.

This allows multiple index records to share the same in-page prefix content instead of each storing a redundant copy.

Structurally, SPC elevates the repeated prefix across records from implicit redundancy to a first-class, page-level object.

SPC record format

An SPC record extends the standard COMPACT record header with an additional SPC header. The two most important fields are:

Pack ID: identifies which dictionary prefix this record references.
Pack Len: indicates how many bytes of that prefix the record actually uses.

With this layout, an SPC record’s physical representation changes from a full index key to “shared in-page prefix + record-specific suffix.” The logical value and sort order of the record remain unchanged, but repeated prefixes within a page need to be stored only once.

SPC data encoding

Split-triggered encoding: encode only when a page is about to split

When to encode is a critical engineering decision for SPC. Encoding the entire page on every DML imposes a hard CPU cost that directly slows the write path; never encoding defeats the purpose of compression.

SPC adopts a split-triggered encoding strategy: newly written records are inserted in their uncompressed form. Only when the page is about to run out of space — and is about to split — does SPC encode the entire page.

The specific trigger point is on InnoDB’s optimistic insert path:

The page does not have enough free space to accommodate the new record.
A reorganize pass (clearing delete marks, defragmenting) still cannot free enough space.
At this point, the original logic would release the page latch and enter a structure modification operation (SMO). SPC intervenes here, first attempting a full-page SPC encoding.
If encoding frees enough space for the new record, the insert follows the “optimistic + compressed” path, avoiding the SMO entirely.
If encoding still cannot make room, the operation falls back to the normal page split flow.

The benefits of this approach are systemic:

• Cold and sparse pages are never encoded, incurring zero additional CPU overhead on the write path.

• The encoding cost is amortized over the N DML operations that fill a page.

• Every avoidable SMO is avoided — the space freed by compression often allows an insert that would have triggered a split to complete in place. Compression itself acts as SMO relief.

In-page encoding algorithm

The goal of SPC encoding is to select, under a dictionary-entry cap of 128, a set of non-overlapping contiguous intervals over the page’s sorted records, each sharing one common prefix, such that total net byte savings is maximized. This is an optimal segmentation problem over the common-prefix-length sequence of adjacent records. The exact optimum requires O(n²) dynamic programming — too expensive for the hot path of a page split.

SPC uses a linear-time, three-phase greedy approximation. In the first phase, the algorithm scans the prefix-length sequence left to right, splitting it at ascending/descending inflection points into maximal monotonic intervals as candidates. Each candidate’s dictionary entry is taken from the record at the longest common-prefix point. In the second phase, adjacent candidates are compared pairwise: keeping them separate vs. merging them is evaluated by net byte savings, and the better option is chosen. Merging causes the shared prefix to degrade to the minimum length at the junction. In the third phase, surviving candidates are committed to the page dictionary, and each record is back-filled with its dictionary reference and compressed length. Records whose prefix is too short or not covered retain their original form and coexist with compressed records on the same page.

Because secondary index records are sorted by key, identical prefixes naturally cluster together. In practice, this greedy strategy produces results very close to the exact optimum.

SPC data decoding

The engineering difficulty of prefix compression lies not in the algorithm itself, but in the fact that decompression must cover every corner of the read path. InnoDB accesses secondary index records through many code paths — each involving rec_get_offsets, offset-based field access, and comparisons between rec_t and dtuple_t — all of which must correctly handle SPC records.

Slice: a unified logical view

In upstream InnoDB code, records are accessed directly via a rec pointer plus offsets. Requiring every call site to distinguish between a contiguous COMPACT record and a split SPC record would be prohibitively expensive to maintain and prone to subtle bugs.

To solve this, SPC introduces a new abstraction in InnoDB’s rem layer — Slice. All upstream read paths access records through a unified Slice interface:

• For contiguous COMPACT records, Slice degenerates to a direct rec pointer access, identical to the upstream community behavior.

• For SPC records, Slice lazily assembles the dictionary prefix and record suffix into a contiguous memory region on demand.

• For merge records generated during DDL external sorting, Slice provides a corresponding adapted form.

The key property of the Slice abstraction is that it changes the implementation without changing the semantics of upstream code. Callers still see logically complete fields. Physical differences are fully encapsulated inside Slice.

Built on this abstraction, SPC is semantically equivalent across MVCC, undo, purge, locking, change buffer, replication, and backup/restore. From the outside, SPC is a purely physical, in-page storage optimization — its logical behavior is identical to that of a standard secondary index.

On performance-sensitive read paths, Slice also supports partial decompression:

• When comparing an SPC record against a dtuple, the comparison can be performed column by column directly in compressed form — using the dictionary entry, the record suffix, and extended offsets — without first reconstructing a full uncompressed record.

• This compressed-form direct comparison eliminates one memcpy and one temporary allocation on the hot path, which is a critical safeguard for SPC’s performance under CPU-bound conditions.

Industry comparison

MySQL built-in compression

As discussed earlier, both table compression and transparent page compression are general-purpose block compressors. They do not exploit the “adjacent records are similar” structural property of B+ tree leaf pages; their compress/decompress pipelines rely on zlib/lz4 with high CPU cost; and table compression keeps both compressed and uncompressed copies in the buffer pool, which hurts memory utilization.

PostgreSQL B-tree deduplication

PostgreSQL 13+ supports B-tree deduplication, which consolidates multiple TIDs for identical key values into aggregated storage. This is fundamentally duplicate-key aggregation rather than prefix-based dictionary compression, and offers limited benefit for keys with long, partially shared prefixes such as URLs or trace IDs.

Oracle key compression

Oracle’s basic index compression — prefix/key compression (COMPRESS n) — splits the index key into prefix and suffix, sharing leading-column prefixes within an index block. It is essentially column-level prefix sharing where the prefix length is specified by column count. SPC, by contrast, operates at the byte level, allowing finer-grained exploitation of actual data distribution.

Building on this, Oracle offers Advanced Index Compression (COMPRESS ADVANCED LOW/HIGH). Unlike the fixed-prefix approach, Advanced Index Compression uses adaptive, per-block compression informed by data distribution. According to public documentation, it internally employs intra-column-level prefixes, duplicate key elimination, and rowid compression.

SQL Server page compression

Among major databases, SQL Server’s page compression is the closest to SPC in its approach of exploiting repeated patterns within a page to reduce storage overhead. Its implementation is not a single algorithm but a three-layer pipeline applied in sequence: row compression (compact row format), then prefix compression (extract column-level prefixes within the page), then dictionary compression (build a dictionary of repeated values across the entire page). The timing of compression is also similar to SPC: when a page fills up and needs to accommodate new rows, the engine re-evaluates the entire page and decides whether to apply prefix and dictionary compression.

Benchmarks

Performance results

To evaluate SPC under realistic OLTP workloads, we designed a set of sysbench-based benchmarks targeting secondary indexes with prefix-repetition characteristics. We compared space consumption and throughput with SPC enabled vs. disabled on identical hardware and datasets.

Test environment and dataset

Benchmarks were run with 128 concurrent threads. The table schema was designed to simulate typical prefix-repetition characteristics: each table contains 3 INT columns simulating low-cardinality fields such as status and category (5 distinct values each), and 6 VARCHAR(64) columns simulating business keys with common prefixes (40-byte prefix, 1,000 distinct prefixes). Twelve secondary indexes were defined: 3 single-column indexes on the enum columns, 6 single-column indexes on the business key columns, and 3 composite indexes pairing an enum column with a business key column. The benchmark used 16 tables, each with 1 million rows. With SPC disabled, a single table occupied 1.8 GB with secondary indexes accounting for 1.16 GB. With SPC enabled, the table shrank to 1.1 GB with secondary indexes at 0.52 GB — a secondary index compression ratio of roughly 55% and an overall compression ratio of roughly 38%.

Three workload profiles were tested:

• read_only: secondary index point lookups + composite index point lookups + composite index range scans, simulating typical online query paths.

• read_write: the read_only workload plus secondary index column updates and delete/insert operations, simulating a mixed transactional workload.

• write_only: only secondary index column updates and delete/insert operations; every write touches all 12 secondary indexes, simulating a write-intensive scenario.

I/O-bound scenario

By tuning buffer pool size so that the index working set cannot reside entirely in memory, we created I/O-bound conditions where random disk reads become the bottleneck.

Buffer pool = 4 GB

Workload	SPC off (QPS)	SPC on (QPS)	Improvement
read_only	82,401	108,384	+31.5%
read_write	33,292	34,060	+2.3%
write_only	20,259	20,615	+1.8%

Buffer pool = 8 GB

Workload	SPC off (QPS)	SPC on (QPS)	Improvement
read_only	105,069	154,215	+46.8%
read_write	47,904	70,655	+47.5%
write_only	27,369	27,818	+1.6%

Under I/O-bound conditions, SPC’s performance gains come from two paths. First, each 16 KB page holds more records after compression, significantly increasing effective buffer pool coverage. The resulting gap in cache hit rate translates directly into fewer random disk reads. Second, compression reduces the total number of B+ tree leaf pages, lowering page-split frequency and indirectly alleviating SX-latch contention caused by SMOs. The 8 GB buffer pool configuration is the optimal comparison point for this dataset: with SPC disabled, the buffer pool covers only about 30% of the data, forcing heavy disk reads. With SPC enabled, coverage rises to nearly 50%, maximizing the cache-hit-rate differential. Read and read-write workloads both achieve roughly 47% throughput improvement.

The write_only workload shows virtually no difference under either buffer pool configuration (+1.6%–1.8%), which is expected: its read path locates rows by primary key and does not traverse the secondary index B+ tree, so SPC’s compression of secondary index pages does not affect read-side cache hit rates. Although the write path maintains all 12 secondary indexes, the split-triggered encoding strategy ensures that routine DML does not incur additional encoding overhead, keeping write throughput essentially flat.

CPU-bound scenario

With buffer pool set to 32 GB — large enough for the entire dataset to reside in memory — disk I/O is eliminated, isolating SPC’s behavior on the pure CPU path.

Workload	SPC off (QPS)	SPC on (QPS)	Difference
read_only	426,476	416,194	-2.4%
read_write	294,917	291,175	-1.3%
write_only	138,673	149,890	+8.1%

With all data in memory, SPC’s I/O advantage disappears. Throughput differences across all three workloads fall within the noise range of the benchmark, confirming that the split-triggered encoding strategy and compressed-form direct comparison effectively control decompression overhead.

Community