Cloud Spanner — The impact of data locality on query latency | by Pablo Arrojo | Google Cloud – Community | Oct, 2025

Data locality is an important criteria when designing your schema. Ensuring related data (tables or rows) being stored together avoids distributed operations which could improve performance. For example, reducing remote calls in queries or avoiding 2PC in writes.

However, in some scenarios data colocation could be a performance caveat.

To begin, I’ll demonstrate the advantages of data locality through a straightforward test.

Small data set

The below query look for ten consecutive Orders’s Ids:

select sum(amount) from Orders where id in unnest(@id);

Stats:

spanner> select TEXT_FINGERPRINT,
-> AVG_LATENCY_SECONDS,AVG_CPU_SECONDS,
-> AVG_ROWS,
-> AVG_BYTES,
-> AVG_ROWS_SCANNED,
-> AVG_REMOTE_SERVER_CALLS 
-> from spanner_sys.query_stats_top_minute where text_fingerprint=7725316002780049317 order by interval_end desc;
+---------------------+---------------------+-----------------+----------+-----------+------------------+-------------------------+
| TEXT_FINGERPRINT    | AVG_LATENCY_SECONDS | AVG_CPU_SECONDS | AVG_ROWS | AVG_BYTES | AVG_ROWS_SCANNED | AVG_REMOTE_SERVER_CALLS |
+---------------------+---------------------+-----------------+----------+-----------+------------------+-------------------------+
| 7725316002780049317 | 0.000825            | 0.000400        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
| 7725316002780049317 | 0.000960            | 0.000407        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
| 7725316002780049317 | 0.000868            | 0.000397        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
| 7725316002780049317 | 0.000899            | 0.000404        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
| 7725316002780049317 | 0.000894            | 0.000454        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
+---------------------+---------------------+-----------------+----------+-----------+------------------+-------------------------+

The query completed in approximately 1ms. We confirmed that all rows were read from a single split by checking that avg_remote_server_calls was zero.

To avoid data locality, I’ve established ten split points, each corresponding to an Order ID that will be searched.

spanner> SELECT
->   table_name,SPLIT_KEY
-> FROM
->   SPANNER_SYS.USER_SPLIT_POINTS where table_name='Orders';
+------------+------------------------------------------------------------------------------------------------------------------+
| table_name | SPLIT_KEY                                                                                                        |
+------------+------------------------------------------------------------------------------------------------------------------+
| Orders     | Orders(order1)                                                                                                   |
| Orders     | Orders(order10)                                                                                                  |
| Orders     | Orders(order2)                                                                                                   |
| Orders     | Orders(order3)                                                                                                   |
| Orders     | Orders(order4)                                                                                                   |
| Orders     | Orders(order5)                                                                                                   |
| Orders     | Orders(order6)                                                                                                   |
| Orders     | Orders(order7)                                                                                                   |
| Orders     | Orders(order8)                                                                                                   |
| Orders     | Orders(order9)                                                                                                   |
+---------------------+---------------------+-----------------+----------+-----------+------------------+-------------------------+

New stats:

spanner> select TEXT_FINGERPRINT,
-> AVG_LATENCY_SECONDS,
-> AVG_CPU_SECONDS,
-> AVG_ROWS,
-> AVG_BYTES,
-> AVG_ROWS_SCANNED,
-> AVG_REMOTE_SERVER_CALLS from spanner_sys.query_stats_top_minute where text_fingerprint=7725316002780049317 order by interval_end desc;
+---------------------+---------------------+-----------------+----------+-----------+------------------+-------------------------+
| TEXT_FINGERPRINT    | AVG_LATENCY_SECONDS | AVG_CPU_SECONDS | AVG_ROWS | AVG_BYTES | AVG_ROWS_SCANNED | AVG_REMOTE_SERVER_CALLS |
+---------------------+---------------------+-----------------+----------+-----------+------------------+-------------------------+
| 7725316002780049317 | 0.002910            | 0.004149        | 1.000000 | 10.000000 | 10.000000        | 10.000000               |
| 7725316002780049317 | 0.003005            | 0.004142        | 1.000000 | 10.000000 | 10.000000        | 10.000000               |
| 7725316002780049317 | 0.003157            | 0.004308        | 1.000000 | 10.000000 | 10.000000        | 10.000000               |
| 7725316002780049317 | 0.000825            | 0.000400        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
| 7725316002780049317 | 0.000960            | 0.000407        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
| 7725316002780049317 | 0.000868            | 0.000397        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
| 7725316002780049317 | 0.000899            | 0.000404        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
| 7725316002780049317 | 0.000894            | 0.000454        | 1.000000 | 10.000000 | 10.000000        | 0.000000                |
+---------------------+---------------------+-----------------+----------+-----------+------------------+-------------------------+

This time the query’s execution time increased to 3ms, which is three times longer than before. We see that the number of avg_remote_server_calls is 10, aligning to the number of split points. Consequently, the number of cpu seconds is 10x higher. This makes sense as the query performs ten short seek operations per server against one short search in a single machine.

Large data set

The previous query scanned just a few rows. Let’s check how it works processing a larger number of rows.

For this test, I will create a secondary index to perform a time range scan on the created_at field.

Important: Indexing columns with monotonically increasing values is an anti pattern as it can lead to write hotspots. This approach is used here solely for testing the reading of consecutive rows.

CREATE INDEX idx_orders_ts_colocate
ON
Orders(created_at) STORING (amount);

New query:

SELECT
SUM(amount) AS total_amount
FROM
Orders@{FORCE_INDEX=idx_orders_ts_colocate}
WHERE
created_at > @ts1
AND created_at < @ts2;

Stats:

spanner> select TEXT_FINGERPRINT,
-> AVG_LATENCY_SECONDS,
-> SPANNER_SYS.DISTRIBUTION_PERCENTILE(latency_distribution[OFFSET(0)], 99.0) AS P99, 
-> AVG_CPU_SECONDS,
-> AVG_ROWS,
-> AVG_BYTES,
-> AVG_ROWS_SCANNED,
-> AVG_REMOTE_SERVER_CALLS 
-> from spanner_sys.query_stats_top_hour where text_fingerprint=8666556102668875839 order by interval_end desc;
+---------------------+---------------------+----------+-----------------+----------+-----------+------------------+-------------------------+
| TEXT_FINGERPRINT    | AVG_LATENCY_SECONDS | P99      | AVG_CPU_SECONDS | AVG_ROWS | AVG_BYTES | AVG_ROWS_SCANNED | AVG_REMOTE_SERVER_CALLS |
+---------------------+---------------------+----------+-----------------+----------+-----------+------------------+-------------------------+
| 8666556102668875839 | 0.097962            | 0.260080 | 0.097394        | 1.000000 | 12.000000 | 196993.127546    | 0.000668                |
+---------------------+---------------------+----------+----------------

The number of remote calls is zero, confirming the locality of scanned rows. Query reads around 195k rows in a single split with an average latency of 100ms and a P99 of 260ms.

Now, I will implement schema changes, beginning with the addition of a sharId column:

alter table Orders add column shardId INT64 NOT NULL AS (MOD(FARM_FINGERPRINT(CONCAT(id, STRING(created_at))),10));

Then I create the index below, using shardId as prefix:

CREATE INDEX idx_orders_ts
ON
Orders(shardId,created_at DESC) STORING (amount);

The idea to use shardId as a prefix is to simplify the creation of split points.

I’ve created a split point for each shardId:

spanner> SELECT
->   table_name,SPLIT_KEY
->  FROM
->   SPANNER_SYS.USER_SPLIT_POINTS where index_name='idx_orders_ts';
+------------+-------------------------------------------------------------------------------------------------------------------+
| table_name | SPLIT_KEY                                                                                                         |
+------------+-------------------------------------------------------------------------------------------------------------------+
| Orders     | Index: idx_orders_ts on Orders, Index Key: (-9,2025-09-24T15:27:50.61328369-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (-8,2025-09-24T15:27:50.573088874-07:00), Primary Table Key: () |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (-7,2025-09-24T15:27:50.623789685-07:00), Primary Table Key: () |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (-6,2025-09-24T15:27:50.632804529-07:00), Primary Table Key: () |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (-5,2025-09-24T15:27:50.574173159-07:00), Primary Table Key: () |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (-4,2025-09-24T15:27:50.574234019-07:00), Primary Table Key: () |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (-3,2025-09-24T15:27:50.634698189-07:00), Primary Table Key: () |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (-2,2025-09-24T15:27:50.573102622-07:00), Primary Table Key: () |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (-1,2025-09-24T15:27:50.573044872-07:00), Primary Table Key: () |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (0,2025-09-24T15:27:50.605654692-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (1,2025-09-24T15:27:50.573044872-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (2,2025-09-24T15:27:50.573102622-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (3,2025-09-24T15:27:50.634698189-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (4,2025-09-24T15:27:50.574234019-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (5,2025-09-24T15:27:50.574173159-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (6,2025-09-24T15:27:50.632804529-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (7,2025-09-24T15:27:50.623789685-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (8,2025-09-24T15:27:50.573088874-07:00), Primary Table Key: ()  |
| Orders     | Index: idx_orders_ts on Orders, Index Key: (9,2025-09-24T15:27:50.61328369-07:00), Primary Table Key: ()   |
+------------+-------------------------------------------------------------------------------------------------------------------+

Adapted query using the new index:

SELECT
SUM(amount) AS total_amount
FROM
Orders@{FORCE_INDEX=idx_orders_ts}
WHERE
shardId BETWEEN -9
AND 9
AND created_at > @ts1
AND created_at < @ts2

Stats:

spanner> select TEXT_FINGERPRINT,
-> AVG_LATENCY_SECONDS,
-> SPANNER_SYS.DISTRIBUTION_PERCENTILE(latency_distribution[OFFSET(0)], 99.0) AS P99, 
-> AVG_CPU_SECONDS,
-> AVG_ROWS,
-> AVG_BYTES,
-> AVG_ROWS_SCANNED,
-> AVG_REMOTE_SERVER_CALLS from spanner_sys.query_stats_top_hour where text_fingerprint=2717461593638314418 order by interval_end desc;
+---------------------+---------------------+----------+-----------------+----------+-----------+------------------+-------------------------+
| TEXT_FINGERPRINT    | AVG_LATENCY_SECONDS | P99      | AVG_CPU_SECONDS | AVG_ROWS | AVG_BYTES | AVG_ROWS_SCANNED | AVG_REMOTE_SERVER_CALLS |
+---------------------+---------------------+----------+-----------------+----------+-----------+------------------+-------------------------+
| 2717461593638314418 | 0.015788            | 0.064290 | 0.126074        | 1.000000 | 11.998082 | 196162.884412    | 19.000000               |
+---------------------+---------------------+----------+-----------------+----------+-----------+------------------+-------------------------+

Query is scanning the same number of rows per execution (around 195K rows), reading across 19 splits (according to the number of split points created). However this time the average query latency is around 15ms and P99 is 64ms, we obtained a reduction of latency of 10x compared to reading rows in a single split.

Why this significant latency improvement?

Scanning a large number of rows in a single split means a large sequential read operation. However, when data is distributed across multiple remote servers we’re splitting one single and expensive read into multiple short read operations which run in parallel fashion.

Press enter or click to view image in full size

By other hand, an expected trade-off here is that the number of cpu seconds per query is higher (an increase of around 25% in this case). This is because in addition to the larger number of machines involved in the query, we’ve added a new seek condition (shardId between -9 and 9).

Conclusion

• Data locality is ideal for accessing small datasets because it avoids the overhead associated with coordinating remote servers.
• When scanning a large number of rows over a table/index, designing your schema to distribute data across multiple splits could lead to a query improvement. This approach leverages parallel processing to enhance query latency.
• Keep in mind that a larger number of remote servers involved in our queries lead to higher instance cpu usage.