PCDE Design innovative, scalable, and highly available cloud database solutions Practice Test 6 — 15 Questions

Question 1

A company is designing a Cloud Spanner database for a global supply chain application. The schema includes a table 'Shipments' with columns: shipment_id (INT64), created_at (TIMESTAMP), origin (STRING), destination (STRING), status (STRING). The application frequently queries for shipments by origin and status. Which three design choices optimize query performance? (Choose THREE.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use the STORING clause in the index to include frequently accessed columns.. Option B is correct because Cloud Spanner secondary indexes support a STORING clause that appends extra columns to the index, letting queries that filter on the indexed keys retrieve columns like destination or created_at directly from the index without an extra lookup back to the base table. Option C is correct because interleaving a child table such as OrderItems under Shipments physically co-locates related rows on the same split, so parent-child joins are executed locally without distributed lookups, improving join performance. Option D is correct because creating a secondary index on (origin, status) matches the application's most frequent predicate pattern, allowing Cloud Spanner to seek directly to the relevant index entries instead of scanning the Shipments table. Option A is not appropriate because Cloud Spanner does not support user-defined table partitioning by region; data placement is managed automatically through splits and replication. Option E is not appropriate because a monotonically increasing integer primary key concentrates new writes at the end of the key space, creating a hotspot that limits write throughput rather than improving it.

Answer

Partition the table by region to limit scans.

Answer

Use a monotonically increasing integer primary key for high write throughput.

Question 2

A retail company is deploying a global inventory system in Cloud Spanner. The primary key of the 'Products' table is a monotonically increasing integer (product_id) generated by the application. As the business expands, the team observes that writes are concentrated on a single Spanner server, causing hotspots and increased latency. They need to distribute writes evenly across all servers without changing the application's logic for generating product_id. What should they do?

Accepted Answer

Use a hash of the product_id as the first part of the primary key, and store the original product_id as a separate column.. The correct solution is to hash the product_id and use it as the leading part of the primary key. This evenly distributes writes across Spanner servers, eliminating hotspots. Keeping the original product_id as a separate column allows the application to continue using it without changes. This approach is a standard best practice for Spanner schema design when dealing with monotonic keys.

Answer

Change the primary key to a randomly generated UUID and update the application to use it as the product identifier.

Answer

Create a secondary index on the product_id column to offload write traffic.

Answer

Enable Spanner's automatic sharding feature by setting the table's schema to 'AUTO_SHARD'.

Question 3

A financial services firm is designing a Cloud Spanner database for a global ledger application. The application requires strong consistency and high availability across three continents. The team must ensure that read-write transactions can tolerate the failure of an entire region without data loss and with minimal downtime. They plan to use a multi-region Spanner instance. Which configuration should they choose to meet these requirements?

Accepted Answer

A multi-region instance with three read-write regions and no witness region.. The correct configuration is a multi-region instance with three read-write regions. This setup allows Spanner to maintain a quorum for writes even if one region fails, ensuring continuous availability and strong consistency. The other options either lack sufficient read-write regions or rely on witness regions, which may not provide seamless failover.

Answer

A multi-region instance with one read-write region and two read-only regions.

Answer

A regional instance with three replicas in different zones within a single region.

Answer

A multi-region instance with two read-write regions and one witness region.

Question 4

A retail company runs its order management system on Cloud SQL for PostgreSQL in a single zone. The database experiences a sudden spike in write traffic during flash sales, causing high write latency and occasional connection timeouts. The company wants to improve write scalability and availability without changing the application's SQL queries or schema. They need a solution that provides automatic failover and read replicas. What should they do?

Accepted Answer

Enable high availability (HA) on the Cloud SQL instance and add read replicas to offload read traffic.. The correct solution is to enable high availability and add read replicas. This maintains the existing SQL schema and queries while improving availability through automatic failover and scaling reads horizontally. It directly addresses the write latency by reducing load on the primary instance, and it is a native Cloud SQL feature that requires minimal application changes.

Answer

Migrate the database to Cloud Spanner and use its horizontal scaling capabilities.

Answer

Upgrade the Cloud SQL instance to a larger machine type with more vCPUs and memory.

Answer

Migrate the database to Bigtable and use its high write throughput.

Question 5

A startup is building a mobile app backend on Google Cloud. They need a fully managed, globally distributed database that scales horizontally and provides low-latency reads and writes for users worldwide. They want to minimize operational overhead and pay only for what they use. Which database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is designed for global applications requiring horizontal scalability, strong consistency, and low latency. It is fully managed, so the startup avoids operational overhead. Its global distribution and automatic replication ensure low-latency access for users worldwide. The pay-per-use model fits a startup's budget. Other options lack either global distribution, horizontal scalability, or strong consistency.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Bigtable

Answer

Firestore

Question 6

A company operates a multi-tenant SaaS application on Cloud SQL for PostgreSQL. They need to scale read traffic across tenants without application changes and minimize connection overhead. The application uses a single database connection pool. They want to distribute read-only queries to replicas while ensuring writes go to the primary. What should they do?

Accepted Answer

Deploy a proxy such as PgBouncer with read/write splitting configured to send reads to replicas and writes to the primary.. Scaling read traffic in Cloud SQL while minimizing connection overhead typically involves a connection pooler that can also perform read/write splitting. PgBouncer, combined with routing logic, can direct reads to replicas and writes to the primary, and it pools connections to reduce overhead. This solution requires minimal application changes and leverages Cloud SQL read replicas effectively.

Answer

Use Cloud SQL's built-in read/write splitting feature by setting the connection parameter 'readOnly' to true.

Answer

Enable the Cloud SQL Auth proxy and configure it to route queries based on SQL statement type.

Answer

Configure Cloud SQL read replicas and use a load balancer to distribute connections.

Question 7

A media company uses Cloud Spanner for its user authentication service. The `Users` table has a monotonically increasing integer `UserID` as the primary key, and the application inserts new users at a rate of 500 per second. During peak hours, the team observes high write latency and hotspotting on the Spanner instance. They want to reduce write latency and avoid hotspots without changing the application's read patterns, which always query by `UserID`. What should they do?

Accepted Answer

Use a composite primary key with the original UserID and a hash of the UserID as the first key part.. Spanner distributes data by primary key ranges, and a monotonically increasing key causes all new inserts to target the same range, creating a hotspot. Prefixing the key with a hash of UserID spreads writes across many ranges while still allowing efficient lookups by UserID. This preserves the application's read pattern and directly mitigates the write latency issue.

Answer

Change the primary key to a UUID generated by the application.

Answer

Increase the number of Spanner nodes in the instance.

Answer

Enable a secondary index on the UserID column.

Question 8

A retail company runs its order management system on Cloud SQL for PostgreSQL in a single region. They need to add a read replica in a different region to serve analytics queries and provide disaster recovery. The application connects using the Cloud SQL Auth Proxy. The replica must be in a different region, and the primary instance is configured with high availability. What should they do?

Accepted Answer

Create a cross-region read replica using the Cloud SQL console or gcloud sql instances create --master-instance-name.. Cloud SQL for PostgreSQL allows creating a read replica in a different region by specifying the primary instance. This replica receives asynchronous updates and can serve read-only queries. It also acts as a disaster recovery target. The other options either do not provide a live replica, are in the same region, or use unsupported methods.

Answer

Configure a failover replica in the same region and use it for analytics queries.

Answer

Export the database to a Cloud Storage bucket and import it into a new instance in another region.

Answer

Use Cloud SQL for PostgreSQL with a cross-region replica created via pglogical logical replication.

Question 9

A financial analytics firm ingests millions of events per second into Bigtable. The events are keyed by a composite row key: reversed timestamp (to enable newest-first scans) followed by a random UUID. The team notices that read performance for the most recent data is excellent, but scans of historical data (older than one week) are slow and often time out. They need to improve historical read performance without changing the write pattern or the row key structure. What should they do?

Accepted Answer

Create a new table with a row key that includes a bucket identifier (e.g., week number) and copy historical data there.. The correct approach is to create a new table with a bucketed row key for historical data. This reorganizes the data so that scans of historical periods access contiguous row ranges, reducing the number of tablets touched and improving performance. It respects the constraint of not altering the existing write pattern or row key structure for the primary table.

Answer

Increase the number of Bigtable nodes in the cluster to handle the additional read load.

Answer

Enable a column family filter to limit the data scanned during historical queries.

Answer

Use a Bigtable row filter to limit the scan to a specific time range.

Question 10

A global e-commerce platform stores customer orders in Cloud Spanner. The orders table has a monotonically increasing order_id as the primary key, and the application inserts about 10,000 orders per second. The operations team notices that write latency is high and hotspotting occurs on a few Spanner splits. They need to redesign the primary key to distribute writes evenly across splits while still allowing efficient lookups by order_id. What should they do?

Accepted Answer

Use a composite primary key with the first column being a hash of the order_id, followed by the order_id itself.. Monotonically increasing primary keys cause hotspots in Cloud Spanner because inserts concentrate on the last split. Hashing the order_id and using it as the leading key column spreads writes across many splits. The original order_id remains in the key, so lookups by order_id can still be efficient via a secondary index or by computing the hash. This is the recommended pattern for high-write workloads with sequential keys.

Answer

Keep the order_id as the sole primary key but increase the number of Spanner nodes to handle the write load.

Answer

Change the primary key to a randomly generated UUID and store the order_id in a separate column with a secondary index.

Answer

Add a TIMESTAMP column as the first column of the primary key and use the order_id as the second column.

Question 11

A healthcare company stores patient records in Cloud Spanner. They need to ensure that queries by patient ID are fast and that related data such as appointments and prescriptions are co-located to minimize network hops. They also want to avoid hotspots on writes. What schema design should they use?

Accepted Answer

Create a patients table with patient_id as the primary key, and interleave appointments and prescriptions tables under it using patient_id as the prefix.. Interleaving child tables under the patients table co-locates related rows, so queries for a patient's appointments and prescriptions can be satisfied with a single scan of the parent row and its interleaved children. This minimizes network latency and avoids hotspots because writes are distributed across different patient IDs.

Answer

Create a patients table with a randomly generated primary key, and use secondary indexes on patient_id for appointments and prescriptions.

Answer

Create separate tables for patients, appointments, and prescriptions, each with a primary key that includes patient_id and a random UUID to distribute writes.

Answer

Use a single table with all patient, appointment, and prescription data denormalized, and a primary key of patient_id.

Question 12

A team is designing a Firestore in Native mode database for a mobile app that requires low-latency reads and writes globally. They need to ensure strong consistency for user profile updates and support complex queries with multiple filters. They also want to minimize costs. Which two design considerations should they prioritize? (Choose two.)

Accepted Answer

Use composite indexes to support queries with multiple filters and orderings.. For Firestore in Native mode, supporting complex queries with multiple filters requires composite indexes. Denormalizing data reduces the need for multiple reads and avoids unsupported joins, which lowers latency and cost. These two practices directly address the requirements for low-latency, complex queries, and cost efficiency, while other options either increase cost or are unnecessary.

Answer

Implement sharding by dividing user data across multiple Firestore databases.

Answer

Enable multi-region replication for the Firestore database to ensure global low latency and strong consistency.

Answer

Use server-side timestamps for all write operations to ensure strong consistency across regions.

Question 13

A financial services firm uses Cloud Spanner for a global ledger application. They need to design a schema that minimizes latency for queries that read transactions by account ID and date range. The primary key currently is (AccountId, TransactionId) where TransactionId is a monotonically increasing integer. They observe hotspots on writes. What should they do to improve write scalability while maintaining efficient range reads?

Accepted Answer

Change the primary key to (ShardId, AccountId, TransactionId) where ShardId is a random integer from 0 to 99.. Introducing a random ShardId as the first part of the primary key distributes writes across multiple key ranges, mitigating hotspots. For reads by AccountId and TransactionId, the application can query all shards in parallel or include ShardId if known. This approach maintains the ability to perform range scans within each shard. The other options either do not address write distribution or harm read efficiency.

Answer

Keep the primary key as (AccountId, TransactionId) and use a timestamp instead of an integer for TransactionId.

Answer

Change the primary key to (AccountId, TransactionId) but use a hash of TransactionId as the first key part.

Answer

Use a composite primary key of (AccountId, TransactionId) and enable interleaving for the transactions table.

Question 14

A global ride-hailing company uses Cloud Spanner for its trip-matching service. The `Trips` table has a monotonically increasing `TripId` as the primary key, and writes are concentrated on the latest trips. During peak hours, writes to the last few key ranges cause hotspots and increased latency. The team must reduce hotspots while preserving the ability to query trips by `TripId` directly. What should they do?

Accepted Answer

Use a composite primary key with a hash of `TripId` as the first column and `TripId` as the second column.. A monotonically increasing primary key concentrates writes on the last key range. Prefixing the key with a hash of `TripId` distributes writes across many ranges, while keeping `TripId` as a trailing column allows point lookups when the hash is computed. The other options either keep the hotspot, use nonexistent features, or add complexity without solving the root cause.

Answer

Enable automatic sharding by setting the table option `sharding=auto`.

Answer

Use a composite primary key with `TripId` as the first column and a hash of `TripId` as the second column.

Answer

Add a secondary index on `TripId` and write to a separate table with a random key.

Question 15

A company is designing a globally distributed Cloud Spanner database for a multiplayer game. They need to ensure low-latency reads and writes for players across North America, Europe, and Asia. They also want to minimize costs while meeting a 99.999% availability SLA. Which two configurations should they use? (Choose two.)

Accepted Answer

Choose a leader region that is geographically central to the player base, such as Europe.. To achieve low latency and high availability, use a multi-region instance with read-write replicas in all three regions. This ensures that a leader can be elected from any region and reads can be served locally. Additionally, choosing a central leader region minimizes write latency for all players. These two choices together provide the best balance of performance, availability, and cost.

Answer

Enable automatic scaling to handle peak loads and reduce costs during off-peak times.

Answer

Use a regional instance in each continent and synchronize data with a custom replication solution.

Answer

Use a multi-region instance with read-only replicas in two regions and a read-write replica in one region.