Table Partitioning with MySQL and Django

Partitioning is a process by which a very large table is split into multiple smaller parts to aid table manageability, query access time and availability. Partitioning allows table, it’s indexes and sub-indexes to be subdivided each of which has their own name and possibly it’s own storage characteristics. We must be aware that table partitioning is fairly complex think and it’s advantages will not come in light unless queries from the Application layer are optimized to take advantage of partitioned table.

The table partitioning table operation requires proper planning and through understanding of low level SQL queries which your application is generating. If you are in doubt whether to partition or not probably you wont need table partitioning as proper indexing of you tables will aid greatly for optimization. However if you are dealing with increasing data and performance issues even after indexing that partition might be what you are searching for.

The partitioning can be done in two ways either by splitting the records itself based on some column value(Horizontal Partitioning) or by splitting the columns of a table and storing them in separate tables(Vertical). Now let us discuss each of them briefly.

• Horizontal Partitioning

It involves putting different rows of a table into different table based on a value of a column in the row. For example, If we have an Employees table with columns emp_id, emp_name and emp_salary we can partition according to the emp_id such that records with id less than 10000 are in one table and elements with emp_id greater than 10000 are in another table.

Horizontal Partitioning for Employees Table

• Vertical Partitioning

This method involves creating table with partition with fewer columns and using another table to store the additional columns. This method is useful when the query doesn’t need to access all the columns of a row in most of queries and columns with long and less frequently accessed data can be partitioned to another table. For example: If we have an employees table with fields emp_id, emp_name and address than we can partition emp_id and emp_name into one table and address which tends to be long field into another partition.

Vertical Partitioning

Partitioning Implementation in MySQL

MySQL supports only horizontal partitioning and vertical partitioning is not yet supported. The beauty in partitioning in MySQL is that it provided native database level implementation for from version 5.7.12. Before going to the implementation let us discuss points regarding table partitioning in MySQL

• Partitioned table cannot have foreign key to other tables and other tables also cannot have foreign key to the partitioned table
• All the Unique and Primary key constraint must include partition key as it member.
• Only horizontal partitioning is supported by MYSQL

In this article I am going to explain partitioning with help of Employees Schema. Employees schema consists of following fields:

• id: An integer field that represents classical unique key for each user
• name: A string field which stores name of the employee
• joining_month: An integer in range [1,12] representing the month which the employee joined
• address: A text field which represents the address of the employee

I am going to create partition with joining_month as key such employees having joining_month Jan to June are in one partition and July to December are in another partition. For doing this I have to define joining_month in every Unique constraint including the Primary Key. This type of partition is known as Range based partitioning and there are other types of partitioning methods as well.

-- Creating schema
CREATE TABLE EMPLOYEES(
    ID INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(50),
    JOINING_MONTH INT NOT NULL,
    ADDRESS TEXT,
    PRIMARY KEY(ID, JOINING_MONTH)
)
PARTITION BY RANGE (JOINING_MONTH) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (13)
);
-- Inserting Records
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Sulav", 1, "Bhaktapur");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Bipul", 2, "Bhaktapur");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Lava", 3, "Kaadbhitta");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Binod", 3, "Jhapa");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Sushma", 4, "Bhaktapur");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Kush", 5, "Darjeeling");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Sangita", 10, "Bhaktapur");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Jamuna", 1, "Pokhara");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Lava", 12, "Bhaktapur");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Nani", 11, "Bhaktapur");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Prajesh", 9, "Chabahil");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Arpan", 7, "Baneshowr");
INSERT INTO EMPLOYEES (NAME, JOINING_MONTH, ADDRESS) VALUES ("Sahil", 6, "Chabahil");

Now by doing this I have partitioned the Employees table to 2 different parts. The thing to note here that the partition is done in Engine level and SQL layers or your queries will not be aware of this and you can query it like normal. Now let’s throw some queries to our newly created schema.

As seen in the screenshot above we have two partitioned and SQL has selected both partition for querying. When we supply a partition key in the ‘ where ’ clause the SQL engine only searches the matching partition or filtered partition. For example if joining_month < 5 is given that only partition 1 (p0) is searched. This is known as Partition Pruning.

Partition Pruning

The thing to be noted here is that we need to select partition key wisely and include it in the where clause to take maximum advantage of partitioning. Failing to choose correct key will have negative impact on query performance.

MySQL also supports other types of partitioning inside horizontal partitioning. The various types of partitioning supported my MySQL are listed below:

• Range Partitioning
• LIST Partitioning
• HASH Partitioning
• Key Partitioning

You can find more about these types of partitioning in MySQL manual. You can choose whatever according to you need.

Django Implementation Tips:

Partitioning is a database level thing but your application layer must be configured to take full advantage of this. I could only find two packages that could helps in partitioning tasks.

• architect — https://pypi.org/project/architect/
• django-db-parti — https://pypi.org/project/django-db-parti/

These packages do most of the thing in database level for MySQL because of no support for dynamic triggers in MySQL. Apart from these packages you will be also need to do some changes to the foreign key constraint. You need to set db_constraint to False to prevent adding the actual foreign key index in the database.

from django.db import models
from department.models import Department
class Employee(models.Models):
    name = models.CharField(null=False, max_length=100)
    joining_month = models.IntegerField(null=False)
    department = models.ForeignKey(
                 Department, null=False,  
                 db_constraint=False,  #Create not FK index in db
                 on_delete=models.CASCASE
    )

Final Notes:

• Perform proper research before implementing table partitioning as it may backfire sometimes if not designed properly.
• Most of the RDBMS support Table Partitioning and implementation and features may vary accordingly.

References and Links:

• MySQL Manual: https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
• Video Lecture: https://www.youtube.com/watch?v=QA25cMWp9Tk
• Stackoverflow: https://stackoverflow.com/users/6711696/jinja2django
• Github: https://github.com/abhiyan52
• LinkedIn: https://www.linkedin.com/in/abhiyan-timilsina-6b679b142/