find-hypertable-candidates_skill

Overview

This skill analyzes an existing PostgreSQL database to find tables that would benefit from conversion to TimescaleDB hypertables. It scores tables by time-series characteristics, scale, and query patterns, and outputs actionable candidate reports to guide migration.

How this skill works

The tool inspects database metadata (pg_stat_user_tables, pg_indexes, pg_total_relation_size, pg_constraint) and optional query stats (pg_stat_statements) to measure insert/update ratios, table size, index patterns, and time-based query usage. It also supports code-level heuristics by scanning application SQL to detect append-only inserts, time filters, and aggregation patterns. Results include a candidacy score, pattern summary, size, access profile, and migration notes.

When to use it

• You have large tables (1M+ rows or 100MB+) with time or timestamp columns.
• Insert-heavy workloads with mostly appends and infrequent updates to historical rows.
• Frequent time-range queries, time-based aggregations, or dashboards that need faster reads.
• You plan to enable compression, retention, or continuous aggregates for analytics.
• Assessing whether TimescaleDB will help before running schema migrations.

Best practices

• Run the analysis against production-like stats (pg_stat_user_tables and pg_stat_statements) for accurate scoring.
• Ensure candidate tables have a clear time column (timestamp/timestamptz) or correlate IDs with time.
• Verify constraints: primary/unique keys usually must include the partition column or be adjusted before migration.
• Prioritize tables scoring 8+ points and test migration on a staging copy first.
• Use companion migration tooling to convert schema, create chunks, and enable compression/retention.

Example use cases

• Identify event_log and audit tables that are insert-heavy and used for time-range queries.
• Find sensor_readings or telemetry tables for IoT pipelines to enable compression and faster aggregations.
• Score financial trade or transaction tables for partitioning by timestamp and segmentation by symbol/account.
• Detect system metrics tables for continuous aggregates powering dashboards.
• Evaluate large append-only order histories where recent data is accessed more frequently.