Design, migrate, and run scalable, highly available databases on Google Cloud across relational and NoSQL services, and track your readiness for the Professional Cloud Database Engineer exam.
Prepare and test your skills
Prepare and test your skills
Analyze Data Characteristics and Schema Flexibility is the decision of whether data shape and change rate require a rigid relational schema or a flexible NoSQL model. Structured data has a fixed, stable shape with consistent fields and relationships, while semi-structured and unstructured data varies in shape or has no fixed fields. Schema flexibility describes how much structure must be declared before data is written and how easily that structure can evolve.
Structured data is data with a known, stable shape that fits naturally into tables with rows, columns, and defined relationships. AlloyDB for PostgreSQL (opens in a new tab) is a relational engine for this pattern because it enforces a rigid schema when data is written. That rigidity supports correctness for transactional workloads where every record must follow the same rules and relationships must stay intact.
Relational databases enforce correctness through a complementary set of features:
These strengths come with tradeoffs for scale and operations. Relational databases are typically hard to scale horizontally and require careful management in a high-availability configuration. Choose a relational schema when the business requires consistent transactions, enforced relationships, and complex queries more than rapid shape changes or massive horizontal growth.
Semi-structured and unstructured data includes variable fields, nested objects, large text or binary payloads, and high-volume event or sensor data that does not fit one fixed table. Firestore in Native mode and with MongoDB compatibility, Datastore, Bigtable, and Memorystore serve these patterns as document, wide-column key-value, and in-memory stores. Bigtable (opens in a new tab) is a NoSQL wide-column service for terabytes or petabytes of large analytical and operational workloads, while Firestore and Datastore are document-oriented stores for web and mobile applications that need automatic scaling and flexible fields.
Flexible stores avoid requiring all storage schema to be specified upfront. Spanner (opens in a new tab) shows the same principle inside a relational system by supporting flexible JSON and BYTES types for semi-structured or unstructured values, like other NoSQL databases. Non-relational databases typically sacrifice some relational features in favor of increased availability and easier scalability, and in terms of the CAP theorem they often choose availability over consistency. Choose a flexible NoSQL store when fields evolve quickly, records vary by type, and the service can be designed to be eventually consistent in exchange for scale and availability.
Schema-on-write means structure, types, and relationships are enforced when data is written, while schema-on-read means raw or loosely structured data is stored first and interpreted when it is read. Relational engines follow schema-on-write because the table definition controls what can be stored and how tables relate. Flexible document, key-value, and wide-column stores lean toward schema-on-read because each item can carry different fields and the application decides how to interpret them later.
This difference shapes how applications evolve. Conventional NoSQL modeling can feel unnatural because it may force all data into one table and require denormalization when joins are unavailable. Spanner preserves relational modeling with defined schemas, joins across tables, table interleaving to optimize access patterns that touch multiple tables, and strongly consistent secondary indexes without size constraints. BigQuery (opens in a new tab) shows another flexible pattern with nested and repeated fields, partitioning, and clustering, which reduce the need for many joins and let storage and queries evolve without rebuilding indexes. Choose schema-on-write when correctness depends on enforcing shape upfront, and choose schema-on-read flexibility when new fields, nested structures, and changing access patterns are expected.
Choosing an engine on Google Cloud means matching data shape, consistency needs, and growth plans to the store that enforces the least structure needed for correctness. Relational and non-relational databases are complementary rather than competing, so different parts of one application can use heterogeneous storage. Spanner bridges the models by combining SQL, full read-write transactions with ACID consistency, global distribution, and NoSQL-style flexibility for JSON data.
| Engine | Best-fit data shape | Schema and consistency approach |
|---|---|---|
| AlloyDB for PostgreSQL | Structured transactional records with stable relationships | Rigid schema-on-write with transactions, referential integrity, and joins |
| Firestore and Datastore | Variable documents for web, mobile, and server workloads | Flexible document schema with automatic scaling for evolving fields |
| Bigtable | Massive key-value and wide-column operational and analytical data | Flexible wide-column schema for terabyte to petabyte scale |
| Spanner | Growing workloads that need both relational correctness and flexible values | Defined relational schema plus JSON and BYTES fields, with ACID transactions and strongly consistent indexes |
Use consistency as the decision criterion when shapes could fit either model. If the data model does not require all relational features and can be designed to be eventually consistent, a NoSQL choice can offer increased availability and scalability. If the workload needs multi-row transactions, joins, and strongly consistent secondary indexes at scale, keep a relational engine and add flexible field types for the parts that evolve.
Exam tip: Choose NoSQL when eventual consistency is acceptable for scale and flexibility, but keep SQL or Spanner when the workload requires strong consistency, multi-table transactions, and joins.
Identify Specialized Requirements for Vector and High-Dimensional Data is the decision of when similarity search over meanings requires vector handling instead of traditional tabular or document lookup. Vector and high-dimensional data refers to vector embeddings, which are long lists of numbers that represent the meaning of text, images, video, or other content. A generative AI model creates each embedding so that similar meanings sit close together in mathematical space, and queries find closest meanings by measuring distance to power similarity search and retrieval-augmented generation (RAG).
Vector data needs special handling because queries ask for semantic similarity rather than exact matches. A traditional SQL lookup returns rows where a column equals a value, and a document lookup matches keywords, but a vector query converts the search input into an embedding and returns the nearest neighbors by distance. Distance is calculated with mathematical measures such as COSINE, L2_SQUARED, and DOT_PRODUCT, where a smaller distance means greater similarity. This meaning-based approach enables workloads that keyword search cannot support well:
Generating and storing vector embeddings is the process of converting source content into numeric vectors and keeping those vectors alongside operational or analytical data. BigQuery (opens in a new tab) generates embeddings with SQL functions and stores embedding columns alongside structured data. The AI.EMBED function generates a single embedding for one text input or for image data represented by ObjectRef values. The AI.GENERATE_EMBEDDING function creates a table of embeddings for every value in a column, and it works with standard tables for all models plus ObjectRef columns or object tables for multimodal models. With autonomous embedding generation, BigQuery maintains an embedding column automatically from a source column, so BigQuery generates or updates the embedding whenever source data is added or modified.
Storing embeddings in Cloud SQL for MySQL (opens in a new tab) means keeping vectors in the same transactional tables as relational data. Each embedding column must use the VECTOR data type with a fixed number of dimensions, and every embedding stored in that column must use exactly those dimensions. The table remains compliant with atomicity, consistency, isolation, and durability (ACID) properties, so vector data uses the same transactional semantics and InnoDB replication behavior as other columns. AlloyDB for PostgreSQL follows the same co-location principle through AlloyDB AI, which enhances the open-source pgvector extension so operational data and embeddings stay in one database without managing a separate vector database.
Performing similarity and hybrid search is the process of comparing a query embedding against stored embeddings to return the closest meanings, alone or combined with keyword matching. The VECTOR_SEARCH function performs semantic vector search or hybrid search by using SQL, while the AI.SEARCH function performs semantic or hybrid search from a text string when the table uses autonomous embedding generation. A hybrid search combines semantic vector search with lexical keyword search to balance meaning and exact terms. The AI.SIMILARITY function compares only two inputs by computing cosine similarity between their embeddings, which fits a small number of ad hoc comparisons without precomputed embeddings.
Search in Cloud SQL for MySQL divides into two types with different performance tradeoffs. A K-nearest neighbors (KNN) search uses the vector_distance function in a SELECT statement to calculate distance to every vector in the dataset, which gives exact results but scans all data. An approximate nearest neighbors (ANN) search uses the approx_distance function in an ORDER BY or SELECT list to calculate distance to only a portion of the dataset, which is faster at large scale but returns approximate results. An ANN query may also include a WHERE clause for post-filtering and a LIMIT clause to bound results, where base-table predicates in the WHERE clause are evaluated after the vector function.
Exam tip: Use VECTOR_SEARCH when performance is critical and you are searching many precomputed embeddings, and use AI.SIMILARITY only for a small number of comparisons without precomputed embeddings.
Vector indexes are specialized structures that organize embeddings into partitions or clusters so an ANN search can check only the closest clusters instead of every record. Without a vector index, VECTOR_SEARCH and AI.SEARCH use brute-force search that measures distance for every record. When a vector index is available, the functions use Approximate Nearest Neighbor search to improve performance with the tradeoff of lower recall, meaning results are more approximate, although brute force can still be requested for exact results. Queries that contain VECTOR_SEARCH or AI.SEARCH are not accelerated by BigQuery BI Engine.
Cloud SQL for MySQL builds vector indexes with the Scalable Nearest Neighbors (ScANN) algorithm and uses the TREE_SQ index type for ANN searches. Index creation requires the base table to contain at least 1,000 rows with loaded embedding values, and more data points produce better partitioning and training. Creation requires the DISTANCE_MEASURE parameter set to L2_SQUARED, COSINE, or DOT_PRODUCT, and the measure must match the measure used in the approx_distance search options. The optional NUM_LEAVES parameter controls how many partitions are built, and the number cannot exceed the number of embeddings in the base table. AlloyDB also offers ScANN alongside full compatibility with pgvector Hierarchical Navigable Small World (HNSW) indexes, where ScANN uses 3x less memory than HNSW, runs vector queries up to 4x faster, and runs index creation and filtered search up to 10x faster.
Cloud SQL for MySQL enforces strict limits for vector indexes. There can be only one vector index per table, the embedding column cannot be a generated column, table-level partitioning is not supported, and certain primary key types including BIT, BINARY, VARBINARY, JSON, BLOB, TEXT, and spatial data are not supported. While a CREATE index statement runs, the base table enters read-only mode and data manipulation language (DML) operations are blocked. After major DML operations, rebuilding the index is best practice, and specifying at least 100 vectors per leaf is recommended when setting NUM_LEAVES manually for best recall.
Choosing a Google Cloud vector solution starts from where the data lives and whether the workload needs analytics, transactions, or specialized large-scale serving. BigQuery fits analytical semantic and hybrid search over large datasets with SQL functions and managed vector indexes, with compute billed for bytes scanned or slots used and storage billed for embeddings and active indexes. Cloud SQL for MySQL fits online transaction processing (OLTP) workloads that already use a SQL database and need low-latency vector search directly from the database alongside relational rows. AlloyDB fits operational workloads that need real-time personalization and RAG without moving data to a separate system.
| Solution | Data pattern | Choose when |
|---|---|---|
| BigQuery | Analytical tables with embedding columns | The workload needs SQL-based semantic or hybrid search and RAG over large analytical datasets |
| Cloud SQL for MySQL | Transactional tables with VECTOR columns | The data is accessed programmatically through OLTP and vectors must share ACID transactions with relational data |
| AlloyDB for PostgreSQL | Operational PostgreSQL tables with pgvector embeddings | The workload needs fast in-database semantic search, hybrid search, and recommendations without a separate vector database |
| Vertex AI Vector Search (opens in a new tab) | Enterprise embeddings organized in a deployed index | The workload needs low-latency, large-scale specialized serving of similarity search for RAG and recommendations |
A RAG flow connects these pieces in order:
Assess Transactional Integrity and Scalability Trade-offs is the decision of whether a workload needs guaranteed correctness for every write or needs to grow quickly and stay available around the world. Relational databases provide ACID-compliant transactions, strong consistency, and complex joins, while NoSQL databases trade some of those guarantees for horizontal scalability and high-throughput, low-latency access. The choice depends on data structure, consistency needs, and whether delayed agreement across replicas is acceptable.
Relational databases are the correct choice when business logic requires that related changes succeed or fail together and remain immediately visible to all readers. A relational engine (opens in a new tab) enforces correctness through a complementary set of features:
These guarantees make relational databases a good fit for financial ledgers, inventory, and other systems where an inconsistent read causes a business error. The tradeoff is that relational databases are typically hard to scale horizontally and require careful management in a high-availability configuration.
NoSQL databases are the correct choice when a workload must handle very large datasets, many users, and continuous growth with high availability. A NoSQL system achieves this scale by sacrificing some relational features in favor of simpler access patterns and distributed storage. In terms of the CAP theorem, NoSQL databases often choose availability over consistency, so replicas stay available for reads even when some replicas have not yet received the latest write.
Whether a NoSQL database is appropriate comes down to the required degree of consistency for a particular service. If the data model does not require multi-table transactions and can be designed to be eventually consistent, choosing a NoSQL database can offer increased availability and easier scalability. Datastore (opens in a new tab) is an example of a widely used NoSQL database that processed trillions of transactions per month by using this model to provide high performance at very large scale.
Eventual consistency is the guarantee that, provided no new updates are made to an entity, all reads of that entity will eventually return the last updated value. The Internet Domain Name System (DNS) works this way because updated values are cached and replicated across many servers, so lookups stay fast and highly available even though some servers briefly return older values. Data flows from the originating write node to each replica over time, and during that window different replicas can return different values.
This delay creates a direct business tradeoff when writes occur in more than one location. With asynchronous replication, data flows from a primary database to a secondary database with replication lag, and the secondary can serve reads that are slightly stale. With bidirectional replication, data flows in both directions between two primary databases, so concurrent updates to the same data item can cause a write-write conflict. The system then depends on a conflict-resolution policy to decide which state becomes the last consistent state, and that resolution is not always straightforward and can require manual intervention.
Exam tip: Choose strong consistency when every reader must see the latest committed write immediately; choose eventual consistency only when the business can tolerate stale reads in exchange for availability and scale.
Synchronous distribution and sharding are opposite distribution models for preserving transactional integrity or relaxing it to gain scale. A fully active-active synchronized distributed system keeps an update transactionally consistent in all primary locations, so a change is reflected in both databases when the changing transaction commits and each application always accesses the last consistent state without delay or conflict risk. This model best supports distributed services where service instances run in all deployment locations against what effectively acts as a single distributed database.
Sharding takes the opposite approach by dividing data into disjointed partitions where a separate database instance manages each partition. The sharded system depends on the ability to add more shards dynamically over time, which provides horizontal scalability for throughput and storage growth. The tradeoff is that cross-shard queries might not be possible because not all systems support that functionality, so workloads that need complex joins across partitions lose transactional simplicity.
Heterogeneous storage is the practice of using relational and non-relational databases together so each service uses the engine that matches its consistency and scale needs. Relational and non-relational databases are complementary rather than competing, so an organization can keep strict transactions for core records while using NoSQL for high-volume, eventually consistent workloads. Caching further supports this design because requests served from memory reduce load on disk-based storage and lower request latency to downstream databases.
Spanner bridges the two sides by providing a strongly consistent, highly available, and globally distributed database with support for SQL. The three options compare across consistency, scale, and fit:
| Option | Consistency model | Scalability and availability |
|---|---|---|
| Relational databases | Strong consistency with transactions and joins | Harder to scale horizontally, needs careful high-availability management |
| NoSQL databases | Eventual consistency with delayed replica agreement | Easier horizontal scale with high availability and low-latency reads |
| Spanner | Strong consistency with SQL at global scope | Global distribution with high availability without giving up transactions |
Choose relational databases when complex joins and immediate correctness are a business necessity, choose NoSQL databases when global availability and high throughput allow BASE properties and eventual consistency, and choose Spanner when a workload needs both SQL transactions and global scale.
Last updated