OverviewHistoryStatsSecurity
npx skills add ...
Documentation
SKILL.md
npx skills add timescale/pg-aiguide --skill find-hypertable-candidates
Use this skill to analyze an existing PostgreSQL database and identify which tables should be converted to Timescale/TimescaleDB hypertables.
npx skills add timescale/pg-aiguide --skill find-hypertable-candidates
Identify tables that would benefit from TimescaleDB hypertable conversion. After identification, use the companion "migrate-postgres-tables-to-hypertables" skill for configuration and migration.
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:
Requirements: Large volumes (1M+ rows), time-based queries, infrequent updates
Look for:
Look for:
✅ 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 = ...)
Compatibility:
✅ GOOD:
❌ POOR:
Sequential ID tables can be candidates if:
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.
✅ Event/Log Tables (user_events, audit_logs)
✅ Sensor/IoT Data (sensor_readings, telemetry)
✅ Financial/Trading (stock_prices, transactions)
✅ System Metrics (monitoring_data)
❌ Reference Tables (countries, categories)
❌ User Profiles (users, accounts)
❌ Settings/Config (user_settings)
For each candidate table provide:
Focus on insert-heavy patterns with time-based or sequential access. Tables scoring 8+ points are strong candidates for conversion.
-- Identify common query dimensions
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY tablename, indexname;-- 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;-- Check migration compatibility
SELECT conname, contype, pg_get_constraintdef(oid) as definition
FROM pg_constraint
WHERE conrelid = 'your_table_name'::regclass;# 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;# 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;CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY, -- Can partition by ID
user_id BIGINT,
created_at TIMESTAMPTZ DEFAULT NOW() -- For sparse indexes
);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_timeCREATE 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 humidityCREATE 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 volumeCREATE 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_usageCREATE TABLE countries (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
code CHAR(2)
);
-- Static data, no time componentCREATE 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)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)