Bug #102606 Asymmetric partitioning
Submitted: 16 Feb 2021 12:34 Modified: 16 Feb 2021 12:48
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: partitioning

[16 Feb 2021 12:34] Daniël van Eeden
Currently all partitions of a partitioned table have to be identical.

This is true for both the field definitions and storage related settings.

How to repeat:
See description

Suggested fix:
The easiest change would be to allow per-partition compression settings: 

create table t1 (id int primary key)
partition by range(id) (
  partition p0 values less than (10),
  partition p1 values less than maxvalue compression="zlib"

Another nice addition to this would be to support this:
You have a big unpartitioned table with a unsigned int as primary key.
You now create a new table with two partitions:
1 for values less then max uint.
2 for values of max unint and larger.

You now exchange the first partition with the big table. While the id of the existing table is an unsigned int and the partition has a bigint unsigned this would not be compatible, but as this partition won't be holding bigger values this could be allowed.  The benefit of this is that this doesn't need a rebuild of the table. This would be very helpful in cases where the max value of the int unsigned (or signed int, etc) is suddenly reached.
[16 Feb 2021 12:35] Daniël van Eeden
This helps to compress older partitions while keeping the active one uncompressed. This also allows one to do the compression of a big partitioned table in multiple steps.
[16 Feb 2021 12:48] MySQL Verification Team
Hello Daniël,

Thank you for the reasonable feature request!