gcp-alloydb

# Enable APIs
gcloud services enable alloydb.googleapis.com \
  servicenetworking.googleapis.com \
  compute.googleapis.com

# Reserve a private IP range for VPC peering (one-time per VPC)
gcloud compute addresses create alloydb-peering \
  --global --purpose=VPC_PEERING --prefix-length=16 \
  --network=default

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

# Create a cluster (storage + config; no compute yet).
# --password sets the initial `postgres` user; keep it in Secret Manager, not in shell history.
gcloud alloydb clusters create prod-cluster \
  --region=us-central1 \
  --network=default \
  --password="$ALLOYDB_POSTGRES_PASSWORD" \
  --automated-backup-days-of-week=MONDAY,THURSDAY \
  --automated-backup-start-times=02:00 \
  --automated-backup-retention-count=14

# Create the primary instance (the actual compute)
gcloud alloydb instances create prod-primary \
  --cluster=prod-cluster \
  --region=us-central1 \
  --instance-type=PRIMARY \
  --cpu-count=4 \
  --availability-type=REGIONAL \
  --database-flags="alloydb.iam_authentication=on"

# Add a read pool (e.g., for analytics or reporting traffic)
gcloud alloydb instances create reports-pool \
  --cluster=prod-cluster \
  --region=us-central1 \
  --instance-type=READ_POOL \
  --read-pool-node-count=3 \
  --cpu-count=4

# Run the Auth Proxy locally (handles IAM + TLS). Download the binary from the URL on the
# AlloyDB docs page "Connect using the Auth Proxy" (v1.17.2 at time of writing), e.g.
#   wget https://storage.googleapis.com/alloydb-auth-proxy/v1.17.2/alloydb-auth-proxy.linux.amd64 -O alloydb-auth-proxy && chmod +x alloydb-auth-proxy
# Google publishes no checksum for it; use only that HTTPS URL and pin the version.
# --auto-iam-authn logs in with your IAM identity; add --public-ip only for instances with a public address.
./alloydb-auth-proxy --auto-iam-authn --port 5432 \
  projects/my-project/locations/us-central1/clusters/prod-cluster/instances/prod-primary

# pip install "google-cloud-alloydb-connector[pg8000]" sqlalchemy
# Python connection using the AlloyDB connector + IAM auth
from google.cloud.alloydb.connector import Connector, IPTypes
import sqlalchemy

connector = Connector()

def getconn():
    return connector.connect(
        "projects/my-project/locations/us-central1/clusters/prod-cluster/instances/prod-primary",
        "pg8000",
        user="app-sa@my-project.iam",
        db="orders",
        enable_iam_auth=True,
        ip_type=IPTypes.PRIVATE,
    )

engine = sqlalchemy.create_engine("postgresql+pg8000://", creator=getconn)

with engine.connect() as conn:
    result = conn.execute(sqlalchemy.text("SELECT current_database(), version()"))
    print(result.fetchone())

-- Enable extensions (once per database; alloydb_scann installs vector too)
CREATE EXTENSION IF NOT EXISTS alloydb_scann CASCADE;
CREATE EXTENSION IF NOT EXISTS google_ml_integration;
GRANT EXECUTE ON FUNCTION embedding TO "app-sa@my-project.iam";

-- Schema with embeddings (text-embedding-005 returns 768 dimensions)
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  embedding vector(768)
);

-- Backfill with a Vertex AI model; embedding() works without registering an endpoint
UPDATE products
SET embedding = embedding('text-embedding-005', description)::vector
WHERE embedding IS NULL;

-- ScaNN index: default is auto-tuned (needs 10,000+ rows, or add WITH (mode='AUTO') to defer the build)
CREATE INDEX products_embedding_idx ON products USING scann (embedding cosine);
-- Manual tuning: WITH (mode = 'MANUAL', num_leaves = 1000, quantizer = 'SQ8')

-- Hybrid search: semantic ranking plus a lexical filter
SELECT id, name,
       1 - (embedding <=> embedding('text-embedding-005', 'wireless headphones with noise cancellation')::vector) AS similarity
FROM products
WHERE description ILIKE '%bluetooth%'
ORDER BY embedding <=> embedding('text-embedding-005', 'wireless headphones with noise cancellation')::vector
LIMIT 10;

# Create a read-only secondary cluster in another region
# (then add its instance with `gcloud alloydb instances create-secondary dr-primary --cluster=dr-cluster --region=us-east1`)
gcloud alloydb clusters create-secondary dr-cluster \
  --region=us-east1 \
  --primary-cluster=projects/my-project/locations/us-central1/clusters/prod-cluster

# Failover (promote the secondary cluster to a standalone read/write cluster)
gcloud alloydb clusters promote dr-cluster --region=us-east1

resource "google_alloydb_cluster" "prod" {
  cluster_id = "prod-cluster"
  location   = "us-central1"
  network_config {
    network = data.google_compute_network.default.id
  }

  initial_user {
    user     = "postgres"
    password = var.alloydb_password
  }

  deletion_protection = true   # the provider's default; set false only to tear down a dev cluster

  automated_backup_policy {
    location      = "us-central1"
    backup_window = "1800s"
    enabled       = true
    weekly_schedule {
      days_of_week = ["MONDAY", "THURSDAY"]
      start_times { hours = 2 }
    }
    quantity_based_retention { count = 14 }
  }
}

resource "google_alloydb_instance" "primary" {
  cluster       = google_alloydb_cluster.prod.name
  instance_id   = "prod-primary"
  instance_type = "PRIMARY"

  machine_config { cpu_count = 4 }
  availability_type = "REGIONAL"

  database_flags = {
    "alloydb.iam_authentication" = "on"
  }

  # In Terraform, add depends_on = [google_service_networking_connection.private_vpc] so the
  # private service access exists before the cluster is created.
}