Does TiDB partition table have an automatic partition creation feature?

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

Original topic: tidb分区表是否有自动创建分区的功能

| username: CHENGX

Currently, we need to partition data according to business time, but we found that it is impossible to achieve automatic partition management at the database level. We need to use custom tasks to operate the database regularly. Have any of you encountered similar usage scenarios, or does the official support this feature?

Similar to the dynamic partitioning feature of StarRocks.

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

TiDB introduced this feature starting from version 6.3

| username: CHENGX | Original post link

Okay, thanks, I’ll give it a try.

| username: CHENGX | Original post link

I checked and found that range-interval automatically creates a certain number of partitions for a specified range and interval, but it does not automatically add partitions daily based on the partition field. :flushed:

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

I don’t understand your requirement. Wouldn’t this work?
Do you also need to periodically delete historical partitions?

| username: CHENGX | Original post link

Yes, it is dynamic partition management, not preset fixed partitions.

| username: CHENGX | Original post link

Currently, we use scheduled tasks in the program to delete historical partitions and add partitions for the next 5 days.

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

Then I suggest considering the TTL feature.

| username: CHENGX | Original post link

This method can also meet the requirement of deleting data, but it still cannot satisfy the function of automatically adding partitions as shown in the picture.

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

What I mean is to use them together. First, create partitions up to 2050 or 2099, and then use TTL to perform periodic deletions. After all, the future partitions are empty, right? Do you have to ensure that there are only 3 partitions in the future?

| username: zhanggame1 | Original post link

If it is a list partition, version 7.3 has added a default partition feature.

| username: CHENGX | Original post link

It seems that there is an upper limit on the number of TiDB partitions, and you can’t create that many. :flushed:

| username: zhanggame1 | Original post link

The current new version has a limit of 8192 partitions per table.

| username: ShawnYan | Original post link

Could you share the link to StarRocks?
How many partitions do you need to create in TiDB?

| username: redgame | Original post link

Build one by one…

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

I’m just giving an example. You can start by creating partitions for 20 years from 2023, up to 8192 partitions, one per day, and 20 years will be fine… After 20 years, you can delete all the previous partitions…

| username: CHENGX | Original post link

| username: TiDBer_vfJBUcxl | Original post link

Starting from today, it will be around 2022.

| username: CHENGX | Original post link

Understood, our tables might be numerous (a few hundred), so manually maintaining them could be quite troublesome.

| username: CHENGX | Original post link

Build one every day.