Physical Design   «Prev  Next»

Lesson 11 Downside to Vertical Partitioning
Objective Describe the trade-offs inherent to Vertical Partitioning

Disadvantages of Vertical Partitioning

Vertical partitioning can significantly improve performance when queries access only a subset of a table’s columns. By storing attributes in multiple column-based fragments, the RDBMS reads fewer pages from disk and can optimize access paths for projection-heavy workloads. This is especially beneficial on modern platforms such as Oracle Database 23ai, PostgreSQL with declarative partitioning, SQL Server 2022, and hybrid transactional, analytical (HTAP) systems.

However, vertical partitioning also introduces trade-offs. These drawbacks become apparent when queries span multiple column groups or when the application requires complete records. Understanding these limitations is critical before adopting a vertical fragmentation strategy.
Below is the partitioned CD example used earlier:


Partitioned table
Table 1: CD 

| CDNo | CDTitle        | DistID |
| ---- | -------------- | ------ |
| 101  | Southern Tales | 102    |
| 102  | Northern Tales | 101    |
| 103  | Western Tales  | 102    |

Table 2: CD 

| CDNo | Price | AgeGroup |
| ---- | ----- | -------- |
| 101  | 12.95 | YA-11    |
| 102  | 13.95 | YA-13    |
| 103  | 14.95 | YA-11    |

Table 1 consists of primary key CDNo, CDTitle, DistID.
Table 2 consists of primary key CDNo, Price, AgeGroup.

Data Analysis for DB Design
When a query needs only the attributes found in a single vertical fragment—such as CDNo and Price—performance improves because the RDBMS reads fewer pages and processes smaller row structures.

But when a query requires the complete row, the RDBMS must join the fragments. This reconstruction step is considerably slower and can offset the benefits of vertical partitioning. As with horizontal partitioning, whether fragmentation helps or harms performance depends entirely on how users interact with the data.

Trade-Offs Inherent to Vertical Partitioning

Although vertical partitioning improves projection performance and storage efficiency, several trade-offs must be considered before implementing this design strategy.

  1. Increased Architectural and Application Complexity:
    Each fragment is a separate physical object that must be tracked, indexed, backed up, and optimized. Applications may require logic to join fragments manually or adjust ORM mappings. Partition-aware SQL must be carefully designed to avoid performance regressions.
  2. Dependence on Column Access Patterns:
    Vertical partitioning is effective only when access patterns are stable. If an application frequently adds features, changes its queries, or introduces new reporting requirements, attribute groupings may become outdated. This misalignment forces more cross-fragment joins and can degrade performance.
  3. Join Overhead for Reconstructing Complete Rows:
    A full table reconstruction requires joining fragments on the primary key. In high-volume OLTP systems, this overhead can be substantial. Even with optimizer improvements such as join elimination and predicate pushdown, cross-fragment queries are slower than reading a single unpartitioned row.
  4. Increased Risk of Data Inconsistency:
    Storing attributes in separate fragments requires careful enforcement of constraints. Ensuring that CDNo is synchronized across fragments and keeping referential integrity intact is more complex than maintaining a single table. Some RDBMS platforms impose restrictions on foreign keys spanning partitions.
  5. Maintenance, Migration, and Repartitioning Costs:
    As usage evolves, columns may need to be moved between fragments or recombined. Repartitioning vertical fragments is I/O-intensive and may require long maintenance windows. Modern systems support online operations, but these still consume compute, I/O, and temporary space.
  6. Vendor-Specific Implementation Differences:
    Each database engine handles vertical partitioning differently. Some rely on columnar storage engines, others on physical row splitting, and some require manual table design. Using proprietary features can increase vendor lock-in and complicate migrations.
  7. Backup, Recovery, and Consistency Challenges:
    Even though fragments are smaller, ensuring consistent backups across all partitions requires coordination. Recovery scenarios sometimes require dependent fragments to be restored together to maintain referential integrity.

In short, vertical partitioning trades lower I/O and improved projection speed for increased join overhead, more complex maintenance, and stricter requirements for understanding access patterns. Designers should analyze real workloads before committing to this technique.

Vertically Partitioning Table - Quiz

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

SEMrush Software 10 SEMrush Banner 10