Disadvantages of Vertical Partitioning

Vertical partitioning offers several advantages, such as improved query performance, storage efficiency, and manageability. However, implementing this technique also involves trade-offs that need to be carefully considered. The primary trade-offs associated with vertical partitioning are as follows:

1. Complexity: Vertical partitioning increases the complexity of the database system. Managing multiple partitions requires additional administration efforts, and the application logic may need to be adapted to handle partition-aware queries and data manipulation. This complexity can lead to higher development and maintenance costs.
2. Column access patterns: Designing an effective vertical partitioning scheme depends on a thorough understanding of column access patterns and data relationships. Incorrect or suboptimal partitioning can result in reduced performance or increased storage requirements. Furthermore, as application requirements evolve, access patterns may change, necessitating the reevaluation and modification of the partitioning scheme.
3. Cross-partition queries: Queries that involve multiple partitions can suffer from performance degradation. Join operations or aggregation across partitions may require additional processing overhead and can be more complex to optimize. Consequently, partition-aware query design and optimization are essential to minimize the impact on performance.
4. Data consistency and integrity: Vertical partitioning can introduce challenges related to data consistency and integrity. Ensuring consistency across multiple partitions and maintaining data integrity constraints, such as primary and foreign key relationships, can become more complex with partitioned data.
5. Migration and repartitioning: Changes in access patterns or application requirements may necessitate repartitioning the data. Migrating or redistributing data across partitions can be a time-consuming and resource-intensive process, potentially impacting system performance and availability.
6. Vendor lock-in: Some database management systems (DBMS) provide native support for vertical partitioning, while others may require third-party tools or custom solutions. Relying on proprietary partitioning features can lead to vendor lock-in, making it more difficult to switch to a different DBMS in the future.
7. Backup and recovery: While partitioning can simplify backup and recovery processes by allowing operations on smaller data subsets, it can also introduce challenges. Ensuring consistent backups across multiple partitions may require additional coordination and planning. Moreover, recovering data from a specific partition may necessitate restoring other related partitions to maintain data consistency and integrity.

Vertical partitioning in database design presents several trade-offs that must be carefully weighed against its benefits. Increased complexity, column access pattern dependencies, cross-partition query performance, and data consistency issues are among the primary concerns. Thorough planning, understanding of column access patterns, and partition-aware application design can help mitigate these trade-offs and maximize the advantages of vertical partitioning.

Like horizontal partitioning, vertical partitioning offers performance advantages and disadvantages, depending on the data a particular operation requires. The performance advantages of vertical partitioning come into play if a join uses only the fields in one of the vertically partitioned tables.
In the example presented in the preceding operations which required only a CD's CDNo and Price would run much more quickly after vertical partitioning because the computer would spend less time reading data from disk and manipulating it in memory.

If, however, the operation required the entire original table (or at least columns not stored in the same partition), the RDBMS would need to join the partitions into a single table, a relatively slow operation. As with horizontal partitioning, deciding whether or not to vertically partition a table depends entirely on how your users interact with the contents of your database. The next lesson explains how to use SQL to implement the physical model of a database.

Before you move on to the next lesson, click the Quiz link below to reinforce your understanding of partitioning.
Vertically Partitioning Table - Quiz