analyzing-timescaledb

name	analyzing-timescaledb
description	Use when working with Timescaledb — timescaleDB hypertable analysis, chunk management, continuous aggregates, compression policies, and time-series optimization.
connection_type	timescaledb
preload	false

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

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

• 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