Can TiDB Partitioning Reduce Memory Consumption for Full Table Scan Scenarios?

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

Original topic: 针对全表查询场景,Tidb分区是否可以降低内存消耗

| username: TiDBer_45iRGzDe

I am using TiDB V6.5, and the data in a single table online has reached tens of millions and is steadily growing. If a full table scan is unavoidable, will adding partitions reduce memory consumption? If partitions are added, will TiDB’s parallel search strategy dynamically adjust based on memory conditions?

Additionally, is there a method to add partitions to an online table? When I directly operate alter table ${name} partition, it reports an error: unsupported.

| username: Billmay表妹 | Original post link

If a single table’s data reaches tens of millions and continues to grow steadily, adding partitions can reduce memory consumption and improve query efficiency. TiDB’s parallel search strategy dynamically adjusts based on memory conditions to achieve optimal query efficiency. You can use the ALTER TABLE command to add partitions to an online table. For specific operations, please refer to Partitioning.

However, it should be noted that TiDB currently does not support adding partitions online; you need to stop writing first and then add partitions. Additionally, if you are using TiDB v6.5.0 or above, you need to use the ALTER TABLE ... ADD PARTITION command to add partitions. For specific operations, please refer to ALTER TABLE. If you encounter an unsupported error when executing the ALTER TABLE command, please check whether your TiDB version supports the command or if your syntax is correct.

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

First question, if you often only need to query data from a specific partition, partitioning can definitely improve efficiency and reduce resource consumption. Enabling dynamic pruning will have an even better effect.
Second question, the command is wrong. Also, 6.5 probably doesn’t support partition reorganization yet, right? If a table is not created as a partitioned table, you cannot add partitions to it. It seems like this is only possible in 6.7.