Choosing BigQuery, Bigtable, Spanner and Cloud Storage

Cloud Bigtable is the managed NoSQL wide-column service for high-velocity streaming, time-series, and real-time analytics workloads that need high throughput and low-latency reads and writes at scale. Cloud Bigtable (opens in a new tab) separates durable storage from compute nodes, so throughput scales by adding nodes while durability is handled in a separate transparent tier. Applications reach Bigtable through app profiles that control routing, isolation, and caching behavior, while change streams, replication, and managed backups provide downstream flow and resilience.

1.1Bigtable Fit for Streaming and Time-Series Workloads

Cloud Bigtable fits workloads with massive write rates and key-based reads, such as IoT sensor streams, application logs, and time-series events. Data flows from producers into Bigtable tables keyed for fast point reads and range scans, then flows out to analytics through scans, exports, or change streams. Bigtable is not a traditional relational database, even though it supports SQL queries. Choose Firestore when highly structured documents with ACID transactions are required, Firebase Realtime Database when data must sync between users in real time, and BigQuery when the need is interactive online analytical processing (OLAP).

1.2Storage Types and Cluster Sizing

Storage and cluster sizing in Bigtable directly determine sustained throughput, latency, and cost. SSD storage supports low-latency results and high queries-per-second (QPS), while HDD storage is preferred for write-intensive workloads where reads are range scans for batch processing. HDD storage is priced at or near Cloud Storage prices and varies by region.

Size a single-cluster instance by taking the larger of storage need and compute need:

1. Estimate unreplicated size with nodetool tablestats by dividing total flushed storage size by the keyspace replication factor, then divide that unreplicated total by 3.5 TB (5 TB * .70) to get SSD nodes for storage alone.
2. Estimate minimum SSD nodes for compute by dividing sustained reads plus writes by 10,000, with more nodes likely for low-latency results.
3. Take the larger of the storage-based and compute-based node counts as the starting size, then adjust manually or with the Cloud Bigtable API.

For a multi-cluster instance, create one cluster for every Cassandra data center, where each Bigtable cluster must store all data in the instance and must handle the total insert rate across the entire instance. Start each cluster with the node capacity of the busiest data center so the instance can handle all writes and all reads to at least two data centers during a cluster outage, then scale clusters individually with zero downtime.

1.3Request Routing With App Profiles

App profiles control how traffic flows from applications to clusters in a Bigtable instance. Standard app profiles use provisioned cluster nodes and support single-cluster routing, which sends all requests to one cluster, and multi-cluster routing, which sends requests to the nearest available cluster among all clusters or a specified cluster group. Data Boost app profiles use serverless compute instead of cluster nodes and are restricted to single-cluster routing. Use single-cluster routing when strong read-your-write consistency or the in-memory tier is needed, use multi-cluster routing when geographic proximity and availability across clusters matter, and use Data Boost when high-throughput read jobs must be isolated from core traffic on provisioned nodes.

1.4Sub-Millisecond Read Optimization and Performance Tuning

The in-memory tier (Preview) accelerates eligible point-read traffic by serving it from memory instead of provisioned nodes and disk. You can enable the in-memory tier only for app profiles that use single-cluster routing, and when configured on a cluster, all eligible point-read traffic uses the in-memory tier. Data Boost isolates heavy scans so batch analytics do not contend with latency-sensitive serving traffic that depends on cluster nodes. Because Bigtable separates storage and compute, adding nodes can yield noticeable improvements within minutes, and cluster size can be changed in the Google Cloud console or programmatically.

Performance tuning depends on observing access distribution and utilization. The Bigtable console provides prebuilt dashboards for throughput and utilization at three levels:

• Instance-level throughput and utilization
• Cluster-level throughput and utilization
• Table-level throughput and utilization

Custom views and alerts can be created in Cloud Monitoring where metrics are automatically available. Key Visualizer visualizes access patterns across keys to find hotspots that limit low-latency reads and writes.

1.5Change Streams for Real-Time Analytics

Change streams provide change data capture (CDC) for Bigtable by capturing data changes to a table as the changes happen. Data flows from the Bigtable table into the change stream, then to a reader such as Dataflow, then to downstream destinations for analytics, audits, archiving, or application logic. This pattern keeps streaming pipelines synchronized without polling the table with repeated scans. Use change streams when downstream systems must react to each write in near real time rather than to periodic batch exports.

1.6Backup, Export, and Replication

Bigtable protects large datasets with replication for availability, backups for restore points, and exports for portability. Bigtable replication across clusters in an instance provides higher availability than a single cluster, additional read throughput, and higher durability in zonal or regional failures. Backups and exports differ in speed, cost, and output:

Bigtable backups are analogous to a managed nodetool snapshot and guard against application-level corruption. Invoke backups programmatically or through the console. For very large databases, limit restore scope by storing data in different tables for specific time periods or by keeping original data on Cloud Storage so only a subset is reloaded.

1.7Fully Managed Operations and Security

Bigtable offloads internal maintenance that self-managed systems require. The Bigtable service handles OS patching, node recovery, node repair, storage compaction monitoring, and SSL certificate rotation. Authorization is integrated into Identity and Access Management (IAM), where granular permissions and roles are granted to organization-level users and service accounts instead of sharing local accounts and passwords.

Security controls apply at defined boundaries with explicit limits. Bigtable automatically encrypts all data at rest and in transit with no option to disable it, and customer-managed encryption keys (CMEK) can control keys for data at rest. All administrative access is fully logged, and VPC Service Controls controls access to instances from outside approved networks. Access can be managed at the project, instance, table, or authorized view levels, where an authorized view exposes a subset of table data without granting table-level permission. Bigtable does not support row-level, column-level, or cell-level security restrictions.

Transactional managed services handle live operational reads and writes for applications, where availability, recovery, and latency matter more than bulk analytics. Cloud SQL (opens in a new tab) and AlloyDB for PostgreSQL provide managed relational databases for standard and demanding workloads, Cloud Spanner provides a relational database for global scale, Firestore provides a serverless document database, and Memorystore provides an in-memory store. Selection depends on data shape, whether hybrid transactional and analytical processing (HTAP) is needed, and how far replication must extend.

2.1Cloud SQL and AlloyDB for PostgreSQL

Cloud SQL is a fully managed relational service offered as Cloud SQL for MySQL, Cloud SQL for PostgreSQL, and Cloud SQL for SQL Server. Cloud SQL stores structured operational data with fixed relational schemas and supports secured, serverless GraphQL APIs through Firebase Data Connect, which is powered by Cloud SQL. Choose Cloud SQL when the application needs familiar relational engines without managing database servers.

AlloyDB for PostgreSQL is a fully managed, PostgreSQL-compatible database designed for demanding workloads, including HTAP. AlloyDB fits cases such as real-time inventory management or live customer profiles where transactions and analytics operate on the same live data. BigQuery AlloyDB federation sends a query from BigQuery directly to live transactional data in AlloyDB, so analytical engines read current rows in place and the latency and overhead of change data capture pipelines are eliminated.

AlloyDB clusters provide high availability (HA) with automatic failover by default. The primary instance uses redundant nodes in two different zones within a region, with one active node serving traffic and one standby node ready to take over if the active node fails. This zone-level redundancy protects against zone outages, while cross-region replication protects against region outages. A primary instance can be changed to a basic instance to reduce cost, but a basic instance is not robust against zone outages and has longer downtime during maintenance.

2.2Cloud Spanner for Global Scale and Recovery

Cloud Spanner is a managed relational database designed for high data availability and global scalability. Spanner replicates data across multiple zones in multiple regions through multi-region configurations, so applications remain available even during a region outage. The replication boundary therefore spans zones and regions, unlike a single-region primary with standby nodes.

Spanner also provides built-in protections for disaster recovery and operator error. Database deletion protection guards against accidental deletion, scheduled backups with cross-region copies guard against regional loss, and point-in-time recovery (PITR) protects against logical corruption or accidental writes for up to seven days. Choose Spanner when an operational application needs relational structure combined with global reach, multi-region survival, and restorable history.

2.3Firestore and Memorystore for Flexible and In-Memory Data

Firestore is a fully managed, scalable, and serverless document database for developing rich applications. Firestore stores semi-structured documents rather than fixed relational tables, so fields can vary across documents as web and mobile requirements evolve. The service manages scaling and server operations, so traffic flows directly from clients and backend services to Firestore documents without provisioning database servers.

Memorystore is a scalable, secure, and highly available in-memory service for Valkey and Redis. Memorystore holds key-value data in memory to reduce latency for reads and writes that would be too slow on disk-backed stores. In an operational design, hot keys, sessions, counters, and cached query results live in Memorystore while the system of record remains in Cloud SQL, AlloyDB, Spanner, or Firestore, so repeated access flows from the application to Memorystore first and only falls through to the persistent database on a miss.

2.4Choosing Transactional Storage for Operational Applications

Transactional choice follows data shape, performance need, and failure scope. Relational options enforce structure for inventory, accounts, and orders, document storage allows flexible app objects, and in-memory storage accelerates repeated access.

Analytical warehousing, data lake, and federated storage in Google Cloud is a lakehouse pattern that combines BigQuery as the warehouse with Cloud Storage as the object store. Cloud Storage (opens in a new tab) holds low-cost objects with no practical limit, while BigQuery provides high-performance SQL, machine learning, and business intelligence. Data flows from Cloud Storage into BigQuery for repeated analysis, or stays in place and is queried through external tables and federated queries.

3.1BigQuery Managed Storage for Analytics

BigQuery is the serverless analytical warehouse that stores datasets, tables, and views with automatic replication across multiple locations for high availability. A dataset is the top-level container for tables and views, and storage supports ACID properties for reliable analytical operations. Centralized administration covers data and compute through jobs that load, export, query, or copy data, and through reservations that switch between on-demand pricing and capacity-based pricing.

Data enters BigQuery through managed ingestion paths. The BigQuery Data Transfer Service automates ingestion on a scheduled basis, streaming ingestion uses the Storage Write API (gRPC), and batch loading moves data from local files or Cloud Storage. Batch loads support multiple open and Google-native formats:

• Avro
• Parquet
• ORC
• CSV
• JSON
• Datastore export format
• Firestore export format

Once stored, data supports business intelligence, ad hoc analysis, geospatial analytics, and machine learning. Query capabilities include ANSI-standard SQL with joins, nested and repeated fields, analytic and aggregation functions, multi-statement queries, and spatial functions, plus a Pandas-compatible Python API through BigQuery DataFrames, shareable views, BigQuery ML for predictive analytics, and BigQuery Studio for Python notebooks with version control for notebooks and saved queries.

3.2Cloud Storage Object Storage for Data Lakes

Cloud Storage is the object store for diverse data types that can be accessed from inside and outside Google Cloud and is replicated across locations for redundancy. Buckets can be created in three location types: regional, dual-region, or multi-region. Data in regional buckets is replicated synchronously across zones in the region, while data in dual-region or multi-region buckets is stored redundantly in at least two separate geographic locations with metadata written synchronously across regions and object data replicated asynchronously.

Dual-region buckets support turbo replication with a recovery point objective (RPO) of 15 minutes for replication across the region pair. Cloud Storage stores or replicates data within the specified region, which helps meet data residency requirements. For cost control, bucket storage class is chosen based on retention and access-frequency requirements, and application and database backups stored in a dual-region bucket survive zone or region outages.

3.3External Tables and Federated Queries for In-Place Analysis

External tables and federated queries let BigQuery query data where it lives instead of requiring all data to be loaded into BigQuery storage. A query starts in the BigQuery engine and reads source data from Cloud Storage, Bigtable, Spanner, or Google Sheets in Google Drive, then returns results without a separate copy step. This pattern keeps raw or infrequently accessed data in low-cost storage while still exposing it to SQL, BI Engine with Looker Studio, Looker, Google Sheets, Tableau, and Power BI, and to geospatial and machine learning functions.

The lakehouse flow therefore has two paths with different tradeoffs. Data flows from Cloud Storage to BigQuery managed storage through batch load or streaming when repeated high-performance querying is needed, and queries flow from BigQuery to Cloud Storage and other sources through external tables when avoiding duplication matters more. Keeping original data on Cloud Storage also supports recovery by creating a new table and reloading uncorrupted data, or by partitioning data into time-period tables so only a subset must be restored.

3.4Access Control, Residency, and Governance

Access control for lakehouse data combines Identity and Access Management (IAM) and access control lists (ACLs) for buckets and objects, with IAM at the bucket and project levels recommended in most cases. IAM applies the same access model used throughout Google Cloud to BigQuery datasets, jobs, and reservations, supporting perimeter security or more granular defense-in-depth controls. Sensitive data loaded through Cloud Storage can be discovered, classified, and de-identified with Sensitive Data Protection before it is exposed to warehouse queries.

Location choice links availability to compliance. BigQuery has locality restrictions that dictate where datasets are stored, and Cloud Storage bucket location determines where objects and replicas reside. Selecting regional versus dual-region or multi-region storage therefore balances synchronous zone-level protection against asynchronous cross-region durability while staying within residency requirements.

Key Facts

• Cloud Bigtable separates durable storage from compute nodes, uses SSD storage for low-latency high-QPS reads and HDD storage for write-intensive batch scans, and routes traffic through standard app profiles or serverless Data Boost app profiles.
• Bigtable change streams capture table changes as they happen and flow from the table to Dataflow and then to downstream analytics, audits, or archiving without repeated table scans.
• AlloyDB for PostgreSQL supports hybrid transactional and analytical processing with BigQuery AlloyDB federation that queries live rows in place, while Cloud Spanner uses multi-region configurations with cross-region backup copies and 7-day point-in-time recovery.
• Memorystore for Valkey and Redis holds hot keys, sessions, and cached results in memory in front of Cloud SQL, AlloyDB, Spanner, or Firestore as the persistent system of record.
• Cloud Storage regional buckets replicate synchronously across zones and dual-region buckets support turbo replication with a 15-minute recovery point objective, while BigQuery queries Cloud Storage, Bigtable, Spanner, and Google Sheets in place through external tables and federated queries.