{"id":"find-hypertable-candidates","name":"find-hypertable-candidates","summary":"このスキルを使って既存のPostgreSQLデータベースを分析し、どのテーブルをTimescaleやTimescaleDBハイパーテーブルに変換すべきかを特定してください。","body":"# PostgreSQL Hypertable Candidate Analysis\n\nIdentify tables that would benefit from TimescaleDB hypertable conversion. After identification, use the companion \"migrate-postgres-tables-to-hypertables\" skill for configuration and migration.\n\n## TimescaleDB Benefits\n\n**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).\n\n**Best for insert-heavy patterns:**\n\n- Time-series data (sensors, metrics, monitoring)\n- Event logs (user events, audit trails, application logs)\n- Transaction records (orders, payments, financial)\n- Sequential data (auto-incrementing IDs with timestamps)\n- Append-only datasets (immutable records, historical)\n\n**Requirements:** Large volumes (1M+ rows), time-based queries, infrequent updates\n\n## Step 1: Database Schema Analysis\n\n### Option A: From Database Connection\n\n#### Table statistics and size\n\n```sql\n-- Get all tables with row counts and insert/update patterns\nWITH table_stats AS (\n    SELECT\n        schemaname, tablename,\n        n_tup_ins as total_inserts,\n        n_tup_upd as total_updates,\n        n_tup_del as total_deletes,\n        n_live_tup as live_rows,\n        n_dead_tup as dead_rows\n    FROM pg_stat_user_tables\n),\ntable_sizes AS (\n    SELECT\n        schemaname, tablename,\n        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,\n        pg_total_relation_size(schemaname||'.'||tablename) as total_size_bytes\n    FROM pg_tables\n    WHERE schemaname NOT IN ('information_schema', 'pg_catalog')\n)\nSELECT\n    ts.schemaname, ts.tablename, ts.live_rows,\n    tsize.total_size, tsize.total_size_bytes,\n    ts.total_inserts, ts.total_updates, ts.total_deletes,\n    ROUND(CASE WHEN ts.live_rows > 0\n          THEN (ts.total_inserts::float / ts.live_rows) * 100\n          ELSE 0 END, 2) as insert_ratio_pct\nFROM table_stats ts\nJOIN table_sizes tsize ON ts.schemaname = tsize.schemaname AND ts.tablename = tsize.tablename\nORDER BY tsize.total_size_bytes DESC;\n```\n\n**Look for:**\n\n- mostly insert-heavy patterns (less updates/deletes)\n- big tables (1M+ rows or 100MB+)\n\n#### Index patterns\n\n```sql\n-- Identify common query dimensions\nSELECT schemaname, tablename, indexname, indexdef\nFROM pg_indexes\nWHERE schemaname NOT IN ('information_schema', 'pg_catalog')\nORDER BY tablename, indexname;\n```\n\n**Look for:**\n\n- Multiple indexes with timestamp/created_at columns → time-based queries\n- Composite (entity_id, timestamp) indexes → good candidates\n- Time-only indexes → time range filtering common\n\n#### Query patterns (if pg_stat_statements available)\n\n```sql\n-- Check availability\nSELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements');\n\n-- Analyze expensive queries for candidate tables\nSELECT query, calls, mean_exec_time, total_exec_time\nFROM pg_stat_statements\nWHERE query ILIKE '%your_table_name%'\nORDER BY total_exec_time DESC LIMIT 20;\n```\n\n**✅ Good patterns:** Time-based WHERE, entity filtering combined with time-based qualifiers, GROUP BY time_bucket, range queries over time\n**❌ Poor patterns:** Non-time lookups with no time-based qualifiers in same query (WHERE email = ...)\n\n#### Constraints\n\n```sql\n-- Check migration compatibility\nSELECT conname, contype, pg_get_constraintdef(oid) as definition\nFROM pg_constraint\nWHERE conrelid = 'your_table_name'::regclass;\n```\n\n**Compatibility:**\n\n- Primary keys (p): Must include partition column or ask user if can be modified\n- Foreign keys (f): Plain→Hypertable and Hypertable→Plain OK, Hypertable→Hypertable NOT supported\n- Unique constraints (u): Must include partition column or ask user if can be modified\n- Check constraints (c): Usually OK\n\n### Option B: From Code Analysis\n\n#### ✅ GOOD Patterns\n\n```python\n# Append-only logging\nINSERT INTO events (user_id, event_time, data) VALUES (...);\n# Time-series collection\nINSERT INTO metrics (device_id, timestamp, value) VALUES (...);\n# Time-based queries\nSELECT * FROM metrics WHERE timestamp >= NOW() - INTERVAL '24 hours';\n# Time aggregations\nSELECT DATE_TRUNC('day', timestamp), COUNT(*) GROUP BY 1;\n```\n\n#### ❌ POOR Patterns\n\n```python\n# Frequent updates to historical records\nUPDATE users SET email = ..., updated_at = NOW() WHERE id = ...;\n# Non-time lookups\nSELECT * FROM users WHERE email = ...;\n# Small reference tables\nSELECT * FROM countries ORDER BY name;\n```\n\n#### Schema Indicators\n\n**✅ GOOD:**\n\n- Has timestamp/timestamptz column\n- Multiple indexes with timestamp-based columns\n- Composite (entity_id, timestamp) indexes\n\n**❌ POOR:**\n\n- Mostly indexes with non-time-based columns (on columns like email, name, status, etc.)\n- Columns that you expect to be updated over time (updated_at, updated_by, status, etc.)\n- Unique constraints on non-time fields\n- Frequent updated_at modifications\n- Small static tables\n\n#### Special Case: ID-Based Tables\n\nSequential ID tables can be candidates if:\n\n- Insert-mostly pattern / updates are either infrequent or only on recent records.\n- If updates do happen, they occur on recent records (such as an order status being updated orderered->processing->delivered. Note once an order is delivered, it is unlikely to be updated again.)\n- IDs correlate with time (as is the case for serial/auto-incrementing IDs/GENERATED ALWAYS AS IDENTITY)\n- ID is the primary query dimension\n- Recent data accessed more often (frequently the case in ecommerce, finance, etc.)\n- Time-based reporting common (e.g. monthly, daily summaries/analytics)\n\n```sql\nCREATE TABLE orders (\n    id BIGSERIAL PRIMARY KEY,           -- Can partition by ID\n    user_id BIGINT,\n    created_at TIMESTAMPTZ DEFAULT NOW() -- For sparse indexes\n);\n```\n\nNote: For ID-based tables where there is also a time column (created_at, ordered_at, etc.),\nyou can partition by ID and use sparse indexes on the time column.\nSee the `migrate-postgres-tables-to-hypertables` skill for details.\n\n## Step 2: Candidacy Scoring (8+ points = good candidate)\n\n### Time-Series Characteristics (5+ points needed)\n\n- Has timestamp/timestamptz column: **3 points**\n- Data inserted chronologically: **2 points**\n- Queries filter by time: **2 points**\n- Time aggregations common: **2 points**\n\n### Scale & Performance (3+ points recommended)\n\n- Large table (1M+ rows or 100MB+): **2 points**\n- High insert volume: **1 point**\n- Infrequent updates to historical: **1 point**\n- Range queries common: **1 point**\n- Aggregation queries: **2 points**\n\n### Data Patterns (bonus)\n\n- Contains entity ID for segmentation (device_id, user_id, product_id, symbol, etc.): **1 point**\n- Numeric measurements: **1 point**\n- Log/event structure: **1 point**\n\n## Common Patterns\n\n### ✅ GOOD Candidates\n\n**✅ Event/Log Tables** (user_events, audit_logs)\n\n```sql\nCREATE TABLE user_events (\n    id BIGSERIAL PRIMARY KEY,\n    user_id BIGINT,\n    event_type TEXT,\n    event_time TIMESTAMPTZ DEFAULT NOW(),\n    metadata JSONB\n);\n-- Partition by id, segment by user_id, enable minmax sparse_index on event_time\n```\n\n**✅ Sensor/IoT Data** (sensor_readings, telemetry)\n\n```sql\nCREATE TABLE sensor_readings (\n    device_id TEXT,\n    timestamp TIMESTAMPTZ,\n    temperature DOUBLE PRECISION,\n    humidity DOUBLE PRECISION\n);\n-- Partition by timestamp, segment by device_id, minmax sparse indexes on temperature and humidity\n```\n\n**✅ Financial/Trading** (stock_prices, transactions)\n\n```sql\nCREATE TABLE stock_prices (\n    symbol VARCHAR(10),\n    price_time TIMESTAMPTZ,\n    open_price DECIMAL,\n    close_price DECIMAL,\n    volume BIGINT\n);\n-- Partition by price_time, segment by symbol, minmax sparse indexes on open_price and close_price and volume\n```\n\n**✅ System Metrics** (monitoring_data)\n\n```sql\nCREATE TABLE system_metrics (\n    hostname TEXT,\n    metric_time TIMESTAMPTZ,\n    cpu_usage DOUBLE PRECISION,\n    memory_usage BIGINT\n);\n-- Partition by metric_time, segment by hostname, minmax sparse indexes on cpu_usage and memory_usage\n```\n\n### ❌ POOR Candidates\n\n**❌ Reference Tables** (countries, categories)\n\n```sql\nCREATE TABLE countries (\n    id SERIAL PRIMARY KEY,\n    name VARCHAR(100),\n    code CHAR(2)\n);\n-- Static data, no time component\n```\n\n**❌ User Profiles** (users, accounts)\n\n```sql\nCREATE TABLE users (\n    id BIGSERIAL PRIMARY KEY,\n    email VARCHAR(255),\n    created_at TIMESTAMPTZ,\n    updated_at TIMESTAMPTZ\n);\n-- Accessed by ID, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is id or email)\n```\n\n**❌ Settings/Config** (user_settings)\n\n```sql\nCREATE TABLE user_settings (\n    user_id BIGINT PRIMARY KEY,\n    theme VARCHAR(20),       -- Changes: light -> dark -> auto\n    language VARCHAR(10),    -- Changes: en -> es -> fr\n    notifications JSONB,     -- Frequent preference updates\n    updated_at TIMESTAMPTZ\n);\n-- Accessed by user_id, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is user_id)\n```\n\n## Analysis Output Requirements\n\nFor each candidate table provide:\n\n- **Score:** Based on criteria (8+ = strong candidate)\n- **Pattern:** Insert vs update ratio\n- **Access:** Time-based vs entity lookups\n- **Size:** Current size and growth rate\n- **Queries:** Time-range, aggregations, point lookups\n\nFocus on insert-heavy patterns with time-based or sequential access. Tables scoring 8+ points are strong candidates for conversion.","author":"@timescale","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/find-hypertable-candidates","license":"Apache-2.0","category":"review","lang":"en","tokens":2233,"stars":0,"calls30d":1,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":[]}}