DEREK DOWNEY: Hi.
I'm Derek Downey, a developer advocate for Google Cloud.
In the last video, I highlighted different options
for controlling the database behavior
for my banking application that I'm deploying to Cloud Spanner.
In this video, I'm going to show how Spanner organizes data
into splits and how that impacts the design of the database
schema.
I will also highlight the tools available in Spanner
for efficient schema design and how
to keep the schema updated with online schema changes.
[MUSIC PLAYING]
To start, Cloud Spanner is a role-based relational database.
Spanner requires a defined schema
and supports standard SQL concepts
for defining that schema.
Table columns must have a data type defined.
Spanner supports standard scalar data types, as well as
more advanced data types to enable different application
requirements.
For instance, JSON allows storing schema-less data
within Spanner.
Constraints, foreign keys, and unique indexes
help enforce the integrity of the data,
and unique and nonunique indexes help provide
faster lookups of the data.
A key reason to use Spanner is the near unlimited scaling
capability for application workloads.
When designing that schema, it's important to understand
how Spanner organizes data to achieve the scalability.
As a horizontally scaling database,
Spanner automatically shards data into splits.
Splits enable Spanner to scale by distributing
compute capacity to multiple servers.
But how do these splits affect the design decisions
for my schema?
For a quick example from my banking app,
I have a Customers table with a CustomerId as primary key.
A common pattern in many relational databases
is to increment an integer for new rows.
With data splits, sequential keys
are an antipattern because your last split
will receive all new rows, creating a hot spot.
Ideally, the primary key should be inserted in random order
so inserts hit multiple splits, which improves right-scaling.
Common methods to get random order inserts
are UUID version 4 or generating hash keys.
Splits also have implications for related data
in other tables.
In my app, a customer can have multiple accounts.
The accounts data could end up in a different split
than the customer it belongs to.
If I have a query to get a customer and their accounts,
Spanner will have to get data from multiple splits.
This increases request latency, especially if the splits are
processed by different servers.
To avoid this, tables can be interleaved together
in the schema definition.
If I interleave Accounts into the Customers table,
then the account rows will be located in the same split
as the customer they belong to and make my query faster.
Keep in mind that interleaving cannot be changed for existing
tables and not all table relationships should be
interleaved.
Interleaving should be used for related data that is frequently
accessed together.
Splits also affect indexes because Spanner implements
indexes as tables internally, so requesting more columns
than an index provides can result in getting data
from multiple splits.
Creating an index that stores columns
that are typically requested by queries can solve this.
In my app, the UniqueUsername index
also stores the password so a login
query that searches for a combination of username
and password will be done efficiently.
The last thing to highlight about Spanner schema design
is that schema changes are online,
so your application can continue reading and writing
to the table during the change.
The time to complete a change depends on the type of change.
Adding a new column that doesn't have a default value
will complete quickly since there's nothing
to do for existing rows.
Adding a new column or modifying an existing column
with a default value requires backfilling data
into each existing row of the table.
Modifying an existing column with a new constraint,
such as NOT NULL, will require data validation.
Data validation and backfilling operations
take longer to complete, but Spanner
will process this in the background
so that your users and applications are not impacted.
This makes schema changes painless.
As a relational database, Spanner
requires a defined schema using supported data types,
constraints, and indexing.
But it makes it as easy as possible to change later
as data requirements change.
Data in Spanner is automatically sharded
into splits that can be powered by different compute nodes
to achieve great scalability.
Splits require different schema design choices
than traditional relational database systems,
and I've shown how to avoid hot-spotting
and how to interleave related data together
so query requests are handled efficiently.
In the final video, I will show how our banking app uses
both standard DML statements and Spanner mutations to write
to our Spanner database.
[MUSIC PLAYING]