All Products
Search

This Product

    PolarDB:Create a hybrid partition

    Document Center

    PolarDB:Create a hybrid partition

    Last Updated:Jun 26, 2026

    You can create a hybrid partitioned table to access data stored in different storage engines.

    The following diagram illustrates how hybrid partition works.

    image

    Hybrid partition separates hot and cold data by storing a table's partitions on different storage media. This reduces storage costs for cold data without affecting query performance or DML operations on hot data.

    Note

    To use this feature, open Quota Center, find the quota name by using the quota ID polardb_mysql_hybrid_partition, and click Apply in the Actions column.

    Prerequisites

    • Your cluster must run PolarDB for MySQL 8.0.2 with revision version 8.0.2.2.34 or later. You can view the version number to check your cluster version.

    • The data files for the partitions you want to create must already exist in OSS, and their names must follow these rules:

      • File extensions and partition markers must be in uppercase.

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

      For example, if the p1 partition of table t1 uses the CSV engine, the data file for the partition is named t1#P#p1.CSV.

    Limitations

    • A hybrid partitioned table must contain at least one partition that uses the InnoDB storage engine.

    • When you create a hybrid partitioned table with subpartitions, the first-level partition's engine must match the table's engine, and at least one subpartition must use the InnoDB storage engine.

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

    • DML operations (including INSERT, UPDATE, DELETE, or LOAD) are not supported on OSS partitions in hybrid partitioned tables.

    • ADD and DROP operations on a hybrid partitioned table require your cluster to run PolarDB for MySQL 8.0.2 with revision version 8.0.2.2.17 or later.

    Parameters

    You can configure the following parameter on the Parameters page of your PolarDB cluster.

    Parameter

    Description

    loose_hybrid_partition_query_mix_engine_enabled

    Controls which partitions are queried in a hybrid partitioned table. Valid values:

    • OFF (Default): Queries return data only from InnoDB and X-Engine partitions.

    • ON: Queries return data from InnoDB, X-Engine, and OSS partitions.

    Create a hybrid partition

    To create a hybrid partitioned table, you need an OSS server. If you do not have one, use the following syntax to create it:

    CREATE SERVER oss_server_name
FOREIGN DATA WRAPPER oss
OPTIONS(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>",
"oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_access_key_id>",
"oss_access_key_secret": "<my_oss_access_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');
    Note

    If the cluster version is PolarDB for MySQL 8.0.2 and the revision version is 8.0.2.2.6 or later, the my_oss_sts_token parameter is supported.

    The following table describes the parameters.

    Parameter

    Type

    Description

    oss_server_name

    string

    The name of the OSS server.

    Note

    This parameter is global and must be unique. The name is case-insensitive and can be up to 64 characters long. Names longer than 64 characters are truncated. You can specify the OSS server name as a quoted string.

    my_oss_endpoint

    string

    The endpoint of the OSS region.

    Note

    If you access the database from an Alibaba Cloud host, use an internal endpoint to avoid incurring internet traffic. An internal endpoint typically contains the word "internal".

    my_oss_bucket

    string

    The OSS bucket that contains the data files. This bucket must already exist in OSS.

    my_oss_access_key_id

    string

    The AccessKey ID for accessing OSS.

    my_oss_access_key_secret

    string

    The AccessKey Secret for accessing OSS.

    my_oss_prefix

    string

    The path prefix for data files in OSS. This value cannot be empty or contain special characters.

    my_oss_sts_token

    string

    OSS temporary access credentials. For details on obtaining OSS temporary access credentials, see Obtain temporary access credentials.

    Note

    The my_oss_sts_token parameter value has a default expiration time. If my_oss_sts_token has expired, you need to reset all parameter values in EXTRA_SERVER_INFO using the following command.

    ALTER SERVER server_name OPTIONS(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>",
"oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_access_key_id>",
"oss_access_key_secret": "<my_oss_access_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');

    The following example shows how to create a hybrid partitioned table:

    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
);

    Query a hybrid partition

    You can query partitions on different storage engines in three ways:

    • Set the hybrid_partition_query_mix_engine_enabled parameter to ON. Queries then return data from InnoDB, X-Engine, and OSS partitions. Example:

      -- t1 is a hybrid partitioned table.
SELECT * FROM t1;

    • Set the hybrid_partition_query_mix_engine_enabled parameter to OFF. Queries then return data only from InnoDB and X-Engine partitions. Example:

      -- t1 is a hybrid partitioned table.
SELECT * FROM t1;

    • Query a specific partition by name. This retrieves data from the specified partition, regardless of its storage engine. Example:

      -- t1 is a hybrid partitioned table, and p1 is the name of the partition to query.
SELECT * FROM t1 partition (p1);