Optimize your database

Last reviewed 2023-08-28 UTC

This document in the Google Cloud Architecture Framework provides best practices to deploy your system based on database design. You learn how to design, migrate, and scale databases, encrypt database information, manage licensing, and monitor your database for events.

Key services

This document in the Architecture Framework system design category provides best practices that include various Google Cloud database services. The following table provides a high-level overview of these services:

Google Cloud service Description
Cloud SQL A fully managed database service that lets you set up, maintain, manage, and administer your relational databases that use Cloud SQL for PostgreSQL, Cloud SQL for MySQL, and Cloud SQL for SQL Server. Cloud SQL offers high performance and scalability. Hosted on Google Cloud, Cloud SQL provides a database infrastructure for applications running anywhere.
Bigtable A table that can scale to billions of rows and thousands of columns, letting you store up to petabytes of data. A single value in each row is indexed; this value is known as the row key. Use Bigtable to store very large amounts of single-keyed data with very low latency. It supports high read and write throughput at low latency, and it is a data source for MapReduce operations.
Spanner A scalable, globally distributed, enterprise database service built for the cloud that includes relational database structure and non-relational horizontal scale. This combination delivers high-performance transactions and consistency across rows, regions, and continents. Spanner provides a 99.999% availability SLA, no planned downtime, and enterprise-grade security.
Memorystore A fully managed Redis service for Google Cloud. Applications that run on Google Cloud can increase performance by using the highly available, scalable, secure Redis service without managing complex Redis deployments.
Firestore A NoSQL document database built for automatic scaling, high performance, and application development. Although the Firestore interface has many of the same features as traditional databases, it is a NoSQL database and it describes relationships between data objects differently.
Firebase Realtime Database A cloud-hosted database. Firebase stores data as JSON and it synchronizes in real time to every connected client. When you build cross-platform apps with Google, iOS, Android, and JavaScript SDKs, all of your clients share one real-time database instance and automatically receive updates with the newest data.
Open source databases Google partners offer different open source databases, including MongoDB, MariaDB, and Redis.
AlloyDB for PostgreSQL A fully managed PostgreSQL-compatible database service for demanding enterprise workloads. Provides up to 4x faster performance for transactional workloads and up to 100x faster analytical queries when compared to standard PostgreSQL. AlloyDB for PostgreSQL simplifies management with machine learning-enabled autopilot systems.

Database selection

This section provides best practices for choosing a database to support your system.

Consider using a managed database service

Evaluate Google Cloud managed database services before you install your own database or database cluster. Installing your own database involves maintenance overhead including installing patches and updates, and managing daily operational activities like monitoring and performing backups.

Use functional and non-functional application requirements to drive database selection. Consider low latency access, time series data processing, disaster recovery, and mobile client synchronization.

To migrate databases, use one of the products described in the following table:

Database migration product Description
Cloud SQL A regional service that supports read replicas in remote regions, low-latency reads, and disaster recovery.
Spanner A multi-regional offering providing external consistency, global replication, and a five nines service level agreement (SLA).
Bigtable A fully managed, scalable NoSQL database service for large analytical and operational workloads with up to 99.999% availability.
Memorystore A fully managed database service that provides a managed version of two popular open source caching solutions: Redis and Memcached.
Firebase Realtime Database The Firebase Realtime Database is a cloud-hosted NoSQL database that lets you store and sync data between your users in real time.
Firestore A NoSQL document database built for automatic scaling, high performance, and ease of application development.
Open source Alternative database options including MongoDB and MariaDB.

Database migration

To ensure that users experience zero application downtime when you migrate existing workloads to Google Cloud, it's important to choose database technologies that support your requirements. For information about database migration options and best practices, see Database migration solutions and Best practices for homogeneous database migrations.

Planning for a database migration includes the following:

  • Assessment and discovery of the current database.
  • Definitions of migration success criteria.
  • Environment setup for migration and the target database.
  • Creation of the schema in the target database.
  • Migration of the data into the target database.
  • Validation of the migration to verify that all the data is migrated correctly and is present in the database.
  • Creation of rollback strategy.

Choose a migration strategy

Selecting the appropriate target database is one of the keys to a successful migration. The following table provides migration options for some use cases:

Use case Recommendation
New development in Google Cloud. Select one of the managed databases that's built for the cloud—Cloud SQL, Spanner, Bigtable, or Firestore—to meet your use-case requirements.
Lift-and-shift migration. Choose a compatible, managed-database service like Cloud SQL, MYSQL, PostgreSQL, or SQLServer.
Your application requires granular access to a database that CloudSQL doesn't support. Run your database on Compute Engine VMs.

Use Memorystore to support your caching database layer

Memorystore is a fully managed Redis and Memcached database that supports submilliseconds latency. Memorystore is fully compatible with open source Redis and Memcached. If you use these caching databases in your applications, you can use Memorystore without making application-level changes in your code.

Use Bare Metal servers to run an Oracle database

If your workloads require an Oracle database, use Bare Metal servers provided by Google Cloud. This approach fits into a lift-and-shift migration strategy.

If you want to move your workload to Google Cloud and modernize after your baseline workload is working, consider using managed database options like Spanner, Bigtable, and Firestore.

Databases built for the cloud are modern managed databases which are built from the bottom up on the cloud infrastructure. These databases provide unique default capabilities like scalability and high availability, which are difficult to achieve if you run your own database.

Modernize your database

Plan your database strategy early in the system design process, whether you're designing a new application in the cloud or you're migrating an existing database to the cloud. Google Cloud provides managed database options for open source databases such as Cloud SQL for MySQL and Cloud SQL for PostgreSQL. We recommend that you use the migration as an opportunity to modernize your database and prepare it to support future business needs.

Use fixed databases with off-the-shelf applications

Commercial off-the-shelf (COTS) applications require a fixed type of database and fixed configuration. Lift and shift is usually the most appropriate migration approach for COTS applications.

Verify your team's database migration skill set

Choose a cloud database-migration approach based on your team's database migration capabilities and skill sets. Use Google Cloud Partner Advantage to find a partner to support you throughout your migration journey.

Design your database to meet HA and DR requirements

When you design your databases to meet high availability (HA) and disaster recovery (DR) requirements, evaluate the tradeoffs between reliability and cost. Database services that are built for the cloud create multiple copies of your data within a region or in multiple regions, depending upon the database and configuration.

Some Google Cloud services have multi-regional variants, such as BigQuery and Spanner. To be resilient against regional failures, use these multi-regional services in your design where possible.

If you design your database on Compute Engine VMs instead of using managed databases on Google Cloud, ensure that you run multiple copies of your databases. For more information, see Design for scale and high availability in the Reliability category.

Specify cloud regions to support data residency

Data residency describes where your data physically resides at rest. Consider choosing specific cloud regions to deploy your databases based on your data residency requirements.

If you deploy your databases in multiple regions, there might be data replication between them depending on how you configure them. Select the configuration that keeps your data within the desired regions at rest. Some databases, like Spanner, offer default multi-regional replication. You can also enforce data residency by setting an organization policy that includes the resource locations constraints. For more information, see Restricting Resource Locations.

Include disaster recovery in data residency design

Include Recovery Time Objective (RTO) and Recovery Point Objective (RPO) in your data residency plans, and consider the trade-off between RTO/RPO and costs of the disaster recovery solution. Smaller RTO/RPO numbers result in higher costs. If you want your system to recover faster from disruptions, your system will cost more to run. Also, factor customer happiness into your disaster recovery approach to make sure that your reliability investments are appropriate. For more information, see 100% reliability is the wrong target and Disaster recovery planning guide.

Make your database Google Cloud-compliant

When you choose a database for your workload, ensure that the selected service meets compliance for the geographic region that you are operating in and where your data is physically stored. For more information about Google's certifications and compliance standards, see Compliance offerings.

Encryption

This section provides best practices for identifying encryption requirements and choosing an encryption key strategy to support your system.

Determine encryption requirements

Your encryption requirements depend on several factors, including company security policies and compliance requirements. All data that is stored in Google Cloud is encrypted at rest by default, without any action required by you, using AES256. For more information, see Encryption at rest in Google Cloud.

Choose an encryption key strategy

Decide if you want to manage encryption keys yourself or if you want to use a managed service. Google Cloud supports both scenarios. If you want a fully managed service to manage your encryption keys on Google Cloud, choose Cloud Key Management Service (Cloud KMS). If you want to manage your encryption keys to maintain more control over a key's lifecycle, use Customer-managed encryption keys (CMEK).

To create and manage your encryption keys outside of Google Cloud, choose one of the following options:

  • If you use a partner solution to manage your keys, use Cloud External Key Manager.
  • If you manage your keys on-premises and if you want to use those keys to encrypt the data on Google Cloud, import those keys into Cloud KMS either as KMS keys or Hardware Key Module (HSM) keys. Use those keys to encrypt your data on Google Cloud.

Database design and scaling

This section provides best practices for designing and scaling a database to support your system.

Use monitoring metrics to assess scaling needs

Use metrics from existing monitoring tools and environments to establish a baseline understanding of database size and scaling requirements—for example, right-sizing and designing scaling strategies for your database instance.

For new database designs, determine scaling numbers based on expected load and traffic patterns from the serving application. For more information, see Monitoring Cloud SQL instances, Monitoring with Cloud Monitoring, and Monitoring an instance.

Networking and access

This section provides best practices for managing networking and access to support your system.

Run databases inside a private network

Run your databases inside your private network and grant restricted access only from the clients who need to interact with the database. You can create Cloud SQL instances inside a VPC. Google Cloud also provides VPC Service Controls for Cloud SQL, Spanner, and Bigtable databases to ensure that access to these resources is restricted only to clients within authorized VPC networks.

Grant minimum privileges to users

Identity and Access Management (IAM) controls access to Google Cloud services, including database services. To minimize the risk of unauthorized access, grant the least number of privileges to your users. For application-level access to your databases, use service accounts with the least number of privileges.

Automation and right-sizing

This section provides best practices for defining automation and right-sizing to support your system.

Define database instances as code

One of the benefits of migrating to Google Cloud is the ability to automate your infrastructure and other aspects of your workload like compute and database layers. Google Deployment Manager and third-party tools like Terraform Cloud let you define your database instances as code, which lets you apply a consistent and repeatable approach to creating and updating your databases.

Use Liquibase to version control your database

Google database services like Cloud SQL and Spanner support Liquibase, an open source version control tool for databases. Liquibase helps you to track your database schema changes, roll back schema changes, and perform repeatable migrations.

Test and tune your database to support scaling

Perform load tests on your database instance and tune it based on the test results to meet your application's requirements. Determine the initial scale of your database by load testing key performance indicators (KPI) or by using monitoring KPIs derived from your current database.

When you create database instances, start with a size that is based on the testing results or historical monitoring metrics. Test your database instances with the expected load in the cloud. Then fine-tune the instances until you get the desired results for the expected load on your database instances.

Choose the right database for your scaling requirements

Scaling databases is different from scaling compute layer components. Databases have state; when one instance of your database isn't able to handle the load, consider the appropriate strategy to scale your database instances. Scaling strategies vary depending on the database type.

Use the following table to learn about Google products that address scaling use cases.

Use case Recommended product Description
Horizontally scale your database instance by adding nodes to your database when you need to scale up the serving capacity and storage. Spanner A relational database that's built for the cloud.
Add nodes to scale your database. Bigtable Fully managed NoSQL big data database service.
Automatically handle database scaling. Firestore Flexible, scalable database for mobile, web, and server development.
To serve more queries, vertically scale up Cloud SQL database instances to give them more compute and memory capacity. In Cloud SQL, the storage layer is decoupled from the database instance. You can choose to scale your storage layer automatically whenever it approaches capacity. Cloud SQL Fully managed database service that helps you set up, maintain, manage, and administer your relational databases on Google Cloud.

Operations

This section provides best practices for operations to support your system.

Use Cloud Monitoring to monitor and set up alerts for your database

Use Cloud Monitoring to monitor your database instances and set up alerts to notify appropriate teams of events. For information about efficient alerting best practices, see Build efficient alerts.

All databases that are built for the cloud provide logging and monitoring metrics. Each service provides a dashboard to visualize logging and monitoring metrics. The monitoring metrics for all services integrate with Google Cloud Observability. Spanner provides query introspection tools like the Key Visualizer for debugging and root cause analysis. The Key Visualizer provides the following capabilities:

  • Helps you analyze Spanner usage patterns by generating visual reports for your databases. The reports display usage patterns by ranges of rows over time.
  • Provides insights into usage patterns at scale.

Bigtable also provides a Key Visualizer diagnostic tool that helps you to analyze Bigtable instance usage patterns.

Licensing

This section provides best practices for licensing to support your system.

Choose between on-demand licenses and existing licenses

If you use Cloud SQL for SQL Server, bringing your own licenses isn't supported; your licensing costs are based on per-core hour usage.

If you want to use existing Cloud SQL for SQL Server licenses, consider running Cloud SQL for SQL Server on Compute VMs. For more information, see Microsoft licenses and Choosing between on-demand licenses and bringing existing licenses.

If you use Oracle and if you're migrating to the Bare Metal Solution for Oracle, you can bring your own licenses. For more information, see Plan for Bare Metal Solution.

Migration timelines, methodology, and toolsets

This section provides best practices for planning and supporting your database migration to support your system.

Determine database modernization readiness

Assess whether your organization is ready to modernize your databases and use databases that are built for the cloud.<