Schema design for Cloud Spanner: interleaving, keys, and transactions. If you build your database tables badly, your application will be slow, expensive, and might even give wrong answers under pressure. This concept matters for your PCDE exam because Google explicitly tests whether you can design a schema that makes Cloud Spanner’s distributed architecture work for you instead of against you.
Jump to a section
A simple way to picture Schema Design for Cloud Spanner: Interleaving, Keys, and Transactions
You are running a large charity that sends care packages. You need to track every package and every item inside it. How do you organise your big filing cabinet so you can find any single item in seconds, even with thousands of packages?
Imagine your filing cabinet has many drawers. Each drawer represents a 'table' in your database. One drawer is labelled 'Packages', and it holds a single index card for every package you send. Each card has a unique 'Package ID' number, like 'PKG-1001', written at the top. This is the 'primary key' – it is the single, unique thing that identifies that package.
Now, inside each package is a list of items: a blanket, a toothbrush, some soup. You could have a second drawer called 'Items' with a card for every blanket and toothbrush from every package. But finding all items for package PKG-1001 would be slow – you would have to flip through every single item card in the entire drawer.
This is where 'Interleaving' comes in. Instead of a separate 'Items' drawer, you physically insert the item cards for PKG-1001 directly behind the package card in the 'Packages' drawer. The items are 'children' of the 'parent' package. When you pull out the PKG-1001 package card, all its item cards are right there, tucked behind it. This means you can locate every item for a package in one go, without searching the whole cabinet. This is precisely how Cloud Spanner stores data: it 'interleaves' child rows physically next to their parent row on the same storage node. The 'primary key' of the child table includes the parent's key (PKG-1001) plus its own unique ID (Item-01). This design makes reading a package and all its items exceptionally fast. 'Transactions' are like the rule that when you update a package card, you must also update all its attached item cards at the same time – no half-finished updates are ever visible.
Cloud Spanner is a managed database service from Google that combines the scalability of a NoSQL system with the consistency of a traditional relational database. To make it work fast at massive scale, you have to design your schema in a very specific way. This chapter covers three interlocking ideas: primary keys (how you label each row), interleaving (how you physically store related rows together), and transactions (how you guarantee updates are atomic and correct).
Let us start with primary keys. A primary key is a column (or set of columns) that uniquely identifies each row in a table. In Cloud Spanner, the primary key is not just a label – it determines where the row is physically stored on the servers. Rows are sorted and distributed based on the first column of the primary key. If you use a monotonically increasing value like an auto-incrementing integer (1, 2, 3…), all new rows are written to the same server node, causing a 'hotspot' – one server does all the work while the rest sit idle. This kills performance. A better design uses a 'universally unique identifier' (UUID) or a 'hash' of a business key (like a user ID) to spread writes evenly across servers. For the exam, remember that the primary key must not be a sequential number; it should be a 'key that spreads the workload'.
Next is interleaving. In a traditional relational database, you create 'foreign keys' to link data in separate tables. For example, an 'Orders' table has a link to a 'Customers' table. To find all orders for a customer, the database searches through the Orders table – this is a separate read. Cloud Spanner introduces a different approach called 'interleaving'. You define a parent-child relationship between two tables. The child table’s primary key must start with the parent’s primary key. When you interleave tables, Cloud Spanner physically stores the child rows directly next to their parent row on the same server node. This means that reading a parent and all its children requires only a single seek operation, not multiple searches across different servers. This is perfect for 'strongly owned' relationships: a user has many photos, an album has many tracks, a customer has many orders. If you try to interleave tables that do not have a strict one-to-many parent-child relationship, you will cause storage and performance problems. The exam tests your ability to recognise which relationships are suitable for interleaving and which are not.
Finally, transactions. A transaction is a group of database operations (read or write) that must be executed as a single unit: either all of them succeed ('commit'), or none of them take effect ('rollback'). Cloud Spanner provides strong consistency: you can read the most up-to-date data from any server. Transactions ensure that if you move money from account A to account B, the debit and credit happen together. If the system crashes midway, both operations are undone. Cloud Spanner implements 'optimistic locking' for read-write transactions: it reads the data, checks if anything changed during the read, and only commits if the data is unchanged. If another transaction changed the data, your transaction is retried automatically. There are also 'read-only transactions' – these are faster and do not block writes. For the exam, know the difference between read-write and read-only transactions, and understand that long-running read-write transactions risk 'aborts' due to contention.
Why does all this matter? Cloud Spanner is designed for global, high-traffic applications. Poor schema design (like sequential keys or missing interleaving) can make your queries 100x slower and increase your bill for computing resources. The PCDE exam expects you to design schemas that embrace Spanner’s strengths: distributable keys, interleaved parent-child tables for common access patterns, and appropriate transaction types for the operations you need to perform.
Identify the parent-child relationships
Look at your application data model. Find strong 'contains' relationships, such as a user 'has' multiple orders, or an album 'contains' many tracks. These are candidates for interleaving. Avoid interleaving relationships that are many-to-many or shared (like a product that belongs to many categories).
Design the primary key for the parent table
Choose a primary key that will spread writes evenly. For the parent table, use a UUID (like a 36-character string) or a hash of a natural business key. Do NOT use an auto-incrementing integer. This ensures that new parent rows are written to different server nodes across the Spanner cluster.
Design the primary key for the child table
The child table's primary key must start with the parent's primary key columns. For example, if the 'Users' table has primary key (UserId STRING), the 'Orders' child table must have primary key (UserId STRING, OrderId STRING). This is mandatory for interleaving. The second part of the key (OrderId) should also be a UUID to avoid hotspots within a parent.
Write the CREATE TABLE statement with INTERLEAVE clause
Write the DDL. For the child table, include 'INTERLEAVE IN PARENT Users ON DELETE CASCADE'. This tells Spanner to physically store child rows next to the parent row in storage. The ON DELETE CASCADE means if you delete a user, all their orders are automatically deleted. Now execute this DDL to create your schema.
Implement the correct transaction type for each operation
For reading a user's orders, use a read-only transaction by starting a new read-only transaction context in your code (e.g., using the Spanner client library). For inserting a new order, use a read-write transaction: begin a read-write transaction, check for conflicts (e.g., that the user exists), run the insert, and commit. Handle any 'Aborted' errors by retrying the entire transaction loop.
Test for hotspotting and latency
Load-test your design. Monitor Spanner's key statistics to see if any single node is handling a disproportionate amount of writes. If you see a hotspot, re-evaluate your primary key selection. Also test the read latency of common queries (fetching a parent and all children) to verify that interleaving is providing the expected performance benefit.
An IT professional designing a schema for a global music streaming service uses these principles daily. Consider a 'Users' table and a 'PlaylistTracks' table. Each user has many playlists, and each playlist has many tracks. The naive approach would be to use a single 'Tracks' table with a foreign key to 'Users' and another to 'Playlists'. But this means to load a user’s home screen (showing their 20 most recent tracks), the system must perform a join across multiple tables, potentially on different servers, resulting in high latency.
Using Cloud Spanner principles, the engineer first designs the primary key for 'Users' as a UUID (e.g., 'user_abc123') to spread writes evenly. Then, they create an interleaved 'Playlists' table whose primary key is the UserID plus a PlaylistID (e.g., 'user_abc123', 'pl_xyz789'). The Playlists rows are stored physically right after the User row. Next, they create an interleaved 'Tracks' table under 'Playlists', with a primary key of UserID, PlaylistID, and TrackID (e.g., 'user_abc123', 'pl_xyz789', 'trk_001'). Now, all tracks for a user are stored contiguously.
When a user opens the app, the engineer issues a single read on the User row, and Cloud Spanner can sequentially read all interleaved Playlist and Track rows in one go, because they are all stored together on one server. This is a 'key range scan' and is extremely efficient. The read-only transaction can be used here, because the backend is not writing anything – just reading the user’s library. The response time is in milliseconds, regardless of whether the user is in Tokyo or London.
What about updates? When a user adds a track to a playlist, the engineer uses a read-write transaction. The transaction reads the current state (to verify the track is not already there), inserts the new row into the interleaved 'Tracks' table, and commits. If two users simultaneously try to add the same track to the same playlist, one transaction will abort (because the data changed during the read), and the system will retry it automatically. The engineer must code the application to handle these retries gracefully – usually with a retry loop with exponential backoff.
Another real-world consideration is hotspotting. If the engineer used a sequential 'Timestamp' as the primary key for the 'Tracks' table (e.g., '2025-01-01-00-00-01'), every new track inserted at the same moment would land on the same server node, causing a hotspot. Instead, the engineer uses a UUID for the TrackID, ensuring that writes are distributed across the entire Spanner cluster.
The takeaway: a database engineer does not just write SQL. They think about how data is accessed (read patterns), how it is written (write patterns), and how to lay it out physically so that the most common operations are as fast as possible. Schema design is the single highest-leverage activity you can do to control performance and cost in Cloud Spanner.
The PCDE exam (objective 4.3) directly tests your understanding of how to design a schema for Cloud Spanner. Expect scenario-based multiple-choice questions where you are given a business requirement (e.g., 'a social media app that shows posts and all comments for a user') and must choose the correct schema design from four options. The exam loves to test whether you can spot a bad primary key or a missing interleaved table.
Key exam topics you must know:
Primary key design: The exam always includes a question about avoiding hotspots. A primary key that is an auto-incrementing integer (like an INT64 with GENERATE_SEQUENCE) is almost always a wrong answer. The correct answer uses a UUID or a hashed key. The exam calls this 'distributing the keyspace'. You must recognise that sequential keys lead to 'hotspotting' on one node.
Interleaving criteria: The exam will test when to interleave and when not to. The rule is: interleave only when there is a 'strong parent-child relationship' and the child table’s primary key starts with the parent’s primary key. Common traps: interleaving tables that are only loosely related (e.g., 'Customers' and 'Addresses' where the address is sometimes shared by multiple customers) is wrong. Interleaving is for 'contains' relationships, not 'references'.
Transaction types: Questions will ask about guaranteed consistency. You need to know that 'read-write transactions' provide 'serialisable' isolation (the highest level) and that 'read-only transactions' are for reading a snapshot of the data. A trap asks you to use a read-only transaction for a write operation – that is wrong. Another trap asks you to use a read-write transaction to simply read a single row – that is also wrong because read-only transactions are faster and do not block other writers.
Locks and retries: The exam expects you to understand that read-write transactions acquire locks on the rows they read. If a transaction reads many rows, it holds locks for a longer time, increasing the chance of deadlocks or aborts. The correct pattern is to keep read-write transactions short. If a transaction aborts, the client library automatically retries, but your application must be idempotent (safe to run multiple times).
Schema and DDL: You might be asked to write a CREATE TABLE statement with an INTERLEAVE clause. For example, 'CREATE TABLE Playlists (UserId STRING(36), PlaylistId STRING(36), ...) PRIMARY KEY (UserId, PlaylistId), INTERLEAVE IN PARENT Users ON DELETE CASCADE'. The ON DELETE CASCADE part is important – it means deleting a parent row automatically deletes all child rows. The exam tests that you know this clause exists.
Data type choice: INT64, STRING, TIMESTAMP. The exam suggests using STRING for IDs because they are stackable with other STRING keys in the parent-child relationship. INT64 keys are also fine, but STRING helps avoid confusion with auto-increment traps.
Traps to watch for:
The 'foreign key' trap: A question will propose using a traditional foreign key constraint (like in MySQL) for a parent-child relationship. This is wrong for Spanner – use interleaving instead. Foreign keys in Spanner are not recommended for common access patterns because they do not physically colocate data.
The 'too many interleaved levels' trap: Spanner supports up to 7 levels of interleaving. A question might ask you to interleave 10 levels deep – that is invalid.
The 'no primary key' trap: Every Spanner table must have a primary key. A missing primary key is a syntax error.
Memorise these definitions:
Interleaving: Physical colocation of parent and child rows on the same server node based on a shared prefix of the primary key.
Hotspotting: Concentrated write traffic on a single server node due to sequential primary keys.
Optimistic locking: A transaction strategy that reads data without locks, checks for conflicts at commit time, and retries if a conflict is detected.
The primary key in Cloud Spanner must distribute writes across the cluster; never use sequential integers like auto-incrementing IDs.
Interleaving physically colocates child rows with their parent row on the same server node for fast key-range scans.
Only interleave tables when there is a strict one-to-many parent-child relationship and the child's primary key begins with the parent's primary key.
Read-write transactions provide serialisable isolation but should be kept short to avoid aborts due to contention.
Read-only transactions are faster and do not block writes; use them for all pure-read operations.
Up to 7 levels of interleaving are allowed in Cloud Spanner; exceeding this limit is a schema design error.
Deleted parent rows cascade to interleaved child rows only if you specify ON DELETE CASCADE in the interleave clause.
Always use a UUID or a hashed value for the leading primary key column to prevent hotspotting during writes.
These come up on the exam all the time. Here's how to tell them apart.
Interleaved Tables
Physically stores child rows next to parent row on the same server node.
Requires child primary key to start with parent primary key.
Provides extremely fast reads for parent-child queries.
Foreign Key Constraints
Only a logical link; no physical colocation.
Does not require any primary key structure relationship.
Queries require a join operation that may span multiple nodes.
Read-Write Transaction
Acquires locks on rows it reads; can write data.
Provides serialisable isolation (highest consistency level).
More expensive; can abort if contention is detected.
Read-Only Transaction
Reads a consistent snapshot without locks.
Provides strong consistency but cannot write.
Faster and cheaper; never aborts.
Sequential Primary Key (e.g., auto-increment INT64)
Causes hotspotting on a single node during writes.
Easy to implement and read; natural ordering.
Leads to severe write throughput bottlenecks.
UUID Primary Key (e.g., STRING(36))
Spreads writes uniformly across all nodes.
Harder for humans to read; no natural order.
Eliminates hotspotting; scales write throughput linearly.
Interleaving for a One-to-Many Relationship
Correct use case; parent has many children.
Child belongs to exactly one parent (strong ownership).
Data is physically colocated, optimising queries.
Interleaving for a Many-to-Many Relationship
Incorrect use case; interleaving is not designed for this.
Child belongs to multiple parents, causing duplication and anomalies.
Data cannot be cleanly colocated; performance degrades.
Mistake
Interleaving is just a fancy way of creating a foreign key that automatically joins tables.
Correct
Interleaving physically stores child rows directly next to their parent row on the same server node. This is fundamentally different from a foreign key which only maintains a logical link and does not affect physical storage. Interleaving dramatically improves read performance for parent-child queries, but it also imposes strict rules about primary key structure.
People are used to relational databases like MySQL where foreign keys are the standard way to relate tables. The concept of physically colocating data is foreign to many beginners.
Mistake
You can use any column as the primary key, as long as it is unique.
Correct
The primary key in Cloud Spanner determines the physical distribution of data across servers. Using a sequential value (like an auto-incrementing integer) causes hotspotting and poor write performance. The primary key should be chosen to spread writes uniformly across the keyspace, such as a UUID or a hashed customer ID.
In many traditional databases, auto-incrementing primary keys are the default and are highly efficient. Beginners naturally carry this assumption to Spanner, not realising the distributed architecture changes the rules.
Mistake
Read-only transactions are not necessary because you can just use a read-write transaction to read data safely.
Correct
Read-only transactions do not acquire locks and do not block other writers, making them much faster and more scalable for pure read operations. Using a read-write transaction to read data is wasteful and can cause unnecessary contention and aborts.
Beginners often think 'transaction' means 'write operation' and do not consider the trade-offs between different transaction types. They default to read-write because it seems like the safest choice.
Mistake
If a transaction aborts, the database automatically fixes the data and you do not need to worry about it.
Correct
When a transaction aborts, all its changes are rolled back. The client library or application must retry the entire transaction. If the application is not written to handle retries (e.g., it does not re-read the data), the operation may fail permanently.
Many beginners assume databases are 'magic' and handle all failures invisibly. In reality, distributed systems like Spanner require application-level resilience.
Mistake
Interleaving works well for any table relationship where one table references another.
Correct
Interleaving is explicitly designed for 'parent-child' relationships where a child belongs to exactly one parent and is always queried together with that parent. It is not suitable for many-to-many relationships or for cases where a child is shared between multiple parents.
People think of the word 'interleaving' and assume it is a general performance optimisation. They do not realise it has a very specific use case and can actually harm performance if used incorrectly.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
You will create a hotspot. All new rows will be written to the same tablet (a unit of storage), causing a 'hotspot' on one server node. This severely limits write throughput and increases latency. Use a UUID or a hash instead.
No. Interleaving is only for strict parent-child relationships where the child belongs to exactly one parent. If you interleave unrelated tables, you lose the benefit of colocation and may even cause storage inefficiencies by splitting data across nodes in a suboptimal way.
A read-only transaction reads a consistent snapshot of the data and does not acquire any locks, so other transactions can continue writing. A read-write transaction can read and write data, acquires locks on the rows it reads, and provides serialisable isolation. Only use read-write when you need to write data.
Cloud Spanner supports up to 7 levels of interleaving (a parent table, its child, its grandchild, etc.). If you need more than 7 levels, you must restructure your schema, for example, by denormalising or using multiple separate interleaved hierarchies.
It means that when a row in the parent table is deleted, all its interleaved child rows are automatically deleted as well. This is the recommended setting for most parent-child interleaved relationships to maintain data integrity.
No. The primary key is immutable after the table is created. You must drop and recreate the table if you need to change the primary key. This is why careful schema design upfront is critical.
You've finished Schema Design for Cloud Spanner: Interleaving, Keys, and Transactions. Continue through the PCDE study guide to build a complete picture of the exam.
Done with this chapter?