find-hypertable-candidates

What the source does

• FilesystemNone observedNo reads or writes in the source
• NetworkNone observedNo outbound calls in the source
• ShellNone observedNo commands run in the source
• DependenciesPinnedNothing floats
• SecretsNone foundNo keys in source

Five audit layers

Gates applied: no behavioural pass.

skills/postgres/references/backfill-strategies.md

skills/postgres/references/complete-example.md

skills/postgres/references/design-postgis-tables.md

skills/postgres/references/design-postgres-tables.md

skills/postgres/references/find-hypertable-candidates.md

Audit 0.4.1 · source sha c8ed45084a23 · 26 Oct 2026Report an issue or request a re-scan

• Publishertimescale GitHub organizationOwner of the repository this skill is read from
• Source foldertimescale/pg-aiguide/skills/find-hypertable-candidatesThe exact folder we audited
• LicenceApache-2.0As the repository states it
• Commit pinned for this verdictc8ed45084a23A new audit runs when the source changes
• Repository statusNot archivedLast push 3 days ago
• Sandboxed runNot runSkills are read, not executed; layer L4 is skipped

Its description: Use this skill to analyze an existing PostgreSQL database and identify which tables should be converted to Timescale/TimescaleDB hypertables.

---
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-Ba

Excerpt, first 6,000 characters of SKILL.md, verbatim from the audited commit. It is text from someone else’s repository, quoted so you can judge it before your agent reads it.

git clone https://github.com/timescale/pg-aiguide
git -C pg-aiguide checkout c8ed45084a23

Works in

1. 2026-10-26
   ✓SAFE B 89 / 100

   first audit · source c8ed45084a23

Questions about find-hypertable-candidates

• github.com/timescale/pg-aiguide/tree/main/skills/find-hypertable — the audited source at commit c8ed45084a23 and every finding aboveObserved · 26 Oct 2026

Spotted something wrong? Report a correction · Methodology