Database Vertical Partitioning

Vertical partitioning, also known as columnar partitioning or partitioning by column, is a technique in database design that involves dividing a table into multiple separate fragments based on columns rather than rows. This approach aims to enhance performance, storage efficiency, and manageability of databases, especially in scenarios where specific columns are frequently accessed together. The key characteristics of vertical partitioning are as follows:

1. Column-based data distribution: In vertical partitioning, a table is split into smaller fragments, where each fragment contains a subset of columns. The partitioning is typically based on column access patterns or data relationships, ensuring that columns frequently accessed together are grouped in the same fragment.
2. Improved query performance: Vertical partitioning can significantly enhance query performance by reducing the amount of data read from disk, particularly for queries that involve a small subset of columns. This is known as "column pruning" or "projection pushdown," where only the required columns are fetched, minimizing I/O operations and processing overhead.
3. Storage efficiency: Vertical partitioning enables more efficient storage by allowing each partition to be stored in a format optimized for its specific data types and access patterns. This can result in reduced storage space requirements and improved I/O performance. Additionally, vertical partitioning can enhance compression efficiency, as similar data types are stored together, leading to better compression ratios.
4. Manageability: Partitioning a table vertically can simplify database maintenance tasks, such as indexing and data archiving. By working with smaller partitions instead of the entire table, these tasks can be performed more efficiently, reducing the impact on overall system performance.
5. Data independence: Vertical partitioning can help achieve data independence by allowing different partitions to be managed independently of each other. This facilitates changes in the database schema, data storage formats, or access patterns without affecting other partitions or the entire table.
6. Partitioning granularity: Vertical partitioning provides finer granularity for partitioning compared to horizontal partitioning, as it allows dividing tables based on columns rather than rows. This granularity enables more tailored partitioning strategies, which can further optimize performance and storage efficiency.
7. Hybrid partitioning: Vertical partitioning can be combined with horizontal partitioning to create a hybrid partitioning scheme, leveraging the advantages of both techniques. In this approach, a table is first divided horizontally into multiple row-based partitions and then further partitioned vertically based on columns.

Vertical partitioning is a valuable technique in database design that can enhance performance, storage efficiency, and manageability. By dividing a table into smaller column-based partitions, it enables efficient query execution, optimized storage formats, and simplified maintenance in a variety of application scenarios. Careful planning, column grouping based on access patterns, and data relationships are crucial to maximize the benefits of vertical partitioning.

Horizontal partitioning redistributes the records in a table into two or more tables in order to reduce the size of the tables used in the most common searches and joins. It is also possible to partition a table into two or more tables that contain every row but not all of the columns in the original table. This process, called vertical partitioning and lets you store the most commonly used columns from a table in a smaller number of blocks on the computer's hard drive.

Partitioning in database design is the process of assigning a logical object (relation) from the logical schema of the database to several physical objects (files) in a stored database. Vertical partitioning subdivides attributes into groups and assigns each group to a physical object. Horizontal partitioning subdivides object instances tuples^[1] into groups, all having the same attributes of the original object. We refer to the physical objects that are a result of vertical or horizontal partitioning as horizontal or vertical fragments.
We use the term vertical partitioning somewhat loosely, in the sense that we allow fragments to overlap, so that partitions are not necessarily disjoint.

**Yes, in the context of relational databases, a logical object in a logical schema is typically synonymous with a relation.** Here's a breakdown of the concepts and their relationship: **Logical Schema:** - **Abstract representation of the database's structure and relationships, independent of the specific database management system (DBMS) or storage technology.** - Focuses on the logical organization of data, entities, attributes, and relationships, as understood by users and applications. **Logical Object:** - **Any entity or construct defined within the logical schema.** - **In relational databases, logical objects primarily refer to relations (tables), which represent collections of related data.** **Relation:** - **A fundamental concept in relational database theory.** - **A two-dimensional table with rows (tuples) representing individual records and columns (attributes) representing data fields.** - **Each relation has a unique name and well-defined constraints (e.g., primary keys, foreign keys) to ensure data integrity.** **Image:** [Image of a logical schema diagram with tables (relations) and relationships] **Key Points:** - **Logical schema design precedes physical schema design.** - **Logical objects are mapped to physical storage structures during database implementation.** - **Understanding logical objects and relations is essential for database design, query writing, and data manipulation.** **Other Logical Objects:** - While relations are the primary logical objects in relational databases, other types can exist depending on the DBMS: - Views: Virtual tables derived from other tables or queries. - Indexes: Structures that enhance data retrieval speed. - Constraints: Rules that enforce data integrity and consistency. - Sequences: Objects that generate unique numbers for primary keys.

Vertical partitioning is used during the design of a database to improve the performance of transactions. Fragments consist of smaller records, and therefore fewer pages in secondary memory are accessed to process a transaction. When allocating data to a memory hierarchy, vertical partitioning is used to store the attributes that are most heavily accessed in the fastest memory. In the design of a multiple-site distributed database, fragments are allocated, and possibly replicated, at the various sites.
Vertical fragments are ultimately stored in the database by using some physical file structure.
In order to obtain improved performance, fragments must be "closely matched" to the requirements of the transactions. The ideal case occurs when each transaction "matches" a fragment, because the transaction has to access that fragment only. If certain sets of attributes are always processed together by transactions, the design process is trivial. In real-life applications one rarely comes across such trivial examples. Hence, for objects with tens of attributes, we need to develop a systematic approach to vertical partitioning.

If you discovered that Stories on CD users needed to know the values for a CD's Title and DistID much more often than the values for the CD's Price and AgeGroup, you could divide the table into two tables linked by the original table's primary key field.

The next lesson discusses the trade-offs inherent to vertical partitioning.

[1]tuple: In the context of relational database design, tuples are another word for the rows of a database table.