alloydb

AlloyDB

Overview

AlloyDB is a fully managed, PostgreSQL-compatible database service on Google Cloud. It combines the familiarity of PostgreSQL with Google's storage and compute innovations for high performance and availability.

Operating Layers

Use this skill in three distinct layers:

1. Provision the managed database on GCP.
2. Connect an agent or client to the database.
3. Operate the database with tuning, observability, backups, and failover guidance.

Keep those layers separate when giving guidance. Provisioning is not the same thing as agent connectivity.

Quick Reference

AlloyDB vs Standard PostgreSQL

Feature	AlloyDB	Cloud SQL for PostgreSQL
Storage	Disaggregated, log-based	Attached disk
Columnar engine	Built-in adaptive columnar cache	Not available
ML embeddings	Native Vertex AI integration	Manual setup
Read scaling	Read pool (auto-managed replicas)	Manual read replicas
Availability	99.99% SLA (regional)	99.95% SLA
Networking	Private IP only (PSA required)	Public or private IP

Key Commands

Action	Command
Create cluster	gcloud alloydb clusters create NAME --region=REGION --network=NETWORK --password=PASS
Create primary	gcloud alloydb instances create NAME --cluster=CLUSTER --region=REGION --instance-type=PRIMARY --cpu-count=N
Create read pool	gcloud alloydb instances create NAME --cluster=CLUSTER --region=REGION --instance-type=READ_POOL --read-pool-node-count=N
Connect via proxy	./alloydb-auth-proxy "projects/P/locations/R/clusters/C/instances/I" --port=5432
Enable columnar engine	SELECT google_columnar_engine_add('table_name');

Connection Pattern

# From GCE VM in same VPC (private IP)
psql "host=ALLOYDB_IP dbname=postgres user=postgres sslmode=require"

# Via AlloyDB Auth Proxy (recommended for external access)
./alloydb-auth-proxy \
    "projects/PROJECT/locations/REGION/clusters/CLUSTER/instances/INSTANCE" \
    --port=5432

psql "host=127.0.0.1 port=5432 dbname=postgres user=postgres"

Workflow

Step 1: Set Up Private Service Access

AlloyDB requires Private Service Access (PSA) before any cluster can be created. Allocate an IP range and create the VPC peering connection.

Step 2: Create Cluster and Primary Instance

Create the cluster with gcloud alloydb clusters create, then add a primary instance. Choose CPU count based on workload (start with 4 vCPUs for small workloads).

Step 3: Configure Read Pool (if needed)

For read-heavy workloads, add a read pool with --instance-type=READ_POOL. AlloyDB automatically manages the read replicas within the pool.

Step 4: Enable Columnar Engine (for analytics)

For analytical query patterns, enable the columnar engine on tables with SELECT google_columnar_engine_add('table'). Check g_columnar_recommended_columns for automatic recommendations.

Step 5: Connect Applications

Use the AlloyDB Auth Proxy for connections from outside the VPC. For applications within GCE/GKE on the same VPC, connect directly via private IP.

Host Integration Order

Use the lowest-admin supported path for the current host, and degrade cleanly:

1. Gemini CLI: use the dedicated alloydb and alloydb-observability extensions.
2. Other agents with MCP support: use MCP Toolbox with the official AlloyDB prebuilt config.
3. No extension / no MCP: fall back to gcloud, Auth Proxy, psql, and SQL guidance from this skill's references.

Do not make the skill Gemini-only. The Gemini extension path is preferred when available, but the operational guidance in this skill must still work for Claude, Codex, OpenCode, Antigravity, and plain terminal workflows.

Guardrails

• Always use Private Service Access — AlloyDB does not support public IP; PSA must be configured before cluster creation
• Use the AlloyDB Auth Proxy for connections outside the VPC — never expose the private IP directly
• Columnar engine is for analytics only — do not enable on tables with heavy OLTP write patterns; it adds overhead to writes
• Size read pools based on read traffic — do not use read pools as a substitute for query optimization
• Set --password at cluster creation — there is no way to recover the initial password; store it in Secret Manager
• Always specify sslmode=require in connection strings for security
• Enable Cloud Monitoring — configure roles/monitoring.viewer and set alerts on CPU, connections, and replication lag before going to production
• Run EXPLAIN ANALYZE before promoting queries — always validate query plans with EXPLAIN (ANALYZE, BUFFERS) on a representative dataset before production deployment
• Rotate credentials periodically — store passwords in Secret Manager and rotate on a schedule; use exponential backoff during rolling restarts

Validation Checkpoint

Before delivering configurations, verify:

• Private Service Access is configured (IP allocation + VPC peering)
• Cluster uses a VPC network, not the default network in production
• Primary instance has appropriate CPU count for the workload
• Connection pattern uses Auth Proxy or private IP (no public exposure)
• Passwords are sourced from Secret Manager, not hardcoded

Example

Columnar engine setup for an analytics workload:

-- Enable columnar engine on the orders table
SELECT google_columnar_engine_add('orders');

-- Check which columns AlloyDB recommends for columnar caching
SELECT table_name, column_name, estimated_benefit
FROM g_columnar_recommended_columns
ORDER BY estimated_benefit DESC;

-- Verify a query uses the columnar engine
EXPLAIN (ANALYZE) SELECT region, SUM(amount)
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY region;
-- Look for "Columnar Scan" in the plan output

Connection string for a Python application using the Auth Proxy:

DATABASE_URL = "postgresql+asyncpg://postgres:password@127.0.0.1:5432/mydb"
# Auth Proxy runs locally, forwarding to AlloyDB private IP

---

Observability

AlloyDB metrics are available under alloydb.googleapis.com/database/postgresql/* in Cloud Monitoring. Enable Cloud Monitoring before production launch.

Key metrics to watch:

• CPU utilization — alert above 80% sustained
• Active connections — alert above 80% of max_connections (200 on pg18)
• Replication lag on read pool nodes — alert above 30 seconds
• Dead tuple count — high values indicate autovacuum falling behind

PromQL patterns (Cloud Monitoring / Google Managed Prometheus):

# CPU utilization
avg_over_time(alloydb_googleapis_com:database_postgresql_cpu_utilization[5m])

# Active connections vs capacity
alloydb_googleapis_com:database_postgresql_network_connections

# Replication lag
max by (instance_id)(alloydb_googleapis_com:database_postgresql_replication_replica_lag_seconds)

Required role: roles/monitoring.viewer. See Observability Reference for full PromQL patterns, alert policy examples, and dashboard recommendations.

---

Data Plane Operations

Before promoting any query to production, validate with EXPLAIN (ANALYZE, BUFFERS). Monitor live workload via pg_stat_activity.

Quick patterns:

-- Active queries with duration
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Tables with bloat
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

See Operations Reference for EXPLAIN ANALYZE interpretation, bloat detection, autovacuum tuning, invalid index detection, and security hardening.

---

Production Patterns

Auth Proxy Sidecar (Kubernetes)

Run alloydb-auth-proxy as a sidecar container alongside the application pod. The sidecar uses the pod's workload identity (requires roles/alloydb.client) and refreshes IAM tokens automatically. The application connects to 127.0.0.1:5432 with no credential management in the app layer.

Credential Rotation

Store the database password in Secret Manager. On rotation: add a new secret version, update the AlloyDB user password via gcloud alloydb users set-password, perform a rolling restart with exponential backoff, then disable the old secret version.

pg18 max_connections

Set max_connections=200 for PostgreSQL 18 instances as the production baseline. For workloads exceeding 200 concurrent connections, add PgBouncer in transaction mode rather than raising the limit further.

See Operations Reference for the full Kubernetes sidecar spec, rotation runbook, and connection pooling guidance.

---

Disaster Recovery

Point-in-Time Recovery (PITR)

AlloyDB continuous backup enables PITR to any second within the retention window (default 14 days). Restoration creates a new cluster — the original cluster is unaffected.

gcloud alloydb clusters restore RESTORED_CLUSTER_ID \
    --region=REGION \
    --network=NETWORK \
    --source-cluster=projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID \
    --point-in-time="2025-06-15T14:30:00Z"

Cross-Region Replica Promotion

When the primary region is unavailable, promote the secondary cluster with:

gcloud alloydb clusters promote SECONDARY_CLUSTER_ID --region=SECONDARY_REGION

After promotion, update connection strings (via Secret Manager or environment config) to the promoted cluster endpoint. See Operations Reference for the full failover runbook and checklist.

---

Gemini CLI and MCP Toolbox

This section is for the connection layer, not for provisioning the AlloyDB cluster itself.

Prefer the dedicated Gemini CLI extensions for managed AlloyDB. They embed the underlying MCP Toolbox flow directly, so Gemini users do not need to configure a separate MCP server first.

Install the core AlloyDB extension:

gemini extensions install https://github.com/gemini-cli-extensions/alloydb --auto-update

Install the observability extension when the user wants metrics, dashboards, alerts, or query-performance monitoring:

gemini extensions install https://github.com/gemini-cli-extensions/alloydb-observability --auto-update

Prefer workspace-scoped configuration:

gemini extensions config alloydb --scope workspace

Guide the user through configuration before starting Gemini:

export ALLOYDB_POSTGRES_PROJECT="<your-gcp-project-id>"
export ALLOYDB_POSTGRES_REGION="<your-alloydb-region>"
export ALLOYDB_POSTGRES_CLUSTER="<your-alloydb-cluster-id>"
export ALLOYDB_POSTGRES_INSTANCE="<your-alloydb-instance-id>"
export ALLOYDB_POSTGRES_DATABASE="<your-database-name>"
export ALLOYDB_POSTGRES_USER="<your-database-user>"        # optional
export ALLOYDB_POSTGRES_PASSWORD="<your-database-password>" # optional
export ALLOYDB_POSTGRES_IP_TYPE="PRIVATE"                   # PRIVATE / PUBLIC / PSC

Important configuration guidance:

• Gemini CLI should be v0.6.0 or newer.
• Application Default Credentials must be available before starting Gemini.
• For read-only discovery, require roles/alloydb.viewer.
• For SQL access, require roles/alloydb.client.
• For admin actions, require roles/alloydb.admin plus roles/serviceusage.serviceUsageConsumer.
• For observability, also require roles/monitoring.viewer.
• Prefer IAM-first auth. Password prompts are fallback-only.
• If the instance uses private IP, Gemini CLI must run in the same VPC network.
• The extension binds connection settings at session start; if the user needs a different instance or database, save/resume chat and restart Gemini with the new environment.
• Recent upstream changes removed broken keychain password behavior, so do not promise keychain-backed credential storage.

For non-Gemini agents, or when the user explicitly wants a shared MCP server, guide them through MCP Toolbox with the AlloyDB prebuilt config instead of inventing a custom server:

{
  "mcpServers": {
    "alloydb": {
      "command": "./toolbox",
      "args": ["--prebuilt", "alloydb-postgres", "--stdio"],
      "env": {
        "ALLOYDB_POSTGRES_PROJECT": "PROJECT_ID",
        "ALLOYDB_POSTGRES_REGION": "REGION",
        "ALLOYDB_POSTGRES_CLUSTER": "CLUSTER_NAME",
        "ALLOYDB_POSTGRES_INSTANCE": "INSTANCE_NAME",
        "ALLOYDB_POSTGRES_DATABASE": "DATABASE_NAME",
        "ALLOYDB_POSTGRES_USER": "USERNAME",
        "ALLOYDB_POSTGRES_PASSWORD": "PASSWORD",
        "ALLOYDB_POSTGRES_IP_TYPE": "PRIVATE"
      }
    }
  }
}

For reusable project workflows, prefer generated workspace skills over one-off prompts:

toolbox --prebuilt alloydb-postgres skills-generate \
  --name alloydb-monitor \
  --toolset monitor \
  --description "AlloyDB monitoring skill" \
  --output-dir .agents/skills

If neither Gemini extensions nor MCP Toolbox are available, fall back to the manual gcloud, Auth Proxy, and SQL workflows already documented in this skill's reference files.

---

References Index

For detailed guides and code examples, refer to the following documents in references/:

• Cluster Setup
  • Cluster creation, instance types (primary, read pool), Private Service Access, gcloud commands.
• Features
  • Columnar engine, adaptive caching, ML embeddings, vector search, pgvector integration.
• Migration
  • Migration from Cloud SQL or on-prem PostgreSQL, Database Migration Service patterns.
• Observability
  • PromQL patterns, Cloud Monitoring setup, key metrics, alert policies, dashboard recommendations, Query Insights.
• Operations
  • EXPLAIN ANALYZE, pg_stat_activity, bloat detection, autovacuum tuning, invalid indexes, security hardening, PITR, cross-region failover, credential rotation, Auth Proxy sidecar.
• Gemini + MCP Guidance
  • Extension install, IAM prerequisites, env vars, private-IP constraints, and MCP Toolbox fallback config.

---

Official References

• https://cloud.google.com/alloydb/docs
• https://docs.cloud.google.com/alloydb/docs/connect-ide-using-mcp-toolbox
• https://github.com/gemini-cli-extensions/alloydb
• https://github.com/gemini-cli-extensions/alloydb-observability

Shared Styleguide Baseline

• Use shared styleguides for generic language/framework rules to reduce duplication in this skill.
• General Principles
• PostgreSQL / psql
• GCP Scripting
• Keep this skill focused on tool-specific workflows, edge cases, and integration details.