The database sharding decision record: why the shard key you chose determines your cross-shard query cost and your rebalancing surface

2026-07-03 · Decision record · Database · Sharding · Scalability

Eighteen months after a 30-person B2B SaaS company shards its primary Postgres database by organization_id, the engineering team has eight shards, each holding the complete data for a subset of tenants. Tenant-scoped queries — "show me all active projects for organization 4471," "show me all invoices this month for organization 4471," "show me all users in organization 4471 who haven't logged in for 30 days" — route to a single shard and return in under 100ms. The shard key was the right choice for the operational workload that made up 85% of query volume at sharding time. Eighteen months later, the data team ships an analytics dashboard that shows company-wide metrics: daily active organizations, feature adoption rates, revenue cohort analysis, churn indicators by plan tier. Every query on that dashboard requires results from all eight shards. The query router fans out to all eight shards in parallel, collects results, and merges them on the application side. With eight shards and the data volume that has accumulated over 18 months, each scatter-gather query takes 3–7 seconds per shard, and the dashboard loads in 45 seconds on a good day.

The team does not know why organization_id was chosen as the shard key. There is no ADR, no Slack thread, no design document. The decision was made during a two-hour session eighteen months earlier when the founding engineer noticed read replica lag exceeding 5 seconds and decided horizontal sharding was the answer. The choice of organization_id was intuitive — it aligns with the tenant isolation model the application already enforces — and was never evaluated against the cross-shard query cost for the workloads that would run on the sharded database eighteen months later. Fixing the analytics dashboard requires either a separate analytics database with its own data model (CQRS, a data warehouse, or a materialized aggregate layer), or a shard key change that makes analytics queries shard-affine — both are multi-month engineering efforts that are now load-bearing because the sharding decision was made without writing down the trade-offs.

The second failure looks different but traces to the same cause. A marketplace shards its orders table by order_date, truncated to the day. This is a common recommendation for time-series data: recent data lives on one shard, old data lives on older shards, archiving is just dropping a shard, and queries for recent orders — the most common operational query — route to one or two shards. The recommendation is correct for read-heavy time-series workloads where writes are distributed evenly across time. For a marketplace running a seasonal promotional event, it is a catastrophic write routing policy. Black Friday: 100% of new order writes go to the shard for today's date. The other eleven shards are effectively read-only. The write-hot shard reaches CPU saturation at 11:00am, 90 minutes into the sale, at 2.8× the peak write throughput observed on any previous day. The database team adds a second writer node for the current-day shard at 11:22am. The application routing layer does not know about the second writer — it routes all writes to the primary for the current-day shard as configured. The 22 minutes between saturation and adding the writer cost 14,000 failed order transactions and $340,000 in lost GMV. The post-incident review concludes that the shard key was wrong for the write pattern. Nobody had written down that the shard key was chosen for the read pattern, that the write distribution implication was not evaluated, or that a date-based shard key is a known write hotspot risk for any workload with predictable traffic spikes on specific dates.

Both companies sharded their databases. Both made reasonable-sounding shard key choices. Neither wrote down the trade-offs. The database sharding decision record is the document that makes the shard key choice explicit — including the query patterns it was optimized for, the query patterns it was not optimized for, the write distribution it produces, and the conditions under which the choice should be revisited.

What a database sharding decision record covers

Sharding is not a single decision — it is a family of interconnected decisions that together determine how data is distributed, how queries route, how the system scales, and how it recovers when the initial assumptions about the workload turn out to be wrong. The shard key is the most visible decision, but it cannot be evaluated in isolation from the query patterns it will serve, the rebalancing policy that will govern how shards split as data grows, and the migration strategy that will apply if the key proves to be wrong under a workload that was not anticipated.

The five decisions that belong in a database sharding ADR are:

Three structural properties that the shard key decides

1. The cross-shard query cost model

The cross-shard query cost model is the relationship between the shard key and the fraction of queries that require scatter-gather across multiple shards. For any given shard key, the shard-affine query set is the set of queries whose WHERE clause includes an equality predicate on the shard key — these queries can be routed to exactly one shard. Every other query either requires scatter-gather (fan out to all shards, collect results, merge) or requires that the application first look up which shards contain the relevant data and then query only those shards (which requires a separate shard directory or secondary index).

The cross-shard query fraction is typically small at sharding time because sharding is usually adopted when the primary scaling bottleneck is write throughput or storage capacity for the most common operational queries — which are usually shard-affine by design. The problem is that the cross-shard query fraction grows over time as new product capabilities add new query patterns. An analytics layer, an admin interface, a billing system, a churn analysis pipeline, a compliance audit query — each of these may access data across shard boundaries. The cross-shard query cost model must therefore project the cross-shard fraction not just for the current workload but for the workloads that will exist when the shard count is 4×, 8×, and 16× the initial count.

The calculation that belongs in the ADR is: for each identified cross-shard query pattern, what is the per-query latency as a function of shard count? If each scatter-gather query fans out to n shards, and each shard sub-query takes t milliseconds, the scatter-gather latency is approximately t × (1 + coordination overhead factor) — the sub-queries run in parallel, but the merge cannot begin until all shards respond, so the effective latency is the slowest shard's response time plus merge overhead, not the average. For a p99 shard latency of 200ms and a merge overhead of 50ms, a scatter-gather across 8 shards takes approximately 250ms per execution — which scales to 250ms per scatter-gather query regardless of shard count, as long as sub-queries run in parallel. The latency appears to not scale with shard count, and this is the trap: the throughput cost of scatter-gather does scale with shard count. A scatter-gather query that fans out to 8 shards consumes 8× the database CPU and I/O budget of a single-shard query. As shard count grows, the throughput cost of each scatter-gather query grows proportionally. At 32 shards, a scatter-gather query consumes 32× the database resources of a single-shard query. If cross-shard queries are 5% of query volume at 8 shards, they are consuming 40% of total database resources. At 32 shards they are consuming 160% of the resources that single-shard queries consume — meaning that cross-shard queries become the dominant database cost driver even though they represent a small fraction of query count.

2. The write distribution and hotspot risk

The shard key determines how writes are distributed across shards. For a shard key with uniform value distribution — a hash of a UUID primary key, or a high-cardinality natural key that is accessed uniformly — writes are distributed approximately uniformly across all shards. Any single shard receives approximately 1/n of total write volume. For a shard key with non-uniform distribution or monotonic growth, write distribution is skewed: certain shards receive disproportionate write volume, and the system's effective write throughput ceiling is the throughput ceiling of the most-written shard, not the aggregate throughput of all shards.

Monotonic shard keys are the canonical write hotspot risk. A date-based shard key concentrates writes on the current period's shard because all new rows have today's date. A sequential integer primary key used as a shard key concentrates writes on the last shard in the range because new rows have the highest ID. An auto-incrementing status field concentrates writes on the "active" shard because status transitions all move toward active. The hotspot is not just a write performance problem — it is a capacity problem. The hot shard's storage grows faster than other shards, its connection pool is hit harder, and its replication lag grows first when the system is under load. The first indication that a monotonic shard key is a problem is often not write saturation but replication lag on the hot shard, which is mistaken for a replication configuration issue rather than a sharding design issue.

The write distribution calculation that belongs in the ADR is: for the observed write workload, what is the coefficient of variation of write volume across shards? A coefficient of variation below 0.2 indicates approximately uniform distribution; above 0.5 indicates significant skew that warrants analysis; above 1.0 indicates that the hot shards are receiving more than twice the average write volume and that the shard key is producing structurally uneven distribution that will get worse as the workload grows. This calculation should be performed against the actual write distribution derived from query logs or application metrics, not estimated from the shard key's theoretical distribution — because the actual access pattern often differs from what the shard key distribution would predict, due to access locality effects in the application logic.

3. The rebalancing surface and operational cost

The rebalancing surface is the set of conditions that trigger a shard split, merge, or move, combined with the operational cost of each event. It is determined by the shard key distribution, the shard count policy, and the rebalancing trigger configuration. A shard key with uniform distribution produces a predictable rebalancing surface: each shard grows at approximately the same rate, so splits occur approximately simultaneously, and the timing of the next split can be forecast from the current data volume and growth rate. A shard key with non-uniform distribution produces an unpredictable rebalancing surface: the largest shard hits the split threshold while other shards are half-full, the split must move a large tenant's data rather than a mathematically defined hash range, and the timing of the next split depends on the growth rate of that specific tenant rather than the overall system growth rate.

The operational cost of a shard split has three components: data movement cost (the volume of data that must be transferred from the splitting shard to the new shard, and the I/O budget consumed during the transfer), routing update cost (the time required to update the shard directory or routing layer to reflect the new shard assignment, and whether that update can happen atomically or requires a maintenance window), and consistency cost (whether writes must be paused during the split, whether a dual-write period is required, or whether the split can happen online without any write interruption). For a shard key that allows range-based splits — where the split point is a value boundary in the shard key space and data on either side of the boundary goes to different shards — the routing update is a single range assignment change and the data movement is the rows whose shard key value falls in the upper half of the split range. For a shard key that does not allow range-based splits — where the shard assignment is computed by a hash function and a split requires rehashing all data, or where each shard holds the complete data for one or more specific entities — the data movement cost is higher and the routing update is more complex.

The rebalancing surface ADR section should document: the expected data volume per shard at the time of the next required split, the trigger that will signal that a split is needed (storage utilization, query latency, or write throughput on the hottest shard), the expected data movement volume per split, the estimated time to complete a split at the current I/O budget, whether splits can happen online or require a maintenance window, and the maximum number of concurrent splits that the system can sustain without degrading query performance for the unaffected shards.

Five ADR sections for a database sharding decision record

1. Shard key selection and query pattern analysis

Document the shard key value and the query pattern analysis that motivated the choice. The analysis should enumerate the top query patterns by volume and latency impact, classify each as shard-affine (routes to one shard given this key), partial-shard-affine (routes to a predictable subset of shards), or cross-shard (requires scatter-gather or is prohibited), and compute the expected distribution of query volume across these three classes at both the current shard count and the projected shard count at 12 months and 24 months of data growth.

Document the candidate shard keys that were considered and rejected. For each rejected candidate, record the reason for rejection: "Rejected user_id because 15% of admin queries filter by organization without filtering by user, and these queries are latency-sensitive (p99 < 200ms requirement). Sharding by user_id makes all organization-level admin queries cross-shard." This record of rejected candidates is often more valuable than the record of the chosen candidate, because it prevents the team from proposing the rejected keys again during future scaling discussions without understanding why they were already evaluated and declined.

Document the access locality assumption that the shard key relies on. Every shard key choice assumes that the actual query workload has certain access locality properties — that most queries access data belonging to one tenant, or one time period, or one region. This assumption is usually correct at sharding time but may not remain correct as the product evolves. Record the assumption explicitly: "This shard key assumes that >90% of query volume by row count is tenant-scoped and includes an equality predicate on organization_id. If cross-tenant analytics workloads grow beyond 10% of total query volume, this shard key will be a primary latency bottleneck for those workloads." This statement tells the future team exactly when the shard key assumption has been violated and a revision is warranted.

Document the write distribution analysis alongside the read query analysis. For each shard key candidate, what is the expected write distribution across shards given the current insert patterns? A shard key that produces excellent read locality may produce write hotspots if inserts are concentrated on a small range of shard key values. The write distribution analysis should use actual insert patterns from the current workload, not theoretical analysis of the shard key's cardinality, because access patterns in application code often differ from what the key distribution would predict.

2. Shard count policy and initial capacity model

Document the initial shard count and the capacity model that determined it. The shard count is not a free parameter — it is constrained by the minimum data volume per shard below which shard management overhead exceeds the performance benefit, the maximum data volume per shard above which rebalancing cost grows prohibitively, the connection pool limit per shard node, and the number of concurrent scatter-gather queries the application will need to support at acceptable latency. The initial shard count should be calculated from these constraints, not chosen intuitively.

The capacity model documents: the current total data volume, the projected data volume at 12 and 24 months (from observed growth rate), the maximum acceptable data volume per shard before a split is required (the split threshold — typically set to 60–70% of the node's tested capacity to leave room for the split operation itself), the initial data volume per shard under the chosen key (which may not be uniform if the key has non-uniform distribution across existing data), and the derived calculation: at the observed growth rate, when will the first shard hit the split threshold? This date should be in the ADR as the "next required rebalancing event" so that the team can plan for it rather than discover it during an incident.

Document the shard count growth policy explicitly. The shard count will grow over time as data accumulates. The ADR should specify: what triggers a shard count increase (a specific shard hitting the split threshold, overall cluster storage exceeding a percentage of total capacity, or a write throughput metric on any shard exceeding a threshold), whether shard count grows incrementally (one new shard at a time) or by doubling (because doubling preserves clean hash range boundaries for hash-based shard keys), and what the maximum planned shard count is given the cross-shard query cost model. If the cross-shard query cost model shows that scatter-gather queries become unacceptably expensive at 32 shards, then the maximum shard count is 32 — and the team should begin evaluating shard key alternatives before the shard count reaches that limit, not when it hits it.

3. Rebalancing strategy and shard split/move policy

Document the rebalancing procedure as a step-by-step process with explicit rollback steps at each decision point. The procedure should specify: the data movement approach (streaming replication to a new shard node, a logical dump-and-restore, or an online copy using a tool like pg_partman, Vitess, or the database's native partitioning); the dual-write window and its duration; the read switchover mechanism and whether it can be done without application changes; the verification step that confirms data integrity after the move (row count comparison, checksum comparison, or query result comparison against the source shard for a sample of queries); and the rollback step that restores reads to the source shard if verification fails.

Document the data movement rate limit. A shard split that consumes 100% of the I/O budget of the source shard will cause query latency degradation for the data on that shard during the split. The rate limit should be set to consume no more than 20–30% of the I/O budget observed during the previous 7-day peak, leaving 70–80% for live query traffic. At the chosen rate limit, calculate the expected duration of each split operation for the expected data volume at the time of the next split. If the expected duration is longer than the acceptable maintenance window, either the rate limit must be relaxed (accepting latency degradation) or the split must be performed in segments (which increases operational complexity).

Document the hot-shard detection and emergency split policy separately from the routine rebalancing policy. Routine rebalancing is planned — the split threshold is reached on a schedule that can be forecast from the growth rate, and the split can be performed during a low-traffic window. Hot-shard events are unplanned — a write hotspot concentrates writes on one shard faster than the data volume trigger would predict, and the split must be performed under load during peak traffic. The emergency split policy should specify the write saturation threshold (CPU, I/O, or connection pool utilization on a single shard) that triggers an emergency split, the expedited split procedure (which may accept more operational risk than the routine procedure), and the traffic shaping policy during the emergency split (whether writes to the hot shard are rate-limited to protect the split operation, and whether a circuit breaker kicks in if the shard becomes completely saturated).

4. Cross-shard query patterns and scatter-gather policy

Document every identified cross-shard query pattern and how it is handled. For each pattern, the options are: accept scatter-gather (allow the application to fan out to all shards and merge results, with a documented latency and resource cost that is acceptable for that specific query type); prohibit scatter-gather (require that the application be redesigned so that this query pattern is either removed or replaced with a shard-affine alternative); materialize (pre-compute the cross-shard aggregate and store it in a separate table or cache that is updated synchronously or asynchronously), or route to a separate analytics tier (a data warehouse, a read replica with full data access, or a CQRS query model that is populated from the sharded data by an event stream).

Each policy choice has a different consistency model. Scatter-gather on the live database returns strongly consistent results but at scatter-gather cost. Materialized aggregates return eventually consistent results (the staleness is determined by the update frequency) at near-zero query cost. A separate analytics tier returns eventually consistent results (the staleness is determined by the replication lag) at near-zero cost for the primary database. The consistency model is a product decision, not just a technical decision: an analytics dashboard showing yesterday's data is fine for a growth dashboard but unacceptable for a billing display. The ADR should document the consistency requirement for each cross-shard query pattern and the chosen handling strategy relative to that requirement.

Document the scatter-gather query circuit breaker policy. Unbounded scatter-gather queries are the most common cause of database capacity incidents in sharded systems, because they scale with shard count and can consume disproportionate resources when combined with heavy primary workload. The circuit breaker should specify: the maximum number of concurrent scatter-gather queries the system will accept (above this, additional scatter-gather queries are queued or rejected); the timeout per scatter-gather query (the maximum wall-clock time the merge tier will wait for all shards to respond before timing out and returning a partial result or an error); and the resource isolation policy (whether scatter-gather queries are executed in a separate connection pool from single-shard queries so that scatter-gather overload does not degrade single-shard query latency).

5. Shard key migration strategy

Document the conditions that would trigger a shard key migration: what specific observable state — not a general principle but a specific measured condition — would cause the team to conclude that the current shard key is no longer fit for the workload? The conditions are typically: cross-shard query workloads have grown to consume more than a defined percentage of total database resources (for example, 30%), AND application-layer mitigations (caching, materialized aggregates, analytics tier offload) have been evaluated and are either not sufficient or would introduce a consistency model that is unacceptable for the affected query patterns, AND the expected cost of operating with the current shard key over the next 12 months exceeds the estimated cost of a shard key migration.

Document the dual-write migration approach. A shard key migration requires that the same data exist in both the old shard layout and the new shard layout simultaneously during the migration window, so that reads can be served from the old layout while the new layout is being populated, and then switched atomically to the new layout once validation is complete. The dual-write approach specifies: where the routing decision happens (application code, a proxy layer like Vitess, or a database trigger), how long the dual-write window lasts (the time required to copy all historical data to the new shard layout at the throttled migration rate), how the routing switch is controlled (a feature flag, a configuration update, or a deploy), and how in-flight writes during the switchover are handled (whether the switchover is instantaneous or requires a brief write pause to avoid routing ambiguity).

Document the validation approach for the new shard layout before the routing switch. The validation must cover: total row count comparison between old and new layout for each table; a random sample of rows verified to exist in the correct shard under the new key; a set of production query patterns executed against both the old and new layout and compared for result equivalence; and a write test that verifies writes in the new layout are correctly routed and are not silently dropped. The validation must complete successfully before the routing switch, and each validation step must have a defined acceptance criterion (not just "results look similar" but "row counts match within 0.01%" or "all 500 sample queries return identical results").

Document the rollback plan with a specific RTO. A shard key migration that reveals a routing bug, a data integrity failure, or an unexpected performance regression after the routing switch must be reversible within a defined time window. The rollback plan must specify: how long the old shard layout is retained after the routing switch (the rollback window), the procedure to restore reads to the old layout (a configuration change, a deploy, or an emergency routing override), how writes that occurred in the new layout during the rollback window are reconciled with the old layout (because the old layout did not receive those writes if dual-write was terminated at the routing switch), and the maximum acceptable data loss or data drift during the rollback window. If the reconciliation is complex or the data loss is unacceptable, the dual-write must be maintained for longer after the routing switch — which increases migration cost but reduces rollback risk.

The decisions that look like configuration but are actually data model commitments

Database sharding is often treated as a database administration decision — a configuration change to the database tier that is independent of the application model. This classification is incorrect and produces the failure modes described in the opening. The shard key is not database configuration; it is a constraint that every query in the application must satisfy. Queries that do not satisfy the shard key constraint pay the scatter-gather cost or must be prohibited. Application features that require cross-shard data access must either pay that cost, be redesigned to not require it, or be served from a separate data tier with different consistency guarantees. These are product decisions, not operations decisions.

The connection between the shard key and the application data model is the reason sharding decisions belong next to the database vendor decision record, the database migration strategy decision record, and the API schema design decision record — not in the database runbook. The shard key constrains the API data model (because endpoints that cross shard boundaries require a different implementation than endpoints that are shard-affine). It constrains the migration strategy (because schema changes that affect the shard key column require a special procedure that a normal migration script cannot handle). It constrains the vendor choice for future scaling events (because some sharding strategies are native to specific database vendors and switching vendors is more difficult after sharding than before).

The decisions that matter most — the shard key value, the rebalancing trigger, the scatter-gather policy, the migration path — are made in a two-hour session when the read replica lag first becomes unacceptable or the single-node storage limit is first approached. They live in the AI chat history of that session: the conversation where the lead engineer explored different sharding approaches, considered tenant-based vs. hash-based vs. range-based keys, and concluded with "let's go with organization_id because everything else looks overcomplicated." That reasoning is the shard key decision — not the Terraform change that implements it. The ADR is the structured form of that reasoning, written to be legible to the engineer who will inherit the system in eighteen months and needs to understand not just what the key is but why, and what the conditions are under which it should be changed.

An export of the AI chat sessions around database architecture decisions typically surfaces the sharding exploration conversation, the rejected alternatives, and the reasoning behind the chosen key — including the access pattern assumptions that were made explicitly and the ones that were made implicitly. The five ADR sections above are the structured form of those decisions. Writing them down at sharding time, rather than reconstructing them from Terraform history and chat logs when the analytics dashboard is timing out at 45 seconds, is the difference between a sharding strategy and a sharding constraint inherited by accident.

Further reading