The magic of Spanner appears to be based on the idea of a global monontonically increasing clock as described in https://cloud.google.com/spanner/docs/true-time-external-consistency. I’m interested in understanding specifically how this relates to indexes.
If I have a table
| Stream Id | Category | Timestamp |
… where the timestamp is created as a commit timestamp, and with a secondary index (Category, Timestamp DESC):
Is it possible that someone querying this index can see a timestamp inserted “out of order”, e.g. is it possible that an index is queried returning timestamps with the milliseconds as 100, 110, 120 but then when queried again, has an older value appear e.g. 100, 110, 115, 120.
Additionally, if the index is read once containing timestamp 100 for the most recent entry, is it possible that later on another entry is added with exactly the timestamp 100?
Indexes are implemented as tables under the hood, so they have the same consistency guarantees as tables. Indexes are also updated synchronously when the referenced table is updated, so this inconsistent view of indexes should not occur.
Commit timestamp values are not guaranteed to be unique. Transactions that write to non-overlapping sets of fields might have the same timestamp. Transactions that write to overlapping sets of fields have unique timestamps.
For more information, please reference the following documentation: https://cloud.google.com/spanner/docs/commit-timestamp