find-hypertable-candidates

Q: How do I install find-hypertable-candidates?

Run npx skills add https://github.com/timescale/pg-aiguide --skill find-hypertable-candidates in your project. The skills CLI detects the agents you use and copies the skill into the right folder. For Claude Code you can also place the skill folder at ~/.claude/skills/find-hypertable-candidates/ (personal) or .claude/skills/find-hypertable-candidates/ (project).

by timescaleData & Databases

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.

Installs: 392
Last 24h: —
Trending rank: —
Security: Not audited

Install

Works with every agent the skills CLI supports.

npx skills add https://github.com/timescale/pg-aiguide --skill find-hypertable-candidates

01
Install
Run the command in your project. The CLI asks which agents to install for.
02
Describe the task
Ask your agent for something this skill covers. It loads find-hypertable-candidates automatically when relevant.
03
Review the result
The agent follows the SKILL.md steps, scripts and references bundled with the skill.

SKILL.md

The instructions your agent reads when the skill is triggered.

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

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)

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)

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)

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)

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)

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

❌ User Profiles (users, accounts)

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)

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.

FAQ

What does the find-hypertable-candidates skill do?

How do I install find-hypertable-candidates?

Run npx skills add https://github.com/timescale/pg-aiguide --skill find-hypertable-candidates in your project. The skills CLI detects the agents you use and copies the skill into the right folder. For Claude Code you can also place the skill folder at ~/.claude/skills/find-hypertable-candidates/ (personal) or .claude/skills/find-hypertable-candidates/ (project).

Which AI agents can use find-hypertable-candidates?

Any agent that supports the open Agent Skills format (a folder with a SKILL.md file), including Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, Windsurf, Cline, OpenCode and more.

Is find-hypertable-candidates safe to install?

No partner security audit is available yet. Skills can run commands and scripts, so read the SKILL.md and any bundled files before installing.

Who maintains find-hypertable-candidates?

It is published by timescale in timescale/pg-aiguide. It has 392 recorded installs.

More from timescale/pg-aiguide

View all →

postgres

timescale/pg-aiguide

Use this skill for any PostgreSQL database work — table design, indexing, data types, constraints, extensions (pgvector, PostGIS, TimescaleDB), search, and migrations. Trigger when user asks to: - Explore an existing PostgreSQL database to understand its objects and relationships - Design or modify PostgreSQL tables, schemas, or data models - Choose data types, constraints, indexes, or partitioning strategies - Work with pgvector embeddings, semantic search, or RAG - Set up full-text search, hybrid search, or BM25 ranking - Use PostGIS for spatial/geographic data - Set up TimescaleDB hypertables for time-series data - Migrate tables to hypertables or evaluate migration candidates - Plan or execute safe schema migrations with zero downtime Keywords: PostgreSQL, Postgres, SQL, schema, table design, indexes, constraints, pgvector, PostGIS, TimescaleDB, hypertable, semantic search, hybrid search, BM25, time-series, migration

Data & Databases1.4K installs

pgvector-semantic-search

timescale/pg-aiguide

Use this skill for setting up vector similarity search with pgvector for AI/ML embeddings, RAG applications, or semantic search. Trigger when user asks to: - Store or search vector embeddings in PostgreSQL - Set up semantic search, similarity search, or nearest neighbor search - Create HNSW or IVFFlat indexes for vectors - Implement RAG (Retrieval Augmented Generation) with PostgreSQL - Optimize pgvector performance, recall, or memory usage - Use binary quantization for large vector datasets Keywords: pgvector, embeddings, semantic search, vector similarity, HNSW, IVFFlat, halfvec, cosine distance, nearest neighbor, RAG, LLM, AI search Covers: halfvec storage, HNSW index configuration (m, efconstruction, efsearch), quantization strategies, filtered search, bulk loading, and performance tuning.

AI & Agents943 installs

design-postgres-tables

timescale/pg-aiguide

Use this skill for general PostgreSQL table design. Trigger when user asks to: - Design PostgreSQL tables, schemas, or data models when creating new tables and when modifying existing ones. - Choose data types, constraints, or indexes for PostgreSQL - Create user tables, order tables, reference tables, or JSONB schemas - Understand PostgreSQL best practices for normalization, constraints, or indexing - Design update-heavy, upsert-heavy, or OLTP-style tables Keywords: PostgreSQL schema, table design, data types, PRIMARY KEY, FOREIGN KEY, indexes, B-tree, GIN, JSONB, constraints, normalization, identity columns, partitioning, row-level security Comprehensive reference covering data types, indexing strategies, constraints, JSONB patterns, partitioning, and PostgreSQL-specific best practices.

Data & Databases615 installs

setup-timescaledb-hypertables

timescale/pg-aiguide

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 design SQL schemas/tables AND Timescale/TimescaleDB/TigerData/Tiger Cloud is available - Set up hypertables, compression, retention policies, or continuous aggregates - Configure partition columns, segmentby, orderby, or chunk intervals - Optimize time-series database performance or storage - Create tables for sensors, metrics, telemetry, events, or transaction logs Keywords: CREATE TABLE, hypertable, Timescale, TimescaleDB, time-series, IoT, metrics, sensor data, compression policy, continuous aggregates, columnstore, retention policy, chunk interval, segmentby, orderby Step-by-step instructions for hypertable creation, column selection, compression policies, retention, continuous aggregates, and index

Data & Databases509 installs

postgres-hybrid-text-search

timescale/pg-aiguide

Open the skill to read its SKILL.md instructions.

Data & Databases373 installs

migrate-postgres-tables-to-hypertables

timescale/pg-aiguide

Open the skill to read its SKILL.md instructions.

Data & Databases357 installs

Popular data & databases skills

View category →

supabase-postgres-best-practices

supabase/agent-skills

Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pgcron, pgmq), vector/semantic search (pgvector), and restoring dumps (pgrestore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query.

OfficialData & Databases462.2K installs

prisma-client-api

prisma/skills

Prisma Client API reference covering model queries, filters, operators, and client methods. Use when writing database queries, using CRUD operations, filtering data, or configuring Prisma Client. Triggers on "prisma query", "findMany", "create", "update", "delete", "$transaction".

OfficialData & Databases393.5K installs

prisma-database-setup

prisma/skills

Deprecated compatibility name for prisma-orm-setup. Use only when a user or existing integration explicitly invokes prisma-database-setup; otherwise select prisma-orm-setup.

OfficialData & Databases392.3K installs

prisma-cli

prisma/skills

Prisma ORM 7 CLI reference for existing Prisma 7 applications or explicitly selected Prisma 7 workflows. Covers prisma init, generate, migrate, db, dev, studio, and mcp. For setup, connection repair, or Prisma 8, use prisma-orm-setup; not the Prisma Platform CLI.

OfficialData & Databases390.8K installs

prisma-postgres

prisma/skills

Deprecated compatibility name for prisma-postgres-setup. Use only when a user or existing integration explicitly invokes prisma-postgres; otherwise select prisma-postgres-setup.

OfficialData & Databases384.4K installs

prisma-driver-adapter-implementation

prisma/skills

Required reference for Prisma ORM 7 SQL driver adapter work. Use when implementing or modifying adapters, adding database drivers, or touching SqlDriverAdapter, Transaction, savepoint, result mapping, or DriverAdapterError behavior. Covers current transaction lifecycle, optional savepoint hooks, original database-error preservation, and verification.

OfficialData & Databases378.9K installs

find-hypertable-candidates

Install

Install

Describe the task

Review the result

SKILL.md

PostgreSQL Hypertable Candidate Analysis

TimescaleDB Benefits

Step 1: Database Schema Analysis

Option A: From Database Connection

Table statistics and size

Index patterns

Query patterns (if pg_stat_statements available)

Constraints

Option B: From Code Analysis

✅ GOOD Patterns

❌ POOR Patterns

Schema Indicators

Special Case: ID-Based Tables

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

Time-Series Characteristics (5+ points needed)

Scale & Performance (3+ points recommended)

Data Patterns (bonus)

Common Patterns

✅ GOOD Candidates

❌ POOR Candidates

Analysis Output Requirements

FAQ

More from timescale/pg-aiguide

Popular data & databases skills