Professional Cloud Developer

Row Key Design and Data Distribution in Bigtable

Row keys are the main way Bigtable organizes and sorts all its data, similar to how words are sorted in a dictionary. This order is crucial because it determines how your data is spread across the different servers, or nodes, in the cluster. A good design prevents hotspotting, which happens when one node gets overloaded with all the new traffic while others sit idle. This often occurs if you use a simple, sequential key like a timestamp by itself, as all new data will go to the same place.

To avoid hotspots and ensure even data distribution, you should design your row keys to be more random. Strategies include using a hash of a value, reversing common strings (like using com.google.maps instead of maps.google.com), or avoiding predictable, increasing numbers. For time-series data, you have a choice between tall tables (many rows) and wide tables (fewer rows with more data in each). Using time buckets, where you store multiple events from the same hour in one row, can reduce the total number of rows and speed up reads for analytical workloads.

You must never put sensitive personally identifiable information (PII), like names or emails, into a row key. Row keys are considered service data and may appear in logs. For privacy, use internal IDs or hashed values instead. Finally, for flexible data that changes often, you can store it as a serialized protocol buffer (protobuf) inside a single column. This gives you schema flexibility without needing many column families, but you lose the ability to filter on individual fields inside that data.

Relational Schema Optimization for Spanner and AlloyDB

Designing for Cloud Spanner is different because it’s a globally distributed database. The most important goal is to spread data evenly to prevent hotspots. You achieve this by choosing non-sequential primary keys, like UUIDs or bit-reversed sequences, instead of simple, increasing numbers. This ensures writes and reads are distributed across all servers, maintaining performance at scale.

A key feature for performance in Spanner is table interleaving. This creates a parent-child relationship where child rows are stored physically next to their parent row. This acts like a pre-join, keeping related data together for much faster queries that involve both tables. To further speed up queries, you should create secondary indexes on columns that are often searched but aren't part of the primary key. You can make these indexes even more efficient by using a covering index, which uses the STORING clause to include extra data right in the index, so the database doesn't need to fetch the main table.

AlloyDB is a PostgreSQL-compatible database optimized for heavy workloads. It includes a special columnar engine that can make complex analytical queries run much faster by scanning data more efficiently. To get the best performance from AlloyDB, you need to properly size your instances and monitor CPU utilization. Overall, schema design is an iterative process; you should use tools like Query Insights and run realistic load tests to find and fix bottlenecks.

Document Modeling and Indexing Strategies for Firestore

Firestore is a NoSQL document database, storing data as documents within collections. It doesn't use fixed tables, which gives you flexible, schemaless design. To model relationships, you use a hierarchical structure by nesting subcollections inside documents. This groups related data logically and optimizes it for the specific ways your app needs to query it.

Indexing is critical in Firestore for fast reads. Firestore automatically creates indexes for single fields, but for complex queries that filter or sort on multiple fields, you must create composite indexes. Firestore can sometimes merge indexes for equality filters, which helps reduce the total number of indexes you need to manage. A common technique to improve read speed is denormalization, which means duplicating data across different documents. This avoids the need for complex joins, which Firestore doesn't support natively, leading to highly scalable applications.

You must also be aware of Firestore's limits to keep your application reliable. Key constraints include a 1 MB maximum size for a single document and a limit of 40,000 index entries per document. A single composite index can have up to 100 fields, and each index entry itself cannot be larger than 7.5 KiB. Understanding these quotas helps you design your data model to stay within the system's capabilities.