Optimize cost: Databases and smart analytics

Last reviewed 2024-06-03 UTC

This document in the Google Cloud Architecture Framework provides recommendations to help you optimize the cost of your databases and analytics workloads in Google Cloud.

The guidance in this section is intended for architects, developers, and administrators responsible for provisioning and managing databases and analytics workloads in the cloud.

This section includes cost-optimization recommendations for the following products:

Cloud SQL

Cloud SQL is a fully managed relational database for MySQL, PostgreSQL, and SQL Server.

Monitor usage

Review the metrics on the monitoring dashboard, and validate that your deployment meets the requirements of your workload.

Optimize resources

The following are recommendations to help you optimize your Cloud SQL resources:

  • Design a high availability and disaster recovery strategy that aligns with your recovery time objective (RTO) and recovery point objective (RPO). Depending on your workload, we recommend the following:
  • Provision the database with the minimum required storage capacity.
  • To scale storage capacity automatically as your data grows, enable the automatic storage increase feature.
  • Choose a storage type, solid-state drives (SSD) or hard disk drives (HDD), that's appropriate for your use case. SSD is the most efficient and cost-effective choice for most use cases. HDD might be appropriate for large datasets (>10 TB) that aren't latency-sensitive or are accessed infrequently.

Optimize rates

Consider purchasing committed use discounts for workloads with predictable resource needs. You can save 25% of on-demand pricing for a 1-year commitment and 52% for a 3-year commitment.


Spanner is a cloud-native, unlimited-scale, strong-consistency database that offers up to 99.999% availability.

Monitor usage

The following are recommendations to help you track the usage of your Spanner resources:

  • Monitor your deployment, and configure the node count based on CPU recommendations.
  • Set alerts on your deployments to optimize storage resources. To determine the appropriate configuration, refer to the recommended limits per node.

Optimize resources

The following are recommendations to help you optimize your Spanner resources:

  • Run smaller workloads on Spanner at much lower cost by provisioning resources with Processing Units (PUs) versus nodes; one Spanner node is equal to 1,000 PUs.
  • Improve query execution performance by using the query optimizer.
  • Construct SQL statements using best practices for building efficient execution plans.
  • Manage the usage and performance of Spanner deployments by using the Autoscaler tool. The tool monitors instances, adds or removes nodes automatically, and helps you ensure that the instances remain within the recommended CPU and storage limits.
  • Protect against accidental deletion or writes by using point-in-time recovery (PITR). Databases with longer version retention periods (particularly databases that overwrite data frequently) use more system resources and need more nodes.
  • Review your backup strategy, and choose between the following options:
    • Backup and restore
    • Export and import

Optimize rates

When deciding the location of your Spanner nodes, consider the cost differences between Google Cloud regions. For example, a node that's deployed in the us-central1 region costs considerably less per hour than a node in the southamerica-east1 region.


Bigtable is a cloud-native, wide-column NoSQL store for large scale, low-latency workloads.

Monitor usage

The following are recommendations to help you track the usage of your Bigtable resources:

  • Analyze usage metrics to identify opportunities for resource optimization.
  • Identify hotspots and hotkeys in your Bigtable cluster by using the Key Visualizer diagnostic tool.

Optimize resources

The following are recommendations to help you optimize your Bigtable resources:

  • To help you ensure CPU and disk usage that provides a balance between latency and storage capacity, evaluate and adjust the node count and size of your Bigtable cluster.
  • Maintain performance at the lowest cost possible by programmatically scaling your Bigtable cluster to adjust the node count automatically.
  • Evaluate the most cost-effective storage type (HDD or SSD) for your use case, based on the following considerations:

    • HDD storage costs less than SSD, but has lower performance.
    • SSD storage costs more than HDD, but provides faster and predictable performance.

    The cost savings from HDD are minimal, relative to the cost of the nodes in your Bigtable cluster, unless you store large amounts of data. HDD storage is sometimes appropriate for large datasets (>10 TB) that are not latency-sensitive or are accessed infrequently.

  • Remove expired and obsolete data using garbage collection.

  • To avoid hotspots, apply best practices for row key design.

  • Design a cost-effective backup plan that aligns with your RPO.

  • To lower the cluster usage and reduce the node count, consider adding a capacity cache for cacheable queries by using Memorystore.

Additional reading


BigQuery is a serverless, highly scalable, and cost-effective multicloud data warehouse designed for business agility.

Monitor usage

The following are recommendations to help you track the usage of your BigQuery resources:

  • Visualize your BigQuery costs segmented by projects and users. Identify the most expensive queries and optimize them.
  • Analyze slot utilization across projects, jobs, and reservations by using INFORMATION_SCHEMA metadata tables.

Optimize resources

The following are recommendations to help you optimize your BigQuery resources:

  • Set up dataset-level, table-level, or partition-level expirations for data, based on your compliance strategy.
  • Limit query costs by restricting the number of bytes billed per query. To prevent accidental human errors, enable cost control at the user level and project level.
  • Query only the data that you need. Avoid full query scans. To explore and understand data semantics, use the no-charge data preview options.
  • To reduce the processing cost and improve performance, partition and cluster your tables when possible.
  • Filter your query as early and as often as you can.
  • When processing data from multiple sources (like Bigtable, Cloud Storage, Google Drive, and Cloud SQL), avoid duplicating data, by using a federated access data model and querying data directly from the sources.
  • Take advantage of BigQuery's backup instead of duplicating data. See Disaster recovery scenarios for data.

Optimize rates

The following are recommendations to help you reduce the billing rates for your BigQuery resources:

  • Evaluate how you edit data, and take advantage of lower long-term storage prices.
  • Review the differences between flat-rate and on-demand pricing, and choose an option that suits your requirements.
  • Assess whether you can use batch-loading instead of streaming inserts for your data workflows. Use streaming inserts if the data loaded to BigQuery is consumed immediately.
  • To increase performance and reduce the cost of retrieving data, use cached query results.

Additional reading


Dataflow is a fast and cost-effective serverless service for unified stream and batch data processing.

Monitor usage

The following are recommendations to help you track the usage of your Dataflow resources:

  • Predict the cost of Dataflow jobs by running small load experiments, finding your job's optimal performance, and extrapolating the throughput factor.
  • Get increased visibility into throughput and CPU usage by using observability dashboards.
  • After creating a job, view the estimated cost of the resources that job uses and set alerts to receive notifications when the resource usage exceeds specified thresholds. For more information, see Dataflow cost monitoring.

Optimize resources

The following are recommendations to help you optimize your Dataflow resources:

  • Consider Dataflow Prime for processing big data efficiently.
  • For batch pipelines, reduce the resources (CPU, memory, and Persistent Disk storage) consumed on the worker VMs by using Dataflow Shuffle.
  • Reduce batch-processing costs by using Flexible Resource Scheduling (FlexRS) for autoscaled batched pipelines. FlexRS uses advanced scheduling, Dataflow Shuffle, and a combination of preemptible and regular VMs to reduce the cost for batch pipelines.
  • For more responsive autoscaling, and to reduce resource consumption, use Streaming Engine, which moves pipeline execution out of the worker VMs and into the Dataflow service backend.
  • If the pipeline doesn't need access to and from the internet and other Google Cloud networks, disable public IP addresses. Disabling internet access helps you reduce network costs and improve pipeline security.
  • For workloads that can tolerate duplicate records, you can reduce cost by using the at-least-once streaming mode. For more information, see At least once Streaming: Save up to 70% for Streaming ETL workloads.
  • For pipelines that process large datasets, you can reduce the number of workers (Compute Engine VMs) that are required by dynamically increasing the number of parallel threads on each worker. For more information, see Dynamic thread scaling.


Dataproc is a managed Apache Spark and Apache Hadoop service for batch processing, querying, streaming, and machine learning.

The following are recommendations to help you optimize the cost of your Dataproc resources:

What's next