Analyzing Alloydb

AlloyDB Analysis Skill

Analyze and optimize AlloyDB clusters with safe, read-only operations.

MANDATORY: Two-Phase Execution

You MUST follow this two-phase pattern. Skipping Phase 1 causes hallucinated cluster/instance names.

Phase 1: Discovery (ALWAYS run first)

#!/bin/bash

# 1. List AlloyDB clusters
gcloud alloydb clusters list --project="$GCP_PROJECT" --region="$REGION" --format=json

# 2. List instances in a cluster
gcloud alloydb instances list --cluster="$CLUSTER_NAME" --region="$REGION" --project="$GCP_PROJECT" --format=json

# 3. List databases
psql "$ALLOYDB_URI" -c "SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname;"

# 4. List tables (never assume names)
psql "$ALLOYDB_URI" -c "SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY schemaname, tablename;"

# 5. Describe table
psql "$ALLOYDB_URI" -c "\d my_schema.my_table"

Phase 1 outputs:

• AlloyDB clusters and instances
• Databases, schemas, and tables
• Table schemas with confirmed column names

Phase 2: Analysis (only after Phase 1)

Only reference clusters, instances, databases, and tables confirmed in Phase 1.

Shell Script Patterns

Helper Function

#!/bin/bash

# Core AlloyDB query runner — always use this
alloydb_query() {
    local query="$1"
    psql "${ALLOYDB_URI}" -t -A -F$'\t' -c "$query"
}

# gcloud AlloyDB helper
alloydb_cmd() {
    gcloud alloydb "$@" --project="${GCP_PROJECT}" --region="${REGION}" --format=json
}

Anti-Hallucination Rules

• NEVER reference a cluster or instance without confirming via gcloud alloydb clusters/instances list
• NEVER reference database/table names without confirming via catalog queries
• NEVER reference column names without running \d or information_schema queries
• NEVER assume columnar engine status — always check configuration
• NEVER guess maintenance window — always query cluster settings

Safety Rules

• READ-ONLY ONLY: Use only SELECT, EXPLAIN, pg_catalog/information_schema queries, gcloud list/describe
• FORBIDDEN: DROP, ALTER, INSERT, UPDATE, DELETE, gcloud alloydb instances delete without explicit user request
• ALWAYS add LIMIT to user table queries
• Use EXPLAIN ANALYZE carefully — it executes the query

Common Operations

Cluster Health Overview

#!/bin/bash
echo "=== AlloyDB Clusters ==="
alloydb_cmd clusters list | jq '.[] | {name: .name, state: .state, databaseVersion: .databaseVersion, network: .network}'

echo ""
echo "=== Instances ==="
alloydb_cmd instances list --cluster="$CLUSTER_NAME" | jq '.[] | {name: .name, instanceType: .instanceType, state: .state, machineConfig: .machineConfig, availabilityType: .availabilityType}'

echo ""
echo "=== Database Sizes ==="
alloydb_query "SELECT datname, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database WHERE datistemplate = false ORDER BY pg_database_size(datname) DESC;"

echo ""
echo "=== PostgreSQL Version ==="
alloydb_query "SELECT version();"

Query Insights

#!/bin/bash
echo "=== Slow Queries (last 1h) ==="
alloydb_query "SELECT calls, ROUND(total_exec_time::numeric, 2) as total_ms, ROUND(mean_exec_time::numeric, 2) as mean_ms, rows, SUBSTRING(query, 1, 100) as query_preview FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;"

echo ""
echo "=== Active Queries ==="
alloydb_query "SELECT pid, state, EXTRACT(EPOCH FROM (now() - query_start))::int as runtime_sec, wait_event_type, wait_event, SUBSTRING(query, 1, 80) as query_preview FROM pg_stat_activity WHERE state = 'active' AND pid != pg_backend_pid() ORDER BY runtime_sec DESC;"

echo ""
echo "=== Table I/O Statistics ==="
alloydb_query "SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables ORDER BY seq_tup_read DESC LIMIT 15;"

Columnar Engine Analysis

#!/bin/bash
echo "=== Columnar Engine Status ==="
alloydb_query "SHOW google_columnar_engine.enabled;" 2>/dev/null || echo "Columnar engine setting not available"

echo ""
echo "=== Columnar Engine Tables ==="
alloydb_query "SELECT * FROM g_columnar_recommended_columns ORDER BY estimated_size_reduction DESC LIMIT 20;" 2>/dev/null || echo "Columnar recommendations not available"

echo ""
echo "=== Columnar Cache Usage ==="
alloydb_query "SELECT * FROM g_columnar_relations;" 2>/dev/null || echo "No columnar relations found"

Maintenance & Backup Status

#!/bin/bash
echo "=== Maintenance Window ==="
alloydb_cmd clusters describe "$CLUSTER_NAME" | jq '.maintenanceUpdatePolicy'

echo ""
echo "=== Backups ==="
gcloud alloydb backups list --project="$GCP_PROJECT" --region="$REGION" --format=json | jq '.[] | {name: .name, state: .state, type: .type, createTime: .createTime, sizeBytes: .sizeBytes}'

echo ""
echo "=== Instance Metrics ==="
gcloud monitoring time-series list \
    --project="$GCP_PROJECT" \
    --filter='metric.type="alloydb.googleapis.com/database/cpu/utilization"' \
    --interval-start-time="$(date -u -v-1H +%Y-%m-%dT%H:%M:%SZ 2>/dev/null || date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ)" \
    --format="table(points.value)" 2>/dev/null

Output Format

Present results as a structured report:

Analyzing Alloydb Report
════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Counter-Rationalizations

Common Pitfalls

• AlloyDB is PostgreSQL-compatible: Most pg_* system views work, but some extensions differ
• Columnar engine: Auto-columnar may not cover all tables — check recommendations
• Read pool routing: Read replicas serve read traffic — ensure connection string targets the correct pool
• IAM authentication: AlloyDB supports IAM auth — check if password or IAM is configured
• VPC-only: AlloyDB instances are VPC-only — ensure network connectivity before debugging
• pg_stat_statements: Must be enabled in database flags — check before relying on query stats