Skip to content

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

analyzing-timescaledb

Verified

Use when working with Timescaledb — timescaleDB hypertable analysis, chunk management, continuous aggregates, compression policies, and time-series optimization.

TimescaleDB Analysis Skill

Analyze and optimize TimescaleDB hypertables with safe, read-only operations.

MANDATORY: Two-Phase Execution

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

Phase 1: Discovery (ALWAYS run first)

#!/bin/bash

# 1. Check TimescaleDB version
psql "$TIMESCALE_URI" -c "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';"

# 2. List hypertables
psql "$TIMESCALE_URI" -c "SELECT hypertable_schema, hypertable_name, num_dimensions, num_chunks, compression_enabled FROM timescaledb_information.hypertables ORDER BY hypertable_name;"

# 3. Get hypertable schema (never assume column names)
psql "$TIMESCALE_URI" -c "\d my_schema.my_hypertable"

# 4. Check dimensions (time column, partition columns)
psql "$TIMESCALE_URI" -c "SELECT * FROM timescaledb_information.dimensions WHERE hypertable_name = 'my_hypertable';"

# 5. Sample data
psql "$TIMESCALE_URI" -c "SELECT * FROM my_schema.my_hypertable ORDER BY time_column DESC LIMIT 5;"

Phase 1 outputs:

  • TimescaleDB version and hypertable list
  • Hypertable schemas with confirmed column names
  • Dimension info (time column, partitioning)

Phase 2: Analysis (only after Phase 1)

Only reference hypertables, columns, and chunks confirmed in Phase 1.

Shell Script Patterns

Helper Function

#!/bin/bash

# Core TimescaleDB query runner — always use this
tsdb_query() {
    local query="$1"
    psql "${TIMESCALE_URI:-postgres://localhost:5432/tsdb}" -t -A -F$'\t' -c "$query"
}

Anti-Hallucination Rules

  • NEVER reference a hypertable without confirming via timescaledb_information.hypertables
  • NEVER reference column names without seeing them in \d output
  • NEVER assume the time column name — always check timescaledb_information.dimensions
  • NEVER guess chunk intervals — always read from dimension info
  • NEVER assume compression settings — check timescaledb_information.compression_settings

Safety Rules

  • READ-ONLY ONLY: Use only SELECT, EXPLAIN, information views, pg_catalog queries
  • FORBIDDEN: DROP, ALTER, INSERT, UPDATE, DELETE, add_compression_policy, remove_compression_policy without explicit user request
  • ALWAYS add LIMIT to queries or use time-bounded WHERE clauses
  • Use EXPLAIN ANALYZE carefully — it executes the query
  • Prefer information views over raw chunk access

Common Operations

Hypertable Health Overview

#!/bin/bash
echo "=== TimescaleDB Version ==="
tsdb_query "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';"

echo ""
echo "=== Hypertables ==="
tsdb_query "SELECT hypertable_schema, hypertable_name, num_chunks, compression_enabled FROM timescaledb_information.hypertables ORDER BY hypertable_name;"

echo ""
echo "=== Total Hypertable Sizes ==="
tsdb_query "SELECT hypertable_schema || '.' || hypertable_name as hypertable, pg_size_pretty(hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)) as total_size, pg_size_pretty(hypertable_data_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)) as data_size, pg_size_pretty(hypertable_index_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)) as index_size FROM timescaledb_information.hypertables ORDER BY hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass) DESC;"

Chunk Management Analysis

#!/bin/bash
HYPERTABLE="${1:-my_hypertable}"

echo "=== Chunk Summary ==="
tsdb_query "SELECT count(*) as total_chunks, count(*) FILTER (WHERE is_compressed) as compressed_chunks, min(range_start) as oldest_data, max(range_end) as newest_data FROM timescaledb_information.chunks WHERE hypertable_name = '$HYPERTABLE';"

echo ""
echo "=== Recent Chunks (last 10) ==="
tsdb_query "SELECT chunk_name, pg_size_pretty(chunk_size(chunk_schema, chunk_name)) as size, range_start, range_end, is_compressed FROM timescaledb_information.chunks WHERE hypertable_name = '$HYPERTABLE' ORDER BY range_end DESC LIMIT 10;"

echo ""
echo "=== Chunk Sizes Distribution ==="
tsdb_query "SELECT CASE WHEN is_compressed THEN 'compressed' ELSE 'uncompressed' END as state, count(*) as chunks, pg_size_pretty(sum(chunk_size(chunk_schema, chunk_name))) as total_size FROM timescaledb_information.chunks WHERE hypertable_name = '$HYPERTABLE' GROUP BY is_compressed;"

Continuous Aggregates Analysis

#!/bin/bash
echo "=== Continuous Aggregates ==="
tsdb_query "SELECT view_schema, view_name, materialization_hypertable_schema, materialization_hypertable_name, view_definition FROM timescaledb_information.continuous_aggregates;"

echo ""
echo "=== Continuous Aggregate Policies ==="
tsdb_query "SELECT application_name, hypertable_schema, hypertable_name, schedule_interval, config FROM timescaledb_information.jobs WHERE application_name LIKE '%continuous%' ORDER BY hypertable_name;"

echo ""
echo "=== Job Stats ==="
tsdb_query "SELECT job_id, application_name, last_run_status, last_run_started_at, last_run_duration, next_start FROM timescaledb_information.job_stats ORDER BY last_run_started_at DESC LIMIT 15;"

Compression Analysis

#!/bin/bash
HYPERTABLE="${1:-my_hypertable}"

echo "=== Compression Settings ==="
tsdb_query "SELECT * FROM timescaledb_information.compression_settings WHERE hypertable_name = '$HYPERTABLE';"

echo ""
echo "=== Compression Ratio ==="
tsdb_query "SELECT pg_size_pretty(before_compression_total_bytes) as before, pg_size_pretty(after_compression_total_bytes) as after, round((1 - after_compression_total_bytes::numeric / before_compression_total_bytes) * 100, 1) as compression_pct FROM hypertable_compression_stats('$HYPERTABLE');" 2>/dev/null || echo "No compression stats available"

echo ""
echo "=== Retention Policies ==="
tsdb_query "SELECT application_name, hypertable_name, schedule_interval, config FROM timescaledb_information.jobs WHERE application_name LIKE '%retention%';"

Output Format

Present results as a structured report:

Analyzing Timescaledb 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

  • Chunk interval sizing: Too small = many chunks and overhead; too large = slow queries. Match to your query patterns
  • Compression timing: Compressing too recent data blocks inserts; too old wastes storage
  • Continuous aggregate refresh lag: Real-time aggregates have overhead; materialized-only have staleness
  • Index on time column: Hypertables automatically index the time dimension — avoid duplicate indexes
  • Cross-chunk queries: Queries spanning many chunks can be slow — use time-bounded WHERE clauses
  • Space partitioning: Hash partitioning adds complexity — only use when data exceeds single-node capacity
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: 193kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 193kUpdated 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: 193kUpdated Claude CodeCodexVerifiedInstall