title: DynamoDB GSI vs LSI for Numerical fields
body: <p>I am going through a DynamoDb udemy course and came across the following example
  for leaderboard design,</p>
  <p><a href="https://i.stack.imgur.com/DVw55.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DVw55.png" alt="Base Table" /></a></p>
  <p>Now, to easily fetch the top scores (or the top 10 in leaderboard),
  the course goes on to do the following</p>
  <p><strong>GSI</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>PK</th>
  <th>SK</th>
  <th>player</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>top_score</td>
  <td>150</td>
  <td>player#2</td>
  </tr>
  <tr>
  <td>top_score</td>
  <td>200</td>
  <td>player#1</td>
  </tr>
  <tr>
  <td>top_score</td>
  <td>250</td>
  <td>player#3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Now, this works quite well for the instructor in the course,
  however, if player#2 now scores 50 more, then the primary key(PK + SK)
  wont be unique (top_score + 200) will point to two records.</p>
  <p>I then think, that numerical fields will never make sense in a
  GSI, unless they are IDs.</p>
  <p>Even if we make the score field unique by appending player_id along
  with score, that will need the score field to be string, rather than
  numerical, and then lexicographical order will be followed and not the
  numerical ordering.</p>
  <p>From AWS Dynamo docs regarding <a href="https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-indexes-general.html#:%7E:text=In%20general%2C%20you%20should%20use%20global%20secondary%20indexes%20rather%20than%20local%20secondary%20indexes" rel="nofollow noreferrer">GSI vs LSI in general</a> :</p>
  <blockquote>
  <p>In general, you should use global secondary indexes rather than local secondary indexes. The exception is when you need strong consistency in your query results, which a local secondary index can provide but a global secondary index cannot (global secondary index queries only support eventual consistency).</p>
  </blockquote>
  <p>I think this is a clear case of using LSI (even
  though the problem I just described in not related to consistency),
  use LSI on the total_score attribute.</p>
  <p>Am I right, or is there a way to actually use GSI for this ?</p>
  <p><strong>Edit:</strong></p>
  <p>The only thing I can think of is to pad sufficient 0s in scores,
  and append player_id at the end</p>
  <p>Like this,</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>PK</th>
  <th>SK</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>top_score</td>
  <td>000150#2</td>
  </tr>
  <tr>
  <td>top_score</td>
  <td>000200#1</td>
  </tr>
  <tr>
  <td>top_score</td>
  <td>000250#3</td>
  </tr>
  </tbody>
  </table>
  </div>