Introduction to Google Cloud Spanner
Google Cloud Spanner is a fully managed, horizontally scalable, relational database service that combines the benefits of traditional relational databases with the horizontal scalability of NoSQL systems. It offers ACID transactions, SQL semantics, and high availability across regions, making it ideal for mission-critical applications that require global consistency and massive scale.
Unlike conventional databases that force you to choose between consistency and availability, Spanner delivers both through Google's proprietary TrueTime API and Paxos consensus algorithm. This unique architecture enables external consistency — the strongest isolation level available — across geographically distributed data centers.
Why Spanner Matters
Modern applications often face a difficult trade-off: relational databases provide strong consistency and familiar SQL interfaces but struggle to scale horizontally, while NoSQL databases scale effortlessly but sacrifice transactional guarantees. Spanner eliminates this compromise by offering:
- Global ACID Transactions: Execute multi-row, multi-table transactions with strong consistency across regions.
- Horizontal Scalability: Automatically distribute data and load across multiple nodes without downtime.
- High Availability: Replicate data synchronously across multiple regions with automatic failover.
- SQL Support: Use standard SQL queries, joins, and secondary indexes without learning a new query language.
- Managed Infrastructure: Google handles patching, backups, replication, and scaling operations.
These capabilities make Spanner particularly well-suited for financial services, e-commerce platforms, gaming leaderboards, inventory management systems, and any application where data correctness is non-negotiable.
Prerequisites and Setup
Creating a Google Cloud Project
Before using Spanner, you need an active Google Cloud project with billing enabled. You can create a new project through the Google Cloud Console or using the gcloud CLI. Ensure you have the appropriate permissions, specifically the roles/spanner.admin role, which grants full control over Spanner resources.
# Set your project ID as an environment variable
export PROJECT_ID="my-spanner-project"
# Create a new Google Cloud project
gcloud projects create $PROJECT_ID
# Set the project as your active configuration
gcloud config set project $PROJECT_ID
# Enable the Spanner API
gcloud services enable spanner.googleapis.com
# Verify the API is enabled
gcloud services list --filter="spanner.googleapis.com"
Installing Client Libraries
Google provides client libraries for multiple programming languages. The most commonly used are Python, Java, Go, and Node.js. Below are installation instructions for Python and Node.js, which are popular choices for Spanner development.
# Python client library
pip install google-cloud-spanner
# Node.js client library
npm install @google-cloud/spanner
# Go client library
go get cloud.google.com/go/spanner
Authentication Setup
For local development, you should create a service account key and set the GOOGLE_APPLICATION_CREDENTIALS environment variable to point to the JSON key file. For production environments, use Workload Identity or attached service accounts on Compute Engine or Cloud Run.
# Create a service account for Spanner
gcloud iam service-accounts create spanner-sa \
--display-name="Spanner Service Account"
# Grant the Spanner admin role
gcloud projects add-iam-policy-binding $PROJECT_ID \
--member="serviceAccount:spanner-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
--role="roles/spanner.admin"
# Generate and download the key file
gcloud iam service-accounts keys create spanner-key.json \
--iam-account="spanner-sa@${PROJECT_ID}.iam.gserviceaccount.com"
# Set the environment variable
export GOOGLE_APPLICATION_CREDENTIALS="./spanner-key.json"
Creating a Spanner Instance
A Spanner instance is a container for your databases. When creating an instance, you choose a configuration that determines the geographic placement of your data. Regional configurations keep data within a single region, while multi-region configurations replicate data across multiple regions for higher availability and lower global latency.
Instance Configurations
Choosing the right configuration is critical. Regional configurations like us-central1 provide low latency for users in a specific geographic area. Multi-region configurations like nam3 (North America) or eur3 (Europe) offer 99.999% availability SLA and are ideal for globally distributed applications. Each configuration has different pricing, so evaluate your latency requirements against cost.
# List available instance configurations
gcloud spanner instance-configs list
# Create a regional instance with 3 processing units (minimum)
gcloud spanner instances create my-spanner-instance \
--config=regional-us-central1 \
--description="Production Spanner Instance" \
--processing-units=1000
# For multi-region instances
gcloud spanner instances create my-global-instance \
--config=nam3 \
--description="Global Production Instance" \
--processing-units=2000
# Verify the instance was created
gcloud spanner instances list
Processing units determine the compute capacity of your instance. Each 100 processing units equals 1 node. Start with the minimum required for your workload and scale up as needed. Spanner automatically distributes load across processing units, and you can adjust capacity without downtime.
Creating Databases and Schemas
Creating a Database
Once your instance is ready, create a database within it. A single instance can host multiple databases, each with its own schema. Database names must be 2-64 characters long and contain only lowercase letters, numbers, and hyphens.
# Create a database using gcloud
gcloud spanner databases create ecommerce-db \
--instance=my-spanner-instance
# List databases in the instance
gcloud spanner databases list --instance=my-spanner-instance
Defining the Schema
Spanner uses a SQL-based schema definition language. You define tables with primary keys, columns, and data types. One important concept is interleaved tables, which physically co-locate parent and child rows for efficient hierarchical queries. This is particularly useful for one-to-many relationships.
-- Create the schema using DDL
CREATE TABLE Customers (
CustomerId STRING(36) NOT NULL,
Email STRING(MAX) NOT NULL,
FirstName STRING(100),
LastName STRING(100),
CreatedAt TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
UpdatedAt TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
) PRIMARY KEY (CustomerId);
CREATE TABLE Orders (
OrderId STRING(36) NOT NULL,
CustomerId STRING(36) NOT NULL,
OrderDate TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
Status STRING(20) NOT NULL,
TotalAmount NUMERIC NOT NULL,
) PRIMARY KEY (CustomerId, OrderId),
INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
CREATE TABLE OrderItems (
CustomerId STRING(36) NOT NULL,
OrderId STRING(36) NOT NULL,
ProductId STRING(36) NOT NULL,
Quantity INT64 NOT NULL,
UnitPrice NUMERIC NOT NULL,
) PRIMARY KEY (CustomerId, OrderId, ProductId),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
-- Create a secondary index for email lookups
CREATE INDEX CustomersByEmail ON Customers(Email);
-- Create an index for order status queries
CREATE INDEX OrdersByStatus ON Orders(Status, OrderDate);
Apply the schema using the gcloud CLI or programmatically through the client library:
# Apply DDL statements via gcloud
gcloud spanner databases ddl update ecommerce-db \
--instance=my-spanner-instance \
--ddl="CREATE TABLE Customers (CustomerId STRING(36) NOT NULL, Email STRING(MAX) NOT NULL, FirstName STRING(100), LastName STRING(100), CreatedAt TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true), UpdatedAt TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)) PRIMARY KEY (CustomerId)"
Connecting and Working with Data
Python Client Example
The Python client library provides a clean interface for connecting to Spanner and executing operations. Below is a comprehensive example showing connection setup, data insertion, querying, and transaction handling.
from google.cloud import spanner
from datetime import datetime
import uuid
# Initialize the Spanner client
spanner_client = spanner.Client()
# Get the instance and database
instance = spanner_client.instance("my-spanner-instance")
database = instance.database("ecommerce-db")
# Insert a new customer
def insert_customer(email, first_name, last_name):
customer_id = str(uuid.uuid4())
with database.batch() as batch:
batch.insert(
table="Customers",
columns=("CustomerId", "Email", "FirstName", "LastName", "CreatedAt", "UpdatedAt"),
values=[
(customer_id, email, first_name, last_name, spanner.COMMIT_TIMESTAMP, spanner.COMMIT_TIMESTAMP)
],
)
print(f"Inserted customer with ID: {customer_id}")
return customer_id
# Insert a customer with an order in a single transaction
def insert_customer_with_order(email, first_name, last_name, order_total):
customer_id = str(uuid.uuid4())
order_id = str(uuid.uuid4())
def transaction_fn(transaction):
# Insert the customer
transaction.insert(
table="Customers",
columns=("CustomerId", "Email", "FirstName", "LastName", "CreatedAt", "UpdatedAt"),
values=[(customer_id, email, first_name, last_name,
spanner.COMMIT_TIMESTAMP, spanner.COMMIT_TIMESTAMP)],
)
# Insert the order
transaction.insert(
table="Orders",
columns=("OrderId", "CustomerId", "OrderDate", "Status", "TotalAmount"),
values=[(order_id, customer_id, spanner.COMMIT_TIMESTAMP, "PENDING", order_total)],
)
# Execute the transaction
database.run_in_transaction(transaction_fn)
print(f"Inserted customer {customer_id} with order {order_id}")
return customer_id, order_id
# Query customers by email using a secondary index
def get_customer_by_email(email):
query = """
SELECT CustomerId, Email, FirstName, LastName, CreatedAt
FROM Customers@{FORCE_INDEX=CustomersByEmail}
WHERE Email = @email
"""
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
query,
params={"email": email},
param_types={"email": spanner.param_types.STRING}
)
for row in results:
print(f"Found customer: {row}")
return row
return None
# Execute the examples
if __name__ == "__main__":
customer_id = insert_customer("john.doe@example.com", "John", "Doe")
get_customer_by_email("john.doe@example.com")
Node.js Client Example
For JavaScript and TypeScript developers, the Node.js client offers an async/await-based API that integrates well with modern application architectures.
const { Spanner } = require('@google-cloud/spanner');
const { v4: uuidv4 } = require('uuid');
// Initialize Spanner client
const spanner = new Spanner({
projectId: 'my-spanner-project',
});
// Get instance and database references
const instance = spanner.instance('my-spanner-instance');
const database = instance.database('ecommerce-db');
// Insert a customer
async function insertCustomer(email, firstName, lastName) {
const customerId = uuidv4();
await database.table('Customers').insert({
CustomerId: customerId,
Email: email,
FirstName: firstName,
LastName: lastName,
CreatedAt: Spanner.timestamp(),
UpdatedAt: Spanner.timestamp(),
});
console.log(`Inserted customer with ID: ${customerId}`);
return customerId;
}
// Read a customer by ID
async function getCustomerById(customerId) {
const query = {
sql: `SELECT CustomerId, Email, FirstName, LastName, CreatedAt
FROM Customers
WHERE CustomerId = @customerId`,
params: {
customerId: customerId,
},
types: {
customerId: 'string',
},
};
const [rows] = await database.run(query);
if (rows.length > 0) {
const customer = rows[0].toJSON();
console.log('Found customer:', customer);
return customer;
}
return null;
}
// Execute a read-write transaction
async function transferOrderStatus(orderId, customerId, newStatus) {
await database.runTransactionAsync(async (transaction) => {
const [rows] = await transaction.run({
sql: `SELECT OrderId, Status FROM Orders
WHERE CustomerId = @customerId AND OrderId = @orderId`,
params: { customerId, orderId },
types: { customerId: 'string', orderId: 'string' },
});
if (rows.length === 0) {
throw new Error('Order not found');
}
const order = rows[0].toJSON();
console.log(`Current status: ${order.Status}`);
await transaction.update('Orders', {
CustomerId: customerId,
OrderId: orderId,
Status: newStatus,
UpdatedAt: Spanner.timestamp(),
});
await transaction.commit();
console.log(`Order ${orderId} status updated to ${newStatus}`);
});
}
// Run the examples
async function main() {
try {
const customerId = await insertCustomer('jane.smith@example.com', 'Jane', 'Smith');
await getCustomerById(customerId);
} catch (error) {
console.error('Error:', error);
} finally {
await database.close();
}
}
main();
Advanced Configuration
Secondary Indexes and Query Optimization
Secondary indexes are essential for query performance in Spanner. Without them, queries that filter on non-key columns require full table scans. When creating indexes, consider including additional columns in the index using the STORING clause to avoid additional lookups.
-- Create an index with stored columns to avoid extra lookups
CREATE INDEX OrdersByCustomerWithDetails ON Orders(CustomerId, OrderDate)
STORING (Status, TotalAmount);
-- Create a composite index for common query patterns
CREATE INDEX OrderItemsByProduct ON OrderItems(ProductId, OrderId)
STORING (Quantity, UnitPrice);
-- View the execution plan for a query to verify index usage
-- Run this in the Spanner console or gcloud
SELECT o.OrderId, o.OrderDate, o.Status, o.TotalAmount
FROM Orders o
WHERE o.CustomerId = 'customer-123'
ORDER BY o.OrderDate DESC
LIMIT 10;
Partitioned Queries for Large Datasets
For analytical queries that scan large portions of your data, Spanner supports partitioned queries. These queries distribute work across multiple workers, enabling parallel processing of large result sets.
from google.cloud import spanner
spanner_client = spanner.Client()
instance = spanner_client.instance("my-spanner-instance")
database = instance.database("ecommerce-db")
# Execute a partitioned query
def run_partitioned_query():
query = """
SELECT CustomerId, COUNT(*) as OrderCount, SUM(TotalAmount) as TotalSpent
FROM Orders
GROUP BY CustomerId
"""
with database.snapshot() as snapshot:
# Get the partitioned query plan
partitions = snapshot.generate_query_partitions(
query,
partition_size_bytes=500 * 1024 * 1024, # 500 MB per partition
max_partitions=100
)
total_customers = 0
for partition in partitions:
results = snapshot.execute_sql(
query,
partition=partition
)
for row in results:
print(f"Customer: {row[0]}, Orders: {row[1]}, Total: {row[2]}")
total_customers += 1
print(f"Total customers processed: {total_customers}")
run_partitioned_query()
Backup and Restore Configuration
Spanner supports automated backups and point-in-time recovery. You can create backups on a schedule and restore them to new databases. Backups are encrypted, regionally replicated, and can be retained for up to a year.
# Create a backup of the database
gcloud spanner backups create ecommerce-backup-$(date +%Y%m%d) \
--instance=my-spanner-instance \
--database=ecommerce-db \
--retention-period=30d \
--expiration-date=$(date -u -d "+30 days" +%Y-%m-%dT%H:%M:%SZ)
# List all backups
gcloud spanner backups list --instance=my-spanner-instance
# Restore a backup to a new database
gcloud spanner databases create ecommerce-db-restored \
--instance=my-spanner-instance \
--source-backup=projects/my-spanner-project/instances/my-spanner-instance/backups/ecommerce-backup-20240115
# Set up a backup schedule
gcloud spanner backups schedules create daily-backup-schedule \
--instance=my-spanner-instance \
--database=ecommerce-db \
--retention-period=7d \
--spec-cron="0 2 * * *" \
--timezone="America/New_York"
Best Practices
Schema Design
Proper schema design is the most critical factor in Spanner performance. Follow these principles to ensure your schema scales efficiently:
- Choose monotonically increasing primary keys carefully. Sequential keys like auto-incrementing integers create hotspots because all new writes target the same range. Use UUIDs, reversed timestamps, or hash-based keys to distribute writes evenly.
- Use interleaved tables for parent-child relationships. This co-locates related rows, reducing the number of splits Spanner needs to access during joins and improving read performance.
- Avoid hotspots with timestamp keys. If you must use timestamps as keys, prefix them with a hash or shard key to distribute writes across the key space.
- Design for your query patterns. Create secondary indexes that match your most frequent query predicates, and use the STORING clause to include columns needed in the result set.
Transaction Management
Spanner supports both read-write and read-only transactions. Use read-only transactions whenever possible, as they do not acquire locks and can be served from replicas, reducing latency and improving throughput.
from google.cloud import spanner
spanner_client = spanner.Client()
instance = spanner_client.instance("my-spanner-instance")
database = instance.database("ecommerce-db")
# Read-only transaction (no locks, can read from replicas)
def get_order_summary(customer_id):
with database.snapshot(multi_use=True) as snapshot:
# First query
orders = snapshot.execute_sql(
"SELECT OrderId, OrderDate, Status FROM Orders WHERE CustomerId = @cid",
params={"cid": customer_id},
param_types={"cid": spanner.param_types.STRING}
)
order_list = list(orders)
# Second query using the same snapshot (consistent read)
total = snapshot.execute_sql(
"SELECT SUM(TotalAmount) FROM Orders WHERE CustomerId = @cid",
params={"cid": customer_id},
param_types={"cid": spanner.param_types.STRING}
)
total_amount = list(total)[0][0]
return order_list, total_amount
# Read-write transaction with retry logic
def update_order_status_with_retry(order_id, customer_id, new_status, max_retries=5):
import time
from google.api_core import exceptions
for attempt in range(max_retries):
try:
def transaction_fn(transaction):
# Read the current order
results = transaction.execute_sql(
"SELECT Status FROM Orders WHERE CustomerId = @cid AND OrderId = @oid",
params={"cid": customer_id, "oid": order_id},
param_types={
"cid": spanner.param_types.STRING,
"oid": spanner.param_types.STRING
}
)
rows = list(results)
if not rows:
raise ValueError("Order not found")
current_status = rows[0][0]
print(f"Current status: {current_status}")
# Update the order
transaction.update(
table="Orders",
columns=("CustomerId", "OrderId", "Status"),
values=[(customer_id, order_id, new_status)]
)
database.run_in_transaction(transaction_fn)
print(f"Successfully updated order {order_id} to {new_status}")
return True
except exceptions.Aborted as e:
print(f"Transaction aborted, retrying (attempt {attempt + 1}/{max_retries})")
# Exponential backoff
time.sleep(2 ** attempt * 0.1)
except Exception as e:
print(f"Error: {e}")
return False
return False
Connection Pooling and Performance
Spanner client libraries manage connection pools internally, but you should still follow best practices for session management. Each session consumes resources on the server, so avoid creating excessive sessions. Reuse client instances across your application rather than creating new ones for each request.
const { Spanner, Database } = require('@google-cloud/spanner');
// Singleton pattern for Spanner client
class SpannerManager {
constructor() {
this.spanner = new Spanner({
projectId: process.env.GOOGLE_CLOUD_PROJECT,
});
this.instance = this.spanner.instance('my-spanner-instance');
this.database = this.instance.database('ecommerce-db', {
// Configure session pool
min: 10, // Minimum sessions in pool
max: 100, // Maximum sessions in pool
maxIdle: 60, // Max idle sessions before cleanup
incStep: 25, // Sessions to create when pool needs to grow
concurrency: 10, // Max concurrent requests per session
});
}
async query(sql, params = {}, types = {}) {
try {
const [rows] = await this.database.run({
sql,
params,
types,
});
return rows.map(row => row.toJSON());
} catch (error) {
console.error('Query error:', error);
throw error;
}
}
async transaction(callback) {
return this.database.runTransactionAsync(callback);
}
async close() {
await this.database.close();
}
}
// Export a singleton instance
module.exports = new SpannerManager();
Monitoring and Observability
Spanner integrates with Google Cloud Monitoring, providing detailed metrics about instance health, query performance, and resource utilization. Key metrics to monitor include CPU utilization, storage usage, and the number of aborted transactions. Set up alerting policies to notify your team when these metrics exceed thresholds.
# View instance metrics via gcloud
gcloud spanner instances describe my-spanner-instance
# Monitor CPU utilization (should stay below 65% for regional, 45% for multi-region)
# Set up an alerting policy using Cloud Monitoring
gcloud alpha monitoring policies create --policy-from-file=alert-policy.yaml
# Example alert-policy.yaml
# displayName: "Spanner High CPU Alert"
# conditions:
# - displayName: "CPU utilization above 80%"
# conditionThreshold:
# filter: |
# resource.type="spanner_instance" AND
# resource.label.instance_id="my-spanner-instance" AND
# metric.type="spanner.googleapis.com/instance/cpu/utilization"
# comparison: COMPARISON_GT
# thresholdValue: 0.8
# duration: 300s
# notificationChannels:
# - "projects/my-spanner-project/notificationChannels/12345"
Security Configuration
Protect your Spanner data with proper IAM policies, encryption settings, and network controls. Use Customer-Managed Encryption Keys (CMEK) for additional control over encryption, and consider VPC Service Controls to create a security perimeter around your Spanner instances.
# Enable CMEK encryption for a new instance
gcloud spanner instances create secure-spanner-instance \
--config=regional-us-central1 \
--description="CMEK-encrypted instance" \
--processing-units=1000 \
--cmek-key=projects/my-spanner-project/locations/us-central1/keyRings/spanner-keyring/cryptoKeys/spanner-key
# Apply fine-grained IAM policies at the database level
gcloud spanner databases add-iam-policy-binding ecommerce-db \
--instance=my-spanner-instance \
--member="serviceAccount:app-sa@my-spanner-project.iam.gserviceaccount.com" \
--role="roles/spanner.databaseReader"
# Apply database writer role to a different service account
gcloud spanner databases add-iam-policy-binding ecommerce-db \
--instance=my-spanner-instance \
--member="serviceAccount:writer-sa@my-spanner-project.iam.gserviceaccount.com" \
--role="roles/spanner.databaseUser"
Cost Optimization
Spanner charges based on processing units (compute), storage, and network egress. To optimize costs, right-size your instance by monitoring CPU utilization and adjusting processing units accordingly. Spanner supports processing units as low as 100, making it accessible for smaller workloads. Use the autoscaling feature to automatically adjust capacity based on demand.
# Enable autoscaling for a Spanner instance
gcloud spanner instances update my-spanner-instance \
--autoscaling-config=min-nodes=1,max-nodes=10,target-high-priority-cpu-utilization-percent=65,max-scale-down-rate=0.5,max-scale-up-rate=2.0
# Monitor storage usage
gcloud spanner databases describe ecommerce-db \
--instance=my-spanner-instance
# Delete unused databases and backups to reduce storage costs
gcloud spanner databases delete old-staging-db \
--instance=my-spanner-instance
gcloud spanner backups delete old-backup-20230101 \
--instance=my-spanner-instance
Conclusion
Google Cloud Spanner provides a powerful solution for applications that require the consistency of relational databases combined with the scalability of distributed systems. By following the setup and configuration steps outlined in this guide, you can create a robust Spanner environment tailored to your application's needs. Remember that the key to Spanner success lies in thoughtful schema design, strategic use of secondary indexes, proper transaction management, and continuous monitoring of performance metrics. Start with a regional configuration for development, benchmark your workload, and scale to multi-region deployments as your global requirements evolve. With the best practices and code examples provided here, you are well-equipped to build production-grade applications on Spanner that deliver strong consistency, high availability, and seamless scalability.