Adding database servers does not guarantee that an application can handle more traffic. If every new write still reaches the same server, the extra capacity sits unused while one shard becomes overloaded.
A shard key determines how records are distributed across a database cluster. Choosing it requires more than finding a unique field. The key must distribute actual work, support common queries, and remain manageable as the data grows. A social application storing posts provides a useful example of these competing requirements.
Introduction
Sharding divides a dataset across independently managed parts so several machines can store and process it. The shard key connects an application record to that distribution. Depending on the database, a routing layer might map a range of values, a hash range or a logical partition to its current physical location.
The practical objective is to distribute the expensive work while keeping important operations efficient. Evenly distributing bytes is useful, but it does not help if one partition receives most requests. Conversely, spreading every row randomly can balance writes while forcing every read to contact the entire cluster.
For this article, imagine a posts service with ordinary authors, occasional viral posts and a small number of very active accounts. It must serve individual posts, author timelines and downstream feed-building workers. All example rates and bucket counts are hypothetical design inputs, not platform guarantees.
Before sharding, confirm that distribution addresses the actual bottleneck. An unindexed query, an overloaded connection pool or repeated reads of one popular object can remain expensive after adding shards. Sharding also introduces routing, migration and cross-shard operation costs that a single well-tuned database avoids.
When sharding is justified, choosing a key becomes a workload-design exercise. Start with access patterns, examine the shape of real traffic, compare candidate keys and define how exceptions will be handled. A key that is convenient in a table definition can be expensive throughout the rest of the system.
The examples use concepts shared by several databases, but implementation details differ. A MongoDB shard key, a DynamoDB partition key and an application-managed SQL routing key do not have identical transaction or movement behaviour. Translate the design into the guarantees of the specific engine before implementing it.
Start with the Queries and Transactions
Before selecting a key, list the operations the application must support. For a posts service, these might include retrieving a post by identifier, listing an author's recent posts, deleting an author's content, and building a home feed from several authors.
Record the expected frequency and latency needs of each operation. Include writes and background work, not just page loads. A monthly deletion job might become painfully expensive if it must search every shard for one user's records.
Also identify data that must change atomically. Co-locating related records can keep some transactions local, although the exact transaction boundary depends on the database. No single key necessarily makes every access pattern efficient; the design should state which operations receive priority.
Build an Access-Pattern Worksheet
For each operation, record its routing inputs, result size, frequency and consistency requirement. "Fetch a post" is incomplete until you know whether the caller supplies only postId or also authorId. An author-based key is easy to route when both are available and needs another mechanism when only the post identifier is known.
A useful starting worksheet for the example service is:
| Operation | Inputs available | Desired behaviour |
|---|---|---|
| Read one post | Post identifier | Direct lookup without searching every shard |
| Read author timeline | Author identifier and cursor | One bounded ordered page |
| Publish post | Trusted author and content | Durable insert with predictable latency |
| Delete author data | Author identifier | Enumerate all relevant records reliably |
| Build home feed | Recipient and followed authors | Bounded work despite many relationships |
| Moderate reported post | Post identifier and report | Locate content even after routing changes |
Some rows conflict. A key based on author makes author timelines convenient but does not automatically support direct lookup by an unrelated post identifier. A key based on post makes that lookup convenient but scatters an author's history. Write the trade-off down rather than assuming a secondary index removes it for free.
Include maintenance operations with their own budgets. Deleting one account may not be latency-sensitive, but a design that requires a full cluster scan for each deletion can become costly as requests accumulate. Data exports, moderation sweeps, historical imports and retention jobs can also dominate off-peak capacity.
Consider the maximum scope of a transaction. If publishing a post also changes an author's quota record, co-location may make that operation easier. If the authoritative quota lives on another shard, choose whether a distributed transaction, reservation workflow or different invariant is appropriate. The key influences correctness mechanisms as well as performance.
Know What the Router Can Actually Use
Applications sometimes choose a sensible shard key and then omit it from queries. The database must then inspect multiple shards because the request does not provide enough information to identify one destination. Make routing information part of API and repository design where doing so is safe and stable.
A post locator can map postId to its current author or logical partition. That adds a read and creates another dataset to maintain. Alternatively, a public identifier can encode a stable logical routing component. Avoid exposing a physical server number in the identifier: servers change as the cluster grows.
The locator itself needs consistency and recovery rules. If the post commits but its locator entry is lost, direct reads can fail despite valid stored content. Keep them in an appropriate transaction when possible, or make the locator rebuildable through durable events and reconciliation. A routing solution is part of the application architecture, not merely a naming convention.
Evaluate Cardinality, Frequency and Growth
Cardinality means the number of distinct key values. A key such as country offers few values and can group most traffic into a handful of ranges. An author identifier offers many more values, but that alone does not guarantee balance.
Frequency matters: a prolific author may own millions of records while most authors own ten. Activity matters too: equally sized author collections can receive very different numbers of reads.
Finally, consider whether values steadily increase. Under range sharding, an increasing timestamp or identifier can direct new inserts to the current end of the key space. MongoDB's hashed sharding documentation explains this moving write hotspot and the trade-offs of hashing.
Distinguish Data Skew from Traffic Skew
Cardinality gives the distribution system room to manoeuvre. A boolean key offers two distinct values regardless of cluster size. A key with millions of distinct authors offers many more possibilities, but its usefulness depends on how records and requests are distributed among those authors.
Imagine one million authors, with 99% of stored posts belonging to a few hundred archival accounts. That is data skew: some logical keys own much more storage. Now imagine each author owns roughly the same number of posts, but one celebrity receives half the reads. That is traffic skew. A storage balance report can look healthy while request latency is dominated by the celebrity's partition.
Write skew is another shape. A migration process might backfill one customer's history at a high rate, even though normal interactive traffic is balanced. A moderation job might update every post with one status. Test background workloads as first-class users of capacity instead of assuming only foreground requests matter.
Calculate the share of total work attributable to the largest keys. If one key represents 40% of all writes and cannot be split, adding ten more shards cannot spread that 40% among them simply by moving the key. It may move the bottleneck to a different server without reducing it.
Measure a distribution over time, not just one daily total. A key can be cold for most of a day and intensely hot for five minutes after a scheduled event. Averaging over 24 hours hides the peak that determines whether requests are throttled.
MongoDB's shard-key selection guidance examines cardinality, frequency, monotonic change and query patterns. Those dimensions are useful questions for a design review even when another database implements the final distribution.
Consider Whether the Key Can Change
Prefer a key whose meaning remains stable for the lifetime of a record. An author identifier normally changes less often than an author's username, subscription tier or country. A mutable key can turn a routine profile edit into data movement and require old links or queued messages to resolve historical routing information.
Business ownership can still change. An organisation may merge accounts or transfer projects between tenants. Decide whether stored records retain their original partition identity, move to a new partition, or use a stable internal ownership identifier with a separate business mapping.
Avoid hiding this cost by declaring key changes impossible unless the product can really maintain that constraint. A documented migration workflow is more useful than a supposedly immutable field that support staff eventually need to edit manually.
Compare Range and Hash Distribution
Range sharding keeps neighbouring values together. A time-range query can then target relevant ranges efficiently, but recent writes may concentrate on one range.
Hashing spreads distinct input values through the key space. Hashing a post identifier can distribute inserts, but finding all posts by an author now needs another access path or a query across shards.
Hashing the author identifier keeps an author's data together and can spread authors across the cluster. However, one extremely busy author still maps to one key value. Hashing changes placement; it does not split the load represented by an identical value.
Choose based on the workload you measured. A more uniform distribution that turns every important read into a cluster-wide search may simply move the bottleneck.
Work Through Candidate Keys
Consider four candidates for the posts service. A creation timestamp groups recent posts together and helps time-based retention, but range distribution concentrates new writes at the newest range. A random post identifier spreads individual posts well, while making an author's timeline a distributed query unless another index or prepared view supports it.
An author identifier co-locates an author's posts and fits timeline and deletion operations. Its weakness is the unsplittable workload of a very large or active author. A composite author-and-bucket key spreads exceptional authors further, but increases the number of queries needed to reconstruct a timeline.
There is no need to describe one as universally best. Select the candidate that serves the high-value operations and explicitly fund the extra paths. If direct post reads use a locator and author timelines are the primary workload, author-based routing can be a coherent design. If almost every read is by post identifier, the balance may favour a different model.
Compound keys need careful interpretation. An index on (authorId, createdAt) does not necessarily mean the database independently distributes all author-and-time combinations. The configured shard or partition key determines placement; an additional sort key may only order items within a logical partition. Read the engine's model before assuming that another field splits a hot author.
Range sharding can be attractive when neighbouring records are commonly read together and ranges can be managed effectively. Historical data can become comparatively cold while current ranges receive more resources. But a time-range design still needs a response to the newest range becoming saturated.
Hashing trades locality for distribution among distinct inputs. Hashing a monotonically increasing post identifier can prevent its natural order from directing all inserts to the final range. The same transformation does not split repeated writes to one post identifier, because identical input produces identical output.
Separate Logical Placement from Physical Placement
Avoid a routing rule such as hash(authorId) modulo currentServerCount unless the consequences of changing the server count are deliberately handled. Changing that divisor can remap a large fraction of all keys at once and requires moving their data while keeping reads correct.
A common application-managed approach maps keys to a stable set of logical buckets, then maps those buckets to physical shards through routing metadata. Moving a bucket updates a bounded unit of ownership without redefining every key's hash rule. Managed databases provide their own mechanisms, which should be used and understood rather than recreated unnecessarily.
Routing metadata needs a version and an update protocol. An application instance with an old map may contact the previous owner during migration. The system should redirect, retry with refreshed metadata or follow a defined transition path, rather than silently reading an incomplete old copy.
Additional shards only help after data or logical ownership moves to them. Provisioning capacity and redistributing work are separate operations. Watch the balance and migration process, including the extra load generated by copying data, before concluding that scaling the cluster had no effect.
Split Exceptional Hot Keys Deliberately
Suppose one author generates enough activity to overwhelm the ordinary author-based design. A bucket can divide that author's posts across several logical keys:
Partition key: author-42:bucket-3
Sort key: 2026-09-12T10:15:00Z:post-981
For example, a stable hash of the post identifier could choose one of several buckets. Reading that author's latest posts now queries the relevant buckets and merges the results by time and identifier.
This is a trade-off: extra write distribution adds read requests, merge logic, and more complex pagination. Persist or version the bucket scheme so changing the bucket count does not make older records unreachable. AWS describes comparable write sharding building blocks.
A time bucket alone limits collection size but can leave all current writes in today's bucket. Combining time and hash buckets may help, at the cost of another dimension to query and manage.
Calculate What Bucketing Buys
Suppose a hypothetical author creates 1,600 write operations per second and testing shows the chosen logical-key design should stay around 400 of those operations per second to retain headroom. Four evenly used buckets would average 400 each, so eight might provide more margin. Those figures are workload assumptions; actual capacity depends on item size, indexes, service limits and contention.
Choose a bucket deterministically from a stable input when readers can reproduce it. For example, bucket = stableHash(postId) % 8 lets a direct reader calculate the bucket if it knows the post identifier and scheme version. A platform-dependent language hash that changes between processes is unsuitable as a durable routing algorithm.
Random bucket assignment can also distribute writes, but the assigned bucket must be stored or discoverable. Otherwise a read for one post must try every bucket. Determinism avoids that lookup at the price of maintaining the routing algorithm as part of the data contract.
Bucketing does not remove all shared writes. If every post creation also updates one author-level PostCount, that single counter can remain hot even while posts distribute perfectly. Inspect the complete write path, including quotas, sequence allocation, audit records and secondary indexes.
Design the Fan-Out Read Before Shipping the Write
An author timeline spread across eight buckets requires eight ordered reads followed by a merge. If the client asks for 20 results, fetching 20 from each bucket is a simple bounded strategy but can read up to 160 candidates to return 20. More selective fetching can reduce waste while making coordination more complex.
Use one deterministic total order, such as (createdAt, postId), across all buckets. Timestamps alone can tie, and independent bucket cursors can otherwise skip or repeat records. The merge must choose the newest eligible item from each bucket consistently.
Pagination needs state. A cursor may contain per-bucket continuation positions, a global boundary that each bucket can apply, or a server-side cursor reference. Choose the approach according to the database's query semantics and the level of consistency promised across pages.
Concurrent inserts mean a later page is not necessarily a snapshot of the first page unless the system explicitly provides one. Document whether the user sees a live timeline or a fixed snapshot. Protect cursor integrity and bind it to the author and query filters so clients cannot use it to bypass access rules.
AWS's data-modelling building blocks discuss write sharding and the corresponding need to query multiple keys. The broader design lesson is that additional write destinations create additional read coordination.
Version Bucket Changes
Changing from eight to sixteen buckets changes the result of a modulo calculation for many identifiers. If old records remain under the previous scheme, simply deploying the new formula makes some reads look in the wrong place.
Keep a scheme version and the applicable bucket count in routing metadata. Existing records can remain under the old version while new writes use a new one, with readers querying both until migration completes. Alternatively, move records with an explicit cutover procedure and preserve a way to resolve stale references.
Use bucketing selectively when it makes sense. Ordinary authors may remain on one logical key while exceptionally busy authors receive several. This reduces routine read fan-out, but requires trustworthy per-author routing metadata and a transition procedure when an author becomes hot.
Thresholds should incorporate sustained load and recovery headroom rather than reacting to every brief spike. Otherwise the system can repeatedly change placement while a cache would have absorbed the burst. Placement changes are operational work, so use them where their durable benefit justifies the complexity.
Treat Read and Write Hotspots Differently
A popular post might cause millions of reads for one record. Caching, request coalescing, and appropriately consistent read replicas can reduce that pressure without changing its storage key.
Frequent writes to one counter need a different approach. Splitting increments across counter buckets can distribute writes, but reading the total requires aggregation and a defined freshness guarantee.
Check secondary indexes as well. A well-distributed base table can still have an index that funnels all pending posts under one status value. AWS's partition key guidance recommends considering activity across table and secondary-index keys.
Absorb Repeated Reads at the Right Layer
For a public viral post, caching the immutable content or a short-lived representation can prevent repeated database reads. Request coalescing allows one request to refill an expired cache entry while others wait or receive an acceptable stale version. Without coalescing, simultaneous cache misses can recreate a hotspot even with a high average hit rate.
Authorisation may need a different freshness policy from content. A cached post body can be reusable while a private account's access rules require a current check. Cache keys and invalidation must reflect tenant, visibility and relevant policy versions so distribution improvements do not expose restricted data.
Read replicas help when the workload can tolerate their actual consistency and when reads can be routed across them. They do not resolve a single authoritative write bottleneck. If the application always directs a popular read to the primary for freshness, adding unused replicas changes little.
Change the Write Model for Contended Values
A like counter updated in one row for every like can become a write hotspot. One alternative records unique like facts by user and post, then updates several counter buckets asynchronously. Reads sum the buckets or use a periodically refreshed total with an explicit freshness guarantee.
The authoritative rule remains one like per user and post. Splitting the display counter does not remove the need to enforce that identity or process duplicate events safely. Otherwise retries can inflate the total even though write throughput looks better.
Some values cannot be split without changing their meaning. A strict global sequence or an exact inventory balance may require coordination. If every operation depends on that value, acknowledge its serialisation cost and consider reservations, batching or a different invariant rather than presenting hash buckets as a universal solution.
Secondary indexes deserve independent workload diagrams. An index partitioned only by status = Pending can receive every new write, despite excellent distribution in the base table. A time-and-bucket suffix or separate queue design may distribute it, but changes how workers discover pending work and how ordering is maintained.
Test Skew and Plan for Movement
Test with uneven traffic: busy authors, viral posts, concentrated imports, and peak-hour writes. Uniform random data can conceal the exact failure the shard key needs to prevent.
Measure latency, throttling, CPU, and work per shard, together with the number of shards contacted per query. Logical keys and physical partitions are not interchangeable; inspect how the selected database splits and moves data.
Document the migration path before it is urgent. Resharding may require copying records, changing routing, and validating queries while writes continue. If the platform provides online resharding, test its restrictions and operational cost on representative data.
Test the Shape of Production Work
Use distributions with a long tail: many quiet authors, fewer busy authors and several extreme cases. Add a viral read burst, an import concentrating writes on one tenant and a deletion job scanning a large history. A benchmark with uniformly random keys mainly proves that uniform traffic distributes well.
Observe tail latency as well as averages. A cross-shard read often waits for its slowest required response, so fan-out can amplify a small number of overloaded destinations. Record shards contacted, items examined, items returned, retries and throttling alongside total request time.
Measure data volume, read activity and write activity separately per shard. Include index work, replication and background maintenance where the platform exposes them. A shard with moderate storage but high write amplification may need attention before the largest shard does.
Test degraded conditions. Take a worker or replica out of service in a safe environment and measure whether routing and retries concentrate extra traffic on the remaining owners. A design that is balanced only when every component is healthy may fail during routine maintenance.
Treat Migration as a Consistency Workflow
For application-managed movement, a typical outline is to prepare a destination, copy existing records, capture writes occurring during the copy, verify the destination and switch routing through a controlled ownership change. The exact protocol depends on the database, but concurrent writes must have one understood authoritative path throughout.
Avoid casual dual writes to old and new shards. If one write succeeds and the other fails, the copies diverge. Use supported migration facilities or durable change capture with reconciliation, and define which copy wins while the move is in progress.
Verify more than row counts. Compare identifiers, versions and representative query results; a matching total can conceal missing and duplicated records that cancel each other numerically. Include deleted records and tombstones so copying an old row cannot resurrect content removed during migration.
Make rollback conditional on reality. After new writes reach the destination, switching back to an old snapshot can lose data. A safe rollback may need reverse change propagation or a period in which both sides remain recoverable. Record this before the cutover, when decisions can be considered calmly.
Further Design Improvements
Keep analytical scans away from the request-serving routing path when their workload differs substantially. A reporting pipeline or warehouse can process broad historical questions without forcing the primary shard key to serve every possible query.
Use tenant-aware admission controls when one customer's legitimate burst can consume a shared shard. This is a complement to good distribution: the system still needs to protect capacity while a hot tenant is being investigated or moved.
Review the key as the product changes. A design optimised for author timelines may need adjustment when collaborative ownership, global search or large group publishing becomes central. Retain the access-pattern worksheet and measured skew so that future changes build on evidence rather than repeating the original debate from memory.
Summary
A good shard key balances work while keeping important queries and transactions practical. Evaluate cardinality, uneven frequency, changing values, and the cost of locating related records.
Use hashing to spread distinct keys, buckets to split exceptional loads, and caching for repeated reads. Validate the design with realistic skew and a migration plan. Sharding becomes useful when additional machines receive useful work, not simply when the cluster contains more machines.