find-hypertable-candidates

1# PostgreSQL Hypertable Candidate Analysis
2 
3Identify tables that would benefit from TimescaleDB hypertable conversion. After identification, use the companion "migrate-postgres-tables-to-hypertables" skill for configuration and migration.
4 
5## TimescaleDB Benefits
6 
7**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).
8 
9**Best for insert-heavy patterns:**
10 
11- Time-series data (sensors, metrics, monitoring)
12- Event logs (user events, audit trails, application logs)
13- Transaction records (orders, payments, financial)
14- Sequential data (auto-incrementing IDs with timestamps)
15- Append-only datasets (immutable records, historical)
16 
17**Requirements:** Large volumes (1M+ rows), time-based queries, infrequent updates
18 
19## Step 1: Database Schema Analysis
20 
21### Option A: From Database Connection
22 
23#### Table statistics and size
24 
25```sql
26-- Get all tables with row counts and insert/update patterns
27WITH table_stats AS (
28    SELECT
29        schemaname, tablename,
30        n_tup_ins as total_inserts,
31        n_tup_upd as total_updates,
32        n_tup_del as total_deletes,
33        n_live_tup as live_rows,
34        n_dead_tup as dead_rows
35    FROM pg_stat_user_tables
36),
37table_sizes AS (
38    SELECT
39        schemaname, tablename,
40…