migrate-postgres-tables-to-hypertables

1# PostgreSQL to TimescaleDB Hypertable Migration
2 
3Migrate identified PostgreSQL tables to TimescaleDB hypertables with optimal configuration, migration planning and validation.
4 
5**Prerequisites**: Tables already identified as hypertable candidates (use companion "find-hypertable-candidates" skill if needed).
6 
7## Step 1: Optimal Configuration
8 
9### Partition Column Selection
10 
11```sql
12-- Find potential partition columns
13SELECT column_name, data_type, is_nullable
14FROM information_schema.columns
15WHERE table_name = 'your_table_name'
16  AND data_type IN ('timestamp', 'timestamptz', 'bigint', 'integer', 'date')
17ORDER BY ordinal_position;
18```
19 
20**Requirements:** Time-based (TIMESTAMP/TIMESTAMPTZ/DATE) or sequential integer (INT/BIGINT)
21 
22Should represent when the event actually occurred or sequential ordering.
23 
24**Common choices:**
25 
26- `timestamp`, `created_at`, `event_time` - when event occurred
27- `id`, `sequence_number` - auto-increment (for sequential data without timestamps)
28- `ingested_at` - less ideal, only if primary query dimension
29- `updated_at` - AVOID (records updated out of order, breaks chunk distribution) unless primary query dimension
30 
31#### Special Case: table with BOTH ID AND Timestamp
32 
33When table has sequential ID (PK) AND timestamp that correlate:
34 
35```sql
36-- Partition by ID, enable minmax sparse indexes on timestamp
37SELECT create_hypertable('orders', 'id', chunk_time_interval => 1000000);
38ALTER TABLE orders SET (
39    timescaledb.sparse_index = 'minmax(created_at),...'
40);
41…