PCDE Design and implement database schemas Practice Test 5 — 15 Questions

Question 1

A Cloud SQL for PostgreSQL database experiences lock contention during heavy concurrent writes on a single table. Which schema design change can most effectively reduce contention?

Accepted Answer

Partition the table by a key that spreads write load. Partitioning the table by a key that spreads write load (e.g., a hash of the user ID or timestamp) reduces lock contention because each partition is a separate physical storage unit with its own lock manager. Concurrent writes targeting different partitions can proceed in parallel without blocking each other, directly addressing the contention on a single table.

Answer

Deploy read replicas to offload reads

Answer

Use a connection pooler like PgBouncer

Answer

Create materialized views for read queries

Question 2

A Cloud Spanner database needs to add a column 'discount' to the 'Products' table without any downtime. The table is actively used. What is the correct approach?

Accepted Answer

Execute ALTER TABLE Products ADD COLUMN discount FLOAT64. Cloud Spanner supports online schema changes via ALTER TABLE without downtime. The operation is performed asynchronously in the background, allowing the table to remain fully available for reads and writes. The new column 'discount' is automatically populated with NULL for existing rows. Option A is incorrect because creating a new table and copying data introduces downtime and complexity. Option C is incorrect because a secondary index cannot add a column; it only indexes existing columns. Option D is incorrect because a generated column is derived from other columns, not used to add a new independent column.

Answer

Create a new table with the column and copy data over

Answer

Create a secondary index that includes the new column

Answer

Define a generated column based on an existing column

Question 3

Which TWO are best practices for designing a Cloud Spanner schema?

Accepted Answer

Use commit timestamp columns to track row versions. Cloud Spanner's commit timestamp columns allow you to automatically track the time of the last write to a row, which is essential for implementing optimistic concurrency control, ordering versions, and building change data capture pipelines. This feature leverages the TrueTime API to provide globally consistent timestamps, making it a best practice for versioning and auditing.

Answer

Avoid secondary indexes to keep writes faster

Answer

Use monotonically increasing primary keys

Answer

Store all related data in a single row to avoid joins

Question 4

Which THREE are considerations when designing a schema for Cloud Firestore?

Accepted Answer

Use subcollections to organize related data. Subcollections in Cloud Firestore allow you to organize related data hierarchically within a document, enabling efficient queries and scalability without hitting the 1 MiB document size limit. This structure is ideal for data like user posts or product reviews, where each subcollection can grow independently.

Answer

Use nested maps for deeply structured data

Answer

Always use transactional writes to ensure consistency

Question 5

Which TWO techniques can help avoid hot spotting in a Cloud Spanner table?

Accepted Answer

Add a hash of the primary key as the first part of the key. Adding a hash of the primary key as the first part of the key distributes writes evenly across Cloud Spanner's split boundaries. This prevents hot spotting, which occurs when monotonically increasing keys cause all new writes to land on the same tablet server. By hashing the key, writes are spread across multiple nodes, avoiding contention.

Answer

Use a monotonically increasing integer as the key

Answer

Use interleaved tables to distribute writes

Answer

Create a secondary index on a high-cardinality column

Question 6

Based on the exhibit, what is the primary key of the Readings table?

Accepted Answer

(SensorId, Timestamp). The Readings table captures sensor measurements over time, so the natural primary key is the combination of SensorId and Timestamp, which uniquely identifies each reading. This is a classic example of a composite primary key in relational database design, ensuring that no two readings from the same sensor at the same time can exist.

Answer

(SensorId, SensorType)

Answer

(SensorId)

Answer

(ReadingsId)

Question 7

Refer to the exhibit. Which BigQuery SQL query correctly flattens the items into rows?

Accepted Answer

SELECT * FROM orders CROSS JOIN UNNEST(items) AS items. `CROSS JOIN UNNEST(items)` is the standard BigQuery syntax to flatten a repeated (array) column into individual rows. The `UNNEST` operator expands each array element into a separate row, and `CROSS JOIN` ensures that all non-array columns from the `orders` table are preserved alongside each element. This is the only option that correctly transforms the nested `items` array into a normalized row-per-item structure.

Answer

SELECT * FROM orders WHERE items IS NOT NULL

Answer

SELECT * FROM orders, UNNEST(items) AS items

Answer

SELECT * FROM orders INNER JOIN items ON true

Question 8

Refer to the exhibit. What is the most likely performance issue with this schema?

Accepted Answer

Hotspotting on TransactionId due to monotonically increasing values. The schema uses TransactionId as the partition key with monotonically increasing values (e.g., timestamps or auto-incrementing integers). In a distributed database like Cloud Spanner or Bigtable, this causes all writes to land on a single partition, creating a hotspot that throttles throughput and increases latency. The correct answer is C because this hotspotting is the most likely performance issue.

Answer

No performance issue; the schema is optimal

Answer

Hotspotting on UserId due to frequent queries

Answer

Too many secondary indexes causing write amplification

Question 9

A company is designing a Cloud Spanner database for a global user base. They need to support strong consistency and low-latency reads across multiple regions. Which schema design practice is most important?

Accepted Answer

Use interleaved tables to co-locate related rows that are queried together.. Interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same split, enabling local joins with strong consistency and low latency across regions. This design minimizes cross-node communication, which is critical for global workloads that require both strong consistency and fast reads.

Answer

Denormalize data into wide tables to reduce the number of joins.

Answer

Use a single table with composite primary key to avoid joins.

Answer

Create secondary indexes on every column to optimize read queries.

Question 10

A Cloud SQL for PostgreSQL instance is used for an OLTP application. The database schema has many foreign key constraints. Which action improves write performance?

Accepted Answer

Create indexes on foreign key columns.. Creating indexes on foreign key columns prevents full table scans when checking referential integrity, reducing lock contention and improving write performance. Option B is wrong because dropping foreign key constraints sacrifices data integrity, but it could actually improve write performance; however, this is not a best practice and violates data consistency. Option C is wrong because adding triggers adds overhead to write operations, slowing performance. Option D is wrong because increasing storage size does not address the performance bottleneck caused by missing indexes on foreign key columns.

Answer

Drop all foreign key constraints.

Answer

Add more triggers to enforce integrity.

Answer

Increase the instance storage size.

Question 11

A game company uses Cloud Bigtable to store player session data. Access patterns include looking up a player's most recent sessions and scanning sessions by time range. Which row key design is most appropriate?

Accepted Answer

Use a row key of player ID followed by reversed timestamp.. It leverages Cloud Bigtable's sorted row key structure to group all sessions for a given player ID together, while the reversed timestamp ensures that the most recent session appears first within that group. This design enables efficient point lookups for a player's latest sessions and supports range scans over time ranges by using the timestamp portion of the key.

Answer

Use only player ID as row key with column qualifiers for timestamps.

Answer

Prefix with timestamp and append player ID.

Answer

Use a hash of player ID as row key and store timestamps in cell versions.

Question 12

A team is migrating an on-premises MySQL database to Cloud SQL. The current schema usesMyISAM tables. What is the recommended approach?

Accepted Answer

Convert MyISAM tables to InnoDB before migration.. Cloud SQL for MySQL does not support MyISAM tables because MyISAM lacks transaction support, row-level locking, and crash recovery, which are essential for a managed database service. Converting MyISAM tables to InnoDB before migration ensures compatibility, data integrity, and performance. The recommended approach is to alter the table engine to InnoDB prior to export or use a migration tool that handles the conversion.

Answer

Keep the schema as is; Cloud SQL supports MyISAM.

Answer

Replicate the on-premises MySQL to Cloud SQL using Database Migration Service.

Answer

Export the database using mysqldump and import directly into Cloud SQL.

Question 13

A financial services company uses Cloud Spanner for transaction processing. They need to run analytical queries that scan large portions of the database without impacting OLTP performance. What schema design technique should they use?

Accepted Answer

Design the primary key so that analytical queries scan a small number of tablets by using interleaved tables.. Interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same tablet (split). This ensures that analytical queries scanning a large portion of the database can be served by a small number of tablets, minimizing cross-tablet reads and reducing contention with OLTP traffic. By designing the primary key to leverage interleaving, you keep analytical scans localized and avoid the performance penalty of scattering reads across many tablets.

Answer

Export data periodically to BigQuery and run queries there.

Answer

Create multiple secondary indexes on frequently scanned columns.

Answer

Use a read replica instance to offload analytical queries.

Question 14

A Cloud Firestore database stores documents for a mobile app. The app frequently queries for documents where a specific Boolean field is true. The field is not part of the collection group index. What should the developer do to improve query performance?

Accepted Answer

Create a composite index that includes the Boolean field and the query ordering field.. Cloud Firestore requires a composite index to efficiently query on a Boolean field combined with an ordering field. Without this index, the query would perform a full collection scan, leading to poor performance. Creating a composite index that includes the Boolean field and the query ordering field allows Firestore to use the index to directly locate matching documents, avoiding expensive sequential scans.

Answer

Add a synthetic field that combines the Boolean with a timestamp for range queries.

Answer

Denormalize the Boolean field into separate subcollections.

Answer

Rely on the automatic single-field index already created.

Question 15

A multinational corporation uses Cloud Spanner with a multi-region configuration. The schema includes a table that is updated frequently by users in two distant regions. They are experiencing high commit latencies due to distributed transactions. Which schema change would most reduce latency?

Accepted Answer

Use a table-level leader placement configuration to keep the table's splits in a single region.. Table-level leader placement ensures that all writes for the table are processed in a single region, minimizing cross-region coordination and reducing commit latency. Option A is wrong: reducing replicas can hurt availability but doesn't address transaction distribution. Option C is wrong: interleaving tables does not affect region placement. Option D is wrong: more granular primary keys increase splits, which can exacerbate distributed transactions and latency.

Answer

Reduce the number of replicas in the Spanner configuration.

Answer

Convert the table into an interleaved child of a parent table.

Answer

Increase the number of splits by using a more granular primary key.