Optimize database cost and performance in Google Cloud

Assessing scaling up and scaling out means deciding whether to make a single database instance larger or to add more copies across zones and regions. Scaling up adds vCPUs, memory, and storage to one instance for more compute and throughput. Scaling out adds replicas, clusters, or distributed copies to handle more reads, more connections, and failures without overloading the primary.

1.1Vertical Scaling and Instance Sizing in Cloud SQL

Vertical scaling in Cloud SQL (opens in a new tab) increases vCPUs, memory, disk size, throughput, and IOPS on one primary instance. Correct sizing matches those resources to peak demand, because an undersized instance shows slow inserts, updates, deletes, high memory pressure, and application slowdowns. AlloyDB for PostgreSQL (opens in a new tab) raises vertical limits further with larger machine types and a data cache that improves read performance.

Storage performance depends on disk size plus vCPU count working together. A larger disk provides higher throughput and IOPS, while more vCPUs let the instance actually use that storage performance. CPU and storage must be scaled together, and at least 20% free disk space must remain for maintenance:

Table counts also limit upgrades, so keep below 500 databases per instance and fewer than 50,000 tables per instance, or 500,000 tables only with 32 or more cores and 200 GB or more memory. For performance-intensive workloads, provide at least 60 GB of memory. Changing memory size requires an instance restart that drops connections, so applications need connection pooling, exponential backoff, and small transactions, plus a maintenance window and a metrics-based alert for disk utilization.

1.2Regional and Multi-Regional Trade-offs for Consistency and Cost

Regional versus multi-regional deployment trades lower cost and latency against survival of a region loss. A regional deployment places primary and standby resources in different zones of one region, so traffic stays inside the region with low latency and no cross-region transfer cost. A multi-regional deployment spreads tiers and replicas across at least two regions, so the system withstands zone and region outages but pays continuously for extra capacity, replication, and cross-region networking.

Consistency follows the same split. High availability (HA) inside one region typically uses synchronous replication between zones with zero or near-zero recovery time objective (RTO) and recovery point objective (RPO) and automatic failover. Disaster recovery (DR) across regions typically uses asynchronous replication with higher RTO, nonzero RPO, and promote-and-failover steps, unless a managed multi-regional service provides built-in synchronization. Region choice also balances service and machine-type availability, end-user latency, resource cost, cross-region transfer cost, regulatory and data residency rules, and sustainability such as low-carbon regions.

Reader and writer location directly affect latency. Sending writes over long distances slows inserts, updates, and deletes, and distant readers slow read performance even when throughput looks sufficient. Chained tiers also multiply availability downward, where aggregate availability equals tier1_availability x tier2_availability x tierN_availability, so four tiers at 99.9% yield only about 99.6% overall. Parallel services instead use 1 minus (1 minus A) x (1 minus B), so two 99% services in parallel yield 99.99%.

1.3Horizontal Scaling with Read Replicas and Distributed Copies

Horizontal scaling moves read pressure off a single primary onto separate capacity. In Cloud SQL, application writes flow to the primary instance while application reads flow to read replicas, so heavy reads no longer compete with writes. A cross-region replica can be promoted to become the new primary after a disaster, replicas can be configured for HA in advance, and cascading cross-region replicas protect the middle replica immediately after promotion.

Cloud Spanner and Cloud Bigtable distribute copies differently but with the same goal of serving traffic from more than one location:

A Spanner regional configuration maintains three read-write replicas in three zones, where each replica holds a full copy and serves read-write and read-only requests. A Bigtable production instance requires a minimum of two clusters in unique zones of a region. Choose Cloud SQL replicas to isolate reads from one relational primary, Spanner for distributed read-write capacity with synchronous copies, and Bigtable for zonal clusters with automatically managed copies.

Scaling database instances based on workload means adding the right type of capacity before traffic spikes while avoiding permanent over-provisioning. Horizontal scaling adds separate read or distributed capacity, vertical scaling enlarges one instance, and monitoring connects forecasts to scaling actions. The choice depends on whether reads, writes, storage, or all three are growing.

2.1Horizontal Read Capacity with Replicas, Nodes, and Sharding

Horizontal scaling adds separate capacity to handle more concurrent reads and higher throughput. Cloud SQL (opens in a new tab) read replicas receive copied data from the primary instance and serve read queries, which lowers load on the primary and improves overall performance. Traffic flows from the application to the primary for writes and from the application to the replica for reads, but the application must be changed to connect to the replica endpoint for reads.

Cloud Spanner scales reads, writes, and storage together by adding nodes and processing units (PUs), where 1000 PUs equals one node. Capacity is spread across servers in different zones, with one server task per zone for instances of 1000 PUs and smaller and multiple tasks per zone above that size. Storage allotment is tied to compute, with 1024.0 GiB per 100 PUs below 1 node and 10 TiB per node at 1 node and larger, and Spanner bills only for storage actually utilized.

Database sharding splits one database across multiple smaller instances by a criterion such as customer or subject, so writes and reads flow from the application to different shards. Sharding scales both writes and reads but requires re-architecture, migration, and an even shard key to avoid hotspots that concentrate load on one shard.

2.2Vertical Resource Allocation for Cloud SQL and AlloyDB

Vertical scaling increases compute on the existing instance to handle more workload without adding instances. In Cloud SQL, a larger machine type provides more vCPU and RAM, and higher vCPU plus a disk size of 4 TB or greater provides more IOPS and throughput from persistent disk SSD storage. Compute can be scaled up as frequently as needed, but compute should be scaled down infrequently, changing memory requires a restart, and only the first scale-down in a three-hour period gets near-zero downtime.

AlloyDB for PostgreSQL uses disaggregated compute and storage where writes are acknowledged as soon as write-ahead log (WAL) files persist to regional storage. Data flows automatically between buffer cache, ultra-fast cache, and the intelligent storage engine, so transactions per second (TPS) or transactions per minute (TPM) is the primary sizing metric and IOPS is not useful. Correct sizing targets about 60-70% steady-state central processing unit (CPU) utilization to leave margin for spikes.

To estimate AlloyDB size from current and upcoming workload, follow this ordered workflow:

1. Identify existing workload TPS: Use current TPS from self-managed PostgreSQL or another commercial database when migrating.
2. Analyze critical queries: Identify the most important queries and their performance requirements.
3. Benchmark with HammerDB or pgbench: Run load tests to check whether a machine size satisfies TPS requirements.
4. Compare with the OLTP Benchmarking Guide: Match results to published performance data for AlloyDB configurations.
5. Choose size with growth margin: Select a machine type that covers current data plus future growth at 60-70% CPU.

During AlloyDB scale operations, the primary instance has sub-second downtime and read pool nodes have zero downtime. Keep at least 20% free disk space on Cloud SQL, aim for under 10,000 tables per instance, and enable automatic storage increases for growth.

2.3Proactive Monitoring and Peak Capacity Planning

Proactive monitoring keeps capacity ahead of demand by turning history into forecasts, alerts, and scaling policies. Forecasting starts with user count and request rate, then adds historical trends, seasonal variation, special-event spikes, and business growth such as new geographies. That forecast flows into a capacity estimate shaped by managed instance group (MIG) size, virtual machine (VM) machine type, persistent disk count and size, plus N+1 redundancy where every stack component keeps one redundant component beyond the minimum.

Cloud Monitoring collects CPU, memory, disk, connections, replication lag, and transaction rates and provides predefined dashboards including Cloud SQL monitoring and Query Insights. Teams create custom dashboards, custom metrics, and alert policies that notify through PagerDuty or Slack before limits are reached, while Cloud Logging, Error Reporting, Cloud Trace, and Cloud Profiler add logs, error grouping, latency tracing, and code profiling. Benchmarking validates estimates with load tests under normal and peak conditions, repeated regularly because efficiency degrades under very high load.

Automated scaling then acts on those signals. Enabling automatic storage increases lets Cloud SQL grow as data grows, while stateless MIG autoscaling adds VMs when traffic rises and removes them when need falls. Stateful MIGs cannot be autoscaled and depend on manual planning. Rightsizing with Active Assist recommendations, committed use discounts for predictable needs, and SSD as the efficient default control cost, while quota planning ensures scaling is allowed:

Replication strategies define how database copies stay synchronized within a region for availability and across regions for disaster recovery. Intra-region replication keeps one cluster writable through failure and adds read capacity. Cross-region replication links a primary cluster to a secondary cluster that serves local reads and stands ready for promotion.

3.1Intra-Region High Availability and Read Pools in AlloyDB Omni

Intra-region high availability in AlloyDB Omni (opens in a new tab) uses a primary instance, standby replicas, and read pool instances managed by the AlloyDB Omni operator. The primary instance owns the authoritative copy, application writes flow to the primary first, and replication then carries changes from the primary to standby replicas and readers. In container deployments the primary runs inside a three-node Patroni cluster with a three-node etcd configuration store and HAProxy in front, so clients connect through routing while Patroni and etcd track which node is primary.

A standby replica stays current by continuously applying all changes from the primary and provides the failover target, switchover target, and optional read-only capacity. High availability is enabled with the availability.numberOfStandbys parameter, which tells the operator how many standbys to keep synchronized and requires capacity for two parallel instances with storage for two complete copies. Failover moves write ownership from the failed primary to the standby, healing rebuilds an unhealthy standby from the current primary, and a standby accepts writes only after promotion to primary.

A read pool instance adds dedicated compute for read-intensive workloads without changing the primary. The node count sets its capacity, and a cluster supports a maximum of 20 nodes across all read pools. A new secondary cluster starts with zero read pools even if the primary has them, and later primary changes are not applied automatically:

A read pool is added to a secondary cluster in order:

1. Open Clusters: Go to the Clusters page and open the secondary cluster in the Resource Name column.
2. Start Add read pool: In the Instances in your cluster section, select Add read pool.
3. Configure identity and size: Enter the read pool instance ID and node count for computing capacity.
4. Select machine type: Choose the machine type and create the instance within the 20-node limit.

3.2Cross-Region Replication and Disaster Recovery Operations

Cross-region replication links a primary cluster to a secondary cluster in a different region through asynchronous replication. Data flows from the primary database server to the secondary database server as a streamed WAL flow without waiting for secondary confirmation, so the secondary normally lags slightly behind. That lag determines RPO because unstreamed transactions can be lost, and it affects RTO because a lagging secondary needs catch-up time before promotion.

The secondary cluster starts with one secondary instance and no read pools, and configuration does not stay in sync after creation. An administrator must manually update the secondary to match later primary changes and add read pools for local reads and failover capacity. Creation requires AlloyDB Admin, Owner, or Editor roles, and if the primary uses a customer-managed encryption key (CMEK) the secondary must also use CMEK. For AlloyDB Omni, the primary manifest must set allowExternalIncomingTraffic: true so traffic flows from the secondary data center Kubernetes cluster to the primary data center cluster, and WAL retention must cover cross-data-center lag.

WAL retention is controlled by two parameters that must be sized to write rate, network characteristics, and lag:

If a secondary falls behind by more than wal_keep_size, the primary may remove a needed WAL segment and terminate the replication connection, with downstream connections eventually failing. With HA enabled, both primary and standby servers need adequate WAL space because either may supply WAL to the remote secondary.

Promotion breaks replication and converts the secondary from read-only to an independent primary that accepts writes, with application traffic flowing to the surviving region. Switchover is the planned equivalent where replication is kept in sync and writes move in a controlled order for testing or maintenance. Choose promotion when the primary region is unavailable and some loss within RPO is preferable to extended downtime, and choose switchover when both regions are healthy and minimal loss with predictable RTO is required.

3.3Throughput, Sizing, and Cost Trade-offs in Replication

Replication performance in AlloyDB for PostgreSQL comes from disaggregated storage where writes flow from the primary to regional storage and are acknowledged on WAL persistence while block materialization is offloaded. Reads flow through buffer cache, ultra-fast cache, and the intelligent storage engine, which produces low lag and allows sub-second primary scaling and zero-downtime read pool scaling. Because caching hides disk behavior, TPS/TPM measures throughput and IOPS does not.

Instance sizing balances throughput against cost using workload type such as OLTP, OLAP, or HTAP, latency and throughput targets, total and active data size, and growth in size, rate, and concurrency. Practical rules aim for 60-70% steady-state CPU, start with 8 GB of RAM per vCPU for shared buffers and working memory, and note that open source PostgreSQL generally does not benefit beyond 16 vCPUs even though AlloyDB scales linearly for many workloads. Adding a secondary cluster and read pools improves distribution and recovery but increases operational cost because each instance is billed and manual updates are required.

Continuously optimizing database cost means matching capacity to actual use, finding waste in spending trends, and choosing pricing that fits stable versus variable demand. Rightsizing and autoscaling adjust what is provisioned, billing analysis shows who spent what, and commitments lower the rate for predictable baselines. Together they protect performance while removing idle cost.

4.1Dynamic Scaling and Iterative Rightsizing

Rightsizing continuously adjusts allocation to observed demand rather than initial estimates. Over-provisioned instances create unnecessary cost while under-provisioned resources cause bottlenecks and slow queries. Cloud Monitoring exposes CPU, memory, disk, connections, replication lag, and transaction rates, and Recommender turns that history into suggestions for smaller or more cost-effective types. For Cloud SQL (opens in a new tab) rightsizing also starts with minimum storage and compute, enables automatic storage increases, removes unneeded tables, and removes HA or DR where not required.

Storage and commitment choices balance cost against stability. Choose SSD for most uses as the efficient default, choose HDD only for datasets of 10 TB or more that are latency-insensitive or infrequently accessed, and purchase committed use discounts (CUDs) for steady needs over one to three years. Autoscaling then follows fluctuations within minimum and maximum bounds:

GKE combines Cluster Autoscaler for nodes, Horizontal Pod Autoscaler for replica count, Vertical Pod Autoscaler for requests and limits, and Node Auto-Provisioning for optimized pools. The Spanner Autoscaler runs outside the database on Cloud Run functions or GKE with minor consumption, often within Free Tier for its own Cloud Run functions, Scheduler, Pub/Sub, and Firestore use, while managed Spanner instances are still billed normally. Optimization repeats in order: first assess with monitoring, billing, and Active Assist, then establish recovery, latency, and cost goals, then optimize with rightsizing, autoscaling, replicas, or storage changes, then tune dashboards, alerts, labels, and budgets for the next cycle.

4.2Cost Visibility with Billing, Recommendations, and Budgets

Cost visibility turns raw spend into trends by owner, environment, and workload. Cloud Billing reports show usage costs, invoice details, breakdowns, and pricing tables for every project under a billing account, and the FinOps hub shares that view between finance and engineering. When built-in views are insufficient, billing exports to BigQuery enable custom SQL analysis by project, service, label, or time, and Looker or Data Studio dashboards visualize anomalies and spikes.

Recommender analyzes utilization and Active Assist applies those capabilities at no cost without configuration across Cloud SQL, BigQuery, Compute Engine, and Cloud Run. Common findings include:

• Idle resource recommendations that flag unused resources that can be decommissioned after impact review.
• Idle VM recommendations that flag Compute Engine instances with consistently low utilization, including self-managed database hosts.
• Machine type recommendations that suggest a smaller, more cost-effective type when a host is over-provisioned.

Labels as key-value pairs for project, team, environment, or cost center allow costs to be filtered and grouped in reports and exports, with Terraform automation keeping them consistent for chargeback or showback. Control actions include reviewing changes before apply, documenting preferred environment state, detecting drift with Cloud Asset Inventory, and limiting creation with organization policies, quotas, and least-privilege access. Reduction actions include decommissioning, rightsizing, enabling autoscaling, and migrating suitable self-managed workloads to managed services. Cloud Billing budgets complete the loop with notifications when actual spend crosses planned thresholds.

4.3Pricing Models and Regional Deployment Choices

Pricing models change the rate paid without changing resources consumed. The central FinOps team typically aggregates commitments while product teams rightsize resources. Enterprise discounts negotiate a lower rate for minimum total spend, resource-based CUDs commit to Compute Engine quantity in a region for one or three years with sharing across billing-account projects, and spend-based CUDs commit to spend on a product such as Cloud SQL for one or three years. CUDs can stack on enterprise discounts, Spot VMs offer more than 80% lower cost for fault-tolerant batch work that tolerates preemption, sustained use discounts credit continuous Compute Engine and GKE use, and BigQuery offers on-demand versus edition pricing with commitments and autoscaling.

Cloud SQL CUDs are spend-based commitments for vCPUs and memory in one region, billed monthly for the full term even if use drops, with excess use at on-demand rates:

Cloud SQL CUDs apply to CPU and memory for MySQL, PostgreSQL, and SQL Server in the committed region, including Enterprise and Enterprise Plus editions, where an Enterprise commitment can cover part of an upgraded Enterprise Plus instance. They do not apply to shared-CPU types such as db-f1-micro and db-g1-small, and they never apply to storage, backups, IP addresses, outbound transfer, or licensing. Production baselines that run 24/7 are commonly covered at 100%, while burst, development, or staging use stays on demand. For example, 100 HA instances with 2 vCPUs and 7.5 GB RAM for 730 hours cost about $19,345 per month on demand in us-central1 versus $25,550 in asia-southeast1, with a 25% one-year CUD lowering them to about $14,508 and $19,162.

Regional versus multi-regional choices add location cost on top of commitments. Compute prices vary by region, and multi-region buckets plus inter-region replication and network egress add charges that should be used only when reliability justifies distribution. Closely related resources should stay in the same region where data is replicated, and Object Lifecycle Management plus Autoclass reduce storage spend for backups, exports, and cold data that CUDs never discount.

Optimizing queries for cost and performance means finding the statements that consume the most resources and rewriting them to scan less data. Query Insights measures query load and surfaces expensive fingerprints, strategic indexing gives the optimizer narrow access paths, and schema design plus sizing ensure the instance matches the remaining work. The same pattern applies across Cloud Spanner, Cloud SQL, and AlloyDB for PostgreSQL.

5.1Query Execution Analysis with Query Insights and Indexing

Query Insights (opens in a new tab) is the observability dashboard that defines query load as total CPU utilization for all queries, reported as CPU seconds per minute over the selected database and time range. The dashboard shows total CPU for all queries, total CPU per query or request tag, a TopN queries and tags table limited to 100 entries in Spanner, plus database, time-range, user, address, and tag filters. A spike in query CPU that matches instance CPU points to expensive queries rather than infrastructure noise.

Bottleneck triage separates CPU, I/O wait, and lock wait while time passes from source to completion. The dark blue line shows CPU capacity as available CPUs, so load above that line means demand exceeds compute. CPU load can appear above the maximum core line because it counts runtime plus time waiting for the Linux scheduler. CPU and CPU Wait is active time versus wall-clock time, IO Wait is time waiting for read and write I/O, and Lock Wait is time waiting for locks including lightweight and buffer-pin locks.

Triage moves from system load to one fingerprint in order:

1. Confirm system load: Check whether total load is spiking or elevated and how long it lasted by zooming in and out up to one week.
2. Locate the source: Change database, user, address, or wait dimension to find the highest load and whether CPU, I/O, or lock wait dominates.
3. Identify the query: Open the TopN table sorted by total execution time and select the fingerprint with the highest contribution.
4. Inspect execution detail: Review average execution time, times called, and average rows fetched, where the string truncates to 1024 characters by default.

Active views show the top 50 longest-running transactions in active and idle in transaction states, grouped by connection state, wait-event type, and duration. A normalized query removes literals into a digest, so select * from test_table where id=1; and select * from test_table where id=2; both become select * from test_table where id=?;. In Enterprise Plus, a 24-hour anomaly marks a query with a Warning and slower than usual, and Gemini Cloud Assist can open an Investigation pane with Issue, Observations, and Hypotheses. Strategic indexing then lowers I/O and latency by avoiding full scans, while removing unused and unnecessary foreign-key indexes reduces write cost, with vacuum analyze, reindex, statistics updates, defragmentation, and fill-factor tuning keeping plans stable.

5.2Query Refactoring and Scanning Cost Control

Query refactoring rewrites SQL and access paths so each statement touches fewer rows. A tuned query uses available indexes instead of full scans, avoids long lock and resource waits, and matches the data model with correct column types and structure. Because compute, I/O, and maintenance scale with data scanned and written, scanning less data directly costs less.

Refactoring centers on the objects the slow query touches, including rewriting complex joins into simpler index-friendly forms, adding indexes for critical filters, joins, and ordering, removing non-critical indexes and foreign keys, correcting types and selective denormalization for read-heavy paths, and scheduling maintenance on heavily updated tables. Adding an index speeds supported reads but increases write and maintenance work, while outdated statistics cause suboptimal plans that scan far more data than necessary. Parameterized queries with placeholders let repeated executions share one plan and one digest, and request tags group application operations the same way.

Monitoring flows from total load to the costly fingerprint. First select scope by database, time range, user, and read-pool node, then check total CPU for spikes or persistent elevation, then rank by cost in the TopN table, then inspect total and average CPU, execution count, rows returned, average time, and latency history. Lasting control then limits what must be read or moved:

Vertical scaling with more vCPU, RAM, and disk throughput can relieve urgent slowness but does not reduce per-query scan cost.

5.3Schema Design and Resource Sizing for Efficient Queries

Schema optimization designs tables so queries scan less data and resources match transaction rate, concurrency, size, and latency. Efficient patterns add supporting indexes, remove costly unused indexes, normalize to avoid duplication but denormalize selectively to remove expensive joins, partition large tables and archive cold data, keep under 10,000 tables and at least 20% free disk space, and set growth controls such as autogrow in megabytes rather than percent. AlloyDB for PostgreSQL helps with automatic memory management, adaptive autovacuum, and optimized defaults, but cost still depends on index and table choices.

Partitioning splits a large table within an instance so queries prune unneeded partitions, while sharding splits a database across instances by a partition key. Choose partitioning when one primary still handles writes but scans and maintenance are growing, and choose sharding only when writes must scale horizontally and the team can manage complexity and even key distribution. AlloyDB for PostgreSQL uses disaggregated compute and storage where writes flow from the primary to regional storage and are acknowledged on WAL persistence, and data flows from buffer cache to ultra-fast cache to intelligent storage, so TPS/TPM is the sizing metric rather than IOPS.

To estimate OLTP size from TPS, first capture current TPS from the source database, then analyze critical queries including read-write mix such as 65% reads and 35% writes in the TPC-C example, then benchmark with HammerDB or pgbench, then compare to the OLTP Benchmarking Guide, then choose size with headroom at 60-70% steady-state CPU. For OLTP deploy zonal single-node or HA two-node plus read pools and a secondary cluster for distributed reads or DR. For AlloyDB Omni start from matched CPU, RAM, and disk, allocate from 8 GB RAM per vCPU, choose NVMe for performance or SAN for scalability and sharing, and note open source PostgreSQL generally does not benefit beyond 16 vCPUs. Scaling choices then trade speed against cost and changes, where scale-up relieves urgency quickly, scale-out adds read capacity but may need application routing changes, and partitioning or sharding fixes data-model cost with migration effort.

Key Facts

• Vertical scaling enlarges one Cloud SQL instance with vCPUs, memory, and storage, but memory changes require a restart, while horizontal scaling moves reads to Cloud SQL replicas, Spanner replicas, or Bigtable clusters.
• High availability uses synchronous replication within one region with zero or near-zero RTO and RPO, while cross-region disaster recovery typically uses asynchronous replication with promotion.
• Cloud Spanner ties storage allotment to compute at 1024.0 GiB per 100 PUs below 1 node and 10 TiB per node at 1 node and larger, with 1000 PUs equal to one node.
• AlloyDB secondary replication streams WAL data asynchronously, where wal_keep_size defaults to 100MB and max_wal_size defaults to 1504MB and must be set higher than wal_keep_size.
• Cloud SQL committed use discounts provide 25% off for one year and 52% off for three years on vCPUs and memory in the committed region, excluding storage, backups, network egress, and licensing.