PolarDB:Set a compression algorithm

Supported algorithms

ZSTD is the default algorithm (imci_default_codec = ZSTD).

Set compression when creating a table

Specify the codec_opt parameter in the COMMENT field of a CREATE TABLE statement.

Syntax:

COMMENT 'COLUMNAR=1 codec_opt={LZ4}'

Valid values: LZ4, ZSTD, and NONE (no compression).

Precedence rules:

A column-level codec_opt value overrides both the table-level value and imci_default_codec. To change the default algorithm at the session level, run:

SET imci_default_codec="{LZ4}";

Example:

-- Table-level: all columns use LZ4
CREATE TABLE t12(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB COMMENT 'COLUMNAR=1 codec_opt={LZ4}';

-- Mixed: column-level overrides session default
SET imci_default_codec="{LZ4}";

CREATE TABLE t13(
  col1 INT COMMENT 'codec_opt={NONE}',        -- No compression
  col2 DATETIME,                               -- Inherits imci_default_codec (LZ4)
  col3 VARCHAR(200) COMMENT 'codec_opt={ZSTD}' -- Column-level overrides default
) ENGINE InnoDB COMMENT 'COLUMNAR=1';

In this example:

• t12: All three columns use LZ4 because it is set at the table level.

• t13: col1 has no compression (NONE). col2 inherits the session-level default (LZ4 from SET imci_default_codec). col3 uses ZSTD because the column-level value takes precedence.

Update the compression algorithm for a column

ALTER TABLE does not rebuild IMCI data, so a new codec_opt value set via ALTER TABLE does not take effect immediately.

-- Original table: col1 and col2 use ZSTD
CREATE TABLE t14(
  col1 INT COMMENT 'COLUMNAR=1 codec_opt={ZSTD}',
  col2 DATETIME COMMENT 'COLUMNAR=1 codec_opt={ZSTD}',
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- Attempt to switch col2 to LZ4
ALTER TABLE t14 COMMENT 'COLUMNAR=1', MODIFY COLUMN col2 DATETIME COMMENT 'codec_opt={LZ4}';

After this ALTER TABLE statement:

To apply the new algorithm immediately, delete the column's original data and recreate the table with the updated codec_opt value.

Direct modification of IMCI compression without a data rebuild will be supported in a future release.