All Products
Search

This Product

    PolarDB:Create a hybrid partition

    Document Center

    PolarDB:Create a hybrid partition

    Last Updated:Sep 02, 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 archive a partitioned table as a hybrid partitioned table, the following conditions must be met:

    • MySQL 8.0.2 and the minor version is 8.0.2.2.34.1 or later. You must set the loose_polar_allow_create_hybrid_partition parameter to ON.

    • MySQL 8.0.2 and the minor version is earlier than 8.0.2.2.34.1. Upgrade to a later minor version.

    Prerequisites

    • Your cluster must run PolarDB for MySQL 8.0.2 with revision version 8.0.2.2.34.1 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.

    • Partitioned tables archived through cold data archiving can be archived as hybrid partitioned tables.

    Limitations

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

    • 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 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.

    Parameters

    When you use hybrid partitioned tables, you can configure the following parameters on the Parameters page of your PolarDB cluster based on your business requirements.

    Parameter

    Level

    Description

    loose_hybrid_partition_query_mix_engine_enabled

    Global

    Specifies the rule for querying hybrid partitioned tables. Valid values:

    • OFF (default): When you query a hybrid partitioned table, the query covers partitions in both the InnoDB engine and the X-Engine engine.

    • ON: When you query a hybrid partitioned table, the query covers partitions in the InnoDB engine, the X-Engine engine, and OSS.

    loose_polar_dlm_storage_mode

    Global

    The switch for the automatic cold data archiving (DLM) feature. Valid values:

    • OFF (default): DLM storage is disabled. The scheduler does not start and no archiving operations are performed.

    • RO (not recommended): DLM storage is read-only. Archived cold data partitions can be queried, but no new archiving write operations are performed. This value is rarely used in production.

    • RW (recommended): DLM storage is read-write. The scheduler starts and supports automatic archiving based on DLM policies, and archived data can also be queried.

    Note

    • This parameter applies only to MySQL 8.0.2 clusters with revision version 8.0.2.2.34.1 or later.

    • After you enable cold data archiving, the system automatically sets this parameter to RW. No manual modification is required.

    • If you only use the X-Engine high-compression engine to archive partitioned tables (without OSS cold data archiving), you must manually set this parameter to RW to enable DLM scheduling.

    loose_allow_create_hybrid_partition

    Global

    Controls whether to allow creating or archiving partitioned tables that contain non-InnoDB engine partitions. Valid values:

    • OFF (default): Creating partitioned tables that contain non-InnoDB engine partitions is prohibited. All partitions must use the InnoDB engine.

    • ON (recommended): Creating partitioned tables that contain non-InnoDB engine partitions is allowed. You can specify OSS (CSV/ORC) or the X-Engine engine at the partition level.

    Note

    • This parameter applies only to MySQL 8.0.2 clusters with revision version 8.0.2.2.34.1 or later.

    • After you enable cold data archiving, the system may automatically set this parameter to ON depending on the version.

    loose_use_oss_meta

    Global / Session

    Controls whether OSS metadata (meta) management is enabled for OSS tables. The loose_csv_oss_file_filter parameter depends on this parameter. Valid values:

    • ON: OSS metadata management is enabled. Meta information is written when OSS tables are created or archived. The file list is then retrieved by reading the metadata, and the loose_csv_oss_file_filter filtering feature is supported.

    • OFF: OSS metadata management is disabled. No meta information is written. Metadata is obtained by directly listing OSS files, and loose_csv_oss_file_filter is not supported.

    loose_csv_oss_file_filter

    Global / Session

    Controls whether file-level filtering (File Filter) is enabled when querying OSS tables. This feature uses min/max statistics and Bloom filters to skip irrelevant data files and reduce the amount of OSS data scanned. This feature requires the loose_use_oss_meta parameter to be enabled. Valid values:

    • OFF: File filtering is disabled. All data files in the OSS table are scanned during queries.

    • ON (recommended): File filtering is enabled. During queries, loose_csv_oss_file_filter is used with WHERE conditions to improve query performance.

    dlm_prune_archiving_oss_partitions

    Global / Session

    The switch for automatic pruning of archived OSS partitions by the Data Lifecycle Management (DLM) feature. When DML operations (UPDATE/DELETE) are executed, the system automatically prunes partitions that are being archived to OSS (ORC/CSV) and partitions that have already been archived to OSS. This prevents DML operations from being blocked by archiving and resolves conflicts between DML and DDL. Valid values:

    • ON (default, recommended): DML operations (UPDATE/DELETE) automatically prune partitions that are being archived to OSS.

    • OFF: DML operations do not automatically prune partitions that are being archived to OSS.

    hybrid_partition_query_mix_engine_enabled

    Global / Session

    The switch for automatic query pruning. When a SELECT query is executed, the system automatically prunes OSS (ORC/CSV) partitions to prevent slow queries caused by accessing cold data. Valid values:

    • OFF (default, recommended): Queries automatically prune OSS partitions.

    • ON (not recommended): Queries do not automatically prune 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);