Google PCD: Application Data, State and Storage Patterns — Study Guide

By Laurynas Brazys · 3 September 2026 · #google #PCD #certification #exam-prep #study-guide

Part of the Google Professional Cloud Developer — Study Guide. Practice with verified answers in the Google exam hub, or take timed practice tests on ExamRoll.io.

Overview

Modern applications on Google Cloud routinely combine multiple data stores to balance latency, consistency, scalability, cost, and operational complexity. Selecting fit-for-purpose services and patterns—and understanding their failure modes—is central to resilient design. This section summarizes practical guidance for Cloud SQL, Cloud Spanner, Firestore, Bigtable, Memorystore, and Cloud Storage, and addresses migrations, partitioning, and data protection.

Relational Data on Cloud SQL

Cloud SQL provides managed MySQL, PostgreSQL, and SQL Server with familiar RDBMS semantics.

• Private connectivity

  • Use private IP to keep database traffic on your VPC. This eliminates public ingress rules and IP allowlists and avoids NAT egress complexity.
  • Ensure routes and firewall rules permit VPC-to-instance traffic. Name resolution for private IP is handled automatically when private IP is enabled.
  • For serverless (Cloud Run, App Engine, Cloud Functions), prefer Cloud SQL connectors which handle IAM auth and TLS, even with private IP.

• High availability and replicas

  • Regional HA places primary and standby in different zones with synchronous disk replication. Expect a brief connection drop on failover; apps should retry transient errors and reconnect.
  • Read replicas are asynchronous and offload read traffic. Use cross-region replicas for DR and read proximity, understanding replicas are eventually consistent.
  • Promote a read replica for recovery or planned role swaps. Test promotion procedures regularly.

• Backups and point-in-time recovery

  • Enable automated backups and transaction/PITR logs. Schedule backups during off-peak to reduce IO contention.
  • Retain multiple copies and periodically validate restores to a separate instance. A backup you cannot restore is operationally equivalent to no backup.

• Connection pools and limits

  • Cloud SQL enforces max connections; excessive short-lived connections cause CPU thrash and latency. Use application-side pooling (e.g., HikariCP, PgBouncer, ProxySQL).
  • Size pools based on CPU cores and workload concurrency, not instance memory alone. Start small and scale empirically.
  • For ephemeral/serverless compute, the language-specific Cloud SQL connector maintains a per-revision pool; still cap concurrency to avoid connection storms after cold starts.

• Data partitioning and performance

  • Shard large multi-tenant schemas by customer or region to reduce contention. Keep hot tenants isolated where possible.
  • Create covering indexes carefully; over-indexing slows writes and increases storage. Verify cardinality and predicate selectivity.
  • Use optimistic locking or SELECT FOR UPDATE for hot rows; tune autovacuum (PostgreSQL) or InnoDB settings (MySQL) for sustained write workloads.

Common failure modes and mitigations:

• Thundering herds after VM/node restarts: bound pool sizes and use exponential backoff.
• Replica lag for read-your-writes: pin reads to primary when session consistency is required.
• HA failover flaps due to noisy neighbors or maintenance: implement connection and transaction retries with idempotency.

Planet-scale Relational on Cloud Spanner

Cloud Spanner delivers horizontal scalability with global consistency options.

• Consistency and transactions

  • Strong reads and read-write transactions provide strict external consistency using TrueTime; commits wait briefly to ensure linearizability.
  • Stale and bounded-staleness reads lower latency and improve availability for read-heavy workloads when freshness can be slightly relaxed.
  • Read-only transactions span multiple reads at a timestamp without locks; use for consistent analytics snapshots.

• Regionality, availability, and latency

  • Regional instances provide high availability within a region. Multi-regional configurations (for example, nam-asia-eur1) deliver very high availability and low-latency local reads across continents with globally consistent writes.
  • Choose instance configs aligned with your users’ geography; write latencies increase with intercontinental quorum size.

• Scalability and schema design

  • Spanner shards data into splits by primary key ranges, distributed across nodes. Hotspotting occurs when keys are monotonically increasing. Avoid keys like auto-increment IDs or always-increasing timestamps at the leading position.
  • Use composite primary keys that distribute writes (for example, customer_hash, customer_id, reverse_timestamp).
  • Interleaved tables colocate child rows with parents for locality and efficient joins. Use when child cardinality and access strongly correlate with the parent. Complement with secondary indexes; consider STORING clauses to reduce table lookups.
  • Monitor CPU, storage, and high-priority vs. best-effort ops; scale nodes to maintain headroom under P95 latencies.

• Operational patterns

  • Clients use session pools; tune min/max sessions to avoid creation storms. Retries should be limited and idempotent; on ABORTED, retry read-write transactions with backoff.
  • Backups are lightweight and consistent; validate restores to separate instances. Change streams and CDC integrations can power downstream systems.

Trade-offs:

• Strong global writes add commit-wait; use stale reads for UX-critical, read-mostly paths.
• Interleaving improves locality but can concentrate write pressure; test with production-like traffic.

NoSQL Operational Stores: Firestore and Bigtable

Choose the NoSQL model that matches query patterns and throughput profile.

• Firestore (document)

  • Data model: collections contain documents; documents can have subcollections. Model around query patterns; avoid deep fan-out writes to single “hot” documents.
  • Access and transactions: document reads and queries are strongly consistent in Native mode. Use batched writes for at-most-once atomicity across multiple documents, and transactions for read-modify-write with contention checks.
  • Indexes: single-field indexes are automatic. Multi-field composite indexes must be defined when using multiple range/inequality filters or sort orders. Denormalization is common to make queries index-only.
  • Client synchronization: real-time listeners stream changes; offline caches reconcile with last-write-wins semantics. Guard against unbounded listener fan-out; prefer query cursors and filters.
  • Limits and failure modes: write rate to a single document is serialized; sustained high-QPS updates to one doc create contention. Use sharded counters with N subdocuments and aggregate on read.

• Cloud Bigtable (wide-column)

  • Row-key design is paramount. Bigtable lexicographically partitions rows; leading key segments determine hotspotting. Avoid sequential keys like timestamps-first or unsharded user IDs.
  • Patterns:
    • Reverse timestamp within the key for time-series reads: key = device#hash(device_id)#reverse_ts.
    • Hash or bucket the first component to spread writes: bucket = crc32(user_id) % 128.
  • Store small, many-column cells; avoid large rows that span tablets. Leverage multiple column families for access control and GC policy separation.
  • Throughput and serving:
    • Use multiple clusters for replication and regional read proximity; cross-cluster writes become eventually consistent.
    • Tune app profiles and routing; maintain generous client-side thread pools and channel pools.
  • GC and TTL: version- and time-based GC removes old cells asynchronously; data persists until compaction, so do not rely on immediate deletion for regulatory deadlines.

Caching and Object Storage Patterns

• Memorystore (Redis/Memcached)

  • Caching strategies:
    • Read-through: application fetches from cache; on miss, loads from source and populates cache.
    • Write-through: writes go to cache and source synchronously.
    • Write-behind: buffer writes in cache and flush asynchronously; use with caution due to loss risk.
  • Expiration and invalidation:
    • Apply TTLs consistent with data staleness tolerance. Invalidate keys on source-of-truth changes; for aggregate caches, use versioned keys to avoid stampedes.
    • Use mutex or single-flight to prevent cache stampedes on popular keys.
  • Sessions: store ephemeral session data with TTL; encrypt values or store only opaque tokens if sensitive.
  • Rate limiting with Redis:
    • Fixed-window: INCR with EXPIRE on a per-identity key.
    • Sliding-window or token-bucket for smoother limits; consider Lua scripts for atomicity.
  • Availability: Basic tier has no failover; Standard tier provides regional HA. Treat cache as volatile; never as authoritative storage.

  Example: simple fixed-window rate limit

  • Commands:
    • INCR rate:login:USER123:20260903T1000
    • EXPIRE rate:login:USER123:20260903T1000 60

• Cloud Storage

  • Objects and consistency: strong global consistency for reads, writes, overwrites, deletes, and listings. Objects are immutable; updates create new generations.
  • Signed URLs: offload large uploads/downloads directly between clients and buckets without proxying through your app. Set short expirations; restrict method, path, and content headers.
  • Resumable uploads: use for files >5 MB and unreliable networks; handle 5xx/429 with truncated exponential backoff and resume tokens.
  • Lifecycle: define rules to transition storage classes, delete old versions, and enforce retention. Combine with object versioning for safety during rollouts.
  • Notifications: integrate Pub/Sub notifications to trigger downstream processing upon object finalize/delete, and include preconditions (ifGenerationMatch) to protect against races.

  Example: upload local files

  • gsutil cp ./data/*.parquet gs://my-bucket/ingest/

Migration, Consistency, Partitioning, and Data Protection

• Database migration

  • Choose online vs. offline: online with Database Migration Service for minimal downtime; offline for simplicity when maintenance windows are acceptable.
  • Schema-first: reconcile types and constraints; for Spanner, consider tools to map MySQL/PostgreSQL schemas and data, then tune keys and indexes for distribution.
  • Dual-run and cutover: during online migration, dual-write or replicate changelogs. Validate row counts, checksums, and critical query behavior before final cutover.

• Schema migrations and rollback

  • Use versioned, automated migrations (for example, with a migrations tool) as part of CI/CD. Design additive, backward-compatible changes: add columns and indexes, backfill, deploy code that reads/writes both, then remove deprecated artifacts.
  • Plan rollback with data transforms: if code deploy fails, be prepared to disable new writes and rely on feature flags; avoid destructive migrations that block rollback.

• Transactional vs. eventually consistent workflows

  • Use ACID transactions when invariants must hold synchronously (fund transfers, inventory decrements).
  • Prefer eventual consistency for read-mostly, user-facing features where latency dominates (feeds, search, counters). Implement idempotency keys, outbox/Saga patterns, and retries with backoff.
  • Combine: commit authoritative state in a transactional store; publish events for eventually consistent projections.

• Data partitioning and connection management

  • Partition by tenant, geography, or workload type to isolate hotspots. For Bigtable and Spanner, encode partition keys in primary keys; for Cloud SQL, use schema-per-tenant or table sharding with routers.
  • Manage connections:
    • Cloud SQL: pool and reuse; cap concurrency; stagger cold starts.
    • Spanner: reuse sessions; warm pools on startup; bound retries.
    • Memorystore: reuse TCP connections; avoid per-request connects.

• Data protection, archival, restore verification, and deletion behavior

  • Backups and archival:
    • Cloud SQL: automated backups + PITR; test restores.
    • Spanner: managed backups; test restore into non-prod.
    • Firestore: scheduled exports to Cloud Storage; verify imports.
    • Bigtable: backups and snapshots; test clone-and-restore.
    • Cloud Storage: retention policies, object holds, and bucket-level uniform access for governance; archive to colder classes via lifecycle.
  • Restore verification: periodically restore to isolated environments and run validation queries and app smoke tests. Track RTO/RPO against policy.
  • Deletion behavior:
    • Bigtable GC and lifecycle are asynchronous—do not promise immediate erasure.
    • Cloud Storage versioning retains generations until lifecycle removes them.
    • Firestore TTL and export-based deletes are asynchronous.
    • For hard-deletion SLAs, design processes that mark-delete, queue, and verify removal, with audit logs.

Practical Problem Scenario

Aurora Outfitters is migrating a monolithic ecommerce platform to Google Cloud. They must: 1) lift-and-shift MySQL to reduce risk, 2) handle 500 MB product media uploads without overloading the app, 3) scale read throughput for product catalogs, and 4) enforce per-user rate limits during peak sales.

Approach:

1. Migrate MySQL to Cloud SQL with private IP and regional HA

   • Rationale: Private IP removes public exposure and IP allowlists, simplifying secure connectivity from GKE and Compute Engine. Regional HA protects against zonal failures; expect brief failover connection drops, so the app will implement retryable transactions and reconnect logic.

2. Enable automated backups and PITR, and validate restore

   • Rationale: Automated backups and transaction logs enable point-in-time recovery from user or application errors. A scheduled restore to a non-production instance each week verifies that backups are usable and measures RTO.

3. Add a read replica for catalog reads

   • Rationale: Moving catalog queries to a read replica reduces contention on the primary. The app reads from primary when write-after-read is needed (cart/checkout), and from the replica for catalog browsing, understanding replica lag trade-offs.

4. Introduce application-side connection pooling and cap concurrency

   • Rationale: PgBouncer/HikariCP limits and reuses connections, avoiding connection storms during autoscaling and HA failovers. Pools are sized to CPU cores, not to maximum pods, preventing overload.

5. Offload media uploads to Cloud Storage with signed URLs and resumable uploads

   • Rationale: The app issues short-lived signed URLs for clients to upload directly. Resumable uploads accommodate unreliable networks; the media service listens to Pub/Sub finalize notifications to trigger processing. Precondition headers (ifGenerationMatch) protect against overwrite races.

6. Implement Memorystore for Redis for page caching, sessions, and rate limiting

   • Rationale: Read-through caches reduce database load for product pages with TTLs aligned to update frequency. Session data is kept ephemeral in Redis with short TTLs; application state remains in Cloud SQL. A fixed-window token strategy uses INCR/EXPIRE for per-user request caps. Cache is treated as non-authoritative; the app tolerates cache loss and repopulates on misses.

7. Prepare a phased path to Cloud Bigtable for high-throughput catalog browse features

   • Rationale: As traffic grows, denormalized, read-optimized catalog views move to Bigtable. Row keys are designed as bucket#category#reverse_ts to distribute writes and support time-ordered listings without hotspotting.

8. Establish schema migration and rollback procedures

   • Rationale: Migrations are additive: add columns/indexes, backfill with idempotent jobs, deploy code that reads/writes both, then remove old fields later. Feature flags guard new paths; rollback disables writes to new fields without destructive DDL.

9. Set data lifecycle and protection policies

   • Rationale: Cloud Storage buckets use lifecycle rules to transition thumbnails to colder storage and delete outdated temporary uploads. Cloud SQL backups and Spanner/Bigtable backups (as adopted) are regularly restored for verification. Audit logs capture deletion workflows; Bigtable GC is acknowledged as asynchronous in compliance docs.

10. Implement client and server retries with truncated exponential backoff

    • Rationale: Cloud Storage may return 429/5xx during spikes; backoff smooths load and reduces error rates. Database and cache operations use idempotency keys to ensure safe retries, particularly during failover and network blips.

This plan delivers immediate risk reduction via Cloud SQL with private connectivity and HA, keeps the app responsive and cost-efficient with caching and signed URL uploads, and builds a clear path to scale read throughput and data resilience as traffic grows.

← API Design · All domains · Identity →

Practice these questions → · Timed practice on ExamRoll.io →

Browse Google →