Part 3 of 5 · 2 min read
Range partitioning and hash partitioning
Range partitioning assigns a row by where its key falls in sorted order; hash partitioning assigns it by a hash of the key.
Think of range partitioning as sorting a shelf of files by last name, then giving each branch library a specific alphabet range. Hash partitioning, by contrast, assigns each file to a branch using a lottery number computed from its name, spreading names evenly regardless of the alphabet.
In plain words: range partitioning assigns each row to a partition based on where its partitioning key falls within a sorted range of key values, meaning rows with nearby key values end up on the same or adjacent partitions. This makes range queries efficient, since a query for a range of keys touches only the partitions covering that range, but it risks concentrating writes on one partition if keys are written in sorted order, such as an ever-increasing timestamp. Hash partitioning instead assigns each row to a partition based on a hash function applied to its partitioning key, which spreads keys evenly across partitions regardless of their original order, at the cost of making range queries inefficient, since a range of keys is scattered across many partitions.
A worked example. A table partitioned by range on a timestamp column places all of today's rows on one partition, making "last 24 hours" queries fast but making every new row land on that same partition as it's written. The same table partitioned by a hash of a random customer ID spreads new rows evenly across all partitions, but a "last 24 hours" query must now check every partition.
The key lines. No new SQL; the partitioning strategy and its key are typically set when a table is created in a distributed database, as a schema-level configuration rather than a per-query clause.
Range partitioning, hash partitioning
Range partitioning assigns a row to a partition based on where its key falls in a sorted range, keeping nearby keys together. Hash partitioning assigns a row to a partition based on a hash of its key, spreading keys evenly regardless of order.
Example: sorting files by last name into alphabet-range branches is range partitioning; assigning files to branches by a computed lottery number is hash partitioning.