Add Partitioning Feature to MySQL

[vkoori]

Description

I would like to propose adding a partitioning feature to MySQL. Partitioning allows for better organization and management of large tables by dividing them into smaller, more manageable pieces. This can greatly improve performance and query efficiency.

Official Reference

For more information on partitioning in MySQL, please refer to the official documentation here.

[lauxjpn]

There seems to be a lot of specialized syntax involved, so I am not sure if we want to spend the time to implement it in all its glory at this point in time.

Currently, you would need to craft and execute your own ALTER TABLE statement to handle partitions when using Pomelo.

We could add a generic .PartitionBy(string partitionOptions) extension method to use on an entity type in DbContext.OnModelCreating(). But it would really just add a PARTITION BY clause plus the supplied partitionOptions string. So I am not sure, how much value that would actually bring over what you can do already at the moment with a hand-crafted ALTER TABLE statement.

@vkoori Any thoughts?

[roji]

See related conversation for the PostgreSQL side. FWIW I'm also generally skeptical about going into a big effort to model complex DDL stuff, where users can add e.g. ALTER TABLE manually into their migrations - EF providers really can't be expected to cover everything. In the case of partitioning specifically, I do think I've maybe received enough votes to consider at least a subset of the syntax, but we'll see whether I actually get around to devoting the time...