There has been much talk about the benefits of table partitioning in Relational DBMS.

I would like to know:

  • Are there any risks to table partitioning ?
  • What measures are there to take to minimize such risks ?
  • If the values for your partitioning key change a lot then updates will be much more expensive because the database needs to physically move the rows to a different partition.
    – user1822
    Feb 24, 2014 at 15:56
  • @Kin: how do you know Computernerd is using SQL Server?
    – user1822
    Feb 24, 2014 at 19:58
  • Did you have a specific RDBMS is mind ??? Feb 24, 2014 at 21:53

2 Answers 2


MySQL Perspective

Here is the hard limit for MySQL

  • MySQL 5.6: the maximum number of partitions is 8192.
  • Before MySQL 5.6, the maximum number of partitions is 1024.

This presents a daunting challenge for file handles depending on Storage Engine


There will be 8192 (1024 for older versions) file handles, that is one file handle for each .ibd file dedicated to the partition PER TABLE. The option innodb_open_files get autosized (default is 300 in older version). InnoDB will cache file handles rather decently.


There will be 16384 (2048 for older versions) file handles, that is one file handle for each .MYD file and one for each .MYI file dedicated to the partition PER TABLE. The option open_files_limit gets autosized and the maximum is 65535. This can present extreme challenges for those with several partitioned MyISAM tables.

The only thing you can do is raise the OS ulimit to accommodate. Only then can you raise open_files_limit beyond 65535.


Each time you access one or more partitions, you have to remember to explicitly close the file handles using FLUSH TABLES table_name; to close open file handles you no longer need. This makes room for new file handles to be opened. You can identify which tables have open handles with SHOW OPEN TABLES;. When doing these things, it becomes a little more manageable to have a table with a vast number of file handles. You just have to be very meticulous and scrupulous about closing unused open file handles often, even if touching just one partition.


It's not that easy to answer. Just a few thought maybe helping you:

Partitioning is adding more complexity to your database layout. So e.g. depending on your DBMS you will might will need to maintain trigger functions and indexes. Also it might get complicated for other users to understand what you did there.

There is also an impact on I/O. On the first hand, you can split data up so single files might not get that big, but on the other hand having all data on one disc, you risc a hight fragmentation of data. Of course, if you can split up between often used data and not very often used data, you could move the partitions to different file systems (e.g. a fast one for often used data and a cheaper one for less often used data).

Not the answer you're looking for? Browse other questions tagged or ask your own question.