Partitioning in aurora mysql


Hello All,
We have not used partitioning in mysql before. Now that we are opting for daily range partitioning on a date column of a table in AWS Aurora mysql. I have few questions around same

1)In other databases (Like oracle) if we want to have an index to be local then any unique or primary key must contain the partition key column in it. Is same holds correct for mysql too?

2)I believe, if a partition doesn't exist for a specific day/date and if someone trying to insert data for that same date, it will fail the insert query. So, we should have the required partitions created beforehand. In that context, for adding new future partitions, we must manually add the partitions to the table. But is there exists some automated feature which will automatically add the new partition (without making the insert query fail) for a day if the data for that same day getting inserted into the table? Something like interval partitioning in oracle,in which it automatically creates new partitions when any data inserts into the table for that future date. and we don’t have to create the partitions manually then.

3)Is there exists local and global indexes in mysql like other databases? In case of local index, separate indexes pointing each of the partitions. But in global cases a single index structure is pointing to all the partition table data.

4)What is the Maximum number of range partitions allowed per table in aurora mysql?

asked 9 days ago67 views
1 Answer
Accepted Answer


  1. In MySQL, just like Oracle, if you're defining a primary key or unique key on a partitioned table, the partition key column must be part of every unique key. This means, if you're partitioning by a date column, that column needs to be part of any primary or unique key.

  2. Unlike Oracle's interval partitioning which automatically creates new partitions for new data, MySQL does not have such a feature natively. In MySQL, you'd have to manage and create partitions manually ahead of time. If an appropriate partition does not exist for data, the insert will indeed fail. Automating this process would typically require a custom script or scheduled task to pre-create partitions for future dates.

  3. In MySQL, there's no distinction between local and global indexes as there is in Oracle. When you create an index on a partitioned table in MySQL, it essentially becomes a 'local' index, meaning each partition has its own distinct set of index data. There's no equivalent to Oracle's global index in MySQL's partitioning model.

  4. For MySQL 5.7, the default limit is 8192 partitions. However, having thousands of partitions on a single table may lead to its own management and performance challenges, so always consider the specific needs and access patterns of your application.

I hope this helps! If this solution works for you, please accept the answer. Otherwise, do leave a comment, and I'll try to assist you.

profile picture
answered 8 days ago
  • Thank you so much. It helped.

  • One related question, as one of the use cases of partitioning is easy maintenance. One of the key things is data retention or purging requirement. And I believe we need to have to have Jobs/schedulers created for dropping the partitions.

    But wanted to know, irrespective of table being partitioned or not, does Aurora mysql provides any easy automated way to purge the data? Something like, just define the retention period (or time to live) for a table and rest will be taken care automatically by the aurora mysql internally? Or we need to write the delete rows/drop partition script and schedule lamda manually for that?

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions