Design innovative, scalable, and highly available cloud database solutions practice questions

Cloud Bigtable

Why wrong: Cloud Bigtable is a NoSQL database and does not support SQL queries.

Cloud Spanner

Why wrong: Cloud Spanner is a globally distributed relational database for OLTP, not a data warehouse for analytics.

Dataproc

Why wrong: Dataproc is a managed Hadoop/Spark service, not a data warehouse, and does not provide sub-second SQL queries on petabytes.

Cloud SQL

Why wrong: Cloud SQL is limited to smaller datasets and cannot handle petabyte-scale analytics.

Use a hash of the auto-increment value as the primary key

Why wrong: Using a hash of the auto-increment value as the primary key effectively distributes writes across Cloud Spanner's key space, thereby preventing hotspots that arise from sequential key inserts. This approach is tempting as it directly addresses the hotspot concern for write operations. However, it fails this scenario because it destroys the natural ordering of the original auto-increment values. While point lookups by the *hashed* key remain efficient, performing range scans or ordering results based on the *original* auto-increment sequence becomes impossible or highly inefficient without an additional index, which contradicts the goal of preserving efficient lookups based on the original sequential logic.

Use the auto-increment value as a secondary index and a random UUID as the primary key

Why wrong: This adds complexity; the secondary index would still be monotonically increasing and cause hotspots.

Keep the auto-increment primary key; Spanner handles hotspots automatically

Why wrong: Auto-increment keys still cause hotspots in Spanner. Spanner does not automatically fix hotspotting from monotonically increasing keys.

Bigtable

Why wrong: Bigtable supports IAM at the instance/table level but is not a relational database and does not support SQL access control.

Firestore

Why wrong: Firestore supports IAM but is a NoSQL document database, not relational.

72 nodes

Why wrong: Incorrect: 72 nodes is an unrealistic overestimate. The workload requires only 5 nodes for throughput and storage.

50 nodes

Why wrong: Incorrect: 50 nodes is excessive. The required QPS can be met with 5 nodes.

7 nodes

Why wrong: Incorrect: 7 nodes is not minimal. While it would work, the minimum is 5 nodes.

Set max_connections = 8 * vCPU_count.

Why wrong: This formula is not used in Cloud SQL. max_connections is based on memory, not vCPU.

Set max_connections = 10 * IOPS.

Why wrong: IOPS does not determine max_connections.

Set max_connections = vCPU_count * 1000.

Why wrong: This is not a standard Cloud SQL formula.

Cloud Bigtable

Why wrong: Bigtable is for real-time, high-throughput NoSQL workloads, not analytical queries with sub-second latency on TB-scale data.

AlloyDB

Why wrong: AlloyDB is good for HTAP but typically used for transactional workloads with some analytics, not pure batch analytics at this scale.

Cloud SQL

Why wrong: Cloud SQL is an OLTP database not designed for complex analytics on TB-scale data.

Increase the number of nodes to handle the load.

Why wrong: More nodes help only if data is already spread; they don't fix key hotspotting.

Reverse the timestamp and prepend it to the key.

Why wrong: Reversing timestamp helps with time-range queries, not write distribution.

Move userId to the end of the row key.

Why wrong: This would likely make things worse as timestamp is monotonically increasing.

Random salt

Why wrong: Salt helps write distribution but not query filtering.

Timestamp (reversed)

Why wrong: If timestamp is first, queries by severity would require scanning all timestamps.

Message ID

Why wrong: Message ID is not a filter criterion.

Create a secondary index on PostId.

Why wrong: This helps lookups by PostId, not by user.

Use a monotonically increasing integer for PostId.

Why wrong: Monotonically increasing keys can cause hotspots in Spanner.

Use a UUID for PostId.

Why wrong: UUID distributes writes but does not improve query performance for user posts.

Firestore

Why wrong: Firestore is a document database suited for mobile/web apps with hierarchical data. It does not scale to millions of QPS for time-series workloads.

Cloud Spanner

Why wrong: Spanner is a globally distributed relational database optimised for ACID transactions, not single-digit ms time-series reads at millions of QPS.

BigQuery

Why wrong: BigQuery is an analytics data warehouse with query latency measured in seconds. It is not designed for millisecond lookups at IoT scale.

Use the most frequently queried column as the primary key

Why wrong: While query performance is important, for write distribution you need to avoid sequential patterns.

Use a composite key with the leading column being a timestamp

Why wrong: Leading with a timestamp still creates a hotspot.

Use a monotonically increasing timestamp as the primary key

Why wrong: Monotonically increasing keys create hotspots because all writes go to the same tablet.

200

Why wrong: 200 is the current pool size but not the maximum recommended.

64

Why wrong: 64 would be too low and not based on the formula.

1024

Why wrong: 1024 is too low for 64 GB RAM.

user_id#timestamp

Why wrong: This co-locates by user_id but recent data is at the end, requiring a reverse scan.

reverse_timestamp#user_id

Why wrong: This would co-locate by time, not user, so user-specific queries would scatter.

timestamp#user_id

Why wrong: This would co-locate by timestamp, not user_id, making user-specific queries inefficient.

2 processing units (PUs)

Why wrong: 2 PUs would provide 4000 writes/s and 4000 reads/s, which is more than needed.

4 processing units (PUs)

Why wrong: 4 PUs provide 8000 writes/s and reads/s, far exceeding requirements.

0.5 processing units (PUs)

Why wrong: PUs are indivisible; minimum is 1.

Denormalized form

Why wrong: Denormalization is for OLAP/read-heavy workloads, not for OLTP with high write volumes.

Second Normal Form (2NF)

Why wrong: 2NF eliminates partial dependencies but not transitive dependencies.

First Normal Form (1NF)

Why wrong: 1NF only eliminates repeating groups, not all anomalies.

Create a view to join the tables

Why wrong: Bigtable does not support views or joins.

Maintain two separate tables with foreign keys

Why wrong: Bigtable does not support foreign keys or joins.

Use Cloud Spanner to maintain relational structure

Why wrong: The customer chose Bigtable, not Spanner.

Use a hash index instead of a secondary index

Why wrong: Spanner does not support hash indexes.

Drop the secondary index and rely on the primary key

Why wrong: Dropping the index may affect query performance.

Create a covering index without STORING

Why wrong: Without STORING, the index still stores the primary key; to include other columns you must use STORING.

Cloud Bigtable

Why wrong: Bigtable is a NoSQL database, not designed for complex SQL analytics.

Cloud SQL

Why wrong: Cloud SQL is for OLTP, not petabyte-scale analytics.

Cloud Spanner

Why wrong: Spanner is for global OLTP, not for complex analytical queries.

The column family has too many columns

Why wrong: Column family design affects storage but not write throughput.

The row key is too long

Why wrong: Row key length affects performance but not as drastically as a hotspot.

The cluster has insufficient nodes

Why wrong: Even with more nodes, the hotspot would limit throughput.

B-tree index

Why wrong: B-tree indexes are for equality and range queries, not full-text search.

GiST index

Why wrong: GiST indexes are for geometric data, not full-text search.

Hash index

Why wrong: Hash indexes only support equality comparisons.