GCP – You asked, we listened—more productivity features to close out Spanner’s year

Cloud Spanner has had a busy year. We’ve rolled out more enterprise features, including managed backup and restore, local emulator, and numerous multi-region configurations. It’s easier than ever to test and deploy applications across multiple regions without manual sharding, downtime, or patching, and with an industry-leading SLA of 99.999%.

In addition to enterprise features, we continued our focus on making developers more productive. Earlier this year we launched foreign keys, C++ client library, query optimizer, and various introspection features. In this post, we’ll discuss three recently launched Spanner features: check constraints, generated columns, and NUMERIC data type. We launched these features to boost your productivity when building an application on Spanner. Read on for a brief description of each feature, and an example of how check constraints and generated columns can be combined to provide additional referential integrity to your application. 

Check constraints

A check constraint allows you to specify that the values of one or more columns must satisfy a boolean expression. With check constraints, you can specify predicates (boolean expressions) on a table, and require that all rows in the table satisfy those predicates. For example, you can require that the end time of a concert is later than its start time:

Here, we see the “start_before_end” constraint requires the value of the StartTime column to be less than the EndTime, otherwise an insert or update for any row will fail. Check constraints join foreign keys,NOT NULL constraints, and UNIQUE indexes as methods to add integrity constraints to your database.

Generated columns

A generated column is a column whose value is computed from other columns in the same row. This is useful to push down critical data logic into the database, instead of relying on the application layer. Generated columns can make a query simpler or save the cost of evaluating an expression at query time. Like other column types, they can also be indexed or used as a foreign key. For example, you can create a generated column that concatenates the first and last name fields into a new column:

In this example, the value of “FullName” is computed when a new row is inserted or when “FirstName” and/or “LastName” is updated for an existing row. The computed value is stored and accessed the same way as other columns in the table, but can’t be updated on its own. 

NUMERIC data type

Customer requests are a crucial part of how we prioritize features, and NUMERIC data type was a common request. The NUMERIC data type provides precision, useful across many industries and functions, such as financial, scientific, or engineering applications. NUMERIC is useful, where a precision of 30 digits or more is commonly required. Spanner’s NUMERIC has precision of 38 and scale of 9, meaning it can store a number with a total of 38 digits, 9 of which can be fractional (i.e., to the right of the decimal). When you need to store an arbitrary precision number in a Spanner database, and you need more precision than NUMERIC provides, we recommend that you store the value as its decimal representation in a STRING column.

Using new Spanner features in an example use case

In the following example, we look at an ecommerce application and see how check constraints, generated columns, and basic indexing can help improve application performance and reliability. A common pattern for an ecommerce site might be creating a case-insensitive search for a product. In many cases, product names are stored case-sensitively, and the practice of capitalization can vary greatly. To improve the performance of searching over product names, we can create a generated column that converts product name to uppercase and then create an index on that.

The schema for the product table could look like this:

You now have a generated column that stores the product name column with all capital letters. This generated column is stored like any other column in the table, and can be indexed for faster lookups:

To search a product name, convert the search term into uppercase first. We also need to instruct Spanner to use the index we just created:

Now when we query over the product name, we get faster and consistent results, regardless of how the product is capitalized in the listing.

An ecommerce company may also keep a separate pricing table for its products. A few simple check constraints can make sure that prices for products don’t enter an invalid state, and that discount programs don’t overlap. For example, the following constraint checks that a product price cannot be negative:

The pricing table also has two columns for discounts, one of which is a seasonal discount that applies during certain seasonal holidays, and another that is provided for new customer registrations. Here we can add a check constraint to an existing table to ensure that only one discount is active at a time:

For many customers, check constraints, generated columns, and NUMERIC data type will be welcome additions to the toolbox for defining schema and creating high-performing applications. 

We hope that this year’s launches have made it easier to build, test, and deploy your applications using Spanner as your globally consistent database. We look forward to your feedback, and stay tuned for a busy 2021.

Learn more

To get started with Spanner, create an instanceor try it out with a Spanner Qwiklab.