Configure an IMCI with extended attributes - PolarDB

An In-Memory Column Index (IMCI) exposes several extended attributes that let you tune row group size, compression, sort order, pruning indexes, and write behavior. Set these attributes in the table comment when you create a table, using the syntax COLUMNAR=1 <optName>=<optValue>.

Extended attributes

pack_shift

Controls the row group size of IMCI data, which directly affects how much data resides in memory at a time. Adjust this when working with a large number of tables or partitioned tables.

Valid values: Integer from 6 to 18
Default: The value of the loose_imci_default_pack_shift parameter

To view or change loose_imci_default_pack_shift, see IMCI-related parameters. To configure it in the PolarDB console, see Specify cluster and node parameters.

codec_opt

Sets the compression algorithm for IMCI data. Different algorithms suit different data characteristics — for example, time-series data, low-cardinality columns, and general-purpose workloads each benefit from different choices. For the full list of supported algorithms and guidance on selecting one based on your data, see Set a compression algorithm.

order_key

Sets a sort key for the IMCI. Sorting data by frequently filtered columns lets the engine skip irrelevant row groups during scans, which can significantly improve query performance. For configuration details, see Configure sort keys for IMCIs.

pruner / pruner_minmax / pruner_bloom

Controls whether to build secondary indexes on string columns to skip data during scans. Two index types are available:

Index type	Best for
`pruner_minmax`	Range queries (`>`, `<`, `BETWEEN`) on columns with sortable values
`pruner_bloom`	Equality and IN-list queries on string columns with many distinct values

Use both together on string columns to cover different query patterns.

On PolarDB for MySQL 8.0.1.1.35 or later, minmax indexes and Bloom filters are created on string columns by default.

For full configuration details, see IMCI optimization for statistic queries.

prefix_len

Sets the prefix length, in bytes, for minmax pruners on string columns. A shorter prefix reduces index size but may lower pruning effectiveness for long strings.

Valid values: Integer from 1 to 255
Default: 20

For details on creating minmax indexes when you create a table, see the Create minmax indexes when you create a table section in IMCI optimization for statistic queries.

write_policy

Controls how IMCI data is written to storage files. This setting affects both disk space usage and write I/O performance.

Value	Name	Space efficiency	When to use
`0`	ForCapacity	Highest — max 1 fragment of 4 KB per write; storage space is prioritized and data is split across multiple files to minimize fragmentation	Storage is constrained; fragmentation must be minimized
`1`	ForPerformance	Lowest — maximum fragmentation and space waste; each write goes to a file named ExtentSize for best I/O performance	I/O throughput is the top priority and storage space is not constrained
`2`	Skip4K	High — max 1 fragment of 16 KB per write; based on Page Free Space (PFS); when written data is more than 4 KB, the 4 KB extent is ignored	Balanced workloads where 16 KB alignment meets performance requirements
`3`	Tradeoff	Moderate — has higher priority than Skip4K; behaves like ForPerformance for writes below 1 MB; splits across files at 1 MB or above	Mixed write sizes where large batches should be space-efficient

Default: The value of the loose_imci_default_write_policy parameter, which accepts the same values (0–3)

To view or change loose_imci_default_write_policy in the PolarDB console, see Specify cluster and node parameters.

Set extended attributes

Specify extended attributes in the COMMENT clause when you create a table. Use the format COLUMNAR=1 <optName>=<optValue>, where multiple attributes are separated by spaces.

Syntax

CREATE TABLE lineitem COMMENT "COLUMNAR=1 <optName>=<optValue>";

Parameter	Description
`optName`	Name of the extended attribute. For example, `pack_shift`.
`optValue`	Value of the extended attribute.

Example

The following example creates the lineitem table as a columnstore table with pack_shift set to 10:

CREATE TABLE lineitem (
  l_orderkey       INTEGER NOT NULL,
  l_partkey        INTEGER NOT NULL,
  l_suppkey        INTEGER NOT NULL,
  l_linenumber     INTEGER NOT NULL,
  l_quantity       DECIMAL(15,2) NOT NULL,
  l_extendedprice  DECIMAL(15,2) NOT NULL,
  l_discount       DECIMAL(15,2) NOT NULL,
  l_tax            DECIMAL(15,2) NOT NULL,
  l_returnflag     CHAR(1) NOT NULL,
  l_linestatus     CHAR(1) NOT NULL,
  l_shipdate       DATE NOT NULL,
  l_commitdate     DATE NOT NULL,
  l_receiptdate    DATE NOT NULL,
  l_shipinstruct   CHAR(25) NOT NULL,
  l_shipmode       CHAR(10) NOT NULL,
  l_comment        VARCHAR(44) NOT NULL
) COMMENT "COLUMNAR=1 pack_shift=10";

To set multiple attributes, append each <optName>=<optValue> pair separated by a space within the same comment string.