All Products
Search

This Product

    PolarDB:Create hybrid partitions

    Document Center

    PolarDB:Create hybrid partitions

    Last Updated:Feb 15, 2026

    Hybrid partitions allow you to access data stored in different storage engines.

    The following diagram shows how hybrid partitions work:

    image

    A hybrid partitioned table stores its partitions on different storage media. This lets you separate hot and cold data for independent management. For example, you can store hot data on local PFS and cold data on OSS. This approach reduces the storage cost of cold data without affecting query performance or DML operations on hot data.

    Note

    The hybrid partitioned table feature is in canary release. To use this feature, go to the Quota Center, find the quota with the Quota ID polardb_mysql_hybrid_partition, and then click Request in the Actions column.

    Prerequisites

    • Your cluster must run PolarDB for MySQL 8.0.2 with a minor engine version of 8.0.2.2.5 or later. You can confirm your cluster version by checking the version number.

    • Data files for the partitions must already exist in OSS. Their names must follow these naming rules:

      • The file extensions and partition markers must be in uppercase.

      • The table and partition names must be the same as those in the database.

      For example, if partition p1 of table t1 uses the CSV storage engine, its data file must be named t1#P#p1.CSV.

    Important notes

    • Each hybrid partitioned table must include at least one partition created on the InnoDB engine.

    • If you create a hybrid partitioned table with subpartitions, the hash partition engine must match the table engine. Also, at least one subpartition must be created on the InnoDB engine.

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

    • You cannot run DML operations, such as INSERT, UPDATE, DELETE, or LOAD, on OSS partitions in hybrid partitioned tables.

    • Clusters running PolarDB for MySQL 8.0.2.2.17 or later support ADD and DROP operations on hybrid partitioned tables. Earlier versions do not support these operations.

    Parameters

    When you use hybrid partitioned tables, you must set the following parameters in the Parameters of your PolarDB cluster.

    Parameter

    Description

    loose_hybrid_partition_query_mix_engine_enabled

    Controls how queries scan hybrid partitioned tables. Valid values:

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

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

    Create hybrid partitions

    You can create hybrid partitions using an existing OSS server. If an OSS server does not exist, you can use the following syntax to create one:

    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

    The my_oss_sts_token parameter is supported on PolarDB for MySQL 8.0.2 clusters with Revision version 8.0.2.2.6 or later.

    The following table describes the parameters:

    Parameter

    Type

    Notes

    oss_server_name

    String

    Name of the OSS server.

    Note

    This is a global parameter and must be unique. It is case-insensitive and up to 64 characters long. Names longer than 64 characters are truncated. You can enclose the name in quotes.

    my_oss_endpoint

    String

    Domain name for the OSS region.

    Note

    If you access the database from an Alibaba Cloud host, use the internal same-region endpoint (a domain name that includes "internal") to avoid Internet traffic.

    my_oss_bucket

    String

    OSS bucket where the data files reside. Create this bucket in OSS first.

    my_oss_access_key_id

    String

    OSS account ID.

    my_oss_access_key_secret

    String

    OSS account key.

    my_oss_prefix

    String

    OSS path prefix. Specifies where to store data files. Must not contain special characters or be empty.

    my_oss_sts_token

    String

    OSS temporary access credential. For details, see Obtain temporary access credentials.

    Note

    The my_oss_sts_token value has a default time-to-live (TTL). If my_oss_sts_token expires, reset all values in EXTRA_SERVER_INFO using this 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 hybrid partitions

    You can query partition data across different storage engines in one of the following three ways:

    • Set the hybrid_partition_query_mix_engine_enabled parameter to ON. This allows queries to return data from InnoDB, X-Engine, and OSS partitions. For example:

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

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

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

    • Specify a partition name. This lets you query data from a partition that uses any type of storage engine. For example:

      -- t1 is a hybrid partitioned table. p1 is the partition name.
SELECT * FROM t1 PARTITION (p1);