A partitioned table stores data in separate physical locations based on one or more columns. When you query a partitioned table, MaxCompute scans only the matching partitions instead of the entire table, which reduces computation costs and makes large-scale data management more flexible.
Partitioning options
MaxCompute offers two partitioning approaches:
| Approach | How partitions are created | Best for |
|---|---|---|
| Standard partition tables | You define one or more columns as partition keys | Tables where you know the partition column and its value range in advance — for example, partitioning a sales table by region or date |
| Auto partition tables | Partition columns are generated automatically based on time | Tables where you want partitions created automatically as data arrives, without manual ALTER TABLE operations |
Auto partition tables support two methods:
| Auto-partition method | Description |
|---|---|
| Auto partition table based on time computing function | MaxCompute applies the TRUNC_TIME function to a time-based column (DATE, DATETIME, or TIMESTAMP) and uses the result as the partition value. |
| Auto partition table based on data writing time | MaxCompute applies the TRUNC_TIME function to the ingestion time — the time data is written to the system. This is also known as ingestion time partitioning. |
When to use partitioned tables
Partitioned tables are a good fit in the following situations:
-
Your table is large and queries are slow: Partition filters let MaxCompute skip non-matching partitions entirely, eliminating full table scans.
-
Query patterns concentrate on specific time ranges or categories: The more selective your partition filter, the greater the performance gain.
-
You need to write or delete data at a coarser granularity than individual rows: With partitioned tables, you can target a specific partition directly — for example, dropping last month's data in seconds rather than issuing row-level deletes.
-
You want automatic data expiration: Lifecycle policies operate at the partition level, so cleanup requires no manual intervention.
Partition size guidelines
Partition granularity directly affects query performance and system overhead:
| Constraint | Value | Guidance |
|---|---|---|
| Minimum rows per partition | 10,000 | Partitions with fewer rows increase metadata overhead without improving pruning effectiveness. |
| Maximum partitions per table | 60,000 | Avoid designs that create a large number of fine-grained partitions (for example, partitioning by second or by individual user ID). |
| Maximum partitions per query | 10,000 | If your queries regularly approach this limit, consider coarser partition granularity or restructuring your partition hierarchy. |
Limitations
-
A table can have a maximum of 6 partition levels.
-
A table can have a maximum of 60,000 partitions.
-
A single query can scan a maximum of 10,000 partitions.
-
STRING partition key values cannot contain Chinese characters.
Data types for partition columns
MaxCompute V2.0 supports STRING, TINYINT, SMALLINT, INT, BIGINT, and VARCHAR as partition column types. MaxCompute stores all partition values internally as STRING and converts other types automatically. To avoid type-related errors, define partition columns as STRING.
MaxCompute 1.0 data type version supports only STRING for partition columns. Defining a partition column as BIGINT affects only how the type appears in the table schema — in all computations and comparisons, MaxCompute treats the column as STRING.
This distinction matters for partition pruning. When a WHERE clause compares a STRING partition key to a BIGINT value (without quotes), MaxCompute implicitly casts both sides to DOUBLE. The implicit cast can cause precision loss and breaks partition pruning, which forces a full table scan even though you specified a partition condition. The following example shows this behavior:
-- Create a partitioned table with a BIGINT partition column (MaxCompute 1.0 data type version)
CREATE TABLE parttest (a BIGINT) PARTITIONED BY (pt BIGINT);
-- Insert data
INSERT INTO parttest PARTITION(pt)(a, pt) VALUES (1, 1);
INSERT INTO parttest PARTITION(pt)(a, pt) VALUES (1, 10);
-- Query the rows where the value of pt is greater than or equal to 2.
-- The returned result is empty.
SELECT * FROM parttest WHERE pt >= '2';Time and date types (DATE, DATETIME, TIMESTAMP) are not supported as partition column types. To partition by time, use an auto partition table with the TRUNC_TIME function.
Standard partition table examples
The following examples use a two-level partitioned table: the first level is pt (date) and the second is region.
Step 1: Create the partitioned table.
-- Two-level partitioned table: first level by date (pt), second level by region
CREATE TABLE src (shop_name STRING, customer_id BIGINT) PARTITIONED BY (pt STRING, region STRING);Step 2: Add partitions and insert data.
ALTER TABLE src ADD PARTITION (pt='20170506', region='hangzhou') PARTITION (pt='20180101', region='shanghai');
INSERT INTO src PARTITION (pt='20170506', region='hangzhou') VALUES ('s1', 1), ('s2', 2), ('s3', 3);
INSERT INTO src PARTITION (pt='20180101', region='shanghai') VALUES ('c1', 4), ('c2', 5), ('c3', 6);Step 3: Query using partition filters.
Use string literals for partition key values. MaxCompute locates the matching partitions directly using metadata and skips all non-matching data.
-- Correct: string literals trigger partition pruning
SELECT * FROM src WHERE pt='20170506' AND region='hangzhou';Output:
+------------+-------------+------------+------------+
| shop_name | customer_id | pt | region |
+------------+-------------+------------+------------+
| s1 | 1 | 20170506 | hangzhou |
| s2 | 2 | 20170506 | hangzhou |
| s3 | 3 | 20170506 | hangzhou |
+------------+-------------+------------+------------+Avoid comparing a STRING partition key to a numeric literal. MaxCompute implicitly casts both sides to DOUBLE, which can cause precision loss and bypass partition pruning entirely:
-- Incorrect: numeric literals cause an implicit cast to DOUBLE, breaking partition pruning
SELECT * FROM src WHERE pt = 20170506 AND region = hangzhou;