We store every score backwards so DynamoDB never sorts

We wanted a leaderboard that never sorts anything at read time. We built it on DynamoDB behind AWS SAM and Lambda, because "give me the top 25 of this board" is exactly the read a sorted index is good at, and because we did not want to run a database. This post is the table design, the reasoning from AWS's own papers and docs behind each choice, and an honest split of which numbers are measured, which are arithmetic, and which do not exist yet.

The design landed on 2026-08-11. Histogram counters and the propagation measurement arrived on 2026-08-18, and player erasure on 2026-09-04.

The problem

Hearso is a real-time trivia game. Rounds end together, so scores arrive in bursts, and every player wants to see where they landed before the next question loads. The writes are small and clustered in time; the reads want two different things.

"Who is on top of this board" is a list ordered by score. "Where am I on it" is a position in that list, for someone who may be nowhere near the top. Most of this design is about the first one, and the second is where it gets hard.

Three ideas you need first

The address and the shelf. Think of a coat check: your ticket number decides which room your coat hangs in, and within that room the coats hang in order along a rail.

Precisely, the partition key is fed to a hash function whose output picks the physical storage, and the sort key decides the item's position among everything sharing that partition key. AWS states the consequence plainly: "All items with the same partition key value are stored together, in sorted order by sort key value."

Our leaderboard index is partitioned by a board id like trivia#alltopics#alltime and sorted by a string we build per player. That is the whole design: if items are stored in sort-key order, "the top of the board" is "the first items", and there is nothing left to sort.

The partition and its ceiling. A partition is a shelf with a weight limit; overload one shelf and the shelf fails, not the warehouse. Precisely, it is SSD-backed storage replicated across Availability Zones holding "a disjoint and contiguous part of the table's key-range", and its ceiling is documented: "Each physical partition can support 3,000 read units per second and 1,000 write units per second."

Our largest benchmarked board held 10,100 rows, so we are nowhere near that. It still shapes the design, because one board is one partition key: if a board gets popular, that is where it shows.

The index is always slightly behind. A global secondary index is a second copy of your data filed under a different key, maintained for you but not synchronously: "any global secondary indexes on that table are updated asynchronously, using an eventually consistent model", and you cannot opt out. "Queries on global secondary indexes support eventual consistency only." So a successful write and a readable ranked entry are two different moments, which is why we measured the gap rather than wishing it away.

On the name: DynamoDB shares "most of the name of the previous Dynamo system but little of its architecture". What carried over is a value: Dynamo's authors measured service levels "at the 99.9th percentile of the distribution" rather than the mean, and our SLO is a p95 for the same reason.

Figure 1 draws both jobs of the key at once.

The partition key picks a partition through a hash, and inside it items with the same partition key sit in ascending sort-key order, so the top 25 is the first 25 items.

Figure 1. The two jobs a composite primary key does, drawn for our RankIndex. Partition behaviour per Elhemali et al., USENIX ATC 2022, section 3.

Our tables, exactly as configured

Two tables, one index, no streams.

	Scores table	Snapshots table
Partition key	`playerId`	`leaderboardId`
Sort key	`leaderboardId`	`snapshotTakenAt`
Indexes	one GSI, `RankIndex`	none
Billing mode	`PAY_PER_REQUEST`	`PAY_PER_REQUEST`
TTL	on `expiresAt`	none, on purpose
Point-in-time recovery	enabled	enabled
Encryption	AWS-owned key (the free default)	AWS-owned key
Streams	none	none
Protection	deletion protection, plus `Retain` on stack delete and replace	same

RankIndex is keyed leaderboardId / rankKey and projects three extra attributes: displayName, country, lastPlayedAt. The base table is keyed the other way round on purpose: a player's standings across every board, variant and period is one query on playerId, which is what makes GDPR erasure a query rather than a scan.

Why the score is stored backwards

Imagine a filing cabinet that only hands you folders front to back. If you want the best score first, you have to file it at the front.

The sort key is a string compared byte by byte, ascending by default. So we store the inverted score, SCORE_CEILING − score, zero-padded, and an ordinary ascending read becomes a descending leaderboard. SCORE_CEILING is 10^12 and the pad width is 13 digits, which is exactly enough: at score 0 the inverted value is 1,000,000,000,000.

The full key is <invertedScore(13)>#<achievedAtMillis(13)>#<playerId>. Four made-up players on one board, in the order DynamoDB stores them:

0999999990880#1786458127000#player_123   score 9,120
0999999991250#1786458127000#player_456   score 8,750, achieved 14:22:07Z
0999999991250#1786458131000#player_789   score 8,750, achieved 14:22:11Z
0999999999360#1786458127000#player_042   score 640

That is a plain ascending string sort. Reading the top 25 is one Query with ScanIndexForward: true and Limit: 25.

Here is the part I did not see coming. DynamoDB can already read a sort key backwards, "set the ScanIndexForward parameter to false", so inverting looks redundant. It is not, because our tie-break runs the other way: we want highest score first, then earliest achievement. Read descending and the timestamps come back latest-first, which is the wrong winner.

By inverting only the score, "highest score" and "earliest achievement" both become "smallest string", and one ascending read satisfies both rules with no comparison logic downstream. Delimiter-joined sort keys are a documented AWS pattern, not our invention: the guide's own example is [country]#[region]#[state]#[county]#[city]#[neighborhood].

One guard rail sits under this. A score at or above the ceiling would produce a negative inverted value whose - sorts before 0, silently corrupting the board's order. The key builder throws instead. Figure 2 shows those four keys as the index holds them.

Four example sort keys in ascending order put score 9,120 first and 640 last, with two players tied on 8,750 separated by timestamps four seconds apart.

Figure 2. Example rankKey values for four invented players, in the order DynamoDB stores them. Computed from the shipped key builder on 2026-09-18; no player data here is real.

The index decisions

Global, not local. A local secondary index shares the base table's partition key, so ours would have been partitioned by playerId: answering a question nobody asked. Two other differences confirmed it: an LSI caps indexed items at 10 GB per partition key value, and "You cannot add a local secondary index to an existing table, nor can you delete any local secondary indexes that currently exist." AWS's own gaming write-up reaches the same shape from the other direction: "The index would use the game ID or name as the partition key, and the top-score attribute as the sort key." That post scopes the claim to simple leaderboards, and it is right to.

The projection, where I was wrong. A projection is the set of attributes copied into the index. We chose INCLUDE with three fields so a 25-row page needs no follow-up fetch. Re-reading the guidance for this post, I found our recorded reasoning half wrong: ADR-0010 justified INCLUDE partly on write cost, but the docs say projecting fewer attributes only reduces write cost if those attributes would otherwise exceed 1 KB. "As long as the index items are small, you can project more attributes at no extra cost." Our entries are far under 1 KB, so the choice saves storage and keeps a free-form context map out of the index, and probably saves no write capacity at all.

What a projection does cost is sharper than I expected: "If an update to the table changes the value of an indexed key attribute (from A to B), two writes are required, one to delete the previous item from the index and another write to put the new item into the index." Every improvement changes rankKey, so it costs two index writes, not one.

Rows that never reach the index. The Scores table also holds idempotency records under an idem# prefix, one counter row per board under meta#, and seeding leases under seedlock#. None carries a rankKey, and that omission alone keeps them out of the index: "DynamoDB writes a corresponding index entry only if the index key attributes are present in the item." An index holding only some of a table's items is a sparse index, and global indexes are sparse by default. We got the behaviour by not writing an attribute.

What one submission does

Per board: read the player's row with ConsistentRead: true, commit one transaction, then optionally count. The transaction holds three items (a condition check on the board's seeding lease, the score-row update, and the counter update) and is all-or-nothing. The score update is conditional on exactly the state that was read, so a concurrent winner cancels it and the handler re-reads and retries, three attempts with jittered backoff.

An idempotency record wraps the request. A PutItem conditional on attribute_not_exists reserves the key before any board is touched; the outcome is stored after, so a retry replays the original response rather than re-running the fan-out. By default a submission fans out to three boards: all-time, the UTC day, the ISO week.

There is no DynamoDB stream here, and no stream consumer. Neither table declares a StreamSpecification, and the template has no event source mappings. The reconciler is an offline tool that reads a board twice from the index and compares before it repairs anything. I say this plainly because a streams-based fan-out is the obvious architecture, and it is not the one we have. Figure 3 has the full order.

A submission writes an idempotency reservation, then per board reads its own row strongly and commits a three-item transaction, then optionally runs two COUNT queries, then stores the outcome.

Figure 3. The write choreography for one board, in the order the merged handler issues it. Read from source on 2026-09-18.

Counting the writes is arithmetic, not measurement, but arithmetic over verified code and AWS's documented index rules:

What the score did	Writes on Scores	Writes in RankIndex
New player, three boards	8	3
Improved, crossing a bucket	8	6
Improved, same bucket	5	6
No improvement	5	3

The Scores column is one reservation, three score-row writes, up to three counter writes, one outcome write, and Figure 4 puts the two columns side by side. For contrast, ADR-0009 priced this at nine conditional writes, assuming three variants that still do not exist. Neither number is wrong; they answer different questions, a year apart.

A new player costs 8 writes on the table and 3 in the index; an improvement costs 6 index writes because changing the sort key deletes the old entry and puts a new one.

Figure 4. Item writes per accepted submission with the default three-board fan-out, counted from source on 2026-09-18. Item counts, not billed capacity units.

What one read does

GET /v1/leaderboards issues two calls at once: the index query, and a GetItem on the board's meta# row for the player-count denominator. Both are eventually consistent, which is all a display count needs.

ScanIndexForward: true is stated explicitly even though it is the default, because it is the read path's central claim rather than an accident. Ranks are stamped from each row's position in the page, and the score is decoded back out of the rankKey: the projection omits it deliberately, because the inverted key already carries it. LastEvaluatedKey becomes the next ExclusiveStartKey, wrapped in an opaque cursor carrying the rank the next page starts at.

Ranking deep in a big board

This is where DynamoDB stops helping, and AWS says so. In its leaderboard comparison the cons listed under DynamoDB are "No built-in rank computation" and "You can get top-N but not 'player X is ranked 4,327th' efficiently". There is no order-statistic operation, and Select: COUNT reduces bytes returned, not entries read.

So we cap it. A bounded query counts at most 2,000 entries; under that, rank is exactly count + 1. Past the cap we fall back to a histogram: 128 counters held as top-level attributes bucket000 to bucket127 on the board's meta# row, updated in the same transaction as the score. Assignment is logarithmic, min(127, floor(ln(1 + score) / ln(10^12) × 128)), putting resolution where ordinary trivia scores live.

score	bucket
0	0
10	11
100	21
1,000	32
8,750	42
9,999	42
1,000,000	64
999,999,999,999	127

Scores from 0 to 9,999 occupy buckets 0 through 42 and touch 40 distinct buckets; bucket 42 spans scores 8,659 to 10,745. Those are computed from the shipped function, not measured. Summing the buckets above yours gives a rank range, and the response returns rank: null with both a rank range and a percentile range, so an approximation is never dressed up as a fact.

Two honest limits. The histogram bounds the read cost of a deep estimate: at most 2,000 index entries plus one fixed-size item, whether the board holds a hundred players or a million. It does not spread writes, and there is no bucket prefix in the sort key; an earlier draft of ours got that wrong. And it cannot see ordering inside a bucket, so the range deliberately contains every tied position that could be correct.

What we measured

On 2026-08-18, against DEV in us-east-1 with response caching off, we measured the time from a successful POST /v1/scores to that player being readable through GET /v1/leaderboards.

Board size	p50	p95	p99
100 players	160 ms	235 ms	449 ms
1,000 players	158 ms	181 ms	206 ms
10,000 players	158 ms	210 ms	265 ms

300 samples, 100 per size, zero errors. Run-wide: p50 159 ms, p95 210 ms, p99 322 ms, against a target of 1 second at p95. An earlier run the same day, on two board sizes only, gave 162 / 239 / 338 ms; that is the run ADR-0012 set the SLO from.

The flatness is the interesting part, and Figure 5 plots it against the target: propagation did not degrade as the board grew a hundredfold, which matches AWS's documented expectation that index changes propagate "within a fraction of a second, under normal conditions". Most of our 159 ms is API Gateway, Lambda and network, not index lag.

Propagation stayed flat as the board grew: p50 was 158 to 160 ms at every size, and the worst p95 was 235 ms, far under the 1,000 ms target.

Figure 5. Measured propagation per board size, DEV us-east-1, 2026-08-18T19:38:19Z, 300 samples, 0 errors, against the 1,000 ms p95 target.

The second measurement is less flattering and more useful. On 2026-08-19 we drove 1,000 new players onto a single board:

In flight at once	Succeeded	Failed
8	1,000	0
16	986	14
64	525	475
1,000	204	796

DynamoDB reported zero throttled requests throughout. The binding limit was not capacity: it was transaction conflict on the board's single meta# counter row, which every new player must update. At the clean level the table consumed 6,868 write capacity units in the peak minute.

Capacity, and the arithmetic I can and cannot do

Both tables are PAY_PER_REQUEST. Our traffic is spiky by design: a seeding run dumps hundreds of writes in seconds, then the table sits quiet for hours. Provisioning for the spike wastes money all day; provisioning for the quiet throttles the spike. AWS calls on-demand "the default and recommended throughput option for most DynamoDB workloads", and a new on-demand table already sustains "up to 4,000 writes per second and 12,000 reads per second".

The write arithmetic, with its assumptions: 1,000 accepted submissions a day, all from new players (the worst case), the default three boards, every item under 1 KB. A standard write is 1 write request unit per 1 KB; a transactional write is 2, because DynamoDB "performs two underlying reads or writes of every item in the transaction: one to prepare the transaction and one to commit".

Reservation and outcome: 2 units
Three boards × two transactional items × 2: 12 units
Three index entries: 3 units
About 17 write request units per submission, so roughly 510,000 a month

At the rate printed in AWS's own pricing worked example for US East (N. Virginia), $0.6250 per million writes, that is about $0.32 a month in write requests.

That number is thinner than it looks. It excludes reads, storage, the transaction's condition-check item, and any month where players improve rather than arrive. The rate comes from a worked example embedded in the pricing page (the headline rate table renders in the browser and would not load for us) so treat it as one Region on one date, not a quote. And nothing has ever been billed: the production stack does not exist yet.

The read cost is what I would watch first, and it is not in that sum. Two Select: COUNT queries run per board when a client wants its rank delta at submit time, and a COUNT still reads every entry it counts, so on a large board those dominate the request. That is why rankDelta: false exists on the contract.

What the theory says could hurt us

A hot partition on one popular board. One board is one partition key, so all its index traffic lands in one place against that 3,000 read / 1,000 write per second ceiling. AWS remediates automatically, split for heat "might split the partition into two new partitions" at no extra cost, and the classic manual fix is write sharding. I am wary of that one, because its documented tax lands on our best feature: "to read all the items for a given day, you would have to query the items for all the suffixes and then merge the results." One already-sorted query is the point; sharding turns it into N queries and a merge.

The hot metadata row. Here theory and measurement agree. A single item cannot be split out of trouble, "a partition receiving high traffic to a single item … will not benefit from split", and adaptive capacity's best offer is to isolate it and hand it "the partition maximum of 3,000 RCUs and 1,000 WCUs". Our concurrency-16 failures appeared long before any of that, because serialised transactions on one row bind first. We set a review trigger rather than sharding pre-emptively.

Index back-pressure. The docs warn that "If you perform heavy write activity on the table, but a global secondary index on that table has insufficient write capacity, the write activity on the table will be throttled." That is framed around provisioned settings, and we are on-demand, where the index scales with the table. Something to re-check if we switch modes, not a risk we carry today.

Figure 6 sketches the failure and the two documented ways out, none of which we have needed.

A popular board concentrates traffic on one partition key against 3,000 read and 1,000 write units per second; splitting cannot help a single hot item, leaving write sharding or a changed access pattern.

Figure 6. Theory from AWS guidance, not something we have needed or measured. We have never observed DynamoDB throttling on our tables.

What we would use instead, if the requirements changed

If live global rank for every player became the product, DynamoDB alone would be the wrong tool, and AWS says so. A sorted-set store answers it directly: "Rank computation is built into the data structure, not your application code", with "O(log N) rank lookups regardless of player count". The cost is a second service to run, and a sorted set is memory-bound.

The other direction is Delivery Hero's, published by AWS: a production leaderboard on DynamoDB alone, where "These leaderboards are computed and updated once at the end of the day". Precomputing rank on an interval makes everything cheap, and it is the trade we did not want: a trivia round that ends should show its result now.

So we sit in between on purpose. If live deep rank becomes the feature, the sorted set gets added next to DynamoDB, not instead of it.

Key takeaways

If items are stored in sort-key order, "the top N" is a read, not a computation. Inverting the score is what makes ascending order mean "best first".
Invert only the component whose order you want reversed. Ours breaks ties by earliest achievement, which a plain descending read would get backwards.
An index entry exists only if the item carries the index's key attributes. Omitting one keeps bookkeeping rows off the leaderboard permanently.
Changing an indexed key attribute costs two index writes, not one.
A hot partition can be split automatically. A hot item cannot, and ours failed on transaction conflict long before approaching any capacity limit.
DynamoDB does not do order statistics. Bound the cost and return a range, or add a sorted-set store.

What we still do not know

What any of this costs in production, because there is no production stack. Every measurement is DEV.
Whether a board's write capacity is ever the binding limit. The only contention we have reproduced is transaction conflict on one item, with zero throttles reported.
The metadata row's real consumed capacity. DynamoDB emits no per-item capacity metric; the 2,000 units and 29.1% share in our artifact are inferred from a table-level figure.
How the rank-estimate interval behaves on a genuinely crowded board. We have a 2,501-row local run and nothing at scale.
Whether our measurements survive a longer run. AWS's warning applies to us: "when benchmarking DynamoDB, don't assume that what you see in the first 5 minutes is what you'll see after an hour!"

What we would do next

Measure consumed write capacity on DEV against a populated board, and replace the arithmetic above with a real per-submission figure.
Measure rank-estimate interval widths on a board of 100,000 or more, and set the display policy from the p99 width rather than intuition.
Re-run the propagation harness with response caching on, and measure edge freshness separately rather than inferring it.
Re-check the projection decision with real storage numbers, now that I know it probably saves nothing on write.

Evidence

Our configuration, read on 2026-09-18 from the online-leaderboard repository:

Tables, index, billing mode, TTL, point-in-time recovery, deletion protection, and the absence of any StreamSpecification or event source mapping: template.yaml.
Sort-key format, SCORE_CEILING of 1,000,000,000,000, pad width 13: src/lib/score-key-builder.js, src/constants/score-limits.js.
The four rankKey values in Figure 2 and the bucket table: produced by calling the shipped buildRankKey and scoreToHistogramBucket on 2026-09-18. Player ids are invented.
Histogram, 128 buckets, 2,000-entry cap, range-not-point results: src/lib/rank-percentile-calculator.js, docs/design/rank-percentile.md, ADR-0013.
Write path, conditions, idempotency records, three-item transaction: src/handlers/submit-score-handler.js, src/lib/score-update-command-builder.js, src/lib/leaderboard-metadata-counter.js, src/lib/idempotency-record.js.
Read path, ScanIndexForward, Limit, cursor: src/handlers/query-leaderboard-handler.js, src/lib/leaderboard-page-query-builder.js.
Default fan-out of three periods, one variant: src/config/leaderboard-fanout-config.js.
Two COUNT queries per board and the rankDelta: false opt-out: contracts/README.md.
The reconciler is an offline two-pass recount: src/lib/leaderboard-metadata-reconciler.js, tools/reconcile-leaderboard-metadata.mjs.
No production stack: README.md, "Environments" ("The production deployment path exists, but the stack does not").
Dates from git log on template.yaml, docs/adr and src/lib/score-key-builder.js.

Measurements: propagation percentiles, 300 samples, 0 errors, DEV us-east-1, 2026-08-18T19:38:19.881Z, commit 7e8a1cd. benchmarks/20260818T193819.881Z/report.md and config.json. The earlier same-day run behind the SLO: benchmarks/20260818T094113.895Z/, cited in ADR-0012. Concurrency outcomes, 6,868 peak-minute write units and zero throttles, DEV, 2026-08-19T01:35:30Z, commit e3fb772. benchmarks/LFL26LEADBORD-035/metadata-counter-dev.json. The 2,501-row rank run and its stated limits: reports/LFL26LEADBORD-036/local-route-benchmark.json.

Derived, not measured: every item-write count above and in Figure 4; the ~17 write request units and ~$0.32 a month; the histogram bucket boundaries; the 2,000 units and 29.1% share attributed to the metadata row, which the artifact itself labels inferred because DynamoDB emits table-level rather than item-level capacity metrics.

Not measured at all: production anything; per-board write capacity; rank-estimate interval widths above 2,501 rows; edge freshness.

External sources: every quotation is linked inline and recorded with its access date, section reference and load result in artifacts/blog/_research/dynamodb-foundations.md. Sources that would not load (including four Developer Guide URLs that now redirect to the guide's front page) are listed there rather than cited.

We store every score backwards so DynamoDB never has to sort