How to Partition TiDB by Month

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TIDB 如何根据月份去分区

| username: TiDBer_qgMRUyIV

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.0

I want to partition the data table by month. Is there a way to automatically partition by month? I see that the current tutorials generally require separate configuration for each month, such as:

ADD PARTITION (
PARTITION p202001 VALUES LESS THAN (UNIX_TIMESTAMP(‘2020-02-01 00:00:00’)),
PARTITION p202002 VALUES LESS THAN (UNIX_TIMESTAMP(‘2020-03-01 00:00:00’)),
…
PARTITION p202006 VALUES LESS THAN (UNIX_TIMESTAMP(‘2020-07-01 00:00:00’)),

If configured as above, when it comes to August, I need to manually add partitions again.

2
| username: WalterWj | Original post link

There isn’t one. Write the program and add it yourself.

3
| username: 像风一样的男子 | Original post link

Write a script to create a scheduled task that adds a partition at the end of each month.

4
| username: 有猫万事足 | Original post link

Give this request a thumbs up.

The good news is that range-interval partitioning is now supported. Adding/removing partitions is not complicated.

5
| username: TiDBer_小阿飞 | Original post link

In Range partitioning, you can partition based on the value of the timestamp column and use the unix_timestamp() function, for example:

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

For the timestamp column, using other partition expressions is not allowed.

6
| username: tidb菜鸟一只 | Original post link

Just create it like this.

7
| username: 托马斯滑板鞋 | Original post link

The official team can refer to the automatic partitioning of Doris next door

CREATE TABLE `${tblDate}` (
    `TIME_STAMP` datev2 NOT NULL COMMENT 'Collection Date'
) ENGINE=OLAP
DUPLICATE KEY(`TIME_STAMP`)
AUTO PARTITION BY RANGE date_trunc(`TIME_STAMP`, 'month')
(
)
DISTRIBUTED BY HASH(`TIME_STAMP`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
8
| username: zhanggame1 | Original post link

It’s better to create it in advance. Even if there is an automatic partitioning feature, I don’t think it’s reliable. TiDB’s DDL cannot be executed concurrently, and executing it multiple times will always encounter issues with execution getting stuck. Partition table operations, in particular, are prone to problems.

9
| username: TiDB_C罗 | Original post link

Is using try-catch in the program a good approach?

10
| username: Kongdom | Original post link

Leave it to the application layer to handle. If the DBA can’t solve it, just throw it to the programmers. :yum:

11
| username: 大飞哥online | Original post link

This is possible, haha.

12
| username: zhanggame1 | Original post link

No, it’s best to check through the system table to see if the partition exists, and if not, add it.

13
| username: zhanggame1 | Original post link

Creating the database in advance also has its benefits. Sometimes the DDL for TiDB partitions can get stuck, so it’s better to choose an appropriate time to do it.

14
| username: Kongdom | Original post link

Building it in advance will occupy space ahead of time, right? You can only choose the most suitable option based on your own application scenario.

15
| username: Fly-bird | Original post link

Manually execute the partition, if you’re feeling lazy, write a scheduled task yourself. If that doesn’t work, let the developers handle it.

16
| username: ajin0514 | Original post link

Write a program to implement

17
| username: TiDBer_vfJBUcxl | Original post link

Script implementation, parameters have a pattern.