Multi-tenant applications rely on database storage to serve their customers. As the number of customers grows, so will the amount of data used by the application. Eventually, this will lead to storage issues as the database is being filled with data. A possible way to cope with these storage issues is to partition the database. Partitioning the database results in two (or more) smaller database partitions which can be stored in different locations. Partitioning can be done in two ways:
- Horizontal partitioning - This is done by partitioning the database by separating it by rows. This will result in the same schema in the database partitions, and a performance boost can be achieved by creating local indexes for each partition, which are smaller than the global index.
- Vertical partitioning - This is done by extracting columns from the database. The advantage is that the database becomes smaller, which leads to faster read times since more rows fit into memory at the same time. Unfortunately, it may become necessary to combine partitions using a JOIN operation.
Partitioning can improve the performance of your database, but only if it is done correctly. It is also important to consider the nature of your data. If it is very dynamic, it is possible that your database needs repartitioning after a certain time (dynamic partitioning).
Most large database management systems offer partitioning functionality. See the following links for more information:
- MySQL :: Improving Database Performance with Partitioning
- Partitioned Tables and Indexes in SQL Server 2005
- Oracle partitioning
Cor-Paul,
ReplyDeleteIn SQL you have another option to split up your data.
Within a table you can split up the data in
1) Physical records.
2) Indexes
3) Blobs.
Each of them can be stored in a different filegroup.
Gr. André