What are the differences between horizontal and vertical partitioning in a database?

Horizontal partitioning divides a database into rows, while vertical partitioning divides it into columns.

In more detail, horizontal partitioning, also known as data sharding, involves dividing a database into two or more tables with the same columns but different rows. Each partition forms a distinct unit with a subset of data, which can be located and managed separately. This method is particularly useful when dealing with large tables, as it can improve query performance by reducing the amount of data scanned. For example, a database of a multinational company might be horizontally partitioned by country, so that all the data related to each country is stored separately.

On the other hand, vertical partitioning involves dividing a database into smaller tables that contain fewer columns, but all the rows of the original table. Each partition will hold different fields of data, and a unique identifier is used to match these fields back together. This method is beneficial when tables have a large number of columns, or when certain columns are accessed together frequently. For instance, in a customer database, one partition might hold basic contact information, while another holds purchase history.

Both methods have their advantages and can be used in combination to optimise database performance. However, they also come with their own complexities. Horizontal partitioning can lead to more complex queries and increased maintenance, as data is spread across multiple tables. Vertical partitioning can also complicate queries, as data from a single record is spread across multiple tables, and it may require more joins to retrieve a complete record.

In summary, horizontal and vertical partitioning are techniques used to manage and optimise large databases. The choice between them depends on the specific requirements of the database, such as the size and nature of the data, and the types of queries that will be run.