Analyzing Timescaledb

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

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