Select Two (Multi-Select) Questions

Use nested and repeated fields to avoid JOINs

Why wrong: Nested fields can complicate queries and are not always optimal for BI.

Create indexes on frequently queried columns

Why wrong: BigQuery does not support indexes.

Define secondary indexes on price and category columns to support range queries without considering the primary key design.

Why wrong: While secondary indexes help, primary key design is critical for avoiding hotspots and ensuring efficient data distribution.

Use a timestamp as the first part of the primary key to enable time-based partitioning and efficient range scans.

Why wrong: Monotonically increasing timestamps cause hotspots on the last split, degrading write performance.

Define interleaved tables for all related entities, even if they are not always accessed together, to reduce joins.

Why wrong: Interleaving unrelated tables can lead to hotspots and does not improve performance for independent queries.

timestamp#user_id

Why wrong: Scatters activities across the table; inefficient for per-user queries.

random_uuid

Why wrong: No locality; cannot do range scans.

reverse_timestamp

Why wrong: Does not group by user; requires scanning entire table.

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 a single table with a tenant_id column and enforce filtering in application queries.

Why wrong: This is a shared schema approach; not a true isolation strategy because data is co-located.

Use column-level security to hide tenant data.

Why wrong: Column-level security does not isolate rows.

Use row-level security policies to restrict access per tenant.

Why wrong: MySQL does not have built-in row-level security; requires complex views or triggers.

BigQuery requires explicit indexes on columns used in WHERE clauses.

Why wrong: BigQuery uses columnar storage and automatic pruning; no indexes needed.

BigQuery supports unique constraints and foreign keys for data integrity.

Why wrong: BigQuery does not enforce constraints; it's schema-on-read.

Create secondary indexes on foreign key columns

Why wrong: Secondary indexes do not co-locate data; joins still require distributed reads.

Store foreign key relationships as JSON arrays in the parent table

Why wrong: Violates normalization and complicates queries; not recommended.

Disable binary logging to reduce write I/O.

Why wrong: Compromises point-in-time recovery capability.

Increase the storage size to 200 GB to improve IOPS.

Why wrong: Does not directly reduce latency; IOPS are already sufficient.

Decrease the max_connections parameter to reduce overhead.

Why wrong: Could cause connection failures during peak hours.

Query cache uses disk storage for cached results.

Why wrong: The query cache is stored in memory, not on disk.

Prepared statements always bypass the query cache.

Why wrong: Prepared statements can use the query cache if they are identical binary representations.

Enable synchronous replication on all read replicas.

Why wrong: Synchronous replication increases write latency and does not help with read capacity.

Use smaller machine types for read replicas.

Why wrong: Smaller replicas have less capacity, potentially requiring more replicas.

Increase the max_connections parameter on the primary instance.

Why wrong: Increasing max_connections may cause resource exhaustion and does not directly reduce the need for replicas.

Load data using batch loads instead of streaming

Why wrong: Loading method doesn't directly affect query performance.

Store data in CSV format

Why wrong: CSV is less efficient than columnar formats like Parquet.

Use SELECT * in all queries

Why wrong: Selecting all columns increases bytes processed.

Storage utilization

Why wrong: Storage utilization is a capacity metric.

Commit latency

Why wrong: Commit latency applies to write transactions, not read queries.

Lock wait time

Why wrong: Lock wait time is for write contention, not read queries.

Reduce the binlog retention period on the primary.

Why wrong: Binlog retention affects storage, not replication speed.

Enable binary log compression on the primary.

Why wrong: Compression reduces storage but not replication latency.

Increase the disk size of the Cloud SQL instance before migration to improve performance.

Why wrong: Increasing disk size does not directly reduce migration downtime; it addresses storage capacity, not migration speed.

Use pg_dump to export the database and pg_restore to import into Cloud SQL.

Why wrong: pg_dump/pg_restore requires the source database to be in a consistent state, typically causing downtime; it's not a minimal-downtime approach.

Decrease max_connections to reduce load during migration.

Why wrong: Decreasing max_connections can cause application errors and does not help minimize downtime; it might even increase complexity.

Export the database as a .bak file and restore to Cloud SQL.

Why wrong: This is a one-time migration, not minimizing downtime.

Use BCP to export data and import via gcloud sql import.

Why wrong: BCP does not support ongoing changes.

Migrate to Azure SQL Managed Instance first, then to Cloud SQL.

Why wrong: This adds complexity and doesn't minimize downtime.

Store backups in a multi-regional Cloud Storage bucket.

Why wrong: Spanner backups are stored within the instance's location; you can export to Cloud Storage but that is separate.

Set up read replicas in a different region for failover.

Why wrong: Spanner does not use read replicas; multi-region instances handle failover.

Use interleaved tables to reduce the number of reads required for hierarchical data.

Why wrong: Interleaved tables optimize for hierarchical queries but do not directly address global low latency.

Add read replicas in remote regions to serve reads with eventual consistency.

Why wrong: Read replicas provide eventual consistency, not strong consistency.

Schedule a nightly script to recluster tables based on query patterns.

Why wrong: BigQuery automatically reclusters tables; manual scripting is unnecessary and adds complexity.

Use a single table for all streaming data without partitioning to avoid partition management overhead.

Why wrong: Without partitioning, full table scans will be costly and slow.

Denormalize all historical and lookup data into a single wide table.

Why wrong: Denormalization leads to massive storage costs and update complexity; joins are better optimized.

Export the database to a SQL file and import after the cutover.

Why wrong: This causes downtime during export and import.

Schedule a maintenance window for the cutover.

Why wrong: A maintenance window implies downtime.

Use a single-region instance and rely on application caching.

Why wrong: Single-region instances cannot provide low latency globally.

Use strongly consistent reads from a single region.

Why wrong: Strongly consistent reads from a single region will have high latency for remote users.

Use interleaved tables to optimize query performance.

Why wrong: Interleaved tables improve performance within a region, not across continents.