analyzing-alloydb

name	analyzing-alloydb
description	Use when working with Alloydb — google AlloyDB instance analysis, query insights, columnar engine optimization, maintenance windows, and cluster health.
connection_type	gcp
preload	false

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

Shortcut	Counter	Why
"I'll skip discovery and check known resources"	Always run Phase 1 discovery first	Resource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"	Follow the full discovery → analysis flow	Quick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"	Audit configuration explicitly	Defaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"	Always check relevant metrics when available	API/CLI responses show current state; metrics reveal trends and intermittent issues
"I don't have access to that"	Try the command and report the actual error	Assumed permission failures prevent useful investigation; actual errors are informative

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