PolarDB:WEEK

Limitations

• The partitioning key must be of the DATE, DATETIME, or TIMESTAMP type.

• WEEK applies to table shards only. It cannot be used for database shards.

• Each database shard can have at most 7 table shards, one per day of the week.

• Requires PolarDB-X 1.0 version 5.1.28-1320920 or later.

How it works

WEEK extracts the day-of-week value from the partitioning key and uses it as the table shard subscript.

Use cases

WEEK is suited for workloads where data access patterns follow a weekly cycle. If your application generates and queries data by day of the week, WEEK aligns each physical table shard with a specific day, so queries on a single day's data hit exactly one shard.

Example

The following Data Definition Language (DDL) statement creates a table that partitions data into database shards by name using HASH, and into 7 table shards by day of the week using WEEK on the create_time column.

CREATE TABLE test_week_tb (
    id int,
    name varchar(30) DEFAULT NULL,
    create_time datetime DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
dbpartition by HASH(name)
tbpartition by WEEK(create_time) tbpartitions 7;

• dbpartition by HASH(name) — distributes rows across database shards by hashing the name column.

• tbpartition by WEEK(create_time) tbpartitions 7 — distributes rows across 7 table shards by day of the week, derived from create_time.