Using Partitioned Tables in Conjunction with TiFlash

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

Original topic: 分区表,tiflash 结合使用

| username: 林夕一指

[Test Environment for TiDB]
[TiDB Version] V6.5.0
[Encountered Issue: Problem Description and Impact] Is it possible to create a TiFlash replica for a specific partition of a partitioned table? There is a scenario: Partitioned table A needs to use placement rules to store hot and cold data separately. Partitioned table A involves complex queries including joins and various analytical scenarios using “in”. We hope to use TiFlash for acceleration, but only for analyzing hot data.

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

It seems that specifying TiFlash replicas for individual partitions is not supported. However, if you specify it for the entire table, scanning a specific partition should not scan the TiFlash replicas of the cold data, right?

| username: 林夕一指 | Original post link

The principle is correct, but it feels like the data redundancy is severe.

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

Or try specifying the corresponding partition to add the specified replica through placement rules?

| username: 林夕一指 | Original post link

It’s still not working. I can only locate the data of the entire table, not the partitioned data. Or is there a way to move partitions from table A to A_history? That would work too.

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

Of course, there is:
ALTER TABLE partitioned_table EXCHANGE PARTITION p1 WITH TABLE non_partitioned_table

| username: 林夕一指 | Original post link

This doesn’t work either. With TiFlash, you can’t execute exchange anymore. :rofl:

| username: redgame | Original post link

It needs to be done for the entire table.

| username: cy6301567 | Original post link

Can two large partitioned tables be joined at will? Is the performance high?

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

With TiFlash, not being able to use EXCHANGE PARTITION is a bug. The corresponding issue has already been closed. The original issue mentioned that EXCHANGE PARTITION couldn’t be used when both tables had TiFlash replicas.

In your current situation, it might be that one table has a TiFlash replica and the other does not. I think you could consider creating a minimal reproducible SQL and submitting another issue to see if it helps.

| username: 林夕一指 | Original post link

Submitted.

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

To give you some confidence, I also submitted an issue before. It was resolved in about a week. Whether the open-source community is effective or not, experiencing it yourself is better than a thousand words. :fist:

| username: ShawnYan | Original post link

By the way, GitHub issues also support Markdown. I suggest modifying the format, which can speed up the issue resolution!

| username: ShawnYan | Original post link

True experts still pay great attention to detail.

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

Hahaha, indeed :+1:

| username: 林夕一指 | Original post link

Even non_partitioned_table needs to create TiFlash replicas :sweat_smile:

| username: ShawnYan | Original post link

After all, they are two different things. Once exchanged out, it becomes a single table. The original poster’s requirement is to create a TiFlash replica for a specific partition while retaining the partitioned table’s organizational structure.

| username: 林夕一指 | Original post link

Yes, there may not be a good solution at the moment. :sweat_smile: We can only settle for the next best thing, such as deleting partitions or using more resources to support TiFlash replicas for the two tables.

| username: ShawnYan | Original post link

btw, you will find this feature useful, TiFlash supports dynamic partition pruning.