Hard Difficulty Questions

Monotonically increasing integer (INT64) with auto-increment

Why wrong: Monotonically increasing keys cause write hotspots on the last tablet.

Composite key with user_id as first part

Why wrong: If user_id is sequential, it may still cause hotspots; UUID is better.

Timestamp as primary key

Why wrong: Timestamps are monotonically increasing, causing hotspots.

Create a new table with the column, then copy data and rename

Why wrong: Unnecessary and could cause downtime; Spanner supports online schema changes.

Add the column using gcloud command and set a downtime window

Why wrong: No downtime window needed; schema changes are online.

Backup the database, add column, then restore

Why wrong: Unnecessarily disruptive and may cause data loss.

Add a secondary index on order_id

Why wrong: Secondary indexes do not affect the primary key distribution of writes.

Increase the number of nodes

Why wrong: More nodes help with throughput but don't fix the hotspotting caused by monotonically increasing keys.

Use interleaving with a parent table

Why wrong: Interleaving is for locality and does not solve the hotspotting of a monotonic key.

The Name column is of type STRING(MAX)

Why wrong: STRING(MAX) is valid and does not inherently cause performance issues.

There are no secondary indexes

Why wrong: Lack of indexes affects reads but not write throughput directly.

The table is not interleaved with another table

Why wrong: Interleaving is optional and not related to this performance issue.

Use Cloud Bigtable's online storage migration feature to switch to SSD without downtime.

Why wrong: There is no online storage migration feature in Bigtable.

Update the instance's storage type using 'gcloud bigtable instances update' with the --storage-type flag.

Why wrong: The storage type cannot be modified on an existing cluster; you must create a new cluster.

Export the HDD cluster's data to Cloud Storage, then import into a new SSD cluster.

Why wrong: While exporting and importing data is a valid method for migrating data between Bigtable instances, it is not the correct procedure for changing storage type within an existing cluster. Bigtable does not support in-place conversion of HDD to SSD storage for an active cluster. This option is tempting because it represents a common data migration pattern, and would be the appropriate solution if you needed to move data to a completely separate, new cluster with different configurations, or if an in-place upgrade were not possible.

The version of the source PostgreSQL database.

Why wrong: Version differences are minimal; not a primary factor.

The choice of SSL or non-SSL connection.

Why wrong: SSL adds negligible overhead.

Use a secondary index on transactions.account_id.

Why wrong: While possible, a secondary index is less efficient than interleaving for this access pattern and adds write overhead.

Define a foreign key constraint from transactions to accounts.

Why wrong: Cloud Spanner does not enforce foreign keys; they are informational only.

Store transactions as a JSON array of repeating fields within the account record.

Why wrong: Denormalization limits query flexibility and indexing, and is not recommended for Spanner.

Add a database index on frequently queried columns.

Why wrong: Indexes improve read query performance but do not address write hot spots causing high CPU.

Convert the UUID to a monotonically increasing integer.

Why wrong: Monotonically increasing keys cause hot spots, worsening the problem.

Increase the number of nodes in the instance.

Why wrong: Adding nodes may not help if the hot spot is due to a single tablet; the workload remains on the same node.

Use a distribution metric for latency and set a threshold for good latency

Why wrong: That defines a latency-based SLI, not a request-based availability SLI.

Create a custom metric for requests and use Cloud Monitoring's built-in availability SLI for HTTP services

Why wrong: The built-in availability SLI is a special case, not a general request-based SLI definition.

Use a single metric that indicates success (1 for success, 0 for failure) and set a threshold filter

Why wrong: This is not a request-based SLI; it's a metric threshold approach, not a ratio.

Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.

Why wrong: Network bytes received is a throughput metric, not a direct indicator of connection spikes.

Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.

Why wrong: This metric tracks replication lag, which is unrelated to connection spikes causing performance issues.

Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.

Why wrong: Uptime indicates availability, not connection spikes or performance.

A single-region Spanner instance with a read replica in another region.

Why wrong: Read replicas are not synchronous and do not provide automatic failover.

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

Why wrong: Read-only regions do not vote in writes, so a single read-write region means a regional failure stops writes.

A dual-region Spanner instance using default leader placement.

Why wrong: Dual-region configurations (e.g., nam5) provide synchronous replication but may have higher write latency and still meet RTO requirements.

Create a view with UNION ALL of all region tables

Why wrong: Does not prune tables; scans all tables on every query.

Create materialized views for each region

Why wrong: Materialized views are pre-computed and may not support dynamic region selection.

Store all data in a single table with region as a column

Why wrong: Increases storage and may violate data locality requirements.

Use NULL values in primary key columns to allow optional fields.

Why wrong: Primary key columns cannot be NULL; they must have unique non-null values.

Avoid using composite primary keys; use single-column keys instead.

Why wrong: Composite primary keys are common and recommended in Spanner for data distribution.

`module.terraform_remote_state`

Why wrong: Incorrect. `module.terraform_remote_state` is not a valid configuration element; Terraform does not support a module named `terraform_remote_state`.

`output.terraform_remote_state`

Why wrong: Incorrect. `output.terraform_remote_state` is not a valid configuration element; outputs are used to declare values, not to retrieve remote state.

`resource.terraform_remote_state`

Why wrong: Incorrect. `resource.terraform_remote_state` would imply a resource type, but `terraform_remote_state` is a data source, not a resource.

Update the fact table nightly to replace old customer keys with the current key

Why wrong: Updating facts destroys historical accuracy.

Denormalize customer attributes into the fact table

Why wrong: Denormalization makes updates difficult and duplicates data.

Use the natural customer ID in the fact table and join with the dimension using a BETWEEN condition on effective dates

Why wrong: Between joins can be inefficient and error-prone if dates are not precise.

The Docker credential helper is not configured in the build step.

Why wrong: Cloud Build automatically configures the credential helper.

The repository is in a different region than the Cloud Build worker.

Why wrong: Cross-region push is allowed; not typically a 403 issue.

The Cloud Build service account does not have the 'iam.serviceAccountUser' role on the Compute Engine default service account.

Why wrong: That role is for impersonation, not for API access.

Partition the table by date using PostgreSQL declarative partitioning

Why wrong: Partitioning can help, but the queries already filter by date range; the main issue is the grouping by account_id, which a composite index addresses better.

Increase the shared_buffers setting to 128 GB

Why wrong: While larger buffers can help, they still require reading data into memory, and the optimizer might still choose a sequential scan if the query reads a large fraction of the table.

Disable sequential scans by setting enable_seqscan = off

Why wrong: Forcing the query planner can lead to even worse plans; it's better to provide a proper index.

Enable the pgzstd extension to compress data in memory.

Why wrong: Data compression in memory is not a typical solution for out-of-memory errors and may add CPU overhead.

Set up connection pooling using PgBouncer to limit the number of concurrent connections.

Why wrong: Pooling reduces memory per connection but the main memory usage comes from query operations, not connections.

Decrease the shared_buffers configuration parameter to free memory for other uses.

Why wrong: Shared_buffers is important for caching; reducing it will likely degrade query performance and may not free enough memory.

Use preDeploy and postDeploy hooks to run Cloud Run jobs that check error rate and roll back.

Why wrong: Hooks can trigger jobs but do not natively support automatic rollback based on SLO metrics during canary phases.

Use Binary Authorization with a custom attestor that checks error rate.

Why wrong: Binary Authorization controls image deployment, not canary rollback based on metrics.

Set an approval gate on the staging target that requires manual review.

Why wrong: Approval gates are manual, not automatic based on metrics.

Use LIMIT in subqueries to reduce output

Why wrong: LIMIT does not reduce the scan of the underlying table; it only limits output rows.

Use SELECT * to avoid missing columns

Why wrong: SELECT * often scans all columns, increasing bytes.