This page describes various ways to implement multi-tenancy in Spanner.It also discusses data management patterns and tenant lifecycle management. Itis intended for database architects, data architects, and engineers thatimplement multi-tenant applications on Spanner as theirrelational database. Using that context, it outlines various approaches to storemulti-tenant data. The terms "tenant", "customer", and "organization" are usedinterchangeably throughout the article to indicate the entity that's accessingthe multi-tenant application. The examples provided on this page are based on ahuman resources (HR) SaaS provider's implementation of its multi-tenantapplication on Google Cloud. One requirement is that several customers of the HRSaaS provider must access the multi-tenant application. These customers arecalled tenants.

Multi-tenancy

Multi-tenancy is when a singleinstance, or a few instances, of a software application serves multiple tenantsor customers. This software pattern can scale from a single tenant or customerto hundreds or thousands. This approach is fundamental to cloud computingplatforms where the underlying infrastructure is shared among multipleorganizations.

Think of multi-tenancy as a form of partitioning based on shared computingresources, like databases. An analogy is tenants in an apartment building:the tenants share the infrastructure, such as water pipes and electrical lines,but each tenant has dedicated tenant space in an apartment. Multi-tenancy ispart of most, if not all, software-as-a-service (SaaS) applications.

Spanner is Google Cloud's fully managed, enterprise-grade,distributed, and consistent database which combines the benefits of therelational database model with non-relational horizontal scalability.Spanner has relational semantics with schemas, enforced datatypes, strong consistency, multi-statement ACID transactions, and a SQL querylanguage implementing ANSI 2011 SQL.It provides zero-downtime for plannedmaintenance or region failures, with anavailability SLA of 99.999%.Spanner also supports modern, multi-tenant applications byproviding high availability and scalability.

Criteria for tenant data-mapping criteria

The preceding criteria are called data management patterns and are discussed indetail in theMulti-tenancy data management patternssection. That discussion is based on the following criteria:

• Data isolation: The degree of data isolation across multiple tenants isa major consideration for multi-tenancy. For example, whether data needs tobe physically or logically separated, and whether there are independentACLs (Access Control Lists) that can be set for each tenant's data.Isolation is driven by the choices made for the criteria under othercategories. For example, certain regulatory and compliance requirementsmight dictate a greater degree of isolation.
• Agility: The ease of onboarding and offboarding activities for a tenantwith respect to creating an instance, database, table, or row.
• Operations: The availability or complexity of implementing typical,tenant-specific, database operations, and administration activities. Forexample, regular maintenance, logging, backups, or disaster recoveryoperations.
• Scale: The ability to scale seamlessly to allow for future growth. Thedescription of each pattern contains the number of tenants the pattern cansupport.
• Performance:
  • Resource isolation: The ability to allocate exclusive resources toeach tenant, address thenoisy neighborphenomenon, and enable predictable read and write performance for eachtenant.
  • Minimum resources per tenant: The average minimum amount ofresources per tenant. This doesn't necessarily mean that you need to payat least this amount for each individual tenant, but rather you need topay at least N * this amount for all the N tenants together.
  • Resource efficiency: The ability to use idle resources of othertenants to save overall cost.
  • Location selection for latency optimization: The ability to pickspecific replication topology for each tenant so that the data for eachtenant can be placed in the location that provides the best latency forthe tenant.
• Regulations and compliance: The ability to address the requirements ofhighly regulated industries and countries that require the completeisolation of resources and maintenance operations. For example, dataresidency requirements for France require that personally identifiableinformation is physically stored exclusively within France. Financialindustries usually requirecustomer-managed encryption keys (CMEK),and each tenant might want to use its ownencryption key.

Each data management pattern as it relates to these criteria is detailed in thenext section. Use the same criteria when selecting a data management pattern fora specific set of tenants.

Multi-tenancy data management patterns

The following sections describe the four main data management patterns:instance, database, table, and row.

Instance

To provide complete isolation, the instance data management pattern stores eachtenant's data in its own Spanner instance and database. ASpanner instance can have one or more databases. In this pattern,only one database is created. For the HR application discussed earlier, aseparate Spanner instance with one database is created for eachcustomer organization.

As seen in the following diagram, the data management pattern has one tenant perinstance.

Having separate instances for each tenant allows the use of separateGoogle Cloud projects to achieve separate trust boundaries for different tenants. Anextra benefit is that each instance configuration can be chosen based on eachtenant's location (either regionally or multi-regionally), optimizing locationflexibility and performance.

The architecture can scale to any number of tenants. SaaSproviders can create any number of instances in the wanted regions, without anyhard limits.

The following table outlines how the instance data management pattern affectsdifferent criteria.

In summary, the key takeaways are:

• Advantage: Highest level of isolation
• Disadvantage: Greatest operational overhead and potentially highercost due to the 100 PU minimum per tenant. Sharing resources across tenantsis unsupported.

The instance data management pattern is best suited for the following scenarios:

• Different tenants are spread across a wide range of regions and need alocalized solution.
• Regulatory and compliance requirements for some tenants demand greaterlevels of security and auditing protocols.
• Tenant size varies significantly, such that sharing resources amonghigh-volume, high-traffic tenants might cause contention and mutualdegradation.

Database

In the database data management pattern, each tenant resides in a databasewithin a single Spanner instance. Multiple databases can residein a single instance. If one instance is insufficient for the number of tenants,create multiple instances. This pattern implies that a singleSpanner instance is shared among multiple tenants.

Spanner has ahard limitof 100 databases per instance. This limit means that if the SaaS provider needsto scale beyond 100 customers, they need to create and use multipleSpanner instances.

For the HR application, the SaaS provider creates and manages each tenant with aseparate database in a Spanner instance.

As seen in the following diagram, the data management pattern has one tenant perdatabase.

The database data management pattern achieves logical isolation on a databaselevel for different tenants' data. However, because it's a singleSpanner instance, all the tenant databases share the samereplication topology and underlying compute and storage setup unless thegeo-partitioning feature is used.You can use the Spanner geo-partitioning feature to createinstance partitions in different locations and use different instance partitionsfor different databases in the same instance.

The following table outlines how the database data management pattern affectsdifferent criteria.

In summary, the key takeaways are:

• Advantage: Moderate level of data isolation, and resource isolation;moderate level of resource efficiency; each tenant can have its own backupand CMEK.
• Disadvantage: Limited number of tenants per instance; locationinflexibility if not using the geo-partitioning feature.

The database data management pattern is best suited for the following scenarios:

• Multiple customers are in the same data residency or are under the sameregulatory authority.
• Tenants require system-based data separation and the ability to backup andrestore their data, but are fine with infrastructure resource sharing.
• Tenants require their own CMEK.
• Cost is an important consideration. The minimum resourcesneeded per tenant are less than the cost of an instance. It is desirablefor tenants to use the idle resources of other tenants.

Table

In the table data management pattern, a single database, which implements asingle schema, is used for multiple tenants and a separate set of tables is usedfor each tenant's data. These tables can be differentiated by including thetenant ID in the table names as either a prefix, a suffix, or asnamed schemas.

This data management pattern of using a separate set of tables for each tenantprovides a much lower level of isolation compared to the preceding options (theinstance and database management patterns). Onboarding involves creating newtables and associated referential integrity and indexes.

There's a limit of 5,000 tables per database. For some customers, that limitmight restrict their use of the application.

Furthermore, using separate tables for each customer can result in a largebacklog of schema update operations. Such a backlog takesa long time to resolve.

For the HR application, the SaaS provider can create a set of tables for eachcustomer withtenant ID as the prefix in the table names. For example,customer1_employee,customer1_payroll, andcustomer1_department.Alternatively, they can use tenant ID as a named schema and name their table ascustomer1.employee,customer1.payroll, andcustomer1.department.

As seen in the following diagram, the table data management pattern has one setof tables for each tenant.

The following table outlines how the table data management pattern affectsdifferent criteria.

In summary, the key takeaways are:

• Advantage: Moderate level of scalability and resource efficiency.
• Disadvantage:
  • Moderate level of data isolation, and resource isolation.
  • Location inflexibility if not using the new geo-partitioning feature.
  • Inability to separately monitor tenants. The only available table levelresource consumption info istable size statistics.
  • Tenants can't have their own CMEK and backups.

The table data management pattern is best suited for the following scenarios:

• Multi-tenant applications that doesn't legally require data separation, butyou want logical separation and security control.
• Cost is an important consideration. The minimum per tenantcost is cheaper than per database cost.

Row

The final data management pattern serves multiple tenants with a common set oftables, with each row belonging to a specific tenant. This data management patternrepresents an extreme level of multi-tenancy where everything—frominfrastructure to schema to data model—is shared among multiple tenants. Withina table, rows are partitioned based on primary keys, withtenant ID as thefirst element of the key. From a scaling perspective, Spannersupports this pattern best because it can scale tables without limitation.

For the HR application, the payroll table's primary key can be a combination ofcustomerID andpayrollID.

As seen in the following diagram, the row data management pattern has one tablefor several tenants.

Unlike all the other patterns, data access in the row pattern can't becontrolled separately for different tenants. Using fewer tables means schemaupdate operations complete faster when each tenant has their own databasetables. To a large extent, this approach simplifies onboarding, offboarding, andoperations.

The following table outlines how the row data management pattern affectsdifferent criteria.

In summary, the key takeaways are:

• Advantage: Highly scalable; has low operational overhead; simplifiedschema management.
• Disadvantage: High resource contention; lack of security controls andmonitoring for each tenant.

This pattern is best suited for the following scenarios:

• Internal applications that cater to different departments where strict datasecurity isolation isn't a prominent concern when compared to ease ofmaintenance.
• Maximum resource sharing for tenants using free-tier applicationwhen minimizing resource provisioning at the same time.

Data management patterns and tenant lifecycle management

The following table compares the various data management patterns across allcriteria at a high level.

¹ Performance is heavily dependent on theschema designandquery best practices.The values here are only an average expectation.

The best data management patterns for a specific multi-tenant application arethose that satisfy most of its requirements based on the criteria. If aparticular criterion isn't required, you can ignore the row it's in.

Combined data management patterns

Often, a single data management pattern is sufficient to address therequirements of a multi-tenant application. When that's the case, the design canassume a single data management pattern.

Some multi-tenant applications require several data management patterns at thesame time. For example, a multi-tenant application that supports a freetier, a regular tier, and an enterprise tier.

• Free tier:

  • Must be cost effective
  • Must have an upper data-volume limit
  • Usually supports limited features
  • The row data management pattern is a good free-tier candidate
    • Tenant management is straightforward
    • No need to create specific or exclusive tenant resources

• Regular tier:

  • Good for paying clients who have no specifically strong scaling orisolation requirements.
  • The table data management pattern or the database data managementpattern is a good regular-tier candidate:
    • Tables and indexes are exclusive for the tenant.
    • Backup is straightforward in the database data management pattern
    • Backup isn't supported for the table data management pattern.
      • Tenant backup must be implemented as a utility outsideSpanner.

• Enterprise tier:

  • Usually a high-end tier with full autonomy in all aspects.
  • Tenant has dedicated resources that include dedicated scaling and fullisolation.
  • The instance data management pattern is well suited for the enterprisetier.

A best practice is to keep different data management patterns in differentdatabases. While it's possible to combine different data management patterns ina Spanner database, doing so makes it difficult to implement theapplication's access logic and lifecycle operations.

TheApplication designsection outlines some multi-tenant application design considerations that applywhen using a single data management pattern or several data management patterns.

Manage the tenant lifecycle

Tenants have a lifecycle. Therefore, you must implement the correspondingmanagement operations within your multi-tenant application. Beyond the basicoperations of creating, updating, and deleting tenants, consider the followingadditional data-related operations:

• Export tenant data:

  • When deleting a tenant, it's a best practice to export their data firstand possibly make the dataset available to them.
  • When using the row or table data management pattern, the multi-tenantapplication system must implement the export or map it to the databasefeature (database export), and implement custom logic to take theportion of the data that corresponds to the tenant out.

• Back up tenant data:

  • When using the instance or database data management pattern and backingup data for individual tenants, use the database's export or backupfunctions.
  • When using the table or row data management pattern and backing up datafor individual tenants, the multi-tenant application must implement thisoperation. The Spanner database can't determine whichdata belongs to which tenant.

• Move tenant data:

  • Moving a tenant from one data management pattern to another (or moving atenant within the same data management pattern between instances ordatabases) requires extracting the data from one data management patternand inserting that data into the new data management pattern.

    • When application downtime is possible, perform an export/import.
    • When downtime isn't possible, perform azero downtime database migration.

  • Mitigating a noisy-neighbor situation is another reason to move tenants.

Application design

When designing a multi-tenant application, implement tenant-aware businesslogic. That means each time the application runs business logic, it must alwaysbe in the context of a known tenant.

From a database perspective, application design means that each query must berun against the data management pattern in which the tenant resides. Thefollowing sections highlight some of the central concepts of multi-tenantapplication design.

Dynamic tenant connection and query configuration

Dynamically mapping tenant data to tenant application requests uses a mappingconfiguration:

• For database data management patterns or instance data management patterns,a connection string is sufficient to access a tenant's data.
• For table data management patterns, the correct table names have to bedetermined.
• For row data management patterns, use the appropriate predicates toretrieve a specific tenant's data.

A tenant can reside in any of the four data management patterns. The followingmapping implementation addresses a connection configuration for the general caseof a multi-tenant application that uses all the data management patterns at thesame time. When a given tenant resides in one pattern, some multi-tenantapplications use one data management pattern for all tenants. This case iscovered implicitly by the following mapping.

If a tenant executes business logic (for example, an employee logging in withtheir tenant ID) then the application logic must determine the tenant's datamanagement pattern, the location of the data for a given tenant ID, and,optionally, the table-naming convention (for the table pattern).

This application logic requires tenant-to-data-management pattern mapping. Inthe following code sample, theconnection string refers to the database wherethe tenant data resides. The sample identifies the Spannerinstance and the database. For the data management pattern instance anddatabase, the following code is sufficient for the application to connect andexecute queries:

tenant id -> (data management pattern,              database connection string)

Additional design is required for the table and row data management patterns.

Table data management pattern

For the table data management pattern, there are several tenants within the samedatabase. Each tenant has its own set of tables. The tables are distinguished bytheir name. Which table belongs to which tenant is deterministic.

One approach is to place each tenant's table in a namespace named after thetenant, and fully qualify your table name withnamespace.name. For example, you putanEMPLOYEE table inside the namespaceT356 for the tenant with the ID356, and your application can useT356.EMPLOYEE to address the requeststo the table.

Another approach is to prepend the table names with the tenant ID. For example,theEMPLOYEE table is calledT356_EMPLOYEE for the tenant with the ID356.The application has to prepend each table with the prefixtenantID before sending the query to the database that the mapping returned.

If you want to use some other text instead of the tenant ID, you can maintain amapping from the tenant ID to the named schema namespace or to the table prefix.

To simplify the application logic, you might introduce one level of indirection.For example, you can use a common library with your application to automaticallyattach the namespace or table prefix for the call from the tenant.

Row data management pattern

A similar design is required for the row data management pattern. In thispattern, there's a single schema. Tenant data are stored as rows. To properlyaccess the data, append a predicate to each query to select the appropriatetenant.

One approach to find the appropriate tenant is to have a column calledTENANTin each table. For better data isolation, this column value should be part ofthe primary key. The column value istenant ID. Each query must append apredicateAND TENANT =tenant ID to an existingWHEREclause or add aWHERE clause with the predicateAND TENANT =tenantID.

To connect to the database and to create the proper queries, the tenantidentifier must be available in the application logic. It can be passed in asparameter or stored as thread context.

Some lifecycle operations require you to modify thetenant-to-data-management-pattern mapping configuration—for example, when youmove a tenant between data management patterns, you must update the datamanagement pattern and the database connection string. You might also have toupdate the table prefix.

Query generation and attribution

A fundamental underlying principle of multi-tenant applications is that severaltenants can share a single cloud resource. The preceding data managementpatterns fall into this category, except for the case where a single tenant isallocated to a single Spanner instance.

The sharing of resources goes beyond sharing data. Monitoring and logging isalso shared. For example, in the table data management pattern and row datamanagement pattern, all queries for all tenants are recorded in the same auditlog.

If a query is logged, then the query text has to be examined to determine whichtenant the query was executed for. In the row data management pattern, you mustparse the predicate. In the table data management pattern, you must parse one ofthe table names.

In the database data management pattern or the instance data management pattern,the query text doesn't have any tenant information. To get tenant informationfor these patterns, you must query the tenant-to-data-management-pattern mappingtable.

It would be easier to analyze logs and queries by determining the tenant for agiven query without parsing the query text. One way to uniformly identify atenant for a query across all data management patterns is to add a comment tothe query text that has thetenant ID, and (optionally) alabel.

The following query selects all employee data for the tenant identified byTENANT 356. To avoid parsing the SQL syntax and extracting the tenant ID fromthe predicate, the tenant ID is added as a comment. A comment can be extractedwithout having to parse the SQL syntax.

SELECT*FROMEMPLOYEE-- TENANT 356WHERETENANT='T356';

or

SELECT*FROMT356_EMPLOYEE;-- TENANT 356

With this design, every query run for a tenant is attributed to that tenantindependent of the data management pattern. If a tenant is moved from one datamanagement pattern to another, the query text might change, but the attributionremains the same in the query text.

The preceding code sample is only one method. Another method is to insert aJSONobject as a comment instead of a label and value:

SELECT*FROMT356_EMPLOYEE;-- {"TENANT": 356}

You can also usetagsto attribute queries to tenants, and view the statistics in the built-inspanner_sys tables.

Tenant access lifecycle operations

Depending on your design philosophy, a multi-tenant application can directlyimplement the data lifecycle operations described earlier, or it can create aseparate tenant-administration tool.

Independent of the implementation strategy, lifecycle operations might have tobe run without the application logic running at the same time—for example,while moving a tenant from one data management pattern to another, theapplication logic can't run because the data isn't in a single database. Whendata isn't in a single database, it requires two additional operations from anapplication perspective:

• Stopping a tenant: Disables all application logic access whilepermitting data lifecycle operations.
• Starting a tenant: Application logic can access a tenant's datawhile the lifecycle operations that would interfere with the applicationlogic are disabled.

While not often used, an emergency tenant shutdown might be another importantlifecycle operation. Use this shut down when you suspect a breach, and you needto prohibit all access to a tenant's data—not only application logic, butlifecycle operations as well. A breach can originate from inside or outside thedatabase.

A matching lifecycle operation that removes the emergency status must also beavailable. Such an operation can require two or more administrators to sign inat the same time in order to implementmutual control.

Application isolation

The various data management patterns support different degrees of tenant-dataisolation. From the most isolated level (instance) to the least isolated level(row), different degrees of isolation are possible.

In the context of a multi-tenant application, a similar deployment decisionmust be made: do all tenants access their data (in possibly different datamanagement patterns) using the same application deployment? For example, asingle Kubernetes cluster might support all tenants and when a tenant accessesits data, the same cluster runs the business logic.

Alternatively, as in the case of the data management patterns, differenttenants might be directed to different application deployments. Large tenantsmight have access to an application deployment exclusive to them, while smallertenants or tenants in the free tier share an application deployment.

Instead of directly matching the data management patterns discussed in thisdocument with equivalent application-data management patterns, you can use thedatabase data management pattern so that all tenants share a single applicationdeployment. It's possible to have the database data management pattern and allthese tenants share a single application deployment.

Multi-tenancy is an important application-design-data management pattern,especially when resource efficiency plays a vital role. Spannersupports several data management patterns—use it for implementing multi-tenantapplications.It provides zero-downtime for plannedmaintenance or region failures, with anavailability SLA of 99.999%.It also supports modern, multi-tenant applications by providing highavailability andscalability.