NoSQL vs Relational Databases on Google Cloud

Relational databases (RDBMS) and NoSQL databases represent two fundamentally different approaches to data storage and retrieval. The choice between them impacts application performance, scalability, development speed, and operational complexity. On Google Cloud, you have several managed services for each category:

Relational Databases organize data into tables with predefined schemas. Each table has rows and columns, with relationships defined via foreign keys. The database engine uses ACID transactions (Atomicity, Consistency, Isolation, Durability) to ensure data integrity. Queries are written in SQL (Structured Query Language) and executed via a query planner that optimizes joins and index usage. Storage is typically on disk with buffer pools for caching. Scaling is primarily vertical (more CPU/RAM) or through read replicas; horizontal scaling (sharding) is complex and often application-managed.

NoSQL Databases generally sacrifice ACID for horizontal scalability and flexibility. They come in several types: - Document stores (Firestore): Store data as JSON-like documents. Each document has a unique ID and can have varying fields. Collections group related documents. Indexes are created manually for efficient queries. - Key-value stores (Memorystore, Bigtable as wide-column): Data is stored as key-value pairs. Lookups by key are extremely fast, but querying by value requires scanning or secondary indexes. - Wide-column stores (Bigtable): Data is stored in rows and column families, but columns can vary per row. Designed for high write/read throughput and time-series data. - Graph databases (not natively on GCP but can run on Compute Engine): Focus on relationships between entities.

Cloud SQL (Relational) - Default storage: 10 GB SSD (up to 30 TB). - Max connections: depends on tier (e.g., db-n1-standard-1: 250 connections). - Automated backups: enabled by default, retained for 7 days (configurable up to 365 days). - Point-in-time recovery (PITR): binary log retention 1-7 days. - Maintenance window: default is any window, can set preferred day/time.

Cloud Spanner (Relational + Horizontal Scale) - Strong consistency across regions. - Nodes: minimum 1, each node provides 2 TB storage and 2,000 read/write operations per second. - Schema: relational with interleaved tables for hierarchical data. - Automatic replication; read replicas can be added for read scalability.

Firestore (NoSQL Document) - Maximum document size: 1 MiB. - Maximum field index entries per document: 20,000. - Real-time listeners: enables client-side sync. - Queries: automatically indexed on single-field equality, but composite indexes must be created manually. - Scaling: automatic, no downtime.

Bigtable (NoSQL Wide-Column) - Minimum cluster size: 1 node (but production typically 3+ for availability). - Each node: up to 10,000 writes/second (depending on row key design). - Storage: SSD (default) or HDD (cheaper, slower). - Row key design is critical for performance; avoid hotspots by using a well-distributed key. - No ACID transactions across rows; only single-row atomicity.

Creating a Cloud SQL instance (gcloud command):

gcloud sql instances create my-instance \
    --database-version=POSTGRES_13 \
    --tier=db-custom-2-3840 \
    --region=us-central1 \
    --storage-type=SSD \
    --storage-size=10GB

Verifying Firestore database:

gcloud firestore databases list

Creating a Bigtable instance:

gcloud bigtable instances create my-bigtable \
    --display-name=my-bigtable \
    --cluster-config=id=my-cluster,zone=us-central1-a,nodes=3 \
    --instance-type=PRODUCTION

The GCDL exam expects you to evaluate based on: - Data structure: Highly structured, unchanging schema → relational. Flexible, evolving schema → NoSQL. - Consistency requirements: Strong consistency needed (e.g., financial transactions) → relational (or Spanner). Eventual consistency acceptable → NoSQL. - Scalability needs: Massive write throughput (millions of operations/sec) → Bigtable. Global distribution with strong consistency → Spanner. Moderate scale → Cloud SQL with read replicas. - Query complexity: Complex joins, aggregations → relational. Simple key-value lookups or filtered scans → NoSQL. - Development speed: Agile development with frequent schema changes → NoSQL (especially Firestore).

Scenario 1: E-commerce Platform

A mid-sized e-commerce company uses Cloud SQL (PostgreSQL) for its transactional database: storing user accounts, product catalog, and order history. Orders involve multiple tables (users, products, order_items) that require joins and ACID compliance to ensure inventory accuracy. They set up a read replica for reporting and use Cloud SQL Proxy for secure access from App Engine. They chose Cloud SQL because their workload is moderate (few thousand transactions per second) and they need strong consistency. A common misconfiguration is not setting up connection pooling, leading to connection exhaustion under load. They also underestimated storage growth; they now monitor storage usage and enable automatic storage increase.

Scenario 2: Real-Time Chat Application

A startup builds a chat app using Firestore for storing messages and user profiles. Each chat room is a collection of documents, and messages are written with server timestamps. Firestore's real-time listeners allow clients to receive new messages instantly. They chose Firestore because of its flexible schema (each message can have different metadata) and automatic scaling. However, they initially forgot to create composite indexes for queries like 'messages in room X ordered by timestamp', causing queries to fail with a 'missing index' error. They also hit the 1 MiB document size limit when storing large attachments as base64; they now store attachments in Cloud Storage and reference the URL.

Scenario 3: IoT Sensor Data

A manufacturing company collects sensor readings from thousands of devices every second. They use Bigtable with a row key design of 'device_id#timestamp' to ensure even distribution. Each row stores metrics like temperature, pressure, and vibration in column families. Bigtable handles 1 million writes per second with low latency. They chose Bigtable because of its high throughput and ability to store petabytes of data. A common issue is poor row key design; they initially used 'timestamp#device_id' which caused hotspots on recent timestamps. After redesigning to prefix with device_id, performance improved dramatically. They also use Dataflow to stream data from Pub/Sub into Bigtable for real-time analytics.