Hash Partitioning

Hash partitioning uses a hash function on a partition key to distribute database rows across partitions. It is useful for spreading data relatively evenly when queries commonly use that key.

A database divides a table into multiple partitions, which can be managed separately while still being queried as one logical table. With hash partitioning, the database computes a hash from a chosen partition key and uses the result to determine which partition stores the row.

The partition key should generally have enough distinct values to distribute rows across partitions. A good hash function helps avoid concentrating too much data in one partition, although perfectly even distribution is not guaranteed.

Hash partitioning is especially useful when range boundaries are not meaningful or when you want to distribute workload across partitions. For example, a table could be partitioned by `customer_id` so different customers are spread among several partitions.

A major trade-off is that hash partitioning does not naturally preserve ordering by the key. Queries that filter on the partition key can benefit from partition pruning, while queries using unrelated columns may need to examine many or all partitions.

Key points

• Rows are assigned using a hash of the partition key.
• A high-cardinality key can help distribute rows evenly.
• Partition pruning can reduce work when queries constrain the partition key.
• Hash partitioning does not preserve key order.
• Changing the number of partitions can make data redistribution necessary.

Interview questions

What is hash partitioning?

Hash partitioning divides table rows among partitions using a hash function applied to a partition key. The resulting hash determines the partition that stores the row. The goal is usually to spread data and workload across partitions. It is useful when ordered ranges of the key are not important.

Why might you choose hash partitioning instead of range partitioning?

Hash partitioning is useful when the workload does not naturally divide into meaningful ranges. It can distribute rows more evenly when the partition key has many distinct values. Range partitioning is often better when queries frequently target known intervals, such as dates. The choice should therefore reflect both data distribution and query patterns.

What is partition pruning in a hash-partitioned table?

Partition pruning means the database can eliminate partitions that cannot contain rows relevant to a query. With a suitable predicate on the partition key, the database may determine which partition or partitions need to be accessed. This can reduce I/O and execution work. The exact pruning behavior depends on the database system and query.

What are the risks of choosing a poor hash partition key?

A poor key can produce uneven data distribution or concentrate workload on a small number of partitions. A key with few distinct values may not provide enough distribution opportunities. A key that is rarely used in query predicates may also provide little pruning benefit. Therefore, both cardinality and access patterns matter when selecting the key.