Skip to content

cloudthinker-ai/CloudSkills / skills/connections/analyzing-alloydb

analyzing-alloydb

Unverified

Use when working with Alloydb — google AlloyDB instance analysis, query insights, columnar engine optimization, maintenance windows, and cluster health.

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

ShortcutCounterWhy
"I'll skip discovery and check known resources"Always run Phase 1 discovery firstResource names change, new resources appear — assumed names cause errors
"The user only asked for a quick check"Follow the full discovery → analysis flowQuick checks miss critical issues; structured analysis catches silent failures
"Default configuration is probably fine"Audit configuration explicitlyDefaults often leave logging, security, and optimization features disabled
"Metrics aren't needed for this"Always check relevant metrics when availableAPI/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 errorAssumed 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
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 196kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 192kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 192kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 192kUpdated Claude CodeCodexVerifiedInstall