PCDOE Manage Database Solutions Practice Test 9 — 15 Questions

Question 1

A company runs an e-commerce application on Cloud SQL for MySQL. They need to recover the database to a specific point in time 15 minutes ago after an accidental data deletion. What must be configured beforehand?

Accepted Answer

Configure binary logging and set a PITR retention period. Point-in-time recovery (PITR) in Cloud SQL for MySQL relies on binary logging (binlog) to capture all write operations. To recover to a specific moment (e.g., 15 minutes ago), you must enable automated backups (which provide the base restore point) and configure binary logging with a retention period that covers the desired recovery window. Option C correctly specifies both requirements: binary logging must be enabled, and a PITR retention period (e.g., 1–7 days) must be set to retain the necessary binlog files for replay.

Answer

Create a cross-region replica for failover

Answer

Enable automated backups only

Answer

Set up an on-demand backup every hour

Question 2

A Cloud SQL for PostgreSQL instance is experiencing a surge in read traffic. The team wants to offload read queries without affecting write latency. What should they do?

Accepted Answer

Create a same-region read replica. Creating read replicas allows distributing read traffic, reducing load on the primary instance for writes. Read replicas are the standard solution for scaling read workloads.

Answer

Enable automated backups

Answer

Create a cross-region read replica

Answer

Increase the CPU of the primary instance

Question 3

A Cloud SQL for MySQL instance's storage utilization has reached 95%. The database is 5 TB and needs to grow. The operations team tries to increase storage via gcloud but receives an error. What is the likely cause?

Accepted Answer

The instance is at the maximum storage limit for its machine tier. Cloud SQL allows online storage increases but with limits: the maximum storage cannot exceed 30 TB, and the increase must be done in increments. However, the error is likely because the instance is using the maximum allowed storage for its tier, or the storage cannot be shrunk. But the most common issue is that the instance has an on-demand backup or restore operation in progress, which blocks resize. Another possibility is that the storage is already at the maximum for that machine type. However, the question implies a common misconfiguration: the instance may have a read replica that is replicating from it, and storage resize is not allowed if the replica is in a different region? Actually, storage resize is allowed regardless. More likely: the user has reached the maximum storage size for the current tier (e.g., db-n1-standard-8 supports up to 10 TB). The correct answer is tier storage limit.

Answer

The storage resize operation is only allowed during maintenance windows

Answer

The instance has binary logging enabled, which prevents storage increase

Answer

The instance has automatic storage increase disabled

Question 4

A company uses Cloud Spanner for a global inventory application. They need to add a new index to a large table without downtime. What is the recommended approach?

Accepted Answer

Use CREATE INDEX; Spanner handles it as a non-blocking operation. Option D is correct because Cloud Spanner's CREATE INDEX operation is inherently non-blocking; it performs the index backfill as a background process without locking the table or blocking reads and writes. This allows the index to be added to a large table with zero downtime, as Spanner manages the consistency and availability of the data throughout the operation.

Answer

Create the index using ALTER TABLE with 'ONLINE' clause

Answer

Use gcloud spanner databases ddl update with --async flag

Answer

Take a backup, create index on a new instance, and switch traffic

Question 5

A Cloud Bigtable instance has a hot spot causing performance degradation. Which tool helps identify the hot spot?

Accepted Answer

Key Visualizer. Key Visualizer is a built-in Bigtable tool that visualizes access patterns and helps identify hot spots (uneven load across row keys).

Answer

Cloud Monitoring dashboard

Answer

Cloud Logging

Answer

Bigtable Admin API

Question 6

A Memorystore for Redis instance is running out of memory. The application uses a mix of cache and session data. Which eviction policy should be chosen to minimize cache misses while ensuring session data is not evicted?

Accepted Answer

volatile-ttl. The volatile-ttl policy evicts keys with a TTL set, prioritizing those with the shortest remaining TTL. Since session data typically has a TTL and cache data may or may not, this policy allows you to protect session data by assigning it a longer TTL, while cache data with shorter TTLs is evicted first, minimizing cache misses.

Answer

noeviction

Answer

allkeys-lru

Answer

allkeys-random

Question 7

A company uses Firestore in Native mode. They have a collection with 1 million documents and frequently run queries that filter on two fields: status and createdAt. The queries are slow. What should the team do?

Accepted Answer

Create a composite index on (status, createdAt). Firestore creates single-field indexes automatically but for multi-field queries, a composite index must be created manually. Without it, queries may be slow or fail.

Answer

Create an index exemption for the status field

Answer

Use the Datastore mode instead

Answer

Add an index exemption for the createdAt field

Question 8

A company wants to monitor Cloud SQL database latency for read replicas and set up an alert if the replica lag exceeds 30 seconds. Which metric should be used?

Accepted Answer

cloudsql.googleapis.com/database/replication/replication_lag. The 'replication_lag' metric in Cloud SQL measures the lag between primary and read replica. It is the correct metric to set alerts for replica lag.

Answer

cloudsql.googleapis.com/database/postgresql/num_backends

Answer

cloudsql.googleapis.com/database/cpu/utilization

Answer

cloudsql.googleapis.com/database/disk/bytes_used

Question 9

An e-commerce platform uses Cloud Bigtable for session data. They need to ensure that if one zone fails, the data is still available with eventual consistency. What should they configure?

Accepted Answer

Enable Bigtable replication across zones within the same region. Bigtable replication across zones provides high availability with eventual consistency (async replication). This meets the requirement of surviving a zone failure.

Answer

Create a Cloud Bigtable cluster with multiple nodes

Answer

Export Bigtable tables to Cloud Storage periodically

Answer

Use Cloud Bigtable HDD storage for durability

Question 10

A company is using Memorystore for Redis and needs to persist data across restarts. Which option should they use?

Accepted Answer

Take periodic snapshots and store them in Cloud Storage. Memorystore for Redis does not natively support AOF persistence; it relies on periodic RDB snapshots to persist data. By taking these snapshots and storing them in Cloud Storage, you can restore the Redis instance after a restart or failure, ensuring data durability across restarts.

Answer

Enable AOF persistence in Memorystore settings

Answer

Use a standard tier instance with persistence enabled

Answer

Enable auto-backup in Memorystore

Question 11

A gaming company uses Cloud Spanner to store player profiles and game state. They need to run a one-time analytic query on historical data that would take minutes and cannot impact production performance. What is the best approach?

Accepted Answer

Export the required data to BigQuery using Dataflow and run the query there. Option C is correct because exporting the data to BigQuery via Dataflow isolates the analytic workload from Cloud Spanner, ensuring zero impact on production performance. Cloud Spanner is designed for transactional workloads, not heavy analytic queries that take minutes, and BigQuery is purpose-built for such analytics. This approach also avoids consuming Spanner's CPU or memory resources, which could degrade real-time game state operations.

Answer

Create a read-only replica and query it

Answer

Use the gcloud spanner databases execute-sql command with --query-mode=read-only

Answer

Run the query using a read-only transaction with strong reads

Question 12

A company runs Cloud SQL for PostgreSQL and wants to automatically increase storage when usage reaches a threshold. What should they enable?

Accepted Answer

Enable storage auto-increase in instance settings. Cloud SQL has a built-in 'auto-storage increase' feature that automatically adds storage when necessary, avoiding manual intervention.

Answer

Create a cron job to resize via gcloud

Answer

Configure automated backups

Answer

Use Active Assist recommendations to resize

Question 13

A company is designing a disaster recovery plan for Cloud SQL for MySQL. They need to ensure the database can be recovered with minimal data loss (RPO of minutes) in case of a regional outage. Which TWO actions should they take?

Accepted Answer

Create a cross-region read replica. To achieve low RPO across regions, enable binary logging for PITR and configure a cross-region replica. Automated backups alone have a RPO of up to 24 hours.

Answer

Set up a same-region read replica

Answer

Enable automatic storage increase

Answer

Configure automated daily backups

Question 14

A financial services company uses Cloud Spanner for transaction processing. They need to capture real-time changes from the database to stream to a downstream analytics system. Which THREE services or features can achieve this?

Accepted Answer

Cloud Pub/Sub. Cloud Pub/Sub is correct because it is a fully managed real-time messaging service that can ingest and stream change events from Spanner change streams to downstream analytics systems. It provides at-least-once delivery, low latency, and integrates natively with Spanner via change streams to capture row-level mutations.

Answer

Cloud SQL for PostgreSQL

Answer

Bigtable replication

Question 15

A company is using Memorystore for Redis as a session store. They need to scale horizontally to handle increasing traffic and ensure high availability with automatic failover. Which TWO actions should they take?

Accepted Answer

Enable Redis Cluster mode (sharding). Memorystore for Redis supports sharding for horizontal scaling, and standard tier provides replication with automatic failover.

Answer

Create multiple standalone instances and route traffic manually

Answer

Use Persistent Disk for data persistence

Answer

Use the Basic tier