PolarDB:Secondary Prefix Compression (SPC)

Applicability

Your instance must meet the following requirements:

• instance series: Standard Edition or Enterprise Edition.

• Engine version: MySQL 8.0.

• Storage node version: xcluster8.4.20-20260423 or later, which includes versions released on and after April 23, 2026.

Overview

SPC maintains a page dictionary on the leaf pages of a B+Tree to store the common prefixes of records within a page. Each record then stores only a prefix ID and its suffix data. This avoids redundantly storing long, identical prefix bytes.

Key features of SPC:

• Each page dictionary supports up to 128 distinct prefixes.

• Prefix deduplication can significantly reduce index storage space. The actual space savings depend on the degree of prefix repetition in your data.

• It applies only to non-unique secondary indexes. For unique indexes, you can use Panda Index.

The priority order for index formats is PANDA > SPC > HIPPO. If multiple formats are enabled, a non-unique secondary index prioritizes SPC, while a unique index prioritizes Panda Index.

Enabling and disabling SPC

By default, SPC is disabled for PolarDB-X instances. To change this setting, go to the PolarDB-X console. For your target cluster, navigate to the Configuration Management > Parameter Settings page, select the Storage Layer tab, and adjust the value of the opt_index_format_spc_enabled parameter. This change takes effect immediately and does not require an instance restart.

You can also control the SPC feature at the session level:

-- Enable SPC
SET SESSION opt_index_format_spc_enabled = ON;

-- Disable SPC (default)
SET SESSION opt_index_format_spc_enabled = OFF;

When you create a new table, any eligible non-unique secondary index automatically uses the SPC format:

CREATE TABLE t1 (
  id INT NOT NULL PRIMARY KEY,
  c1 VARCHAR(200),
  c2 VARCHAR(200),
  INDEX idx_c1 (c1),        -- Automatically uses the SPC format
  INDEX idx_c1c2 (c1, c2)   -- Automatically uses the SPC format
) ENGINE=InnoDB;

Convert existing indexes to SPC

To convert a non-SPC index to the SPC format, first ensure that opt_index_format_spc_enabled = ON.

-- Create a new index in SPC format.
ALTER TABLE ... ADD INDEX idx_new ...;
-- Atomically rename the old and new indexes.
ALTER TABLE ... RENAME INDEX idx TO idx_old, RENAME INDEX idx_new TO idx;
-- Drop the original index.
ALTER TABLE ... DROP INDEX idx_old;

System variables

View SPC indexes

Use the following methods to check if an index uses the SPC format.

• SHOW CREATE TABLE FOR EXPORT

  Use SHOW CREATE TABLE ... FOR EXPORT to check if an index uses the SPC format. If the output for an index includes "spc": "true", the index uses the SPC format.

  SHOW CREATE TABLE t_spc_show FOR EXPORT;

  CREATE TABLE `t_spc_show` (
    `id` int NOT NULL,
    `c1` varchar(200) DEFAULT NULL,
    `c2` varchar(200) DEFAULT NULL,
    PRIMARY KEY (`id`) /*!80021 SECONDARY_ENGINE_ATTRIBUTE '{"gpp": "false", "panda index": "false", "hippo index": "false", "spc": "false"}' */,
    KEY `idx_c1` (`c1`) /*!80021 SECONDARY_ENGINE_ATTRIBUTE '{"gpp": "true", "panda index": "false", "hippo index": "false", "spc": "true"}' */,
    KEY `idx_c1c2` (`c1`,`c2`) /*!80021 SECONDARY_ENGINE_ATTRIBUTE '{"gpp": "true", "panda index": "false", "hippo index": "false", "spc": "true"}' */
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

• INFORMATION_SCHEMA.INNODB_INDEX_STATUS

  SELECT SCHEMA_NAME, TABLE_NAME, INDEX_NAME, INDEX_PAGE_TYPE
  FROM INFORMATION_SCHEMA.INNODB_INDEX_STATUS
  WHERE TABLE_NAME = 't_spc_demo' AND SCHEMA_NAME = 'test';

  In the output, the INDEX_PAGE_TYPE for an SPC index is SPC_PAGE. The following table describes the possible values for INDEX_PAGE_TYPE.

  Value	Description
  INDEX_PAGE	Standard B+Tree index page
  SPC_PAGE	SPC prefix-compressed page
  PANDA_PAGE	Panda Index format page
  RTREE_PAGE	Spatial index page
  SDI_PAGE	Serialized Dictionary Information page

Count SPC indexes

• Count all SPC indexes in the current instance, grouped by schema:

  SELECT SCHEMA_NAME, COUNT(*) AS spc_index_count
  FROM INFORMATION_SCHEMA.INNODB_INDEX_STATUS
  WHERE INDEX_PAGE_TYPE = 'SPC_PAGE'
  GROUP BY SCHEMA_NAME;

  Example output:

  +-------------+-----------------+
  | SCHEMA_NAME | spc_index_count |
  +-------------+-----------------+
  | test        |               3 |
  +-------------+-----------------+

• View all tables and indexes that use SPC in a specific schema:

  SELECT TABLE_NAME, INDEX_NAME
  FROM INFORMATION_SCHEMA.INNODB_INDEX_STATUS
  WHERE SCHEMA_NAME = 'test' AND INDEX_PAGE_TYPE = 'SPC_PAGE';

  Example output:

  +--------------+------------+
  | TABLE_NAME   | INDEX_NAME |
  +--------------+------------+
  | t_spc_demo   | idx_c1     |
  | t_spc_demo   | idx_c1c2   |
  | t_mixed_demo | idx_c2     |
  +--------------+------------+

Analyze compression statistics

• Use the dbms_spc.analyze_table stored procedure to view detailed compression statistics for an SPC index:

  -- Analyze a table in the current database
  CALL dbms_spc.analyze_table('table_name');
  -- Analyze a table in a specific database
  CALL dbms_spc.analyze_table('db_name.table_name');

  The following table describes the output metrics.

  Metric	Description
  rec_bytes	Total bytes of all records on the page.
  row_bytes	Total bytes of actual row data.
  comp_recs	Number of compressed records.
  uncomp_recs	Number of uncompressed records.
  dict_bytes	Bytes occupied by the page dictionary.
  dict_slots	Total number of slots in the page dictionary.
  dict_unused_slots	Number of redundant prefixes in the page dictionary that are not referenced by any record.
  spc_page	Number of pages in SPC format.
  compression ratio	The compression ratio, which indicates the percentage of space saved by using SPC.

• Control the sample rate: You can control the sampling percentage for the analysis by using the innodb_spc_analyze_sample_percentage variable. The default value is 100, which samples all pages.

  -- Sample 50% of the pages
  SET GLOBAL innodb_spc_analyze_sample_percentage = 50;  

Limitations

• Instance version: Because SPC involves changes to the underlying storage structure, an instance that uses SPC cannot be downgraded to an earlier version that does not support it.

• Existing index handling: After you enable SPC, an existing index is not automatically converted to the SPC format. You must convert it by rebuilding the index.

  1. Create a new index: ALTER TABLE ... ADD INDEX idx_new ...;

  2. Rename the indexes: ALTER TABLE ... RENAME INDEX idx TO idx_old, RENAME INDEX idx_new TO idx;

  3. Drop the original index: ALTER TABLE ... DROP INDEX idx_old;

• Unsupported table types:

  • Compressed tables (ROW_FORMAT=COMPRESSED)

  • Temporary tables (TEMPORARY TABLE)

  • System tables

  • Tables that do not use the COMPACT or DYNAMIC row format

  • Tables with a page size other than 16 KB (only innodb_page_size = 16384 is supported)

• Unsupported index types:

  • Unique indexes (UNIQUE KEY): Use the Panda Index format.

  • Clustered indexes (PRIMARY KEY): Use the standard format.

  • Full-text indexes (FULLTEXT INDEX)

  • Spatial indexes (SPATIAL INDEX)

  • Multi-valued indexes

  • Indexes that contain virtual columns

• DDL behavior changes: Normally, extending the length of a VARCHAR column requires only a data dictionary update and does not require a table rebuild. However, if an SPC index is involved (for example, if the length of a VARCHAR column changes in an existing SPC index or a new SPC index), the DDL operation is forced into an INPLACE table rebuild and generates the following warning:

  Warning 1088: Extending column 'X' requires table rebuild because it is part of an SPC index

  This occurs because changing the column length requires the capacity of the SPC index's page dictionary to be recalculated, which can only be done by rebuilding the table.

• Export and import limitations:

  Limitation	Description
  Format must match	The index formats of the source and target tables must be identical. For example, if the source uses an SPC index, the target must also use an SPC index.
  CFG version requirement	Requires CFG V9 format support. A .cfg file exported from an older version cannot contain SPC information.
  No cross-version support	A tablespace exported from an older version that does not support SPC cannot be imported as an SPC-formatted tablespace.
  Identical page dictionary capacity	The source and target tables must have identical structures to ensure their page dictionary capacities are the same.