/find-hypertable-candidates

name: find-hypertable-candidates
description: |
  Use this skill to analyze an existing PostgreSQL database and identify which tables should be converted to Timescale/TimescaleDB hypertables.

  **Trigger when user asks to:**
  - Analyze database tables for hypertable conversion potential
  - Identify time-series or event tables in an existing schema
  - Evaluate if a table would benefit from Timescale/TimescaleDB
  - Audit PostgreSQL tables for migration to Timescale/TimescaleDB/TigerData
  - Score or rank tables for hypertable candidacy


  **Keywords:** hypertable candidate, table analysis, migration assessment, Timescale, TimescaleDB, time-series detection, insert-heavy tables, event logs, audit tables

  Provides SQL queries to analyze table statistics, index patterns, and query patterns. Includes scoring criteria (8+ points = good candidate) and pattern recognition for IoT, events, transactions, and sequential data.
license: Apache-2.0
compatibility: Requires PostgreSQL 15+ with TimescaleDB
metadata:
  author: tigerdata

PostgreSQL Hypertable Candidate Analysis

Identify tables that would benefit from TimescaleDB hypertable conversion. After identification, use the companion "migrate-postgres-tables-to-hypertables" skill for configuration and migration.

TimescaleDB Benefits

**Performance gains:** 90%+ compression, fast time-based queries, improved insert performance, efficient aggregations, continuous aggregates for materialization (dashboards, reports, analytics), automatic data management (retention, compression).

**Best for insert-heavy patterns:**

• Time-series data (sensors, metrics, monitoring)
• Event logs (user events, audit trails, application logs)
• Transaction records (orders, payments, financial)
• Sequential data (auto-incrementing IDs with timestamps)
• Append-only datasets (immutable records, historical)

**Requirements:** Large volumes (1M+ rows), time-based queries, infrequent updates

Step 1: Database Schema Analysis

Option A: From Database Connection

Table statistics and size

-- Get all tables with row counts and insert/update patterns
WITH table_stats AS (
    SELECT
        schemaname, tablename,
        n_tup_ins as total_inserts,
        n_tup_upd as total_updates,
        n_tup_del as total_deletes,
        n_live_tup as live_rows,
        n_dead_tup as dead_rows
    FROM pg_stat_user_tables
),
table_sizes AS (
    SELECT
        schemaname, tablename,
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
        pg_total_relation_size(schemaname||'.'||tablename) as total_size_bytes
    FROM pg_tables
    WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
)
SELECT
    ts.schemaname, ts.tablename, ts.live_rows,
    tsize.total_size, tsize.total_size_bytes,
    ts.total_inserts, ts.total_updates, ts.total_deletes,
    ROUND(CASE WHEN ts.live_rows > 0
          THEN (ts.total_inserts::float / ts.live_rows) * 100
          ELSE 0 END, 2) as insert_ratio_pct
FROM table_stats ts
JOIN table_sizes tsize ON ts.schemaname = tsize.schemaname AND ts.tablename = tsize.tablename
ORDER BY tsize.total_size_bytes DESC;

**Look for:**

• mostly insert-heavy patterns (less updates/deletes)
• big tables (1M+ rows or 100MB+)

Index patterns

-- Identify common query dimensions
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY tablename, indexname;

**Look for:**

• Multiple indexes with timestamp/created_at columns → time-based queries
• Composite (entity_id, timestamp) indexes → good candidates
• Time-only indexes → time range filtering common

Query patterns (if pg_stat_statements available)

-- Check availability
SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements');

-- Analyze expensive queries for candidate tables
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%your_table_name%'
ORDER BY total_exec_time DESC LIMIT 20;

**✅ Good patterns:** Time-based WHERE, entity filtering combined with time-based qualifiers, GROUP BY time_bucket, range queries over time **❌ Poor patterns:** Non-time lookups with no time-based qualifiers in same query (WHERE email = ...)

Constraints

-- Check migration compatibility
SELECT conname, contype, pg_get_constraintdef(oid) as definition
FROM pg_constraint
WHERE conrelid = 'your_table_name'::regclass;

**Compatibility:**

• Primary keys (p): Must include partition column or ask user if can be modified
• Foreign keys (f): Plain→Hypertable and Hypertable→Plain OK, Hypertable→Hypertable NOT supported
• Unique constraints (u): Must include partition column or ask user if can be modified
• Check constraints (c): Usually OK

Option B: From Code Analysis

✅ GOOD Patterns

# Append-only logging
INSERT INTO events (user_id, event_time, data) VALUES (...);
# Time-series collection
INSERT INTO metrics (device_id, timestamp, value) VALUES (...);
# Time-based queries
SELECT * FROM metrics WHERE timestamp >= NOW() - INTERVAL '24 hours';
# Time aggregations
SELECT DATE_TRUNC('day', timestamp), COUNT(*) GROUP BY 1;

❌ POOR Patterns

# Frequent updates to historical records
UPDATE users SET email = ..., updated_at = NOW() WHERE id = ...;
# Non-time lookups
SELECT * FROM users WHERE email = ...;
# Small reference tables
SELECT * FROM countries ORDER BY name;

Schema Indicators

**✅ GOOD:**

• Has timestamp/timestamptz column
• Multiple indexes with timestamp-based columns
• Composite (entity_id, timestamp) indexes

**❌ POOR:**

• Mostly indexes with non-time-based columns (on columns like email, name, status, etc.)
• Columns that you expect to be updated over time (updated_at, updated_by, status, etc.)
• Unique constraints on non-time fields
• Frequent updated_at modifications
• Small static tables

Special Case: ID-Ba