Partitioning Overview

In MariaDB, a table can be split in smaller subsets. Both data and indexes are partitioned.

Uses for partitioning

There can be several reasons to use this feature:

• Very big tables and indexes can be slow even with optimizes queries. But if the target table is partitioned, queries that read a small number of partitions can be much faster.
• Partitioning allows to distribute files over multiple storage devices. For example, we can have historical data on slower, larger disks (historical data are not supposed to be frequently read); and current data can be on faster disks, or SSD devices.
• In case we separate historical data from recent data, we will probably need to take regular backups of one partition, not the whole table.

Partitioning for specific storage engines

Some MariaDB storage engines allow more interesting uses for partitioning.

SPIDER allows to:

• Move partitions of the same table on different servers. In this way, the workload can be distributed on more physical or virtual machines (data sharding).
• All partitions of a SPIDER table can also live on the same machine. In this case there will be a small overhead (SPIDER will use connections to localhost), but queries that read multiple partitions will use parallel threads.

CONNECT allows to:

• Build a table whose partitions are tables using different storage engines (like InnoDB, MyISAM, or even engines that do not support partitioning).
• Build an indexable, writeable table on several data files. These files can be in different formats.