PolarDB:Create hybrid partitions

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for MySQL 8.0.2 cluster with minor engine version 8.0.2.2.5 or later. To check your version, see Check the version number.

• Data files for the OSS partitions already uploaded to OSS, named according to these rules: For example, if partition p1 of table t1 uses the CSV storage engine, its data file must be named t1#P#p1.CSV.

  • File extensions and partition markers must be uppercase.

  • Table and partition names must match those in the database exactly.

Limitations

• Each hybrid partitioned table must include at least one InnoDB partition.

• Hybrid partitioned tables support only RANGE and LIST partition types.

• OSS partitions are read-only — INSERT, UPDATE, DELETE, and LOAD operations on OSS partitions are not supported.

• If you create a hybrid partitioned table with subpartitions, the hash partition engine must match the table engine, and at least one subpartition must use the InnoDB engine.

• ADD and DROP partition operations on hybrid partitioned tables require minor engine version 8.0.2.2.17 or later.

Configure the query parameter

Set loose_hybrid_partition_query_mix_engine_enabled in your cluster's Parameters section to control which storage engines are scanned during queries.

Set up an OSS server

Hybrid partitioned tables connect to OSS through a server object. If you haven't created one, run the following statement:

CREATE SERVER <oss_server_name>
FOREIGN DATA WRAPPER oss
OPTIONS(EXTRA_SERVER_INFO '{
  "oss_endpoint": "<oss_endpoint>",
  "oss_bucket": "<oss_bucket>",
  "oss_access_key_id": "<oss_access_key_id>",
  "oss_access_key_secret": "<oss_access_key_secret>",
  "oss_prefix": "<oss_prefix>",
  "oss_sts_token": "<oss_sts_token>"
}');

oss_sts_token is supported on PolarDB for MySQL 8.0.2 clusters with minor engine version 8.0.2.2.6 or later.

Replace the placeholders with your values:

When the STS token expires: The token has a default time-to-live (TTL). If it expires, refresh all values in EXTRA_SERVER_INFO using ALTER SERVER:

ALTER SERVER <server_name> OPTIONS(EXTRA_SERVER_INFO '{
  "oss_endpoint": "<oss_endpoint>",
  "oss_bucket": "<oss_bucket>",
  "oss_access_key_id": "<oss_access_key_id>",
  "oss_access_key_secret": "<oss_access_key_secret>",
  "oss_prefix": "<oss_prefix>",
  "oss_sts_token": "<oss_sts_token>"
}');

Create a hybrid partitioned table

The following example creates table t2 with a RANGE partition on column a1, separating data by age:

• Cold data (older records, values < 2000): partitions p1 and p2 use CSV storage on OSS — read-only, lower cost.

• Hot data (recent records, values 2000–2999): partition p3 uses InnoDB on PFS — supports full read/write.

CREATE TABLE t2 (a1 INT, a2 VARCHAR(30), a3 VARCHAR(256))
CONNECTION = "oss_server_name"
PARTITION BY RANGE (a1)
(
  PARTITION p1 VALUES LESS THAN (1000) ENGINE = CSV,
  PARTITION p2 VALUES LESS THAN (2000) ENGINE = CSV,
  PARTITION p3 VALUES LESS THAN (3000) ENGINE = INNODB
);

CONNECTION specifies the OSS server to use. The ENGINE value per partition determines where that partition's data is stored — CSV partitions are read from OSS, while InnoDB partitions use local PFS storage.

Query hybrid partitions

Query hybrid partition data in one of three ways:

Scan all engines (InnoDB, X-Engine, and OSS): Set loose_hybrid_partition_query_mix_engine_enabled to ON, then run a standard query.

-- Returns data from all partitions, including OSS (cold data).
SELECT * FROM t1;

Scan InnoDB and X-Engine only: Set loose_hybrid_partition_query_mix_engine_enabled to OFF (the default), then run a standard query. OSS partitions are excluded.

-- Returns data from InnoDB and X-Engine partitions only (hot data).
SELECT * FROM t1;

Query a specific partition: Specify the partition name to query data from that partition regardless of its storage engine.

-- Queries partition p1 directly, regardless of its storage engine.
SELECT * FROM t1 PARTITION (p1);