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.