5

Google Cloud Spanner recommends against using monotonically increasing integers for primary keys. This makes a perfect sense for top-level tables as it would create hotspots. But what about interleaved tables? They are going to be stored on the same node anyway. They are also kept in a sorted order so using UUID or some other random ID seems suboptimal, especially if they are going to be retrieved in the sorted order as well.

Is reading the max id and storing maxId + 1 for a new row the right solution here?

Dan McGrath
  • 37,828
  • 10
  • 90
  • 123
Jakub Vrána
  • 533
  • 2
  • 14

1 Answers1

3

The interleaved child tables rows are prefixed with the parent table's key, so it is okay to think of them as non-sequential keys as long the parent key is non-sequential. In this situation it should generally be okay since as you note, this entire key range will be served by one Span Server. If you are concerned about hot spotting a single Span server in this situation it would be best to not interleave the table.

It should also be noted that it's really monotonically increasing or decreasing keys that are recommended against, as they have the same effect, just at opposite sides of the range.

Dan McGrath
  • 37,828
  • 10
  • 90
  • 123
  • Thanks for the answer. What's the best practice to use the increasing numbers in the interleaved tables? Is it simple: max = read("SELECT MAX(SubId) FROM SubTable WHERE ParentId = ..."); write("INSERT INTO SubTable (SubId, ...) VALUES (" + (max + 1) + ", ...)") ? – Jakub Vrána Feb 16 '17 at 18:54
  • 1
    Generally it is still a good idea to avoid and use UUIDs with a column for sequential ids if needed. There are many dangers that lurk there, such as making sure you do it in a transaction, what happens if the most recent (and max) value is deleted - can you reuse it or not?. It's probably better to use a another table to store the counter and make sure you have appropriate transactions. – Dan McGrath Feb 16 '17 at 19:17