MySQL Bugs: #102606: Asymmetric partitioning

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:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	partitioning

Description:
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.

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.

Hello Daniël,

Thank you for the reasonable feature request!

regards,
Umesh