AlloyDB Guide

# Enable the AlloyDB API
gcloud services enable alloydb.googleapis.com

# Configure Private Services Access (required for AlloyDB)
gcloud compute addresses create alloydb-psa-range \
  --global \
  --purpose=VPC_PEERING \
  --prefix-length=16 \
  --network=default

gcloud services vpc-peerings connect \
  --service=servicenetworking.googleapis.com \
  --ranges=alloydb-psa-range \
  --network=default

# Create an AlloyDB cluster
gcloud alloydb clusters create my-alloydb-cluster \
  --region=us-central1 \
  --password=YourStr0ngP@ssword! \
  --network=default

# Create the primary instance
gcloud alloydb instances create my-primary \
  --cluster=my-alloydb-cluster \
  --region=us-central1 \
  --instance-type=PRIMARY \
  --cpu-count=2

# Wait for the instance to be ready
gcloud alloydb instances describe my-primary \
  --cluster=my-alloydb-cluster \
  --region=us-central1 \
  --format="value(state)"

# Get the private IP address
gcloud alloydb instances describe my-primary \
  --cluster=my-alloydb-cluster \
  --region=us-central1 \
  --format="value(ipAddress)"

# Download the AlloyDB Auth Proxy
curl -o alloydb-auth-proxy \
  https://storage.googleapis.com/alloydb-auth-proxy/v1/alloydb-auth-proxy.linux.amd64
chmod +x alloydb-auth-proxy

# Start the proxy (runs in background, connects on localhost:5432)
./alloydb-auth-proxy \
  "projects/MY_PROJECT/locations/us-central1/clusters/my-alloydb-cluster/instances/my-primary" &

# Connect with psql
psql -h 127.0.0.1 -U postgres -d postgres

# Or connect from application code (Python example)
# pip install psycopg2-binary sqlalchemy

import sqlalchemy
from sqlalchemy import text

engine = sqlalchemy.create_engine(
    "postgresql+psycopg2://postgres:YourStr0ngP@ssword!@127.0.0.1:5432/postgres"
)

with engine.connect() as conn:
    result = conn.execute(text("SELECT version()"))
    print(result.fetchone()[0])
    # PostgreSQL 15.x on x86_64-pc-linux-gnu, compiled by gcc ...
    # (AlloyDB-compatible)

# Create a read pool with 2 nodes
gcloud alloydb instances create my-read-pool \
  --cluster=my-alloydb-cluster \
  --region=us-central1 \
  --instance-type=READ_POOL \
  --cpu-count=2 \
  --read-pool-node-count=2

# Scale the read pool up to 4 nodes
gcloud alloydb instances update my-read-pool \
  --cluster=my-alloydb-cluster \
  --region=us-central1 \
  --read-pool-node-count=4

# Get the read pool IP (single endpoint, load-balanced)
gcloud alloydb instances describe my-read-pool \
  --cluster=my-alloydb-cluster \
  --region=us-central1 \
  --format="value(ipAddress)"

-- Check columnar engine status
SHOW google_columnar_engine.enabled;

-- Enable the columnar engine (requires instance restart)
-- Set via instance flags:
-- google_columnar_engine.enabled = on
-- google_columnar_engine.memory_size_in_mb = 4096

-- Manually add a table to the columnar cache
SELECT google_columnar_engine_add(
  relation => 'orders',
  columns => ARRAY['order_date', 'total_amount', 'customer_id', 'status']
);

-- Check which columns are cached
SELECT * FROM g_columnar_recommended_columns;

-- Check columnar engine memory usage
SELECT * FROM g_columnar_memory_usage;

-- Run an analytical query (automatically uses columnar engine)
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Verify the columnar engine was used
EXPLAIN (ANALYZE, COSTS, BUFFERS)
SELECT COUNT(*), SUM(total_amount)
FROM orders
WHERE status = 'completed';
-- Look for "Columnar Scan" in the output

-- Enable the pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create a table for document embeddings
CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding VECTOR(768),  -- 768 dimensions for text-embedding models
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert embeddings (typically generated by an embedding model)
INSERT INTO documents (title, content, embedding)
VALUES (
  'Cloud Architecture Patterns',
  'Microservices communicate through well-defined APIs...',
  '[0.021, -0.034, 0.067, ...]'::vector  -- 768-dim vector
);

-- Create a ScaNN index for fast similarity search (AlloyDB-specific)
CREATE INDEX ON documents
  USING scann (embedding cosine)
  WITH (num_leaves = 100, quantizer = 'SQ8');

-- Alternatively, use standard IVFFlat or HNSW indexes
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 200);

-- Semantic search: find documents similar to a query embedding
SELECT
  id,
  title,
  1 - (embedding <=> '[0.019, -0.041, 0.073, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.019, -0.041, 0.073, ...]'::vector
LIMIT 10;

-- Hybrid search combining vector similarity with metadata filters
SELECT id, title,
  1 - (embedding <=> $1::vector) AS similarity
FROM documents
WHERE metadata->>'category' = 'architecture'
  AND created_at > NOW() - INTERVAL '90 days'
ORDER BY embedding <=> $1::vector
LIMIT 5;

# Create an on-demand backup
gcloud alloydb backups create my-backup-20260314 \
  --cluster=my-alloydb-cluster \
  --region=us-central1

# List all backups
gcloud alloydb backups list \
  --region=us-central1

# Restore a cluster from backup
gcloud alloydb clusters restore my-restored-cluster \
  --region=us-central1 \
  --backup=projects/MY_PROJECT/locations/us-central1/backups/my-backup-20260314 \
  --network=default

# Point-in-time recovery (restore to a specific timestamp)
gcloud alloydb clusters restore my-pitr-cluster \
  --region=us-central1 \
  --continuous-backup-source=projects/MY_PROJECT/locations/us-central1/clusters/my-alloydb-cluster \
  --point-in-time="2026-03-14T10:30:00Z" \
  --network=default

# Configure automated backup policy
gcloud alloydb clusters update my-alloydb-cluster \
  --region=us-central1 \
  --automated-backup-enabled \
  --automated-backup-days-of-week=MONDAY,WEDNESDAY,FRIDAY \
  --automated-backup-start-times=02:00 \
  --automated-backup-retention-count=14

# Create a secondary cluster in another region
gcloud alloydb clusters create my-alloydb-secondary \
  --region=europe-west1 \
  --secondary \
  --primary-cluster=projects/MY_PROJECT/locations/us-central1/clusters/my-alloydb-cluster \
  --network=default

# Create an instance in the secondary cluster
gcloud alloydb instances create secondary-primary \
  --cluster=my-alloydb-secondary \
  --region=europe-west1 \
  --instance-type=PRIMARY \
  --cpu-count=2

# Check replication lag
gcloud alloydb clusters describe my-alloydb-secondary \
  --region=europe-west1 \
  --format="value(secondaryConfig)"

# Promote the secondary to an independent primary (during DR)
gcloud alloydb clusters promote my-alloydb-secondary \
  --region=europe-west1

# Enable the Database Migration Service API
gcloud services enable datamigration.googleapis.com

# Create a connection profile for the source PostgreSQL
gcloud database-migration connection-profiles create source-pg \
  --region=us-central1 \
  --display-name="Source PostgreSQL" \
  --provider=POSTGRESQL \
  --host=10.0.0.5 \
  --port=5432 \
  --username=postgres \
  --password=SourcePassword123

# Create a connection profile for the AlloyDB destination
gcloud database-migration connection-profiles create dest-alloydb \
  --region=us-central1 \
  --display-name="AlloyDB Destination" \
  --provider=ALLOYDB \
  --alloydb-cluster=my-alloydb-cluster

# Create and start the migration job
gcloud database-migration migration-jobs create pg-to-alloydb \
  --region=us-central1 \
  --display-name="PG to AlloyDB Migration" \
  --source=source-pg \
  --destination=dest-alloydb \
  --type=CONTINUOUS

# Monitor migration progress
gcloud database-migration migration-jobs describe pg-to-alloydb \
  --region=us-central1 \
  --format="table(name,state,phase,error)"

# Promote destination when ready (completes the cutover)
gcloud database-migration migration-jobs promote pg-to-alloydb \
  --region=us-central1

-- Check active connections and their state
SELECT state, COUNT(*)
FROM pg_stat_activity
GROUP BY state;

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Check table bloat
SELECT schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  n_dead_tup,
  n_live_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Monitor replication lag for read pools
SELECT
  client_addr,
  state,
  sent_lsn,
  replay_lsn,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

-- Check index usage (unused indexes waste storage and slow writes)
SELECT
  schemaname, tablename, indexname,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexrelid) DESC;

# Delete read pool instances first
gcloud alloydb instances delete my-read-pool \
  --cluster=my-alloydb-cluster \
  --region=us-central1 --quiet

# Delete primary instance
gcloud alloydb instances delete my-primary \
  --cluster=my-alloydb-cluster \
  --region=us-central1 --quiet

# Delete the cluster (also deletes automated backups)
gcloud alloydb clusters delete my-alloydb-cluster \
  --region=us-central1 --quiet

# Delete on-demand backups separately
gcloud alloydb backups delete my-backup-20260314 \
  --region=us-central1 --quiet