ON DELETE CASCADE) work. Interleaving improves query performance by colocating parent and child rows on storage, reducing I/O and network round trips—advantages that grow with scale (millions of rows).
Follow along in the GCP Console to see this in action.
Open the Cloud Spanner Instances page in the GCP Console and select your instance.
Cloud Spanner is a managed, strongly-consistent, horizontally scalable database. It can be costly for long-running demo resources—delete or scale down instances when you finish to avoid unexpected charges.
Example queries: Flights and Bookings
In this demo database we have two tables:Flights (parent) and Bookings (child). The following queries show common access patterns where interleaving provides benefits.
- Get each flight with its total bookings:
- Show detailed bookings for a single flight (example
FlightId = 'FL005-20251002'):
Demonstrating cascading delete (ON DELETE CASCADE)
When defining an interleaved child table you can include ON DELETE CASCADE. That causes Spanner to delete child rows automatically when the parent row is deleted. This is atomic and safe: the parent and all interleaved descendants (children, grandchildren, etc.) are deleted within the same transaction; if anything fails the whole operation rolls back.
Steps to demonstrate:
- Inspect existing child rows for a target flight:
BK10013 and BK10014).
- Verify the total number of bookings in the table:
- Delete the parent flight row:
1 row deleted). Because Bookings is interleaved with ON DELETE CASCADE, Spanner also deletes the associated bookings in the same transaction.
- Verify child rows and totals after the delete:
- Confirm the parent flight is gone:
- List remaining flights:
Quick reference: what to expect and why it matters
| Topic | Behavior / Benefit | Example |
|---|---|---|
| Interleaved storage | Parent and child rows are stored together for better locality and lower latency | Faster JOINs and single-key lookups |
| Cascading delete | ON DELETE CASCADE removes interleaved descendants automatically and atomically | Deleting a flight removes its bookings in one transaction |
| Transactional guarantees | Delete of parent + children is all-or-nothing | No partial deletes or orphaned rows |
| Use cases | Orders/order_items, accounts/transactions, hierarchical records | Any parent-child access pattern at scale |
Important cleanup step
Cloud Spanner instances are billable. Delete demo instances when you’re done to avoid charges.Before leaving the project, delete or scale down your Spanner instance to stop billing. Deleting an instance is permanent—confirm the instance ID when prompted.
- Use interleaved tables to colocate related rows and improve read performance at scale.
ON DELETE CASCADEensures child rows are removed automatically and transactionally when a parent is deleted.- Cascading deletes apply recursively through interleaving depth (children, grandchildren, …).