Skip to content

cloudthinker-ai/CloudSkills / skills/connections/managing-duckdb

managing-duckdb

Verified

Use when working with Duckdb — duckDB database management, query performance analysis, extension management, and storage optimization. Covers database file inspection, table statistics, memory configuration, Parquet/CSV import health, and columnar storage efficiency metrics. Read this skill before any DuckDB operations.

DuckDB Management Skill

Monitor, analyze, and optimize DuckDB instances safely.

MANDATORY: Discovery-First Pattern

Always check database metadata and list tables before any query operations. Never assume table names or column types.

Phase 1: Discovery

#!/bin/bash

DUCKDB_FILE="${DUCKDB_FILE:-my_database.duckdb}"

duck_query() {
    duckdb "$DUCKDB_FILE" -c "$1" 2>/dev/null
}

echo "=== DuckDB Version ==="
duckdb -c "SELECT version();" 2>/dev/null

echo ""
echo "=== Database Info ==="
duck_query "CALL pragma_database_size();"

echo ""
echo "=== Schemas ==="
duck_query "SELECT schema_name FROM information_schema.schemata;"

echo ""
echo "=== Tables ==="
duck_query "SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema') ORDER BY table_schema, table_name;"

echo ""
echo "=== Extensions ==="
duck_query "SELECT extension_name, installed, loaded FROM duckdb_extensions() ORDER BY installed DESC, extension_name;"

echo ""
echo "=== Settings (key) ==="
duck_query "SELECT name, value FROM duckdb_settings() WHERE name IN ('memory_limit','threads','default_order','access_mode','temp_directory');"

Phase 1 outputs: Version, database size, table inventory, installed extensions, key settings.

Phase 2: Analysis

#!/bin/bash

DUCKDB_FILE="${DUCKDB_FILE:-my_database.duckdb}"
TABLE="${1:-my_table}"

duck_query() {
    duckdb "$DUCKDB_FILE" -c "$1" 2>/dev/null
}

echo "=== Table Details ==="
duck_query "DESCRIBE $TABLE;"

echo ""
echo "=== Table Statistics ==="
duck_query "SELECT COUNT(*) as row_count FROM $TABLE;"
duck_query "CALL pragma_storage_info('$TABLE');" | head -20

echo ""
echo "=== Column Stats ==="
duck_query "SELECT column_name, column_type, null_percentage, avg_width, n_unique FROM pragma_table_info('$TABLE');" 2>/dev/null || \
    duck_query "SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name='$TABLE';"

echo ""
echo "=== Memory Usage ==="
duck_query "SELECT * FROM pragma_database_size();"

echo ""
echo "=== Recent Queries Profile ==="
duck_query "SELECT * FROM pragma_last_profiling_output;" 2>/dev/null || echo "Profiling not enabled"

echo ""
echo "=== Storage Info ==="
ls -lh "$DUCKDB_FILE" 2>/dev/null
ls -lh "${DUCKDB_FILE}.wal" 2>/dev/null || echo "No WAL file"

Output Format

DUCKDB ANALYSIS
===============
Version: [version] | File: [path] | Size: [size]
Tables: [count] | Extensions: [loaded]

ISSUES FOUND:
- [issue with affected table/setting]

RECOMMENDATIONS:
- [actionable recommendation]

Anti-Hallucination Rules

  1. NEVER assume resource names — always discover via CLI/API in Phase 1 before referencing in Phase 2.
  2. NEVER fabricate metric names or dimensions — verify against the service documentation or --help output.
  3. NEVER mix CLI commands between service versions — confirm which version/API you are targeting.
  4. ALWAYS use the discovery → verify → analyze chain — every resource referenced must have been discovered first.
  5. ALWAYS handle empty results gracefully — an empty response is valid data, not an error to retry.

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
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexVerifiedInstall