How to index monotonically increasing data in a table?

Question

I have a table with a monotonically increasing field that I want to put into an index. However, the best practices guide says to not put monotonically increasing data into a non-interleaved index. When I try putting the data into an interleaved index, I can't interleave an index in its parent table.

In other words, I want the Cloud Spanner equivalent of this MySQL schema.

CREATE TABLE `my_table` (
  'id' bigint(20) unsigned NOT NULL,
  'monotonically_increasing' int(10) unsigned DEFAULT '0',
  PRIMARY KEY ('id'),
  KEY 'index_name' ('monotonically_increasing')
)

Answer 1

It really depends the rate you'll be writing monotonically increasing/decreasing values.

Small write loads

I don't know the exact range of writes per second a Spanner server can handle before you'll hotspot (and it depends on your data), but if you are writing < 500 rows per second you should be okay with this pattern. It's only an issue if your write load is higher than a single Spanner server can comfortably handle by itself.

Large write loads

If your write rate is larger, or relatively unbounded (e.g. scales up with your systems/sites popularity), then you'll need to look alternatives. These alternatives really depend on your exact use case to work out which trade-offs you're willing to take.

One generic approach is to manually shard the index. Let's say for example you know your peak write load will be 1740 inserts per second. Using the approx 500 writes per server number from before, we would be able to avoid hotspotting if we could shard this load over 4 Spanner servers (435 writes/second each).

Using the INT64 type in Cloud Spanner allows for a maximum value of 9,223,372,036,854,775,808. One example way to shard is us by adding random(0,3)*1,000,000,000,000,000,000 to each value. This will split the index key range into 4 ranges that can be served by 4 Spanner servers. The down-side is you'll need to do 4 queries and merge the results on the client side after masking out x,000,000,000,000,000,000.

Note: Interleaving is when data/indexes from one table are interleaved with date from another table. You cannot interleave with only one table.