Horizontal Partitioning

Horizontal partitioning splits a table’s rows across separate partitions using a partitioning key. It can improve query performance and make large datasets easier to manage, while requiring careful key selection and query routing.

Horizontal partitioning divides rows rather than columns. Each partition contains the same table structure but holds a different subset of rows, commonly determined by a value such as customer ID, tenant ID, or date.

A database can use strategies such as range partitioning, list partitioning, or hash partitioning. For example, orders might be divided by order year, while customer records might be distributed by a hash of customer ID.

The partitioning key matters because queries that restrict that key can benefit from partition pruning: the database can avoid scanning partitions that cannot contain matching rows. Poor key selection can reduce this benefit and may create uneven partition sizes.

Horizontal partitioning is often implemented within a database system, so applications can continue querying the logical table without manually choosing a physical partition. This differs from application-level sharding, where data may be distributed across separate database instances and the application or routing layer may need to direct requests appropriately.

Key points

• Rows are distributed across partitions while the table schema remains consistent.
• A partitioning key determines which partition contains each row.
• Range, list, and hash partitioning are common strategies.
• Partition pruning can reduce unnecessary data scans.
• An uneven partitioning key can create hotspots or oversized partitions.

Interview questions

What is horizontal partitioning?

Horizontal partitioning divides the rows of a table into multiple partitions. Every partition generally follows the same table structure, but each contains a different subset of rows. A partitioning key determines where rows belong. This can help databases manage large tables and avoid reading irrelevant partitions for some queries.

How does partition pruning improve query performance?

Partition pruning allows the database to exclude partitions that cannot contain rows matching a query. For example, if orders are range-partitioned by year and a query requests only 2026 orders, older partitions can be skipped. The amount of work saved depends on the partitioning scheme and the query predicates. Queries that do not restrict the partitioning key may still need to examine many partitions.

What are common horizontal partitioning strategies?

Common strategies include range, list, and hash partitioning. Range partitioning assigns rows to intervals such as date ranges. List partitioning assigns rows to explicitly defined value groups, while hash partitioning uses a hash-based distribution to spread rows across partitions. The appropriate strategy depends on the data distribution and query patterns.

What can go wrong when choosing a partitioning key?

A poor key can create partitions with very different sizes, causing uneven storage and workload distribution. It can also provide little pruning benefit if common queries do not filter on that key. A good candidate usually has useful query correlation and a distribution that avoids extreme concentration. The choice should therefore consider both access patterns and data distribution.

How is horizontal partitioning different from sharding?

Horizontal partitioning describes dividing rows into partitions, while sharding generally refers to distributing those partitions across separate database nodes or instances. A partitioned table can remain inside one database system and be managed by the database engine. With sharding, routing and cross-shard operations become additional architectural concerns. The exact terminology varies across database products, so the implementation should be described rather than relying only on the names.