Free SQL Help for Devs, DBAs, and the Curious

One of the challenges that administrators face as databases grow is managing the performance and storage of these large systems. For databases with large amounts of data, horizontal partitioning can be a great solution. Horizontal partitioning involves dividing the table into smaller, more manageable pieces, each containing a subset of rows. The rows in each partition form a group based on a certain criteria or key, like the date or the range of IDs. This can result in significant performance improvements, as queries can be directed to the appropriate partition instead of scanning the entire table.

MySQL 8.0 supports horizontal partitioning natively, providing a flexible and efficient way to manage large tables. MySQL uses a partitioning key to divide a table into partitions. This key can be any column or a set of columns. The database system will automatically route the queries to the appropriate partition based on the partitioning key.

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL
)
PARTITION BY RANGE( YEAR(order_date) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999),
    PARTITION p3 VALUES LESS THAN (2003),
    PARTITION p4 VALUES LESS THAN (2007),
    PARTITION p5 VALUES LESS THAN (2011),
    PARTITION p6 VALUES LESS THAN (2015),
    PARTITION p7 VALUES LESS THAN MAXVALUE
);

SQL Server 2019 also supports horizontal partitioning through the use of partition functions and partition schemes. A partition function defines how to distribute the rows of a table or index into partitions based on the values of a specified column. A partition scheme maps the partitions of a partition function to filegroups.

CREATE PARTITION FUNCTION myRangePF (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
CREATE PARTITION SCHEME myRangePS
AS PARTITION myRangePF
TO (FG1, FG2, FG3, FG4);

DB2 11.5 also provides support for range partitioning, allowing for the distribution of data across several tables based on a range of values. This can help improve query performance, as only the relevant partitions need to be scanned. DB2’s range partitioning can be created using the CREATE TABLE... PARTITION BY RANGE statement.

CREATE TABLE sales
(order_num INT NOT NULL,
 order_date DATE NOT NULL,
 ...)
PARTITION BY RANGE (order_date)
(PARTITION p0 STARTING FROM ('01.01.2000') ENDING ('31.12.2000'),
 PARTITION p1 STARTING FROM ('01.01.2001') ENDING ('31.12.2001'),
 ...);

For those operating in the cloud, Azure SQL Database and Azure Synapse Analytics provide support for partitioning. Azure SQL uses the same syntax as SQL Server for creating partition functions and schemes, while Azure Synapse provides support for both range and hash partitioning, enabling more flexible distribution of data.

In conclusion, horizontal partitioning is an effective technique for managing large databases, improving query performance, and making data management tasks more manageable. Whether you're using MySQL, SQL Server, DB2, or Azure cloud services, you have powerful tools at your disposal to implement this strategy and keep your databases performing at their best.