From the course: Advanced SQL for Query Tuning and Performance Optimization

Horizontal vs. vertical partitioning - SQL Tutorial

From the course: Advanced SQL for Query Tuning and Performance Optimization

Start my 1-month free trial Buy for my team

Horizontal vs. vertical partitioning

“

- [Instructor] Let's look at some ways we can change our data model implementation to improve query performance. One of the problems with large tables is that they can be difficult to query efficiently. Even with indexes, queries against large tables may not be performant enough. One way to deal with this is by splitting the large table into smaller sub-tables. This is called horizontal partitioning. And basically we treat each partition like a table. The benefit of horizontal partitioning is that we can sometimes limit scans to a small number of partitions. Because partitions are like tables, we can create indexes on columns in those partitions. This leads to smaller indexes than those that would exist in the full table. In addition, partitions can make bulk data operations like dropping old data even more efficient because we can drop an entire partition quickly. If we need to drop a subset of rows, that can also be faster because a smaller index is updated faster rather than a much larger index. Partitions are used widely in several kinds of database applications, including data warehouses. They are often partitioned based on time because time is commonly used as a filter. Timeseries data is also a good candidate for partitioning because the latest data is the most likely to be queried. In other areas, there may be a natural partition strategy that doesn't involve time. For example, you may want to partition by geography or by product type. Vertical partitioning separates the columns of a large table into multiple tables. Designers tend to keep columns that are frequently queried together in the same vertical partition. When using vertical partition, you'll use the same primary key across all of the partitions. Benefits of vertical partitioning include increasing the number of rows stored in a single data block. This means that more rows are returned with each block read. We can create global indexes on each partition. Because columns are separated, we can read less data to satisfy a query and this can reduce IO. Columnar data storage strategies can provide similar benefits as well. You may see vertical partitioning used in data warehouses, in wide-column tables such as product tables with a large number of product attributes, and in data analytics. That's another area where vertical partitioning can be used, sometimes after preliminary analysis to determine which of the attributes are most important.

Contents