Partition Strategies — Range, Hash, List & Composite
Before you invent an application shard layer, master in-engine partitioning: range, hash, list, and composite. The same vocabulary maps onto Vitess, Citus, and TiDB. Pick the strategy from the access pattern. A wrong one creates hotspots, immovable partitions, or queries that never prune.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Hash every table
Prefer
Match the strategy to the predicate
Hash the entity when lookups are by key. Keep a range or list dimension when you prune, archive, or isolate a few buckets. Use virtual buckets when N will change.
- Dashboards that filter by month can prune.
- Whales can sit on a list or dedicated partition instead of a shared hash.
- Membership changes do not depend on a modulo that reshuffles everyone.
Alternative
Hash the primary key and stop
Even distribution and a simple mental model. You give up range locality, and modulo-N resharding is painful.
- A time filter touches every partition.
- Debugging which partition holds a row requires the hash forever.
- Changing the bucket count moves most keys.
Overview
Seniors name which partition strategy fits the access pattern. "We will partition by user_id" is not a strategy. Range, hash, list, and composite are. The same words show up in Vitess keyspaces, Citus distribution columns, and TiDB ranges. Wrong choice means a hot newest partition, a partition you cannot move, or a query that never prunes.
This page is in-engine partitioning and the vocabulary you reuse when you later shard. It does not re-teach how a ring places vnodes. That is Consistent hashing. When the physical bucket count changes, the move is resharding.
By the end you should be able to:
- Pick range, hash, list, or composite from cardinality and whether you range-scan
- Explain why a monotonic id or
created_atis a bad sole write key - Say when the planner can prune
- Refuse a list partition per value when there are thousands of values
- Name the uniqueness limit on your engine
Flow
- 1
Range partition
- next2024-Q1
- 2
2024-Q1
- next2024-Q2
- 3
2024-Q2
- next2024-Q3 hot
- 4
2024-Q3 hot
- 5
Hash partition
- nextbucket 0
- 6
bucket 0
- nextbucket 1
- 7
bucket 1
- nextbucket 2
- 8
bucket 2
- nextbucket 3
- 9
bucket 3
- 10
List partition
- nextus-east
- 11
us-east
- nexteu-west
- 12
eu-west
- nextap-south
- 13
ap-south
Lesson map
Partition Strategies — Range, Hash, List & Composite
Before you invent an application shard layer, master in-engine partitioning: range, hash, list, and composite. The same vocabulary maps onto Vitess, Citus, and TiDB. Pick the strategy from the access pattern. A wrong one creates hotspots, immovable partitions, or queries that never prune.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB r1["2024-Q1"] r2["2024-Q2"] r3["2024-Q3 hot"] r1 -->|2024-Q1 to 2024-Q2| r2 r2 -->|2024-Q2 to 2024-Q3 hot| r3
The three stacks are alternatives, drawn in one column so a phone can read them. Range keeps neighbors together and the newest slice runs hot. Hash spreads buckets and drops range locality. List maps a few known values, such as region.
Strategy comparison
| Strategy | How rows land | Pros | Cons | Prefer when |
|---|---|---|---|---|
| Range | Key in a half-open interval | Locality, prune by time or id, easy archives | Hot newest range; uneven sizes | Time-series, sequential ids with care |
| Hash | hash(key) modulo N | Even spread | No range prune; reshard churn | Uniform random access by key |
| List | Explicit value to partition | Clear tenant or region mapping | Manual rebalance; sparse lists | Few discrete values (region, tenant tier) |
| Composite | Range plus hash, or list plus hash | Escape hatches for whales | Complexity; planner quirks | Multi-tenant with mega-tenants |
Range
Good: partition orders by order_date. Drop old months. Dashboards that constrain the month prune.
Bad as the only write shard key: a monotonic id or created_at. Every insert hits the rightmost partition.
Mitigations: hash-subpartition the hot range; treat time-ordered ids such as UUIDv7 carefully, because they are still time-ordered; or shard by a non-monotonic key and keep range only for archival tables.
Hash
Even load when cardinality is high. The cost is predicates. created_at between two timestamps touches every partition unless you add a composite (hash on user, range on time) or a secondary access path that is not the partition key.
List
Map region IN ('us', 'eu'). That is a clean data-residency boundary. Adding a region is DDL. Uneven regions need capacity per list partition, not a hope that the hash will save you. You did not hash.
Composite patterns
- List on tenant tier, then hash on tenant id. Enterprise tenants are isolated. Smaller tenants share hash buckets.
- Hash on user id, then range on event day. Users spread. Events inside a shard still prune by day.
- Range on id with a hash subpartition. Declarative subpartitions can cool a hot current range. Postgres can express this. Confirm the planner still prunes the outer key.
Pick from the stats
The sandbox encodes the rule: a handful of discrete buckets and no range scan is list; a time-ordered range scan is range or hash-plus-range; high cardinality without range scans is hash; high cardinality with range scans is composite; otherwise do not partition yet.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
The TypeScript router is the composite you can explain on a whiteboard: spread users across N buckets, then a coarse day band so a time filter does not open every bucket. It is not a substitute for the engine's partition bounds.
Engine notes
The access-pattern reasoning transfers. The DDL and the uniqueness rules do not. Say the engine you mean.
| Engine | Native partitioning | Notes |
|---|---|---|
| PostgreSQL | Declarative range, list, hash | Pruning is strong; global UNIQUE is limited |
| MySQL | Range, list, hash, key | Some unique constraints must include the partition key |
| SQL Server | Range plus aligned indexes | Partition switching is the ETL tool |
| Vitess | Keyspace sharding | The app can see one logical MySQL |
| Citus | Distribution column | Postgres-compatible distributed tables |
Postgres partition pruning helps when the WHERE clause constrains the partition key with constants or immutable expressions the planner can prove. A function the planner cannot fold will open every partition. Check the plan.
Global uniqueness needs a global index, which you may not have, or an allocator outside the partitions. MySQL often requires the unique key to include the partition key. Do not promise UNIQUE(email) across hash partitions without reading the engine page.
List antipattern: one value per partition and thousands of values. The catalog bloats. Prefer hash.
Subpartitioning is real. A range of months, each hash-subpartitioned, is how you keep the current month from becoming a single hot chunk.
Changing N under hash modulo is the painful reshard. Virtual buckets, or a consistent-hash ring, let physical moves avoid rewriting the key formula. The formula and the vnode map are consistent hashing. The copy and the cutover are resharding.
Interview Q&A
When does Postgres partition pruning help?
Answer
When the WHERE clause constrains the partition key with constants or immutable expressions the planner can prove. If the predicate does not mention the key, or the planner cannot reduce it, every partition is opened.
Hash or range for multi-tenant SaaS?
Answer
Hash on tenant id for evenness. List or a dedicated partition for whales. Composite when you need both spread and a prune dimension such as time.
Can you UNIQUE across partitions?
Answer
Not as a default. Global uniqueness needs a global index, which is not always available, or an allocator. Read the engine: MySQL often forces the partition key into the unique constraint.
Why composite instead of pure hash?
Answer
You keep prune for a second dimension, usually time, while the primary entity still spreads. Pure hash makes every time-range query a full scan of buckets.
What is the list-partition antipattern?
Answer
One partition per value when there are thousands of values. Catalog bloat and DDL for every new value. Hash those. Keep list for a small set such as region or tier.
What is subpartitioning?
Answer
A second strategy inside each outer partition. Example: range of months, and each month hash-subpartitioned, so the hot current month is not one chunk.
Why is the newest range hot?
Answer
Inserts on a monotonic key all fall in the open right edge. Old partitions go quiet. That is fine for archive-and-drop. It is a write hotspot if that range is your only shard.
Does Citus or Vitess use different words?
Answer
The access pattern is the same. Citus asks for a distribution column. Vitess asks for a vindex and a keyspace. You still choose range versus hash versus a lookup, and you still cannot pretend a missing key will prune.
Pitfalls
- Partitioning by status, plan, or country when there are three values.
- Expecting hash partitions to speed a date-range report.
- Adding a region list in production with no capacity plan for the new partition.
- Assuming
UNIQUE(email)survived declarative partitioning. - Wrapping the partition key in a volatile expression and wondering why the plan is a scan of every child.
- Building an application shard layer before the in-engine partition was even measured.
You have order events queried by user and by day, a residency rule with four regions, and a feature-flag table with three statuses. Assign range, hash, list, composite, or "do not partition." Say which one you refuse to list-partition.
Go deeper
PostgreSQL declarative partitioning is the pruning reference. MySQL documents range, list, hash, and key types, including unique-key rules. Vitess resharding shows the same strategies once the partition is a keyspace. CockroachDB partitioning is the geo and range view on a distributed SQL engine.
Next: Shard keys.