Design, migrate, and run scalable, highly available databases on Google Cloud across relational and NoSQL services, and track your readiness for the Professional Cloud Database Engineer exam.
Prepare and test your skills
Prepare and test your skills
Query execution analysis and indexing strategies are the practices of finding expensive queries and reducing the work they perform. Query Insights (opens in a new tab) is the observability dashboard in Cloud Spanner, Cloud SQL, and AlloyDB for PostgreSQL (opens in a new tab) that measures query load and surfaces the queries responsible for it. Strategic indexing then reduces input/output overhead and latency by letting queries fetch fewer pages and avoid full scans.
Query Insights dashboards show query load for a selected database and time range to connect application behavior to resource cost. Query load is the total central processing unit (CPU) utilization for all queries in the instance, measured as CPU seconds per minute over the selected window. The dashboard aggregates this load so a spike in query CPU that matches a spike in instance CPU points to one or more expensive queries rather than infrastructure noise.
The dashboard exposes the same filtering pattern across services to narrow that load by source and time. Filters select a specific database or all databases, a time range from hours to days or a custom range, and dimensions such as user, client address, tag, or request tag. Use query parameters or query tags consistently, because unparameterized statements create too many distinct entries and the top-queries table may fail to load.
The core views on the dashboard are:
Exam tip: Normalize statements with query parameters and tags or the TopN queries table can return too many results and fail to load.
Bottleneck analysis in Cloud SQL and AlloyDB for PostgreSQL starts with the database load graph split by wait category. The dark blue line shows CPU capacity, which is the number of CPUs available on the instance, so load above that line means demand exceeds provisioned compute. CPU load can appear above the maximum core line because it counts both runtime and time waiting for the Linux scheduler while other processes use the CPU.
The wait categories separate what queries are doing while time passes from source to completion. CPU and CPU Wait is the ratio of time queries spend in an active state to wall-clock time, where input/output (I/O) and lock waits do not block the active state. IO Wait is the ratio of time queries spend waiting for I/O to wall-clock time, including read and write I/O waits. Lock Wait is the ratio of time queries spend waiting for locks to wall-clock time, including lock waits, lightweight lock waits, and buffer-pin lock waits.
A repeatable triage order moves from system load to a single fingerprint:
Active query views show the queries running right now and the longest-running transactions that can explain current latency and high CPU. In AlloyDB for PostgreSQL, active query analysis requires enabling advanced Query Insights features and Active Query Analysis, then confirming a restart when prompted. The Active queries view then summarizes all normalized active queries and lists the top 50 longest-running transactions in active and idle in transaction states ordered by descending execution time.
A normalized query removes sensitive literal values and returns a digest so identical shapes share one entry. Two statements such as select * from test_table where id=1; and select * from test_table where id=2; both normalize to select * from test_table where id=?;. The summary scorecard groups these digests by connection state, wait-event type, and transaction duration, and filters isolate them by database, user, and for read pools by node identifier.
Deep investigation connects a slow normalized query to a cause and a next action. In Cloud SQL Enterprise Plus edition, an anomaly in the 24 hours before the end of the selected range marks the query with a Warning indicator and a slower than usual message. Starting a Gemini Cloud Assist investigation opens an Investigation details pane with Issue describing the time window, Observations such as longer-than-expected lock-wait ratio, and Hypotheses listing recommended remediation actions.
Strategic indexing reduces I/O overhead and query latency by giving the optimizer a narrow access path to only the needed rows. Supporting indexes let critical queries avoid full scans and fetch fewer data pages, which lowers CPU seconds per minute, I/O wait, and average execution time at the same time. Non-critical indexes have the opposite effect because every write must maintain them, so removing unused indexes and unnecessary foreign-key indexes reduces write cost and storage growth.
Index health depends on maintenance that keeps statistics and physical order aligned with changing data. Outdated statistics cause the optimizer to choose suboptimal plans, while fragmentation from frequent updates slows range scans and wastes pages. Regular maintenance keeps plans stable and pages dense without requiring larger instances to compensate for inefficiency.
Apply the following maintenance and tuning actions together to sustain low-latency plans:
Query refactoring and cost control is the practice of rewriting SQL and application logic so each query does less work and its resource cost can be measured. Across Cloud SQL (opens in a new tab), Cloud Spanner, and AlloyDB for PostgreSQL (opens in a new tab), the same pattern applies: refactor joins and access paths to scan less data, group identical statements with parameters so they can be analyzed together, and use Query Insights (opens in a new tab) to find the queries with the highest CPU and execution cost.
Query refactoring in Cloud SQL means checking slow queries and their execution plans and then changing the SQL or supporting indexes so the plan touches fewer rows. A tuned query uses available indexes instead of full index scans, avoids long waits on locks and other resources, and matches the data model with correct column types and table structure. Because compute, I/O, and maintenance all scale with the amount of data scanned and written, a query that scans less data costs less to run.
Refactoring work centers on the objects the slow query touches:
Adding an index speeds the reads it supports but increases write and maintenance work, while removing unused indexes and foreign keys lowers that overhead. Statistics updates matter because outdated statistics can cause the optimizer to choose a suboptimal plan that scans far more data than necessary.
A parameterized query is a SQL statement that uses placeholders for literal values so repeated executions share one plan and one observable identity. Query Insights and fleet monitoring normalize statements by removing sensitive values and returning a digest, so two statements that differ only in WHERE-clause values share the same digest. For example, select * from test_table where id=1; and select * from test_table where id=2; both normalize to select * from test_table where id=?;.
Normalization lets the database reuse work and lets operators see total cost per query shape instead of thousands of separate literals. Tagging queries with request tags provides the same grouping benefit for application-level operations. Without parameters or tags, too many distinct query strings can return and the TopN queries table may not load properly.
Exam tip: Use query parameters or request tags to keep the TopN queries table usable; unparameterized literals create a separate entry per value and can prevent the table from loading.
Query Insights is the dashboard for measuring query load, defined as total CPU utilization for all queries in an instance over the selected time range. Query load is reported as an average rate in CPU seconds per minute, shown both as an aggregate for all queries and broken out per query or request tag. Monitoring flows from total load to the specific fingerprint that causes it.
Long-running work is tracked separately in the active queries view, which shows the top 50 longest running transactions in the active and idle in transaction states ordered by execution time. The Database Center Queries tab extends the same normalized-query model across a fleet of AlloyDB and Cloud SQL resources, showing how many resources use each digest, which product and project they belong to, and how total time, average time, and row counts distribute per resource.
Reducing scanning means limiting the data each query must read, keep in memory, and move through the system. Partitioning tables, archiving data that is no longer needed, and keeping the working set in cache all lower CPU, memory, and I/O consumption for the remaining queries. In AlloyDB for PostgreSQL, data moves automatically through a multi-tier cache between buffer cache, ultra-fast cache, and the intelligent storage engine, so transactions per second and transactions per minute are more meaningful sizing signals than raw IOPS.
| Option | What it reduces | Tradeoff supported by the documentation |
|---|---|---|
| Partition and archive tables | Full-table scans by pruning partitions and removing cold data | Requires more effort than tuning and may involve data migration, but provides a long-term fix |
| Route reads to a read replica | Load on the primary by sending read queries to a replica | Improves overall workload performance but may require application changes to connect to the replica |
| Shard the database | Write and read pressure by splitting one instance into smaller instances by customer or subject criteria | Scales both writes and reads but increases application complexity and can create unbalanced hotspots |
Vertical scaling that adds vCPU, RAM, and disk throughput can resolve an urgent slowdown, but it does not reduce per-query scan cost and leaves the high-cost operation in place. Lasting cost control comes first from rewriting the query and its access path, then from isolating the remaining load with replicas, partitions, or shards only after tuning no longer closes the gap.
Schema optimization and resource management is the practice of designing tables and choosing instance sizes so queries scan less data and compute resources match the workload. A well-designed schema reduces I/O, locking, and cross-node traffic, while right-sized CPU, memory, and storage deliver required throughput without paying for idle capacity. In Google Cloud, this work spans Cloud SQL (opens in a new tab), AlloyDB for PostgreSQL (opens in a new tab), and AlloyDB Omni (opens in a new tab), where the same principles apply: keep the data model efficient, distribute large data deliberately, and scale only as far as the workload requires.
Schema design for query cost starts with tables that fit the workload type. Columns should use the correct data types, tables should be properly structured, and slow queries should be checked to confirm they use available indexes and avoid unnecessary scans, locks, and waits on other resources. AlloyDB for PostgreSQL helps with automatic memory management, adaptive autovacuum, and optimized built-in performance settings, but query cost still depends on decisions such as which indexes to keep and how tables are organized.
Efficient patterns balance normalization, indexing, and data lifecycle:
autogrow in megabytes rather than as a percentage and manage growth proactively before the threshold is reached.When fragmentation or stale plans raise cost, scheduled maintenance restores performance. Index defragmentation, statistics updates, vacuum analyze, and reindexing of heavily updated tables keep the optimizer choosing efficient plans, especially after large data changes or schema changes such as adding a column.
Partitioning and sharding distribute large datasets to keep queries local and avoid expensive cross-node communication. Partitioning splits a large table within an instance so queries prune unneeded partitions, while sharding splits a database across multiple smaller instances by a partition key such as customer or subject. Partitioning is a long-term fix that usually requires data migration, and sharding is a larger re-architecture that changes both the database and the application.
Choose partitioning when a single instance can still handle write throughput but scans and maintenance are growing too costly. Choose sharding only when writes must scale horizontally beyond one primary, because sharding lets the system scale both writes and reads but increases application complexity and migration effort. A sharded design depends on an even partition key, because an unbalanced key creates hotspots where one shard carries most of the load and cancels the benefit of distributing the data.
Sizing resources to workload demands means matching vCPU, memory, and storage to transaction rate, concurrency, data size, and latency requirements. AlloyDB for PostgreSQL uses disaggregated compute and storage where writes are acknowledged as soon as write-ahead log (WAL) files persist to regional storage while block materialization is offloaded to storage. Data flows from buffer cache to ultra-fast cache to the intelligent storage engine through a multi-tier cache, so input/output operations per second (IOPS) is not a useful comparison metric and transactions per second (TPS) or transactions per minute (TPM) is the primary sizing metric.
For online transaction processing (OLTP), deploy as a zonal single-node instance or as a highly available instance with two nodes, and add read pool instances plus a secondary cluster in another region for geographically distributed reads or disaster recovery (DR). Before choosing a size, determine workload type such as OLTP, online analytical processing (OLAP), or hybrid transactional/analytical processing (HTAP), plus performance requirements, total and active data size, and expected growth over time. To estimate size from TPS:
For AlloyDB Omni, start from the source system as the baseline by matching CPU, RAM, and disk resources, then reduce resources only after testing proves the smaller size still meets response-time goals. Allocate memory starting from 8 GB of RAM per vCPU for shared buffers and working memory, choose local NVMe storage for performance or SAN storage for scalability and sharing, and provision capacity for data, indexes, WAL, backups, and growth. Open source PostgreSQL generally does not benefit beyond 16 vCPUs, so scale core count from workload concurrency and computation needs and account for gains from a newer CPU generation or platform.
Exam tip: Use TPS/TPM, not IOPS, as the primary sizing comparison for AlloyDB for PostgreSQL because its multi-tier cache automatically places data across cache tiers and storage.
Scaling and ongoing maintenance keep throughput high while controlling operational expense. Vertical scale-up adds disk throughput, vCPU, and RAM to an instance and resolves most urgent performance issues quickly, while horizontal scale-out routes reads to read replicas or a read pool to relieve the primary. Scale-up is fast but increases cost per instance, and scale-out improves read capacity but may require application changes to connect reads to the replica.
| Approach | What it scales | Choose when |
|---|---|---|
| Scale up infrastructure | CPU, memory, and disk throughput on one instance | Urgent slowness must be relieved quickly before root-cause tuning |
| Scale out reads | Read throughput to read replicas or read pool nodes | Vertical scaling is no longer enough and reads can be routed separately |
| Re-architect with partitioning | Scan efficiency and maintenance on large tables | Poor data-model design needs a long-term fix plus possible migration and archiving |
| Shard across instances | Both writes and reads across many instances | Write scale must grow horizontally and the team can manage complexity and hotspot risk |
Storage and parallelism choices also affect cost and speed. A disk size of 4 TB or greater provides more throughput and IOPS, and higher vCPU provides more IOPS and throughput while requiring monitoring for parallelism waits. Issue I/O in parallel for higher queue depth, reorganize or rebuild indexes on a schedule tied to data change rate, set an appropriate fill factor to reduce fragmentation, and monitor for missing indexes that would improve plans.
id=1 and id=2 into id=?.Last updated