Skip to content
Skills Library
Data & analytics · Analysis

find-hypertable-candidates

/find-hypertable-candidates

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 detectio

Repository
pg-aiguide ↗
Licence
Apache-2.0
Last repository change
22.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 22.10.2026, without references to retired models or APIs.

Licence

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

What it does and when to use it

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 detectio

Original author 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 detectio

How it works

  1. TimescaleDB Benefits
  2. Step 1: Database Schema Analysis
  3. Step 2: Candidacy Scoring (8+ points = good candidate)
  4. Common Patterns
  5. Analysis Output Requirements
Original SKILL.md headings · 302 lines, 1,218 words
SKILL.md preview
---
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

```sql
-- 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

```sql
-- 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)

```sql
-- 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

```sql
-- 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

```python
# 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

```python
# 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-Based Tables

Sequential ID tables can be candidates if:

- Insert-mostly pattern / updates are either infrequent or only on recent records.
- 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.)
- IDs correlate with time (as is the case for serial/auto-incrementing IDs/GENERATED ALWAYS AS IDENTITY)
- ID is the primary query dimension
- Recent data accessed more often (frequently the case in ecommerce, finance, etc.)
- Time-based reporting common (e.g. monthly, daily summaries/analytics)

```sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,           -- Can partition by ID
    user_id BIGINT,
    created_at TIMESTAMPTZ DEFAULT NOW() -- For sparse indexes
);
```

Note: For ID-based tables where there is also a time column (created_at, ordered_at, etc.),
you can partition by ID and use sparse indexes on the time column.
See the `migrate-postgres-tables-to-hypertables` skill for details.

## Step 2: Candidacy Scoring (8+ points = good candidate)

### Time-Series Characteristics (5+ points needed)

- Has timestamp/timestamptz column: **3 points**
- Data inserted chronologically: **2 points**
- Queries filter by time: **2 points**
- Time aggregations common: **2 points**

### Scale & Performance (3+ points recommended)

- Large table (1M+ rows or 100MB+): **2 points**
- High insert volume: **1 point**
- Infrequent updates to historical: **1 point**
- Range queries common: **1 point**
- Aggregation queries: **2 points**

### Data Patterns (bonus)

- Contains entity ID for segmentation (device_id, user_id, product_id, symbol, etc.): **1 point**
- Numeric measurements: **1 point**
- Log/event structure: **1 point**

## Common Patterns

### ✅ GOOD Candidates

**✅ Event/Log Tables** (user_events, audit_logs)

```sql
CREATE TABLE user_events (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    event_type TEXT,
    event_time TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB
);
-- Partition by id, segment by user_id, enable minmax sparse_index on event_time
```

**✅ Sensor/IoT Data** (sensor_readings, telemetry)

```sql
CREATE TABLE sensor_readings (
    device_id TEXT,
    timestamp TIMESTAMPTZ,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
);
-- Partition by timestamp, segment by device_id, minmax sparse indexes on temperature and humidity
```

**✅ Financial/Trading** (stock_prices, transactions)

```sql
CREATE TABLE stock_prices (
    symbol VARCHAR(10),
    price_time TIMESTAMPTZ,
    open_price DECIMAL,
    close_price DECIMAL,
    volume BIGINT
);
-- Partition by price_time, segment by symbol, minmax sparse indexes on open_price and close_price and volume
```

**✅ System Metrics** (monitoring_data)

```sql
CREATE TABLE system_metrics (
    hostname TEXT,
    metric_time TIMESTAMPTZ,
    cpu_usage DOUBLE PRECISION,
    memory_usage BIGINT
);
-- Partition by metric_time, segment by hostname, minmax sparse indexes on cpu_usage and memory_usage
```

### ❌ POOR Candidates

**❌ Reference Tables** (countries, categories)

```sql
CREATE TABLE countries (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    code CHAR(2)
);
-- Static data, no time component
```

**❌ User Profiles** (users, accounts)

```sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255),
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
);
-- Accessed by ID, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is id or email)
```

**❌ Settings/Config** (user_settings)

```sql
CREATE TABLE user_settings (
    user_id BIGINT PRIMARY KEY,
    theme VARCHAR(20),       -- Changes: light -> dark -> auto
    language VARCHAR(10),    -- Changes: en -> es -> fr
    notifications JSONB,     -- Frequent preference updates
    updated_at TIMESTAMPTZ
);
-- Accessed by user_id, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is user_id)
```

## Analysis Output Requirements

For each candidate table provide:

- **Score:** Based on criteria (8+ = strong candidate)
- **Pattern:** Insert vs update ratio
- **Access:** Time-based vs entity lookups
- **Size:** Current size and growth rate
- **Queries:** Time-range, aggregations, point lookups

Focus on insert-heavy patterns with time-based or sequential access. Tables scoring 8+ points are strong candidates for conversion.

Versions

The database has no other skills named find-hypertable-candidates.

Identical content appears in 1 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version 6827962e7359 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100migrate-postgres-tables-to-hypertables
    Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB hypertables with optimal configuration and validation. **Trigger when user asks to:** - Migrate or convert PostgreSQL tables to hypertables - Execute hypertable migration with minimal downtime - Plan…
  • 100setup-timescaledb-hypertables
    Use this skill when creating database schemas or tables for Timescale, TimescaleDB, TigerData, or Tiger Cloud, especially for time-series, IoT, metrics, events, or log data. Use this to improve the performance of any insert-heavy table. **Trigger when user asks to:** - Create or…
  • 100timescaledb-time-series-operations-and-configuration
    Covers TimescaleDB as a PostgreSQL extension for time-series workloads: hypertable creation and chunk-interval sizing, continuous aggregates for pre-computed rollups, and retention/compression policies. Use when the user asks to "set up a TimescaleDB hypertable," "size chunk…
  • 100timescaledb
    Expert TimescaleDB time-series database assistance covering PostgreSQL hypertables, continuous aggregates, and data retention policies. Use when storing high-volume time-series telemetry, IoT metrics, or financial data.
  • 100timescale-db-time-series-storage
    Use this skill when storing, partitioning, and querying massive IoT and metric telemetry using TimescaleDB hypertables.
  • 80role-database:time-series-databases
    Deep operational guide for 14 time-series databases. InfluxDB (Flux, 3.0 Arrow/Parquet, Telegraf), Prometheus (PromQL, Thanos/Mimir), TimescaleDB (hypertables, continuous aggregates), QuestDB, VictoriaMetrics, TDengine, IoTDB, Graphite, KDB+, OpenTSDB, M3DB, CrateDB, Timestream,…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.