Range vs Hash Partitioning: Ordered Splits vs Scattered Buckets
Overview Range and hash partitioning are two strategies for splitting a table’s rows across multiple partitions or nodes based on a partition key. Range partitioning assigns rows to contiguous key intervals (like date ranges), preserving order for efficient range scans but risking uneven load. Hash partitioning runs the key through a hash function to scatter rows evenly, trading away ordering for balanced, predictable distribution. Comparison Diagram RANGE PARTITIONINGHASH PARTITIONINGincoming keysincoming keys1-3334-6667-1001-3334-6667-100hash(key)P1P2P3P1P2P3Ordered, contiguous rangesScattered, uniform spreadEasy to extend: add a boundaryCostly to resize: rehash keysRisk: skew on hot rangesRisk: no range pruning Comparison Table Aspect Range Partitioning Hash Partitioning Partition key requirement Needs an orderable key with defined boundaries (dates, IDs) Any key works; only needs to be hashable Row-to-partition mapping Explicit boundary rules assign rows to intervals Hash function output (often mod N) selects the bucket Data distribution Can be skewed if key values aren’t uniformly spread Near-uniform if the hash function distributes well Range/scan queries Prunes to only the partitions covering the range Must fan out and scan every partition Point/equality lookups Requires a boundary search to find the right partition Direct O(1) computation locates the partition Adding or removing partitions Cheap: append or split a boundary at the edge Expensive: reshuffles most existing keys unless using consistent hashing Hotspot behavior Sequential writes (recent dates, auto-increment IDs) pile onto one partition Spreads writes evenly but destroys any physical data locality Key Differences Range partitioning preserves order, letting the query planner prune partitions; hash partitioning optimizes purely for even distribution Growing the partition count is a cheap boundary edit in range partitioning but forces a rehash of most keys in hash partitioning Range schemes are exposed to skew when writes cluster in a narrow key window; hash schemes avoid this at the cost of locality Point lookups under hashing are a direct hash computation, while range lookups need a boundary search through ordered intervals When to Use Each Range Partitioning ...