Sizing a managed database on Google Cloud begins with the workload, not the catalog of available SKUs. The database engineer collects four families of measurements from the source system and projects them forward to the target horizon: peak and sustained queries per second separated into read and write, working-set size in gigabytes, write amplification driven by indexes and replication, and the 99th-percentile latency the application owner is willing to commit to in the service-level objective. Without these numbers any sizing exercise is a guess, and the resulting instance is either over-provisioned at painful cost or under-provisioned at painful latency. The exam consistently presents scenarios where a candidate is asked to pick between two configurations and the right answer is anchored in one of these four metrics, so the discipline of grounding capacity claims in measurements is itself a tested skill.
The second decision is which family of machine types the workload belongs to. Cloud SQL exposes general-purpose, memory-optimized, and shared-core (sandbox) machines on Enterprise edition, plus the higher-performance Enterprise Plus edition that brings faster failover and larger memory caches for Postgres and MySQL. AlloyDB sizes its primary and read-pool nodes by CPU count, which determines memory and IOPS proportionally. Spanner sizes by processing units: one node equals one thousand processing units, and the production floor sits at one hundred processing units per instance, with smaller numbers reserved for dev and test workloads. Bigtable sizes by node count per cluster, where each node provides roughly ten thousand reads or writes per second at one-row granularity under benign access patterns, and that figure halves for SSD versus HDD storage workloads with skewed access. The exam will sometimes phrase the question as "the team reports thirty thousand writes per second", and the correct answer requires translating that figure into the node count for the named service.
Working-set size is the part of the active dataset the application reads in a typical hour. When the working set exceeds available buffer-cache memory the database falls off the cache cliff and latency for the same workload doubles or triples. For Cloud SQL Postgres and MySQL the buffer cache lives inside the instance memory allocated to the database process; the rule of thumb is that the working set plus index footprint should fit in roughly seventy percent of instance memory. For AlloyDB the columnar engine adds a separate memory area that holds in-memory columnar projections of frequently scanned tables, so analytical workloads can run on hot data without touching persistent disk. For Bigtable the working set should fit in block cache; sequential row-key scans defeat the cache and create hot tablets regardless of node count.
Performing solution sizing based on current environment workload metrics and future requirements
The sizing exercise starts by pulling concrete metrics from the source environment. For an on-premises Postgres or MySQL instance the engineer captures pg_stat_statements or MySQL performance_schema rollups for the busiest weekly peak, splits the queries into read and write counts, then records the disk read and write IOPS the underlying storage carries during that peak. The engineer also records average and 99th-percentile query latency for the top twenty queries by total execution time, and the disk-resident size of the largest tables and indexes. These numbers feed directly into the Google Cloud target.
# Capture peak workload metrics from a Postgres source for sizing
psql -h source-db -U readonly -d production -c "
SELECT
SUM(calls) AS total_calls,
SUM(total_exec_time) AS total_ms,
SUM(rows) AS rows_returned,
PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY mean_exec_time) AS p99_ms
FROM pg_stat_statements
WHERE last_call > NOW() - INTERVAL '7 days';
"
# Capture peak IOPS and throughput from the host
iostat -dxm 5 60 > peak_iops.txt
The future requirement is the year-three projection, not the day-one launch number. If the application owner expects three times the user base in three years, the sized instance must absorb that growth without re-platforming. For Cloud SQL the headroom is bounded by the largest machine type the edition supports; Enterprise Plus on Postgres reaches 128 vCPUs and 864 GB of RAM in the largest tier, while Enterprise on MySQL reaches 96 vCPUs at the same memory footprint. If the year-three projection exceeds those bounds the engineer must either pick a different service (AlloyDB or Spanner) or design a sharding strategy. Spanner avoids the cliff entirely: processing units scale by addition without re-architecting the database, so a three-times growth means roughly three times the processing units in the same instance configuration.
💡 Exam Trap: Sizing answers that pick the smallest machine type that fits day-one load are almost always wrong. The exam frames sizing around the projected horizon stated in the stem, so a question about a workload that triples in two years expects the candidate to size for the three-times figure.
Evaluating performance and cost tradeoffs of different database configurations
Once the workload is characterized, the database engineer evaluates the trade-off space. The two primary cost levers in Cloud SQL are machine type and storage type. SSD persistent disk delivers low latency and high IOPS but costs roughly four times the per-gigabyte rate of HDD persistent disk; HDD persistent disk is appropriate only for cold archival workloads where average IOPS sits well under one hundred and latency targets sit in the multi-hundred-millisecond range. For any OLTP workload SSD is the correct answer. Hyperdisk Balanced is the newer storage class that decouples capacity from IOPS and throughput, allowing the engineer to provision more IOPS without adding capacity; Hyperdisk Extreme delivers the highest IOPS tier for AlloyDB and certain Cloud SQL configurations.
# Create a Cloud SQL Postgres Enterprise Plus instance sized for peak workload
gcloud sql instances create payments-primary \
--database-version=POSTGRES_16 \
--edition=ENTERPRISE_PLUS \
--tier=db-perf-optimized-N-16 \
--region=us-central1 \
--storage-type=SSD \
--storage-size=2000 \
--storage-auto-increase \
--availability-type=REGIONAL \
--backup-start-time=03:00 \
--enable-point-in-time-recovery \
--retained-transaction-log-days=7
The Edition decision (Enterprise versus Enterprise Plus on Cloud SQL Postgres and MySQL) is a cost-versus-performance trade-off. Enterprise Plus offers near-zero-downtime planned maintenance, faster failover, larger in-memory cache via Data Cache, and access to higher-end performance-optimized machine types. Workloads that pay for that lift are those with strict latency objectives, large working sets that benefit from Data Cache, or maintenance windows so narrow that standard restart-and-reload behavior would violate them. Workloads where short maintenance restarts are acceptable and where the working set fits in standard memory should stay on Enterprise.
For analytical workloads layered on top of Postgres-compatible OLTP, AlloyDB's columnar engine is the cost lever. The columnar engine projects hot columns into an in-memory columnar format and answers analytical queries from that projection rather than from row storage. Configured correctly, analytical queries that took minutes against row storage finish in seconds, which can eliminate a separate analytics database and its replication pipeline.
📊 Limit: Spanner production instances must be provisioned with at least one hundred processing units. Smaller sizes (10 PU, 25 PU, 50 PU) are intended for development and pre-production only. Multi-region Spanner instances have additional minimums that scale with the region count.
Sizing database compute and storage based on performance requirements
The translation from workload metrics to instance configuration is mechanical once the metrics are in hand. For Cloud SQL the formula starts from peak queries per second and the average query CPU cost in milliseconds. A workload that sustains five thousand queries per second with average CPU cost of two milliseconds per query requires ten thousand CPU-milliseconds per wall-clock second, which is ten vCPU-equivalents. The engineer adds headroom for replication, vacuum, and burst, picks the next standard machine type up, and validates the resulting instance memory against the working-set estimate.
# Translate measured workload into a Cloud SQL sizing recommendation
def size_cloud_sql(
peak_qps: int,
avg_cpu_ms_per_query: float,
working_set_gb: float,
headroom_factor: float = 1.6,
) -> dict:
cpu_load_vcpu = (peak_qps * avg_cpu_ms_per_query / 1000.0) * headroom_factor
memory_gb_for_cache = working_set_gb / 0.7 # cache fit at 70 percent of memory
candidate_tiers = [
("db-perf-optimized-N-2", 2, 16),
("db-perf-optimized-N-4", 4, 32),
("db-perf-optimized-N-8", 8, 64),
("db-perf-optimized-N-16", 16, 128),
("db-perf-optimized-N-32", 32, 256),
("db-perf-optimized-N-48", 48, 384),
("db-perf-optimized-N-64", 64, 512),
("db-perf-optimized-N-96", 96, 768),
("db-perf-optimized-N-128", 128, 864),
]
for name, vcpu, mem in candidate_tiers:
if vcpu >= cpu_load_vcpu and mem >= memory_gb_for_cache:
return {"tier": name, "vcpu": vcpu, "memory_gb": mem}
return {"tier": "exceeds_cloud_sql_max", "vcpu": cpu_load_vcpu,
"memory_gb": memory_gb_for_cache}
Storage sizing follows a similar discipline. The persistent disk capacity is the on-disk dataset plus a safety margin for write-ahead-log and binary-log growth (Postgres WAL or MySQL binary logs), plus the temporary space the engine needs for index rebuilds and large sorts. The IOPS ceiling on SSD persistent disk scales linearly with capacity until the disk size limit is reached; if the workload demands more IOPS than the capacity-derived ceiling, Hyperdisk Balanced is the next step. Bigtable storage sizing is bounded by node count: each Bigtable SSD node supports a few terabytes of stored data, while each HDD node supports more storage per node but lower IOPS, and exceeding those bounds requires adding nodes regardless of CPU load.
🎯 Scenario: A retail platform reports six thousand peak orders per minute, average transaction touches three tables totaling eight queries, working-set size is 180 GB, and the year-three projection is three times the current order rate. The engineer calculates peak QPS at 800, applies a 1.6 headroom factor for vacuum and replication, arrives at roughly 10 vCPU-equivalents on Postgres at 1.5 ms per query, picks
db-perf-optimized-N-16on Cloud SQL Enterprise Plus to hold three-years headroom, and provisions 1 TB of SSD persistent disk with auto-increase enabled.
⚠️ Anti-Pattern: Sizing the Cloud SQL primary to peak load while leaving read replicas at the smallest tier. Replicas must replay the primary's write stream and serve read traffic; an under-sized replica falls behind and eventually fails health checks, taking the read fleet down even though the primary is healthy.
💡 Exam Trap: Choosing HDD persistent disk for any OLTP workload to save cost. HDD storage on Cloud SQL is only acceptable for cold archive or batch-analytic workloads with sub-100 IOPS averages. OLTP workloads sized to HDD will hit the IOPS ceiling under normal load and the answer choice should be eliminated immediately.
Decision Anchor. Choose Cloud SQL Enterprise Plus with performance-optimized machine types when the workload demands large in-memory caches, near-zero-downtime maintenance, or fast failover targets. Choose Cloud SQL Enterprise with standard machine types when restart-tolerant maintenance windows are acceptable and the working set fits in standard memory tiers. Choose AlloyDB instead of either when the workload mixes OLTP and analytical access on the same Postgres-compatible schema. Choose Spanner instead when the projected scale exceeds Cloud SQL's per-instance ceiling or when strong global consistency is a requirement.