PCDOE Design and Plan Database Solutions Practice Test 8 — 15 Questions

Question 1

A company wants to migrate their on-premises PostgreSQL database to Cloud SQL. The database currently runs mixed workloads: OLTP with heavy writes and occasional complex analytical queries. They want to avoid performance impact on the transactional workload. Which approach should they take?

Accepted Answer

Create a read replica of the Cloud SQL instance and run analytical queries against the replica.. Creating a read replica of the Cloud SQL instance allows analytical queries to be offloaded to the replica, preventing resource contention (CPU, I/O, memory) with the primary OLTP workload. Cloud SQL read replicas are asynchronous and maintain a near-real-time copy of the data, ensuring the transactional workload experiences no performance degradation from heavy analytical queries.

Answer

Migrate to Cloud Spanner to handle both workloads using its analytics interface.

Answer

Use Cloud SQL with the 'analytics' tier enabled.

Answer

Use the same Cloud SQL instance but schedule analytical queries during off-peak hours.

Question 2

You are designing a Spanner schema for a global social media application that stores user posts. Each user can have millions of posts. The most common query is 'get the most recent 10 posts for a user'. Which table interleaving design minimizes latency?

Accepted Answer

Interleave the Posts table under the Users table, with user_id as parent key and post_timestamp as the child ordering key.. Option A is correct because interleaving the Posts table under the Users table in Spanner ensures that all posts for a given user are stored in the same split, co-located on the same tablet server. This allows the query for the most recent 10 posts to be served with a single, local range scan on the interleaved child table, using post_timestamp as the descending ordering key, minimizing cross-node communication and latency.

Answer

Use a secondary index on user_id in the Posts table.

Answer

Create a single table with user_id and post_timestamp as a composite primary key.

Answer

Store posts in a separate Cloud Bigtable table and use the user_id as part of the row key.

Question 3

A company uses Bigtable for time-series data with a row key format: 'deviceID#timestamp'. They notice write hotspotting on a few devices that generate high volumes of data. How should they redesign the row key to distribute writes evenly?

Accepted Answer

Add a random salt prefix to the row key: 'hash(deviceID)#deviceID#timestamp'. Hotspotting occurs when sequential keys (like timestamps) are written to the same tablet server. Salting with a hash prefix distributes writes across nodes evenly.

Answer

Reverse the timestamp: 'deviceID#reverse_timestamp'

Answer

Use a monotonically increasing integer for the row key.

Answer

Store all data in a single column family and use column qualifiers for timestamps.

Question 4

You need to size a Bigtable cluster for a workload that requires 50,000 reads per second (QPS) and 20,000 writes per second. Each read is about 1 KB, each write is about 1 KB. The data volume is 5 TB and growing. You choose SSD storage. What is the minimum number of nodes?

Accepted Answer

72 nodes. The correct answer is A (72 nodes) because Bigtable's SSD nodes provide approximately 10,000 read QPS per node (for 1 KB reads) and 10,000 write QPS per node (for 1 KB writes). With 50,000 reads and 20,000 writes, the read requirement dominates, needing 5 nodes for reads, but writes require 2 nodes. However, Bigtable's architecture requires a minimum of 3 nodes for replication and availability, and the total throughput must be scaled to account for node overhead and growth. The calculation: (50,000 reads / 10,000) = 5 nodes for reads, (20,000 writes / 10,000) = 2 nodes for writes, but the combined load and Bigtable's recommendation for SSD nodes (each handling ~1,000 QPS per core, with 30 cores per node) yields 72 nodes when factoring in the 5 TB data volume (each SSD node stores ~70 GB usable) and growth.

Answer

50 nodes

Answer

7 nodes

Answer

5 nodes

Question 5

A team is migrating a relational database to Bigtable. The existing schema uses foreign keys to join orders, customers, and products. Which data model approach is most suitable for Bigtable?

Accepted Answer

Denormalize orders, customers, and products into a single table with a composite row key.. Bigtable is a wide-column NoSQL database optimized for high-throughput, low-latency access, and it does not support SQL-style joins or secondary indexes in the traditional relational sense. Denormalizing orders, customers, and products into a single table with a composite row key (e.g., customer_id#order_id#product_id) allows all related data to be co-located and retrieved with a single row scan, eliminating the need for joins and aligning with Bigtable's key-value access pattern.

Answer

Store each entity in a separate table and use secondary indexes.

Answer

Use Cloud SQL as a lookup table for joins.

Answer

Keep the normalized structure and use MapReduce to perform joins.

Question 6

A startup needs a fully managed relational database for their e-commerce platform with high availability, automatic failover, and read replicas. They expect moderate traffic and want to minimize operational overhead. Which Google Cloud service should they use?

Accepted Answer

Cloud SQL. Cloud SQL offers fully managed MySQL, PostgreSQL, and SQL Server with high availability and read replicas. It is the best fit for moderate-traffic OLTP workloads.

Answer

Cloud Spanner

Answer

Firestore

Answer

Bigtable

Question 7

You are designing a Cloud SQL for PostgreSQL database. The application has a table with 1 million rows that is frequently queried using equality on the 'email' column and range queries on the 'created_at' column. Which index strategy minimizes query latency?

Accepted Answer

Create a composite B-tree index on (email, created_at).. Option B is correct because a composite B-tree index on (email, created_at) allows the database to satisfy both the equality condition on 'email' and the range condition on 'created_at' in a single index scan. PostgreSQL can use the leftmost column for equality filtering and then traverse the index tree to retrieve the range portion efficiently, minimizing random I/O and query latency.

Answer

Create a full-text index on email.

Answer

Create a B-tree index on email only.

Answer

Create separate B-tree indexes on email and created_at.

Question 8

A global gaming company uses Spanner to store player profiles and scores. The most common query is 'Get the top 10 players by score' across all regions. The 'Players' table has millions of rows. Which schema design and query approach provides the best performance?

Accepted Answer

Add a secondary index on score column and query 'SELECT * FROM Players ORDER BY score DESC LIMIT 10'.. Option D is correct because a secondary index on the `score` column allows Spanner to perform an index scan in descending order, retrieving only the top 10 rows without scanning the entire `Players` table. The `ORDER BY score DESC LIMIT 10` query leverages the index's sorted structure, making it the most efficient approach for this common query pattern in a globally distributed database.

Answer

Add a generated column storing the score as a string and index it.

Answer

Use a STORING clause to store additional columns in the index.

Answer

Use a parent-child interleaving between a 'Leaderboard' parent table and 'Players' child table.

Question 9

A company runs a MySQL database on-premises and wants to migrate to Cloud SQL with minimal downtime. They have a multi-terabyte database. Which migration method is most appropriate?

Accepted Answer

Use Database Migration Service with continuous replication.. Database Migration Service (DMS) supports continuous replication and minimal downtime for large databases. It handles initial snapshot and ongoing CDC.

Answer

Use Cloud SQL's import feature from a CSV file.

Answer

Take a physical backup and restore to Cloud SQL.

Answer

Use mysqldump to export and then import into Cloud SQL.

Question 10

Which Spanner feature allows you to add a new column to an existing table without blocking writes or requiring a rebuild?

Accepted Answer

Online DDL. Online DDL (Data Definition Language) in Spanner allows schema changes such as adding a new column to an existing table without blocking writes or requiring a full table rebuild. This is achieved through a non-blocking, multi-phase schema update process that applies changes in the background while the table remains fully available for reads and writes.

Answer

Optimistic locking

Answer

Interleaved tables

Answer

Schema versioning

Question 11

You need to design a Bigtable row key for a time-series application that records temperature readings from thousands of sensors. The most common query is 'get all readings for a specific sensor in the last hour'. Which row key design is optimal?

Accepted Answer

sensorID#reverse_timestamp. Option C is optimal because it groups all readings for a sensor together (via sensorID as the row key prefix) while using reverse timestamps to ensure the most recent data appears first within each row. This design allows Bigtable to efficiently scan a single row for the last hour's readings using a prefix scan on sensorID with a timestamp range filter, minimizing the number of rows accessed.

Answer

timestamp#sensorID

Answer

sensorID#timestamp

Answer

hash(sensorID)#timestamp

Question 12

An e-commerce platform uses Cloud SQL for PostgreSQL with max_connections set to 500. They plan to increase the number of application instances requiring connections. The instance has 8 vCPUs and 32 GB RAM. What is the maximum number of connections Cloud SQL can support based on the default formula?

Accepted Answer

2048. Cloud SQL for PostgreSQL uses a default formula to calculate max_connections: `max_connections = 4 * vCPUs + 100`. With 8 vCPUs, this gives 4 * 8 + 100 = 132. However, the question asks for the maximum number of connections Cloud SQL can support based on the default formula, which is capped at 2048 for PostgreSQL instances. Option A (2048) is correct because it represents this hard upper limit enforced by Cloud SQL, regardless of the formula's raw output.

Answer

4096

Answer

1024

Answer

500

Question 13

A company is evaluating Google Cloud databases for a new application that requires: (1) strong global consistency across multiple regions, (2) the ability to run complex analytical queries on the same data as the transactional workload, and (3) high write throughput. Which TWO databases should they consider?

Accepted Answer

AlloyDB. AlloyDB is correct because it provides strong global consistency across multiple regions via its AlloyDB Omni and cross-region replication features, supports both transactional and analytical workloads on the same data through its columnar engine for HTAP (Hybrid Transactional/Analytical Processing), and delivers high write throughput with up to 100,000 writes per second on a standard cluster. This makes it ideal for applications needing real-time analytics on transactional data without data movement.

Answer

Bigtable

Answer

BigQuery

Answer

Firestore

Question 14

You are migrating a 5 TB MySQL database to Cloud SQL. The migration must have minimal downtime. Which THREE steps should you include in your migration plan?

Accepted Answer

Configure continuous replication between on-premises and Cloud SQL.. Option A is correct because configuring continuous replication (e.g., using Database Migration Service with CDC) ensures that changes made on the on-premises MySQL database are continuously applied to Cloud SQL, keeping them in sync with minimal lag. This is essential for achieving minimal downtime, as it allows the final cutover to be nearly instantaneous.

Answer

Take a full backup and restore to Cloud SQL.

Answer

Use mysqldump to export the database.

Question 15

You are designing a Spanner schema for a financial application that stores transactions for user accounts. To avoid hotspots and optimize performance, which TWO practices should you follow?

Accepted Answer

Use the STORING clause in secondary indexes to include frequently accessed columns.. Option B is correct because the STORING clause in a Spanner secondary index allows you to include non-key columns directly in the index, enabling index-only scans that avoid a back-join to the base table. This reduces read latency and resource consumption, which is critical for high-throughput financial transaction queries.

Answer

Store all data in a single table without interleaving.

Answer

Use a monotonically increasing integer as the primary key.

Answer

Create secondary indexes without the STORING clause.